Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Retrieving records in VB (2003)

    I am at the extreme limit of my Access knowledge here!
    In post <post:=657,239>post 657,239</post:> I was given some example VB code using DAO.Database and DAO.recordset variables - which was definitely all new to me! This code works fine of course.
    I am now developing another DB; I need to execute a select query when a user sets a checkbox on a form. (I have written a query which does exactly what's required when run freestanding so I know the SQL is correct; I've copied and pasted it into the VB module.)
    I copied and modified the code to use this new SQL, but when it was obeyed it failed because (I now know) the executeSQL method only works for action queries.
    The SQL is a SELECT ie it's going to retrieve records. I need to do this and then interrogate one of the fields "SeniorMarshallCount".
    How does this code have to be modified so I can retrieve the records and interrogate this field, please?
    Thanks
    Silverback
    Silverback

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

    Re: Retrieving records in VB (2003)

    Does the query return multiple records or just one?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving records in VB (2003)

    It's going to return 0 or 1 record.
    Thanks
    Silverback
    Silverback

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

    Re: Retrieving records in VB (2003)

    If so, you can use code like this:

    Dim varCount As Variant
    varCount = DLookup("SeniorMarshallCount", "NameOfQuery")
    If IsNull(varCount) Then
    ...
    Else
    ...
    End If

    where NameOfQuery is the name of the query.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Warrington, Cheshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving records in VB (2003)

    Thanks - it's all working fine, now.

    BTW - Since this posting, I have successfully run a delete function using the DoCmd.RunSQL. Thanks for all your help, once again.
    Silverback
    Silverback

Posting Permissions

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