Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Syntax (Access 2000)

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Syntax (Access 2000)

    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
    <font color=blue>If CLng(DLookup("branch" & city, "Products", strWhere)) < CLng(StrCartons) Then</font color=blue>
    MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
    Else
    DoCmd.RunSQL strSQL ' substract
    End If

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Syntax (Access 2000)

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Syntax (Access 2000)

    strWhere = " WHERE ProductID=" & MySubform.Productid
    In a DLookup you don't have to add the word "Where"
    use
    strWhere = "ProductID=" & MySubform.Productid
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Syntax (Access 2000)

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

    Regards,
    Hans

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL Syntax (Access 2000)

    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:
    <pre>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
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Syntax (Access 2000)

    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

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: SQL Syntax (Access 2000)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: SQL Syntax (Access 2000)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •