Help with SQL please..........

[David]

Hi,

Please can you help me get this code to run ?

--------------------

Dim dbs2 As DAO.Database, rst2 As DAO.Recordset, str2 As String
Set dbs2 = CurrentDb

strtest2 = "SELECT products.ProdCode, products.ProductIdentify FROM
products"
strtest2 = strtest2 & " WHERE Products.ProdCode=" & Me.NewCopyProdCode
& " AND products.ProductIdentify=" & Me.Combo8.Column(1) & ";"
Set rst2 = dbs2.OpenRecordset(strtest2, dbOpenForwardOnly)

If rst2.EOF Then
MsgBox "This product already exists"
Else
End If

--------------------
Basically, a user enters a code into the text field 'NewCopyProdCode' ,
then selects from a dropdown 'Combo8' and then the system will test if
a current version alredy exists prior to creating a new entry, from the
afterupdate event of the combo.


Thank you so much if you can help :-)
David

[Keith]

"David" <da*********@scene-double.co.uk> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hi,

Please can you help me get this code to run ?

--------------------

Dim dbs2 As DAO.Database, rst2 As DAO.Recordset, str2 As String
Set dbs2 = CurrentDb

strtest2 = "SELECT products.ProdCode, products.ProductIdentify FROM
products"
strtest2 = strtest2 & " WHERE Products.ProdCode=" & Me.NewCopyProdCode
& " AND products.ProductIdentify=" & Me.Combo8.Column(1) & ";"
Set rst2 = dbs2.OpenRecordset(strtest2, dbOpenForwardOnly)

If rst2.EOF Then
MsgBox "This product already exists"
Else
End If

--------------------
Basically, a user enters a code into the text field 'NewCopyProdCode' ,
then selects from a dropdown 'Combo8' and then the system will test if
a current version alredy exists prior to creating a new entry, from the
afterupdate event of the combo.

Where is the code run from? Do you try to run it and get errors, if so,
what are they? Are the text box and combo bound to text or number fields?

Regards,
Keith.
www.keithwilby.com

[David]

Thanks Keith,

This is a simple pop-up form with a text box & dropdown.
It is basically to test for duplicates prior to setting up new products

After the dropdown (After Update), I get an error 3075, syntax error
(missing operator) in query expression.......

The text box is unbound - it just takes a name the user wants to use.
The combo selects fields from a table, the required field being of
text type.

Thanks

David

[Keith]

"David" <da*********@scene-double.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Thanks Keith,

This is a simple pop-up form with a text box & dropdown.
It is basically to test for duplicates prior to setting up new products

After the dropdown (After Update), I get an error 3075, syntax error
(missing operator) in query expression.......

The text box is unbound - it just takes a name the user wants to use.
The combo selects fields from a table, the required field being of
text type.

Hi David,

Well for starters, and I'm not sure off the top of my head if this will stop
the error, you need quotation marks for the text box and the combo if they
are dealing with text:

strtest2 = strtest2 & " WHERE Products.ProdCode = '" & Me.NewCopyProdCode &
"' AND products.ProductIdentify = '" & Me.Combo8.Column(1) & "';"

Try that and let us know ...

Keith.

[David]

Keith wrote:

"David" <da*********@scene-double.co.uk> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Thanks Keith,

This is a simple pop-up form with a text box & dropdown.
It is basically to test for duplicates prior to setting up new products

After the dropdown (After Update), I get an error 3075, syntax error
(missing operator) in query expression.......

The text box is unbound - it just takes a name the user wants to use.
The combo selects fields from a table, the required field being of
text type.

Hi David,

Well for starters, and I'm not sure off the top of my head if this will stop
the error, you need quotation marks for the text box and the combo if they
are dealing with text:

strtest2 = strtest2 & " WHERE Products.ProdCode = '" & Me.NewCopyProdCode &
"' AND products.ProductIdentify = '" & Me.Combo8.Column(1) & "';"

Try that and let us know ...

Keith.

-----------------------------------

Thanks Keith,

I had already fixed it .... bugs in my code, I was selecting the wrong
field from the combo and testing a numeric against a text....DOH .... I
win the prize idiot competition. Thanks for your help anyway :-)
David