Error with combobox.column(1) in DoCmd.runSQL

[Barry A&P]

i want to update locations in both the part numbers table and serialnumbers
table with a single form and action

I have a form with two unbound combo boxes one is locationnamecombo it has
two columns location id set to 0" and location name set to 3"

the second combo is SNIDCombo with two columns serialnumberid set to 0" and
partnumberid set to 2"

the first SQL that updates the serialnumber location works as expected
(maybe because combo is bound to column 1)
DoCmd.RunSQL ("UPDATE T_SerialNumbers SET T_SerialNumbers.locationID =
forms!F_setinventory.LocationnameCombo WHERE
(((T_SerialNumbers.SerialNumberID)=forms!F_setinventory.SNIDCombo))"),
dbFailOnError

The second sql updates the Partnumber table location based on the
SNIDcombo's Column(1) in its criteria but i get an error
Undefined function in 'forms!F_setinventory.SNIDCombo.column' in expression
i also tried Me.SNIDCombo.column(1)

DoCmd.RunSQL ("UPDATE T_PartNumbers SET T_PartNumbers.homelocationID =
forms!F_setinventory.LocationnameCombo WHERE
(((T_partNumbers.partNumberID)=forms!F_setinventory.SNIDCombo.column(1)))"),
dbFailOnError

Any help would be greatly appreciated

Thanks
Barry

 

[Dirk Goldgar]

i want to update locations in both the part numbers table and serialnumbers
table with a single form and action

I have a form with two unbound combo boxes one is locationnamecombo it has
two columns location id set to 0" and location name set to 3"

the second combo is SNIDCombo with two columns serialnumberid set to 0"
and
partnumberid set to 2"

the first SQL that updates the serialnumber location works as expected
(maybe because combo is bound to column 1)
DoCmd.RunSQL ("UPDATE T_SerialNumbers SET T_SerialNumbers.locationID =
forms!F_setinventory.LocationnameCombo WHERE
(((T_SerialNumbers.SerialNumberID)=forms!F_setinventory.SNIDCombo))"),
dbFailOnError

The second sql updates the Partnumber table location based on the
SNIDcombo's Column(1) in its criteria but i get an error
Undefined function in 'forms!F_setinventory.SNIDCombo.column' in
expression
i also tried Me.SNIDCombo.column(1)

DoCmd.RunSQL ("UPDATE T_PartNumbers SET T_PartNumbers.homelocationID =
forms!F_setinventory.LocationnameCombo WHERE
(((T_partNumbers.partNumberID)=forms!F_setinventory.SNIDCombo.column(1)))"),
dbFailOnError

Try building the value from the combo box's second column -- .Column(1) --
directly into the SQL string you will execute. Like this:

DoCmd.RunSQL _
"UPDATE T_PartNumbers SET homelocationID = " & _
Me.LocationnameCombo & _
" WHERE partNumberID = " & Me.SNIDCombo.Column(1)

I'm assuming, for that suggested SQL, that the homelocationID and
partNumberID fields are numeric, not text. If they are text, then the SQL
string has to be amended to include quotes around the values that
concatenated in from the combo boxes.

Note: the dbFailOnError constant does not apply to the RunSQL method, so I
have removed it. You should do that elsewhere as well, if you are using it
with RunSQL as in your quoted code.

 

[Barry A&P]

Dirk

Your Query was much simpler and most important it worked.. the Dbfail on
error was included with some SQL on another post so i just included it good
to know it doesnt do anything..

i can kind of see what is happening in your SQL but dont quite understand
the charachters " & _ I have concatenated things with & " but what
specifically does the Underscore do? _

I suppose i could search it and get an Over-My-Head explanation.
but if you have time
Thanks so much for your help

Barry

 

[Dirk Goldgar]

i can kind of see what is happening in your SQL but dont quite understand
the charachters " & _ I have concatenated things with & " but what
specifically does the Underscore do? _

The ampersands (&) are for concatenation, as you already know. The
underscore (_) is the VBA line-continuation character. If you have a line
of VBA code that is getting too long for easy reading, or maybe even
exceeding the maximum line length accepted by the editor (if there is such a
maximum), then you can break it onto multiple lines at any place where a
space would be accepted (except in the middle of a quoted string), following
each break with a space and an underscore.

So I took what could have been written as a single line:

DoCmd.RunSQL "UPDATE T_PartNumbers SET homelocationID = " &
Me.LocationnameCombo & " WHERE partNumberID = " & Me.SNIDCombo.Column(1)

.... (except that by the time you read this, it will have been broken onto
multiple lines by the newsreader)
.... and broke it onto multiple lines for clarity (and to keep it from being
broken by the newsreader), ending each continued line with a space and an
underscore:

DoCmd.RunSQL _
"UPDATE T_PartNumbers SET homelocationID = " & _
Me.LocationnameCombo & _
" WHERE partNumberID = " & Me.SNIDCombo.Column(1)

Do you see how that works?

 

[John W. Vinson]

i can kind of see what is happening in your SQL but dont quite understand
the charachters " & _ I have concatenated things with & " but what
specifically does the Underscore do? _

An underscore preceded by a space at the end of a line is a line continuation
character in VBA. That is, the VBA expression

X = _
A + _
B

is identical to the expression

X = A + B

Breaking up long lines is not obligatory in a VBA module, though it does make
it more readable; however posting VBA code to a newsgroup like this one runs
into line length limitations, so it's customary to include the line
continuation characters so you can just copy and paste the code (and modify to
your own object's names etc.)

 

[Barry A&P]

Thank you both for the elaboration

Barry

An underscore preceded by a space at the end of a line is a line continuation
character in VBA. That is, the VBA expression

X = _
A + _
B

is identical to the expression

X = A + B

Breaking up long lines is not obligatory in a VBA module, though it does make
it more readable; however posting VBA code to a newsgroup like this one runs
into line length limitations, so it's customary to include the line
continuation characters so you can just copy and paste the code (and modify to
your own object's names etc.)