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.ProdCo de, products.Produc tIdentify FROM
products"
strtest2 = strtest2 & " WHERE Products.ProdCo de=" & Me.NewCopyProdC ode
& " AND products.Produc tIdentify=" & Me.Combo8.Colum n(1) & ";"
Set rst2 = dbs2.OpenRecord set(strtest2, dbOpenForwardOn ly)

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

--------------------
Basically, a user enters a code into the text field 'NewCopyProdCod e' ,
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*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g47g2000cwa.goo glegroups.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.ProdCo de, products.Produc tIdentify FROM
products"
strtest2 = strtest2 & " WHERE Products.ProdCo de=" & Me.NewCopyProdC ode
& " AND products.Produc tIdentify=" & Me.Combo8.Colum n(1) & ";"
Set rst2 = dbs2.OpenRecord set(strtest2, dbOpenForwardOn ly)

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

--------------------
Basically, a user enters a code into the text field 'NewCopyProdCod e' ,
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*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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.ProdCo de = '" & Me.NewCopyProdC ode &
"' AND products.Produc tIdentify = '" & Me.Combo8.Colum n(1) & "';"

Try that and let us know ...

Keith.

[David]

Keith wrote:

"David" <da*********@sc ene-double.co.uk> wrote in message
news:11******** **************@ g49g2000cwa.goo glegroups.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.ProdCo de = '" & Me.NewCopyProdC ode &
"' AND products.Produc tIdentify = '" & Me.Combo8.Colum n(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