Insert statement using Docmd.RunSQL won't work

[SarahG]

This may belong in the Queries forum ... but my problem is I have an INSERT statement which is built from the entries the user has selected from the form. It works most of the time, but occasionally it won't run the INSERT properly, but doesn't generate an error message. I've set a breakpoint in the code to ensure the SQL string is being built correctly. When I close the form and open it again it works, or if I paste the INSERT string into a query and run that it works.
Does anyone know of some bug in Access which caused this to happen?

The code is:

DoCmd.RunSQL "insert into tblCrossRef(BookID,SubjectID) select " & nBookID & " as expr1, " & nSubjectID & " as expr2;"

Thanks.

 

[pmessana]

I have never run into that problem, but, I usually break up my select and inserts, you may want to try to do the select in a recordset and then insert inside the loop as you loop through the record set.

example:

-----code-----
Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("SELECT nBookID, nSubjectID FROM table&quot
Do While rst.EOF = False
nBookID = rst.Fields("nBookID&quot
nSubjectID = rst.Fields("nsubjectID&quot

sql = "INSERT INTO tblCrossRef VALUES (" & nBookID & ", " & nSubjectID & &quot"
DoCmd.RunSQL (sql)
rst.MoveNext
Loop

--------end code---------

 

[SarahG]

The nBookID & nSubjectID variables are actually populated from the entries the user has chosen on a text box & list box respectively, not from a SELECT.

nBookID = Forms!frmBook!txtBookID ' a different form
nSubjectID = Me.lstAvailableSubjects.Column(0)

I've confirmed that both of these variables are being populated.

It's a weird one.

 

[pmessana]

Maybe I am missing something, but, why would you not just have your insert written like this?

INSERT INTO tblCrossRef VALUES (" & nBookID & ", " & nSubjectID & &quot

I saw your "select" statement in your insert statement and assumed an imbedded select in an insert.