Create new table record based on Form value

[stacynh]

Hello,

I have a Form that collects information for table tblRC. Table tblRC has a primary key RC_ID.

Sometimes, a record in tblRC should have a corresponding record in tblBC. In this case, the data enterer should click a button, which would create a new record in tblBC with primary key ID_Match (number field) equal to RC_ID (auto number field).

I have tried many iterations, but I am having trouble with syntax errors in the following statement. I have tried docmd.runSQL, brackets, single quotes, double quotes, etc, but can't seem to get this statement to work correctly. I am experienced with Excel VBA, but new to Access VBA. Any help would be appreciated! Thank you!



CurrentDb.Execute "INSERT INTO tblBC ([ID_Match]) VALUES (me.RC_ID)"

[pbaldy]

You would need to concatenate the form value:

CurrentDb.Execute "INSERT INTO tblBC ([ID_Match]) VALUES (" & me.RC_ID & ")"

Most of us would use a variable for the SQL to make debugging easier, though this is pretty simple.

[ranman256]

you cant put the variable in quotes so:
execute "INSERT INTO tblBC ([ID_Match]) VALUES (" & me.RC_ID & ")"

[stacynh]

Thank you for your quick response! I am still receiving the error "Run-Time error 3134: Syntax Error." I am wondering if there is an issue with the quotes because it is a numeric value instead of text? Or, possibly I need to chose a different primary key in tblBC?

[pbaldy]

Try using a variable so you can review/test the SQL:

http://www.baldyweb.com/ImmediateWindow.htm