Help with strSQL

[rocketD]

Hi All,

I posted a couple days ago about a database I'm working on, where I
have to allow users to add data to lookup tables while they are in a
form without using a bunch of subforms. Someone directed me to this
code
http://www.developerbarn.com/access-database-samples/251-combo-box-add-entry-not-list.html
and I've tried to adapt it, but I don't really know what I'm doing. I
need to be able to add 2 fields to the lookup table, one which is the
new data the user types in (fieldname uww), and the other which is the
areaID # selected in the previous field.
The error I get when I test this in the form is "Run-time error
'3346': # of query values and destination fields are not the same." I
think the problem is in the second line of my strSQL command, but I
don't know how to fix it; what I've tried hasn't worked. Can someone
please look at the code below and make a suggestion?

Thanks,
Dara

Private Sub uww_NotInList(NewData As String, Response As Integer)

Dim ctl As Control
Dim strSQL As String
Set ctl = Me!uww

If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then

Response = acDataErrAdded

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & ", " & Me.area & " ' );"
CurrentDb.Execute strSQL

Else

Response = acDataErrContinue
ctl.Undo
End If

End Sub

 

[Douglas J. Steele]

Your single quotes are incorrect. Your SQL statement will end up looking
something like:

INSERT INTO lkpChargeCode(chargeCode, areaID)
VALUES('value1, value2');

when it needs to be something like

INSERT INTO lkpChargeCode(chargeCode, areaID)
VALUES('value1', 'value2');

(assuming both chargeCode and areaID are text fields)

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & " ', ' " & Me.area & " ' );"

 

[Brendan Reynolds]

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & ", " & Me.area & " ' );"

It looks like you've got a single quote before the first value, and after
the second value, but no single quote after the first value or before the
second value. In other words, if NewData contained the text "one" and area
contained the text "two", your SQL string would end up looking like so ...

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one, two');

.... when it needs to look like this ...

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one', 'two');

So the VBA code needs to be modified to something like this ....

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & "', '" & Me.area & " ' );"

Unless, of course, areaID is a numeric field, in which case you don't want
quotes around that value at all. In that case, the SQL string needs to end
up looking like this ...

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one', 2);

.... so the code needs to look like this ...

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & "', " & Me.area & ");"

Debug.Printing the SQL string often helps when trouble-shooting this kind of
problem ...

strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
strSQL = strSQL & NewData & "', '" & Me.area & " ' );"
Debug.Print strSQL

 

[rocketD]

It looks like you've got a single quote before the first value, and after
the second value, but no single quote after the first value or before the
second value. In other words, if NewData contained the text "one" and area
contained the text "two", your SQL string would end up looking like so ....

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one, two');

... when it needs to look like this ...

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one', 'two');

So the VBA code needs to be modified to something like this ....

 strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
 strSQL = strSQL & NewData & "', '" & Me.area & " ' );"

Unless, of course, areaID is a numeric field, in which case you don't want
quotes around that value at all. In that case, the SQL string needs to end
up looking like this ...

INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES ('one', 2);

... so the code needs to look like this ...

 strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
 strSQL = strSQL & NewData & "', " & Me.area & ");"

Debug.Printing the SQL string often helps when trouble-shooting this kindof
problem ...

 strSQL = "INSERT INTO lkpChargeCode(chargeCode, areaID) VALUES( ' "
 strSQL = strSQL & NewData & "', '" & Me.area & " ' );"
Debug.Print strSQL

Thanks Doug and Brendan, that was very helpful and I got my form
working perfectly.

Another question - I used Debug.Print as you suggested to get the SQL
to come out in the Immediate window, and it worked, but I could only
make it print when I test-entered data into the form. Is there a way
to check it in the VBA window without trying the form? I tried
doing ? Debug.Print strSQL in the Immediate Window, but it wouldn't
work.

 

[Brendan Reynolds]

Another question - I used Debug.Print as you suggested to get the SQL
to come out in the Immediate window, and it worked, but I could only
make it print when I test-entered data into the form. Is there a way
to check it in the VBA window without trying the form? I tried
doing ? Debug.Print strSQL in the Immediate Window, but it wouldn't
work.

That would be just ...

? strSQL

However, the variable strSQL is a local variable declared within the
NotInList procedure, and has no meaning outside of that procedure. It's a
pointer to a memory location. Within the procedure, the value you assign to
the variable gets stored in that memory location. When the procedure ends,
the memory location is released so that it is available for other uses. So
you will not be able to inspect its value when the procedure is not running.

 

[rocketD]

That would be just ...

? strSQL

However, the variable strSQL is a local variable declared within the
NotInList procedure, and has no meaning outside of that procedure. It's a
pointer to a memory location. Within the procedure, the value you assign to
the variable gets stored in that memory location. When the procedure ends,
the memory location is released so that it is available for other uses.  So
you will not be able to inspect its value when the procedure is not running.

Alright. Good to know, thank you for explaining that.

Dara