Full Version: INSERT INTO problem
UtterAccess Forums > Microsoft® Access > Access Forms
Clark Center
br />when I run this code:
CODE
    Dim strSQL As String
    Dim strSQL1 As String
trSQL1 = "INSERT INTO tblBox(BoxNumber) VALUES(" & Me!txtSingleBox1.Value & ")"
strSQL = strSQL1 & "INSERT INTO tblImages(fkBox) VALUES(" & Me!txtSingleBox1.Column(1) & ") WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2) & ";"
CurrentDb.Execute strSQL, dbFailOnError

The first line of code works; the second line of code generates the error. Any help would be greatly appreciated.
theDBguy
How about executing each statement separately:
trSQL1 = "INSERT INTO tblBox(BoxNumber) VALUES(" & Me!txtSingleBox1.Value & ")"
strSQL = "INSERT INTO tblImages(fkBox) VALUES(" & Me!txtSingleBox1.Column(1) & ") WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2) & ";"
CurrentDb.Execute strSQL, dbFailOnError
CurrentDb.Execute strSQL1, dbFailOnError
For if you wanted to also include the BoxNumber in tblImages:
strSQL = "INSERT INTO tblImages(BoxNumber, fkBox) VALUES(" & Me.txtSingleBox1 & ", " & Me.txtSingleBox1.Column(1) & ") WHERE pkImage=" & Me.cboSingleImage.Column(2)"
CurrentDb.Execute strSQL, dbFailOnError
HTH
Clark Center
It makes no difference whether the two SQL statements are run separately or together. I get the same result. The string
CODE
"INSERT INTO tblImages(fkBox) VALUES(" & Me!txtSingleBox1.Column(1) & ") WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2) & ";"
gives the error.
oxes and Images are in a one:many relationship and I haven't put Box in tblImages to avoid bloating the db. Well, ok, I know that the box id and the box number are the same and one takes up no more space than the other. It's the principle of the thing.
vtd
Is [txtSingleBox1] a TextBox Control or a ComboBox Control?
If it is a TextBox then using Column with it is invalid as TextBox does not have the Property "Column".
If it is a ComboBox, the use of the prefix "txt" is rather out of character and doesn't follow any naming convention.
Clark Center
"Of course!!!!!", I said , splapping myself on the forehead.
o I changed tblBox to a lookup table and populated it and changed txtSingleBox1 from a text box to a combo. The new code is
CODE
strSQL = "INSERT INTO tblImages(fkBox) VALUES(" & Me!cboSingleBox1.Column(1) & ") WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2) & ";"
CurrentDb.Execute strSQL, dbFailOnError
which gives another erro
ARRRRGGGHHHHH! The only thing that changed in the code was txtSingleBox1.Column(1) to cboSingleBox1.Column(1).
What have I done --or not done?
theDBguy
Not sure we can definitely tell you what you "have done or not done." Could you post a small copy of your db so we can see the problem?
Clark Center
Before I resort to reducing the db to 500k, let me try this.
I have a database to keep track of photographs which includes these two tables, tblImages and tlkpBox.
blImages
pkImage
fkBox
and other fields
tlkpBox
pkBox
BoxNumber
In order to record the number of the box that an image is stored in, I have a form, frmAssignLocation, which has two controls, cboSingleImage and cboSingleBox1. Each control displays a list, one of image numbers and one of box numbers. The user selects the image number and the box number, and clicks on a command button which runs the code which should insert the pkBox number associated with cboSingleBox1 into tblImages.fkBox. Looks simple to me, but something in the code isn't right, and I haven't figured out what it is.
vtd
Why do you have a WHERE Clause ... when you use the "INSERT INTO ... VALUES ..."
The 2 correct syntaxes are:
"INSERT INTO ... VALUES {Explicit value list}"
or
"INSERT INTO ... SELECT ... FROM {DataSource} WHERE ... (if required)"
Clark Center
Sheer ignorance. But I'm very glad to be better informed.
However, after thinking it through, I believe I should be using an UPDATE statement. (I'm adding a value into an existing record.) I've tried this
CODE
strSQL = "UPDATE tblImages SET fkBox=(" & Me!cboSingleBox1.Column(1) & ") WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2) & ";"
but it doesn't add anything to the target field and gives no error. Do you see what I'm missing?
vtd
1. Are fkBox and pkImage both numeric Fields?
. Are you aware that the Indices used with Column zero-based? Column(1) actually refers to second column and Column(2) refers to the third column.
3. Try a Select Query with SQL String like:
CODE
SELECT tblImages.*
FROM tblImages
WHERE tblImages.pkImage = " & Me!cboSingleImage.Column(2)

and see if this returns any row.
P.S. You don't need to use semi-colon at the end of the SQL String.
Clark Center
1. Are fkBox and pkImage both numeric Fields? ----- Yes
2. Are you aware that the Indices used with Column zero-based? Column(1) actually refers to second column and Column(2) refers to the third column. ------ I'm embarassed. I thought I'd double- and triple-checked that. Works fine now.
Thanks once again.
vtd
You're welcome ... Glad we could help ...
theDBguy
Once again, thanks for the assist Van! thumbup.gif
vtd
NP ... We all try to chip in for a good cause ...
Cheers
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.