Extra bracket

[AHopper]

On a form "OmegaTenForm" I have a combo box "NewNumber"
that has nine columns. The nineth column is "GoodUnique"
(the auto number for records)and is set to 0 width. I want
to change a record in table "OmegaTenBatch"
field "NewNumberUsed" (boolean) to True (-1)using the
criteria "GoodUnique"

Dim SQL As String
DoCmd.SetWarnings False
SQL = "UPDATE OmegaTenBatch SET
OmegaTenBatch.NewNumberUsed = -1 " & "WHERE
(OmegaTenBatch.GoodUnique =" & Me.NewNumber.Column(9)
& ");"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

When I use the above I get an error message inidcating I
have an extra bracket.

Thanks in advance for your help.

Allan

 

[Dirk Goldgar]

On a form "OmegaTenForm" I have a combo box "NewNumber"
that has nine columns. The nineth column is "GoodUnique"
(the auto number for records)and is set to 0 width. I want
to change a record in table "OmegaTenBatch"
field "NewNumberUsed" (boolean) to True (-1)using the
criteria "GoodUnique"

Dim SQL As String
DoCmd.SetWarnings False
SQL = "UPDATE OmegaTenBatch SET
OmegaTenBatch.NewNumberUsed = -1 " & "WHERE
(OmegaTenBatch.GoodUnique =" & Me.NewNumber.Column(9)
& ");"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

When I use the above I get an error message inidcating I
have an extra bracket.

Thanks in advance for your help.

Allan

Columns in a combo box are numbered starting from 0, so the ninth column
is

Me.NewNumber.Column(8)

 

[AHopper]

Dirk
Thank you for your help with the combo box numbering. The
right criteria is now being used, however, I am still
getting the error stating I have extra bracket.

 

[Dirk Goldgar]

Dirk
Thank you for your help with the combo box numbering. The
right criteria is now being used, however, I am still
getting the error stating I have extra bracket.

I don't see where the error could be coming from, but set a breakpoint
on the line

and report what the value of the SQL variable is at that point, just
before it is executed. This will also help verify that the error is
being raised on the call to RunSQL, and not elsewhere.

 

[AHopper]

Dirk, I have an appointment and won't be back until
tomorrow morning.
I am not familiar with the procedure you are suggesting.
But I would like to learn.

Thanks
Allan

 

[AHopper]

Dirk, the following has worked.
SQL = "UPDATE OmegaTenBatch SET
OmegaTenBatch.NewNumberUsed = -1 " & "WHERE
(OmegaTenBatch.GoodUnique = " & Me.NewNumber.Column(8)
& ");"
The only difference I can see is the space after the =
(OmegaTenBatch.GoodUnique = "
Thank you again for your help

Allan

 

[Dirk Goldgar]

Dirk, the following has worked.
SQL = "UPDATE OmegaTenBatch SET
OmegaTenBatch.NewNumberUsed = -1 " & "WHERE
(OmegaTenBatch.GoodUnique = " & Me.NewNumber.Column(8)
& ");"
The only difference I can see is the space after the =
(OmegaTenBatch.GoodUnique = "
Thank you again for your help

How odd. I can't think why that would make a difference. But who am I
to argue with success? I'm glad you got it working.