Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable not defined (Access 2000)

    I am trying to build a code that checks the presence of a check box Yes in the query.However the error in my function shows " variable not defined" pointing to orders1.suborder.How can i define this variable ?
    Public Function CheckSubOrder()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim StrSQL As String
    StrSQL = " SELECT affiliates.afid, affiliates.CompanyName, orders1.orderid, orders1.orderdate, orders1.SubOrder " & _" FROM (orders1 INNER JOIN Customers1 ON orders1.customerid = Customers1.Customerid) INNER JOIN
    affiliates ON Customers1.afid = affiliates.afid"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
    If Orders1.SubOrder = True Then
    MsgBox " Attention ! There is an Suborder from Affiliates.CompanyName waiting for you "
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

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

    Re: Variable not defined (Access 2000)

    You open a recordset rst. If you want to refer to a field, you must indicate that it belongs to rst:

    If rst!SubOrder = True Then

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable not defined (Access 2000)

    Thank you for the good advice.I have corrected it accordingly.But why my message box does not work? I get no error, but no message either and i wonder why ?
    Public Function CheckSubOrder()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim StrSQL As String

    StrSQL = " SELECT affiliates.afid, affiliates.CompanyName, orders1.orderid, orders1.orderdate, orders1.SubOrder " & _
    " FROM (orders1 INNER JOIN Customers1 ON orders1.customerid = Customers1.Customerid) INNER JOIN affiliates ON Customers1.afid = affiliates.afid"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
    If rst!SubOrder = True Then
    MsgBox "Attention ! There is an Suborder from " & rst!CompanyName, vbInformation
    rst.MoveNext
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End If
    End Function

    Kind regards

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

    Re: Variable not defined (Access 2000)

    Create a query with the same SQL as in your code, and try to find out why it doesn

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable not defined (Access 2000)

    I have the query and i have made the sql out of this query.It shows all the records.II have also records with Suborder = True. s it possible that there is some error in my code ?

    kind regards
    Keks

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

    Re: Variable not defined (Access 2000)

    Your code only looks at the first record. It does not loop through the records. To loop:

    ...
    Set rst = dbs.OpenRecordset(StrSQL, dbOpenDynaset)
    Do While Not rst.EOF
    If rst!SubOrder = True Then
    MsgBox "Attention ! There is an Suborder from " & rst!CompanyName, vbInformation
    End If
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

Posting Permissions

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