Need help with SQL Insert of Variable

P

Paul B.

I have the following code which pull a table record number which I need to
insert into another table along with other values from the form. When I click
save, I get a pop-up asking for the value of mRecordID. How do I correct this
so it uses the variable defined?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

If Unit1 = True Then
DoCmd.RunSQL "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
EventNumber, Form2, CAMT, AEDDL) VALUES(mRecordID,
[UnitNumber],_[EventNumber], [Form2], [CAMT], [AEDDL])"
End If


Thanks in advance.
 
K

Ken Sheridan

You need to concatenate the values of the variables into the string
expression, e.g.

Dim strSQL As String

strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber," & _
"EventNumber, Form2, CAMT, AEDDL) VALUES(" & _
mRecordID & "," & [UnitNumber] & "," & [EventNumber] & "," & _
[Form2] & "," & [CAMT] & "," & [AEDDL] & ")"

DoCmd.RunSQL strSQL

If any of the values are text rather than a numeric data type you need to
include quotes in the string expression to delimit them. Say CAMT is text
for instance you'd have:

[Form2] & ",""" & [CAMT] & """," & [AEDDL] & ")"

The doubled quotes are interpreted as a literal quotes character.

If any are date/time values you need to delimit them with the # character
and ensure that the value is in US or an otherwise internationally
unambiguous format, e.g. taking CAMT as an example again:

[Form2] & ",#" & Format([CAMT],"mm/dd/yyyy hh:nn:ss" & #"," & [AEDDL] &
")"


Be careful, however, that you are not introducing redundancy by duplicating
values from non-key columns in one table in another table.

Ken Sheridan
Stafford, England
 
P

Paul B.

Thanks for the info Ken,

I got it working, however, if one of the fields (textbox) is blank, I get a
violation error when I run the SQL Insert. How do I avoid this when a textbox
has nothing in it?

Thanks again

Ken Sheridan said:
You need to concatenate the values of the variables into the string
expression, e.g.

Dim strSQL As String

strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber," & _
"EventNumber, Form2, CAMT, AEDDL) VALUES(" & _
mRecordID & "," & [UnitNumber] & "," & [EventNumber] & "," & _
[Form2] & "," & [CAMT] & "," & [AEDDL] & ")"

DoCmd.RunSQL strSQL

If any of the values are text rather than a numeric data type you need to
include quotes in the string expression to delimit them. Say CAMT is text
for instance you'd have:

[Form2] & ",""" & [CAMT] & """," & [AEDDL] & ")"

The doubled quotes are interpreted as a literal quotes character.

If any are date/time values you need to delimit them with the # character
and ensure that the value is in US or an otherwise internationally
unambiguous format, e.g. taking CAMT as an example again:

[Form2] & ",#" & Format([CAMT],"mm/dd/yyyy hh:nn:ss" & #"," & [AEDDL] &
")"


Be careful, however, that you are not introducing redundancy by duplicating
values from non-key columns in one table in another table.

Ken Sheridan
Stafford, England

Paul B. said:
I have the following code which pull a table record number which I need to
insert into another table along with other values from the form. When I click
save, I get a pop-up asking for the value of mRecordID. How do I correct this
so it uses the variable defined?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

If Unit1 = True Then
DoCmd.RunSQL "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
EventNumber, Form2, CAMT, AEDDL) VALUES(mRecordID,
[UnitNumber],_[EventNumber], [Form2], [CAMT], [AEDDL])"
End If


Thanks in advance.
Click to expand...
Click to expand...
 
K

Ken Sheridan

You can use the Nz function to return another value in place of a Null, so to
return a zero if a field is Null use:

Nz(The FieldName,0)

To return a zero-length string use:

Nz(The FieldName,"")

Ken Sheridan
Stafford, England

Paul B. said:
Thanks for the info Ken,

I got it working, however, if one of the fields (textbox) is blank, I get a
violation error when I run the SQL Insert. How do I avoid this when a textbox
has nothing in it?

Thanks again

Ken Sheridan said:
You need to concatenate the values of the variables into the string
expression, e.g.

Dim strSQL As String

strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber," & _
"EventNumber, Form2, CAMT, AEDDL) VALUES(" & _
mRecordID & "," & [UnitNumber] & "," & [EventNumber] & "," & _
[Form2] & "," & [CAMT] & "," & [AEDDL] & ")"

DoCmd.RunSQL strSQL

If any of the values are text rather than a numeric data type you need to
include quotes in the string expression to delimit them. Say CAMT is text
for instance you'd have:

[Form2] & ",""" & [CAMT] & """," & [AEDDL] & ")"

The doubled quotes are interpreted as a literal quotes character.

If any are date/time values you need to delimit them with the # character
and ensure that the value is in US or an otherwise internationally
unambiguous format, e.g. taking CAMT as an example again:

[Form2] & ",#" & Format([CAMT],"mm/dd/yyyy hh:nn:ss" & #"," & [AEDDL] &
")"


Be careful, however, that you are not introducing redundancy by duplicating
values from non-key columns in one table in another table.

Ken Sheridan
Stafford, England

Paul B. said:
I have the following code which pull a table record number which I need to
insert into another table along with other values from the form. When I click
save, I get a pop-up asking for the value of mRecordID. How do I correct this
so it uses the variable defined?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

If Unit1 = True Then
DoCmd.RunSQL "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
EventNumber, Form2, CAMT, AEDDL) VALUES(mRecordID,
[UnitNumber],_[EventNumber], [Form2], [CAMT], [AEDDL])"
End If


Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
P

Paul B.

Thanks Ken,

I am still learning as I go, can you provide a little detail as to how to
incorporate your suggestion into my code?

Cheers


Ken Sheridan said:
You can use the Nz function to return another value in place of a Null, so to
return a zero if a field is Null use:

Nz(The FieldName,0)

To return a zero-length string use:

Nz(The FieldName,"")

Ken Sheridan
Stafford, England

Paul B. said:
Thanks for the info Ken,

I got it working, however, if one of the fields (textbox) is blank, I get a
violation error when I run the SQL Insert. How do I avoid this when a textbox
has nothing in it?

Thanks again

Ken Sheridan said:
You need to concatenate the values of the variables into the string
expression, e.g.

Dim strSQL As String

strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber," & _
"EventNumber, Form2, CAMT, AEDDL) VALUES(" & _
mRecordID & "," & [UnitNumber] & "," & [EventNumber] & "," & _
[Form2] & "," & [CAMT] & "," & [AEDDL] & ")"

DoCmd.RunSQL strSQL

If any of the values are text rather than a numeric data type you need to
include quotes in the string expression to delimit them. Say CAMT is text
for instance you'd have:

[Form2] & ",""" & [CAMT] & """," & [AEDDL] & ")"

The doubled quotes are interpreted as a literal quotes character.

If any are date/time values you need to delimit them with the # character
and ensure that the value is in US or an otherwise internationally
unambiguous format, e.g. taking CAMT as an example again:

[Form2] & ",#" & Format([CAMT],"mm/dd/yyyy hh:nn:ss" & #"," & [AEDDL] &
")"


Be careful, however, that you are not introducing redundancy by duplicating
values from non-key columns in one table in another table.

Ken Sheridan
Stafford, England

:

I have the following code which pull a table record number which I need to
insert into another table along with other values from the form. When I click
save, I get a pop-up asking for the value of mRecordID. How do I correct this
so it uses the variable defined?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Dim mRecordID As Long
mRecordID = DMax("RecordNumber", "tblEpistry")
MsgBox ("Record #" & mRecordID)

If Unit1 = True Then
DoCmd.RunSQL "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber,_
EventNumber, Form2, CAMT, AEDDL) VALUES(mRecordID,
[UnitNumber],_[EventNumber], [Form2], [CAMT], [AEDDL])"
End If


Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Say it’s Form2 which could be Null and it’s a number data type so you want to
insert a zero in place of any Nulls the code would be:

strSQL = "INSERT INTO tblUnits(EpistryTableRecordNumber, UnitNumber," & _
"EventNumber, Form2, CAMT, AEDDL) VALUES(" & _
mRecordID & "," & [UnitNumber] & "," & [EventNumber] & "," & _
Nz([Form2],0) & "," & [CAMT] & "," & [AEDDL] & ")"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nz function in SQL string 3
Need suggestions on how to do this. 4
DoCmd.RunSQL "INSERT INTO (append) 4
Forcing a disk write to save a record 1
write conflicts 1
DoCmd.RunSQL - Need help! 1
Add new record to master and subform at the same time. 0
ReQuery but stay on selected record - Please Help! 5

Top