Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    queries in VBA (A2K)

    I need to calculate rental charges for an invoice, but before I run any reports, I must compare two tables and ensure that the values entered are the same.

    I would like to run a "Find Unmatched.." query and flash a message to the user warning him if the results are not equal (the invoice will be incorrect) and giving him the opportunity to bail out before invoicing. I thought to use VBA, running my sub on opening the report, but I can't figure out how to run a query and then refer to the results of same. Even determining whether or not there are any results would be helpful.

    Any suggestions would be appreciated,
    Suzanne

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: queries in VBA (A2K)

    You could open a recordset in code with the query as its source. Are you more familiar with DAO or ADO? The techniques are different in the two object models.

    However ...
    <hr>ensure that the values entered are the same<hr>
    ... a statement like this tends to set off warning bells for me. Why would you have comparable values entered in two different places? The whole idea of a relational database is that the data is entered in ONE place. Could you explain more about what you're doing and the tables involved?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries in VBA (A2K)

    I know, I know! I don't like it either. But Dates&Times are entered pre-show, a record of what is -supposed- to happen. Usage is a record of what actually -does- happen.
    As I write this, it seems foolish still. I will have another look at the two tables and see if perhaps I can use just the one.
    Thanks,
    Suzanne

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: queries in VBA (A2K)

    That sounds like a slightly different situation. If the actual charges can be calculated based on dates or whatever, they wouldn't ordinarily be entered as well, merely calculated when the invoice was created. You could easily calculate them in the query and compare them to the previously entered value if that's what you're trying to do.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Jun 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: queries in VBA (A2K)

    That's what I was searching for - more specifically, once I've run the query, how do I refer in VBA to the query values returned? I coudn't find an example of this in my travels. So if a query designed to find unmatched values comes back with a date (or a count of entries), how do I use that value in my if statement?

    Thanks,
    Suzanne

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: queries in VBA (A2K)

    Do you want to use DAO or ADO? In either case, you create a recordset object but you can't create a recordset object after you "run" the query. In DAO you would do someting like this:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("MyQuery", dbOpenSnapshot)

    'If any records were returned, do something with them
    If Not rst.EOF Then

    'You refer to the fields in a recordset just as if they were fields
    'in a table

    If rst!ItemDate = varSomething Then
    ... etc.
    End If
    End If

    The assumptions here are that "MyQuery" is the name of a saved query (you could use a SQL string instead), that ItemDate is the name of a field in the recordset, and that the variable, varSomething, was populated elsewhere and passed into the routine.
    Charlotte

Posting Permissions

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