SQL Syntax (Access 2000)

Topic

New Reply

I have a very successful code,built by the way with the help of the Forum, which runs as follwos:
Dim city As Long
Select Case Forms![FOrderInformation]![office] ‘ this is an Option Box called Office, with 8 options
Case 1
city = 0
Case 2
city = 1
Case 3
city = 2
Case 4
city = 3
Case 5
city = 4
Case 6
city = 5
Case 7
city = 6
Case 8
city = 7
End Select
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
StrItems = [Forms]![FOrderInformation]![Forder details extended].[Form].[items]
Dim strSQL As String
Dim strWhere As String
strWhere = ” WHERE ProductID=” & MySubform.Productid
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere
DoCmd.RunSQL strSQL ‘ substract

I want to improve my code and place a “brake’ in the code in case the stock is depleted.
it means that one should not be allowed to order if the goods are not on stock.
I have tried the following line:

If products.branch” & city & ” – StrCartons < 0 then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly

These lines however are not accepted by Access.Is there a way to place these line in my above working code?

Reply | Quote

Replies

In the first place, the whole Select…End Select block can be replaced by

city = Forms![FOrderInformation]![office] – 1

If you decrease the option values of the option buttons in the option box by 1 (start at 0 instead of 1), it would become even easier:

city = Forms![FOrderInformation]![office]

Now, to your question: you can’t just refer to “products.branch” & city in your code – it is the name of a field in the products table. You must either open a recordset to retrieve the value, or use DLookup. You must be careful about how you compare. If you compare two strings, the result may not be what you expect. Try the following, in which the strings are converted to Long integers by CLng:

‘ some code omitted…
strWhere = ” WHERE ProductID=” & MySubform.Productid
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere
If CLng(DLookup(“branch” & city, “Products”, strWhere)) < CLng(StrCartons) Then
MsgBox “There isn’t enough stock to fill this order.”, vbInformation + vbOKOnly
Else
DoCmd.RunSQL strSQL ‘ substract
End If

Reply | Quote

Thank you very much for your answer.Your first suggestion saved for me an amazing amount of coding,since i have to repeat the opton in many places.
In the second part, the whole line which is

If CLng(DLookup(“branch” & city, “Products”, strWhere)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else

Seems not to give some syntx error.Actually the error is the following:
Run time error 2075.
Syntax error(missing operator in query expression Where Productid = 76
Just to clarify. In my expreiment i wanted to update a product with id number 76.

Here again is mny whole function:

Public Function FncUpdateCartons()
Dim city As Long
city = Forms![FOrderInformation]![office] – 1
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
StrItems = [Forms]![FOrderInformation]![Forder details extended].[Form].[items]
Dim strSQL As String
Dim strWhere As String
strWhere = " WHERE ProductID=" & MySubform.Productid
strSQL = "UPDATE Products SET " & _
" products.branch" & city & " = products.branch" & city & " – " & StrCartons & strWhere
If CLng(DLookup("branch" & city, "Products", strWhere)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' substract
End Function

I will be grateful if you point to me my syntax mistake,and also i must express my appreciation for your wonderful replies.

Best regards

Reply | Quote

strWhere = ” WHERE ProductID=” & MySubform.Productid
In a DLookup you don’t have to add the word “Where”
use
strWhere = “ProductID=” & MySubform.Productid

Reply | Quote

Sorry, my mistake. Francois and Rory have already pointed out what was wrong in my code.

Regards,
Hans

Reply | Quote

Hi,
your strWhere variable in the dlookup doesn’t work because the criterion clause in a dlookup function doesn’t include the WHERE part. Try something like:

Public Function FncUpdateCartons()
Dim city As Long
city = Forms![FOrderInformation]![office] - 1
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = MySubform![cartons]
StrItems = MySubform![items]
Dim strSQL As String
Dim strWhere As String, strCondition as string
strCondition = "ProductID=" & MySubform.Productid
strWhere = " WHERE " & strCondition
strSQL = "UPDATE Products SET " & _
" products.branch" & city & " = products.branch" & city & " - " & StrCartons & strWhere
If CLng(DLookup("branch" & city, "Products", strCondition)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' substract
End Function

Hope that helps.

Reply | Quote

Thank you very much for your replies.I have copied exactly the function but i obtain red letters on the following line:
If CLng(DLookup(“branch” & city, “Products”, strCondition)) < CLng(StrCartons) Then
which means i have some syntax problems, but i cannot find them.
I wonder why is it so, may be my Access does not include some references?
Here is how i copied the function:

Public Function FncUpdateCartons()
Dim city As Long
city = Forms![FOrderInformation]![office] – 1
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = MySubform![cartons]
StrItems = MySubform![items]
Dim strSQL As String
Dim strWhere As String, strCondition As String
strCondition = "ProductID=" & MySubform.Productid
strWhere = " WHERE " & strCondition
strSQL = "UPDATE Products SET " & _
" products.branch" & city & " = products.branch" & city & " – " & StrCartons & "strWhere "
If CLng(DLookup("branch" & city, "Products", strCondition)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' substract
End Function

Could you please explain to me why do i receive line fo code coloured with red?

Very Best regards

Reply | Quote

Hi,
I just copied and pasted that straight into a module and got no red error highlighting. I did originally have an extra ” in the code I posted which I then corrected, so you might want to try recopying and pasting the code from my post and see if it’s OK now.
Hope that helps.

Reply | Quote

I may be wrong, but I think your DLookup has incorrect syntax. Your DLookup statement

If CLng(DLookup(“branch” & city, “Products”, strCondition)) < CLng(StrCartons) Then

should probably look like

If CLng(DLookup("branch", "Products", strCondition)) & city < CLng(StrCartons) Then

As I say, I may be wrong.
HTH
Pat

Reply | Quote