Insert statement not updating table?

[Pamela via AccessMonster.com]

Hello,

I have the following statement:

MsgBox [Num]
SQL1 = "INSERT INTO tblContactDateHistory ( RecID, ContactDate) Values (
[Num], [ContactDate])"
MsgBox SQL1
DoCmd.RunSQL SQL1

For some reason, this SQL statement isn't adding [Num] - a field on the form,
to RecID and I don't understand why? It adds the ContactDate just fine.
The table ContactDateHistory id related to another table, with RecID being
the related field. Any thoughts? MsgBox [Num] is returning the correct
value...

 

[Allen Browne]

To find out why, replace the RunSQL line with:
dbEngine(0)(0).Execute SQL1, dbFailOnError

This will generate an error message, and you can then see what's wrong.

 

[Pamela via AccessMonster.com]

Hi & Thanks. Duh. I see I left my brain at home today....the code has been
corrected & now reads:

SQL1 = "INSERT INTO tblContactDateHistory (RecID, ContactDate) Values ("
& [Num] & "," & [ContactDate] & ")"
MsgBox SQL1
DBEngine(0)(0).Execute SQL1, dbFailOnError
'DoCmd.RunSQL SQL1

The SQL statement looks fine when displayed by msgbox but now I get a "Run-
time error 6 overflow" error (before I received too few parameters error
which made me realize I had messed up on the SQL statement). Hmmm...

Thanks for the tip, that's bound to be a useful one as I relearn this stuff.


Any ideas what might be causing the overflow? Still doing research for it on
the net, but Haven't found anything to give me a clue as to what might be
wrong in my case.

Thanks again.

To find out why, replace the RunSQL line with:
dbEngine(0)(0).Execute SQL1, dbFailOnError

This will generate an error message, and you can then see what's wrong.

Hello,

[quoted text clipped - 13 lines]

the related field. Any thoughts? MsgBox [Num] is returning the correct
value...

 

[Pamela via AccessMonster.com]

Figured it out.

Overtime was because I had the field I was inserting into set as an Integer.
Changed it to Long Integer & that fixed that. The date wasn't coming across
correctly because I forgot the quotes around the date.

Thanks again for your help.

Final code was:

SQL1 = "INSERT INTO tblContactDateHistory (RecID, ContactDate) Values ("
& [Num] & "," & """" & [ContactDate] & """" & ")"
MsgBox SQL1
DBEngine(0)(0).Execute SQL1, dbFailOnError
DoCmd.RunSQL SQL1

This worked.

Hi & Thanks. Duh. I see I left my brain at home today....the code has been
corrected & now reads:

SQL1 = "INSERT INTO tblContactDateHistory (RecID, ContactDate) Values ("
& [Num] & "," & [ContactDate] & ")"
MsgBox SQL1
DBEngine(0)(0).Execute SQL1, dbFailOnError
'DoCmd.RunSQL SQL1

The SQL statement looks fine when displayed by msgbox but now I get a "Run-
time error 6 overflow" error (before I received too few parameters error
which made me realize I had messed up on the SQL statement). Hmmm...

Thanks for the tip, that's bound to be a useful one as I relearn this stuff.


Any ideas what might be causing the overflow? Still doing research for it on
the net, but Haven't found anything to give me a clue as to what might be
wrong in my case.

Thanks again.

To find out why, replace the RunSQL line with:
dbEngine(0)(0).Execute SQL1, dbFailOnError

[quoted text clipped - 6 lines]

the related field. Any thoughts? MsgBox [Num] is returning the correct
value...

 

[Pamela via AccessMonster.com]

Meant "Overflow"...yeesh. That's what I get for skipping breakfast...

Figured it out.

Overtime was because I had the field I was inserting into set as an Integer.
Changed it to Long Integer & that fixed that. The date wasn't coming across
correctly because I forgot the quotes around the date.

Thanks again for your help.

Final code was:

SQL1 = "INSERT INTO tblContactDateHistory (RecID, ContactDate) Values ("
& [Num] & "," & """" & [ContactDate] & """" & ")"
MsgBox SQL1
DBEngine(0)(0).Execute SQL1, dbFailOnError
DoCmd.RunSQL SQL1

This worked.

Hi & Thanks. Duh. I see I left my brain at home today....the code has been
corrected & now reads:

[quoted text clipped - 23 lines]

the related field. Any thoughts? MsgBox [Num] is returning the correct
value...

 

[Allen Browne]

That's great. All solved. Time for a toast!

 

[Rick Brandt]

Hello,

I have the following statement:

MsgBox [Num]
SQL1 = "INSERT INTO tblContactDateHistory ( RecID, ContactDate)
Values ( [Num], [ContactDate])"
MsgBox SQL1
DoCmd.RunSQL SQL1

For some reason, this SQL statement isn't adding [Num] - a field on
the form, to RecID and I don't understand why? It adds the
ContactDate just fine. The table ContactDateHistory id related to
another table, with RecID being the related field. Any thoughts?
MsgBox [Num] is returning the correct value...

In VBA code in your form's module [Num] means something. It refers to the
control or field on the form with the name "Num". In a SQL String it is
just 5 characters being passed to the Jet database engine and Jet does not
know anything about your form at the time of execution. (You should be
getting an error or a parameter prompt).

You need to delimit the string so that the string ends up with the values
contained in [Num] and [ContactDate] instead of the reference to them (since
Jet cannot evaluate that reference).

SQL1 = "INSERT INTO tblContactDateHistory ( RecID, ContactDate) " & _
"Values ( " & Me![Num] & ", #" & Me![ContactDate] & "#)"
DoCmd.RunSQL SQL1

 

[Pamela via AccessMonster.com]

Thanks. I didn't know you could do it that way too. I'm used to entering
straight SQL statements and not using them in VBA code...I had realized my
statement was incorrect when msgbox kindly displayed the SQL statement being
executed for me. Just started learning VB & I really appreciate the quality
help I get from this forum.

Hello,

[quoted text clipped - 11 lines]

another table, with RecID being the related field. Any thoughts?
MsgBox [Num] is returning the correct value...

In VBA code in your form's module [Num] means something. It refers to the
control or field on the form with the name "Num". In a SQL String it is
just 5 characters being passed to the Jet database engine and Jet does not
know anything about your form at the time of execution. (You should be
getting an error or a parameter prompt).

You need to delimit the string so that the string ends up with the values
contained in [Num] and [ContactDate] instead of the reference to them (since
Jet cannot evaluate that reference).

SQL1 = "INSERT INTO tblContactDateHistory ( RecID, ContactDate) " & _
"Values ( " & Me![Num] & ", #" & Me![ContactDate] & "#)"
DoCmd.RunSQL SQL1