INSERT INTO doesn't work (1 Viewer)

[justme]

Hi,

Can anyone please tell me why the following code isn't working?

There is data in a text box, txtRq, on frmMain. There is also a subform on frmMain, frmSubSnt. I would like the text in the txtRq inserted into the table the subform is based on, tblSubSnt. None of my searches resulted in anything working.
The code is executed by clicking a command button on a pop up form called frmPopSnt.
I don't get any errors, nothing is inserted into tblSubSnt.

Dim strCpRq as String
strCpRq = Forms!frmMain!txtRq
strSQL = "INSERT INTO tblSubSnt (SntRq) VALUES('" & strCpRq & "')"

MsgBox strSQL returns
INSERT INTO tblSubSnt (SntRq) VALUES('0812-01')

txtRq is automatically calculated when the user adds a new record to frmMain. Also, I need to allow duplicates.

Any help is greatly appreciated.

Thank you.
v/r,
janet

 

[pbaldy]

You have to execute the SQL:

CurrentDb.Execute strSQL

 

[justme]

Thank you PBaldy,

The code I have now is

strCpRq = Forms!frmMain!Rq
CurrentDb.Execute strSQL = "INSERT INTO tblSubSnt (SntRq) VALUES('" & strCpRq & "')"

I get a Run-time error '3078':
The Microsoft Jet database engine cannot find the input table or query "False". Make sure it exists and that its name is spelled correctly.

Why is it looking for "False"?? How can i resolve this?

Thank you for your help. I really appreciate it.

v/r,
janet

 

[pbaldy]

No, I meant to add that line after the line you already had setting the variable.

 

[justme]

Thank you again PBaldy,

I'm sorry to be so slow to understand. The code I have now is

strCpRq = Forms!frmMain!Rq
CurrentDb.Execute strSQL
strSQL = "INSERT INTO tblSubSnt (SntRq) VALUES('" & strCpRq & "')"

I get a Run-time error '3078': The Microsoft Jet database engine cannot find the input table or query ". Make sure it exists and that its name is spelled correctly.

please help.

Thank you.
v/r,
janet

 

[pbaldy]

You're executing the SQL before you create it. Try this:

strCpRq = Forms!frmMain!Rq
strSQL = "INSERT INTO tblSubSnt (SntRq) VALUES('" & strCpRq & "')"
CurrentDb.Execute strSQL

 

[justme]

I now have the code the way you show it (silly me for not realizing that).

strCpRq = Forms!frmMain!Rq
strSQL = "INSERT INTO tblSubSnt (SntRq) VALUES('" & strCpRq & "')"
CurrentDb.Execute strSQL

I don't get any errors but still, nothing gets inserted into the table.
I just don't know what i am missing. It doesn't seem like it should be this difficult.

MsgBox strCpRq returns the correct string 0812-01 and
MsgBox strSQL returns INSERT INTO tblSubSnt (SntRq) VALUES('0812-01')

I started a new database and imported all objects from the old one but the code still doesn't insert anything into tblSubSnt.

Any ideas?

thank you. I really appreciate your kind, patient, and expert help.

v/r,
janet

 

[pbaldy]

Are there other required fields in the table? Try this, which I should have suggested in the first place:

CurrentDb.Execute strSQL, dbFailOnError

 

[HAMMAMABUARQOUB]

the insert into SQL as I tried shoud be fisrt tested in an append query with the parameters you have,, execute it,, if it's ok, open the SQL view of the query.. copy the code and past it as string in your command..
other cases why it fails, you may misplaced a qoute "" or a single qoute ' .. the single is used to enclose variables from the form or ouside value, and the double is used to enclosed the final SQL,, so you should arrange them well

 

[justme]

Thank you PBaldy and Hammamabuarqoub,

Yes, there are other required fields in the table.
When i added your suggestion so i have
Currentdb.Execute strSQL, dbFailOnError
I get a Run-time error '3201': You cannot add or change in a record because a related record is required in 'tblMain'.

I followed the instructions from http://www.databasedev.co.uk/append_query.html
to make my first append query.
In the query I have two tables, tblMain and tblSubSnt. tblMain PK is MainID, and has a one to many relationship with the FK, MainID, in tblSubSnt. Then I clicked on Rq (in tblMain) and drug the cursor to SntRq in tblSubSnt. Is that right?

below the tables (in design view) I have
Field: Rq
Table tblMain

Append To SntRq

Is that right?

The code from the SQL view shows

INSERT INTO tblSubSnt(SntRq)
SELECT tblMain.Rq
FROM tblMain INNER JOIN tblSubSnt ON (tblMain.MainID = tblSubSnt.MainID) AND (tblMain.Rq = tblSubSnt.SntRq);

When I clicked on Query and Run, I get a message that says "you are about to append 0 row(s). I'm sure this isn't right, but I put the sql statement into my code. That results in an error message that there is a missing operator. I have:

strSQL = "INSERT INTO tblSubSnt ( SntRq ) SELECT tblMain.Rq" & _
"FROM tblMain INNER JOIN tblSubSnt ON (tblMain.MainID = tblSubSnt.MainID) AND (tblMain.Rq = tblSubSnt.SntRq);"

I'm really stuck here. How do I fix this please?
Your help is greatly appreciated.

Thank you,
v/r,
janet

I think the quotes i had were fine. single double at the beginning, then double, single at the end.

 

[pbaldy]

To test the SQL, delete the first line (INSERT INTO...) and see what the query returns. In any case, that SQL doesn't populate the MainID, so wouldn't be able to append (if I have the situation right).

Your problem in VBA is probably due to the lack of a space between tblMain.Rq and FROM, a common mistake when learning how to put SQL into VBA.

 

[justme]

Thank you guys so much for your help and patience.

I got it working, thanks very much to you. Syntax is tricky for me.

I appreciate your help and time.

kind regards,
v/r,
janet