Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Udate table using update query in code (Access 2k win 2k)

    Hello Again

    I'm trying to update a record in a datatable using an SQL string with the update instruction in it. Unfortunately nothing appears to be happening when the event it is set to trun from is triggered.

    Dim dBs As DAO.Database
    Dim qDf As QueryDef
    Dim TimVar As String

    Set dBs = CurrentDb()
    TimVar = Now()


    dBs.Execute "Update [operator breakdown entry record] SET [Time_Attended] =" & TimVar & " Where [job_number] =" & Me.Job_Number


    Exit_Sub:
    dBs.close
    Set dBs = Nothing

    Err_Handler:
    On Error GoTo Exit_Sub

    That's my code (pretty much stolen in total from the Access help file) and it is set to run when from the on click event for a check box that is on a sub form, the sub form is displayed as continuos forms.

    At the moment I can't even check if the variable is initialising as nothign at at all appears to be happening, I've tried the code on a 'test' form I use running from a button click event and the result is the same.

    Can anyone see where my 'obvious' error is please?

    Thanks

    Ian

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

    Re: Udate table using update query in code (Access 2k win 2k)

    One problem is that date and time values must be enclosed in # characters, and must be in US date format. You can specify the dbFailOnError option to make Execute display error messages. Try this:

    <img src=/w3timages/blueline.gif width=33% height=2>

    Dim dBs As DAO.Database
    Dim TimVar As String

    Set dBs = CurrentDb
    TimVar = Now

    dBs.Execute "Update [operator breakdown entry record] SET [Time_Attended] =#" & _
    Format(TimVar, "dd/mm/yyyy hh:mm" & "# Where [job_number] =" & _
    Me.Job_Number, dbFailOnError

    Exit_Sub:
    Set dBs = Nothing

    Err_Handler:
    On Error GoTo Exit_Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    (The qDf variable isn't used, at least not in the part you posted, and you don't need to close dBs if it is the current database, that is only necessary if you open an external database)

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Udate table using update query in code (Access 2k win 2k)

    Hans

    Thanks for the advice, but I'm still not having any luck. Although I'm now thinking it's not related to the code. I've replaced all the code with a message box line and even that doesn't appear!! Is there any reason that a control on a sub form might not allow code in its 'on click' event to run?

    I had noticed the qDf was superflous, but as it was in the MS help example I put it in when the code wouldn't work..... Result, no change....

    I have persuaded the database to do what I want from my 'test' form, using the docmd.runsql line of code, however, even that wont work from the dratted subform.

    I'm starting to think it might be down to this being displayed in continous forms??

    Thanks for the help.

    Ian.

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

    Re: Udate table using update query in code (Access 2k win 2k)

    Can you explain what you are trying to accomplish? Why do you want to run an update query in the On Click event of a control?

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Udate table using update query in code (Access 2k win 2k)

    OK, here is what I'm trying to do: -

    The database is used to record all work requests made to our maintenance department along with the response made by that department. Due to a lack of PC skills (the don't like typing) I've been asked if it's possible to enter the date and time by clicking onto a button or control. As there is a control (check box) that they have to click to identify what task they are currently working on I wanted to use this checkbox to populate the date / time field. It was at this point that I started to have problems.

    To be honest, the database is a bit of a mess, mainly due to a host of modifications made to try and reduce the amount of typing to be done. This is just one more job.

    It's times like this that I start to think it would be a fun job if not for the users........

    Thanks for the assistance so far.

    Ian

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

    Re: Udate table using update query in code (Access 2k win 2k)

    The On Click event of a check box should occur whether the check box is in a main form or in a subform. Perhaps the code is not connected to the event properly. Try the following:
    - Open the subform in design view.
    - Select the check box.
    - Make sure that the On Click event in the Event tab of the Properties window says [Event Procedure].
    - Click the ... button to the right of the dropdown arrow in the On Click event.
    - You should end up in the code you posted.

    On another note: is the date/time field a field in the record source of the main form or subform? If so, you shouldn't use an update query to set the date, but set the value of (a text box bound to) the field itself.

  7. #7
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Udate table using update query in code (Access 2k win 2k)

    Hans

    Thanks foi the help, I'd tried the ... bit, it takes me straight to my code, so it is attached to the right control.

    The information I'm trying to update is in the datatable, it is not displayed on the form, nor is it in the query used to populate the form.

    I have just tried adding it to the query and then updating a text box on the form, but no luck. I'm now fairly convinced that some of the problems are down to very bad database design, I've come across a few comments about spaces in field names not beng a good idea, this database has lots of them. While Access alters the name by adding an underscore when it's dropped onto a form, the table still has the original name with the space.

    I've looked into the database in depth and there are a great many occasions where this has been done, almost every field in every table!!

    To confirm my theory I've copied a small amount of data into a new database, recreated the form and subform, the code works, so unless I'm wrong about the spaces in the field names it looks like that's my problem.

    Thanks again Hans

    Ian

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

    Re: Udate table using update query in code (Access 2k win 2k)

    There is a possibility that your database has become corrupt. You could create a blank new database and import all database objects from the problem database into it, and see if the code works correctly.

  9. #9
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Udate table using update query in code (Access 2k win 2k)

    I'll give that a try, unfortunately it's not the first time that will have been done to this database, I think it has got to the point that a major overhaul is in order.

Posting Permissions

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