Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0)

    If There a Faster Way To Do This DLookup?
    a2k (9.0.3821) SR-1 Jet 4.0

    I have the following code in Report Detail_Format and it works, however the Dlookup seems to work very slow, the following is one of many Dlookup routines I

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

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    Why not include tblPersonal in your query and pull those fields in that way. DLookups are slow and doing a whole series of them for each record in a report is going to be very slow. I you can't include the tblPersonal table in the query, open a recordset on tblPersonal for the current strSsNo and get all the values at once instead of using separate DLookups.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    Hi Charlotte

    I

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

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    According to the "old" code, the field name is strDRSS, not strDSS. So try

    strSQL = "SELECT * FROM tblDentists WHERE "
    strSQL = strSQL & "[strDRSS] = '" & strID & "'"

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

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    Normally you get that message when you try to open a recordset on a query with a parameter setting, not on a table. However, since strDSS is in parens, I suspect that's the problem and is being interpreted as a parameter. If the spelling of the field name is correct, the the only other cause I can think of is the ODBC driver installed by MDAC. What version of ADO do you have installed on your machine?
    Charlotte

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

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    Was there a specific reason why you can't follow Charlotte's suggestion to pull the extra fields into the query? That would be much easier and more efficient than using code.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If There a Faster Way To Do This DLookup? (a2k (9.0.3821) SR-1 Jet 4.0

    Hi Charlotte & Hans

    This check writer program prints check to Members, Doctors or Vendors based on pay to code in check header, wasn

  8. #8
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If There a Faster Way To Do This DLookup? (a2k

    If you're getting the same set of information for three different groups, where the only difference is that the information is in 3 different tables, this sounds like the perfect place for a union query. Union queries are a pain in the butt because they have to be done directly as SQL and everything has to be in exactly the right place, but I think it would probably save you time in the long run. I'm guessing on your field names, but here's what the query might look like:

    SELECT "Personal" as [PayeeType], [strMPSS] AS [strID], [strLastName], [strFirstName], [strMidInit], [strStreet], [strUnit], [strCity], [strState], [strZip] FROM [tblPersonal]

    UNION

    SELECT "Dentist", [strDRSS], [strDrLNam], [strDrFNam], [strDri], [strDraddr1], [strDraddr2], [strDrCity], [strDrState], [strDrZip] FROM [tblDentists]

    UNION

    SELECT "Vendor", [strVendorSS], [strVendorLNam], [strVendorFNam], [strVendori], [strVendoraddr1], [strVendoraddr2], [strVendorCity], [strVendorState], [strVendorZip] FROM [tblVendors];

    Note that the fields are all in the same order and the query will use the column names from the first SELECT statement (which is why I renamed it strID to reduce confusion). This assumes that the ID fields (strMPSS, strDRSS and strVendorSS) aren't necessarily all unique respective to each other -- if they are you may want to remove the PayeeType field. Also, I assumed that strUnit in tblPersonal could go in the same place as strDraddr2 in tblDentists.

    Then you can link this union query to whatever query you're using for the checks, and not have to go through the trouble and slow-down of explicitly setting the field values in the code of the report.

    Brent

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If There a Faster Way To Do This DLookup? (a2k

    Hi Brent

    Thanks for your suggestion.

    John

Posting Permissions

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