Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Queries (Access 2003)

    Hi,
    I have a form which the client enters a beginning and ending date in and then depresses a button to produce a report. Behind the scenes, after I check the dates, I delete records in a linked table, run a query, delete records from another table and run another query. The first query I will call Meals03, takes and receives its input from query Meals02 and inputs records into an empty table called CurrMeals. Query Meals02 gets its input from query Meals01. Query Meals01 select data from a Table called Meals , using the date data from the form as a parameter. The second query I will call Funds03, takes and receives its input from query Funds02 and inputs records into an empty table called Funds. Query Funds02 gets its input from query Funds01. Query Funds01 select data from a Table called Control , using the date data from the form as a parameter. What is happening is that the form is run and no data is present. The form can be run many times in a day and no output. Then the client comes in the next day and tries it, and it works. The client has shut down the database, the computer and has gone to lunch. Sometime it works, usually it does not. I am not sure what I should look at , can you give me a clue?
    Thanks,
    Sarge

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

    Re: Queries (Access 2003)

    I think we'd have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 2003)

    Hi Hans,
    These databases are large, and are password protected. And as I said, the problem is random to the Client's computer. I was asking if any one knows of any reason why queries executed from VBA code may not work. If you want all the databases I will ship them, but can not be sure that the problem will raise it head.
    Thanks,
    Sarge

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 2003)

    Using dates as parameters can be problematic - particularly when the computer uses a different date setting to US. SQL uses US date formats. Your client may be entering the dates in a non-recognisable format. As Hans says, we would really need to see how the dates are entered and how the queries are constructed to tell. Hope that gives one area to investigate anyway.

    Another longshot could be a problem with the system date. If the CMOS battery is dying then the system date may reset when booting up. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Queries (Access 2003)

    You could insert lines in the code to log what is happening, for example by writing the values of key variables and a time stamp to a text file. Analysing the log file might give you an idea of what causes the problem.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 2003)

    Thanks Steve,
    But I format the dates, MM/DD/CCYY. I will have to check on the battery, but I doubt it.
    Thanks,
    Sarge

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 2003)

    Hi Hans,
    I sorry but you lost me at the bakery. (from Laugh In) Insert code where? I know that I run a delete, then load a table, then do another delete, and load another table. After which I check to see if there are any records in the second table, and it is empty. The code is :

    Build_The_Meals_Fund_Table:

    DoCmd.OpenQuery "qryDelMealsFund", acNormal, acEdit
    DoCmd.Close acQuery, "qryDelMealsFund"

    DoCmd.OpenQuery "qryMealsFund04", acNormal, acAdd
    DoCmd.Close acQuery, "qryMealsFund04"

    Get_CurrFunds_From_FundingTable:

    DoCmd.OpenQuery "qryDelCurrFunds", acNormal, acEdit
    DoCmd.Close acQuery, "qryDelCurrFunds"

    DoCmd.OpenQuery "qryBldCurrFunds03", acNormal, acAdd
    DoCmd.Close acQuery, "qryBldCurrFunds03"

    Bring_Funding_Data_Into_Hold_Areas:

    lngKount = DCount("*", "tblCurrFunds")
    If lngKount = 0 Then
    MsgBox "long Kount was " & lngKount
    strProgErr = "No Legal Add Meals Option was Choosen"
    Call ProgErr
    GoTo Exit_butBldMeals_Click
    End If
    End of code.
    So where do I add what code???
    More kornfused then ever,
    Sarge

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

    Re: Queries (Access 2003)

    I assume that all those queries are action queries, not select queries. If so, there is no reason to specify acEdit or acAdd - this argument is ignored. And there is no reason to close the query - "opening" an action query executes it, there is nothing to be closed after it has run.

    But this shouldn't influence the result.

    If you have the standard action query warnings turned on, you should get message boxes such as "You are about to delete 345 records" and "You are about to insert 456 records". If you have turned off these warnings, either in the Edit/Find tab of Tools | Options or using code, you should turn them on again. Do the message boxes give you a clue of what is happening?

  9. #9
    2 Star Lounger
    Join Date
    Jun 2002
    Location
    Loudon, New Hampshire, USA
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Queries (Access 2003)

    Hi Hans,
    Okay, though I hate for a client to see all the messages, I guess I will have to turn them back on. As I have stated I never have been able to cause this problem, so it will have to be run by the client at month end. Let you know what happens then.
    I found closing a query allows you to run it again. In other words. If the Client ran the form, and then made some data changes and ran it again. I get the wrong results. Lets say I deleted a record, without the close query, Access gives me the previous query results, and the deleted record is there. Closing the query, some how forces the query to be run.
    Sarge

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

    Re: Queries (Access 2003)

    I can understand that you wouldn't want the action query warnings visible all the time, but they are very useful when investigating a problem.

Posting Permissions

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