Update Query Syntax - RTE 424

[T 5]

Hi,



Could someone tell me where I am going wrong with the code below. Have tried numerous tweeks but nothing works.

Dim varSql As String
varSql = "UPDATE tblPriceList SET tblPriceList.SupplierID =" & Me.Combo45.Column(0) _
& "WHERE tblPriceList.ProductID=" & Forms.frmProducts.cboFindProduct.Column(0) & "And" _
& tblPriceList.SupplierID = " Forms.frmProducts.cboFindProduct.Column(4)"

Thanks

[JoeM]

A few things:

1. You need to leave space between your words so they don't run together.
For example, replace "AND" with " AND ".

2. Any Text criteria is going to need literal text qualifiers around them (double-quotes). These often get confused with the text qualifiers you are using to build your string in VBA. As such, I usually like to use Chr(34) to return a literal double-quote.
So, are any of your fields (SupplierID or ProductID) text, or are they both numeric?

There is an easy way of determining if you have written your SQL code correctly. Create a manual query in Query Builder that does EXACTLY what you want to do. Now switch to SQL code, and copy and paste the code out somewhere where you can reference it (NotePad, Word, etc). This is what the code you want to build in VBA should look like.

Now, if your VBA code, after you have defined varSql, return it to a Message Box, i.e.

MsgBox varSql

Compare that to the code you copied out. Does it match?

[T 5]

Hmmm. I had no luck with this, so tried to create the query in the query builder, which would only go as far as the combo, not the combo column?
In a little more researching, I have seen mention that the SQL query will only allow linking to one bound column, which would kind of suggest the reason why the builder doesn't do to the columns. There appears to be a work around of creating an invisible text box(s) which are linked to the combo column(s) value, which can then be referenced within the query!?

Can anybody confirm if this actually true and if there is any better alternatives?

Thanks and regards.

[ItsMe]

If you are in SQL you only need one column from a ComboBox control. Use the Bound Column property of the combo to assign the Key value from the RowSource to the .Value property.

Adding criteria to an SQL statement to include multiple columns of a combo is illogical.