Insert Into Not passing the values 3

[cariengon]

I'm running some code to create a new record in a table based on values in another table/form. When the user clicks the button to open the form, I'm running the below code to create the new record. The new record is being created, however, the values are not populating into the two fields I've designated.

I need the same number to populate in two fields in table 1002 based on fields in the main table 1001, which is referenced in the Me.

Dim recordnum As Long
    If IsNull(Me.recordnum) = True Then
       Me.fld_Created_User = Me.txt_Current_User
       recordnum = Me.fld_ACC_Master_Record_No

       DoCmd.RunSQL ("INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES (recordnum, recordnum);")
     End If

You're probably wondering why I put the same number in two different fields - it's because the recordnum is an old tracking number that I need to continue to use.

Thanks for any help!
Carie

 

[RoyVidar]

You'll need to concatinate the values of the variables into the string, not the variable names:

[tt]DoCmd.RunSQL ("INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES (" & recordnum & ", " & recordnum & ");")[/tt]

Roy-Vidar

 

[RickSpr]

You need:

        DoCmd.RunSQL ("INSERT INTO [1002_Bankruptcy_Schg_tbl] (fld_Acc_Master_Record_No, recordnum) VALUES ([red]" & recordnum & ", " & recordnum & "[/red]);")

The problem is that your string contains the literal "recordnum", instead of the value of the variable named recordnum. When the SQL executes, it tries to insert the string value "recordnum" into the numeric fields, which of course doesn't work.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[randy700]

Change this...
DoCmd.RunSQL ("INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES (recordnum, recordnum);")

to this...
DoCmd.RunSQL "INSERT INTO 1002_Bankruptcy_SchG_tbl(fld_ACC_Master_Record_No, recordnum) " & _
"VALUES(" & recordnum & ", " & recordnum & ")"



Randy

 

[cariengon]

Thank you!!

Some day I'm going to have the SYNTAX of this stuff nailed... But until then - thanks so much for your help!

Stars all around!!!

Carie

 

[RickSpr]

Here's an easy technique to get the syntax right the first time, every time.

1. Start with an SQL statement using sample data:
INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES ([red]500[/red], [red]500[/red]);

2. Replace each piece of sample data with [green]" & ? & "[/green] (including the quotes):
INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES ([red]" & ? & "[/red], [red]" & ? & "[/red]);

3. Replace each "?" with the name of a variable or control, or a VBA expression:
INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES (" & [red]recordnum[/red] & ", " & [red]recordnum[/red] & ");

4. Put quotes around the whole thing:
[red]"[/red]INSERT INTO [1002_Bankruptcy_SchG_tbl] (fld_ACC_Master_Record_No, recordnum) VALUES (" & recordnum & ", " & recordnum & ");[red]"[/red]


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein