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

Topic

New Reply

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

Reply | Quote

Replies

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:

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

(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)

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote