Button to add time to table (1 Viewer)

[Jolland26]

Hi everyone,

Been a bit stuck on this for a couple days so thought I'd ask on here. I've added a button to my form for a start time for the form being filled out, and have another for the end time so my users/managers can produce a report about how long their engineering process is taking. I have found how to add the time to a textbox so I want to just use this code for my command button and then use the textbox it inputs it into to save to my table. The code I found is:

Dim strSQL As String

Me.txtStartTime = Now()

strSQL = "UPDATE tblEQNResponse SET StartTime = #" & Me.txtStartTime & "#;"
CurrentDB.Execute strSQL, dbFailOnError

However, when I put this code behind my command button and then open the form and click the command button, nothing happens. I'm really unsure as to why this is and have scoured the forum and other pages on the internet to find an answer but I cannot seem to find one.

I'm sure it's something really stupid and easy to fix, but any help would be appreciated!

 

[arnelgp]

You have Update query, meaning at least you have a record already.

If the table has no record try Insert query.

 

[Jolland26]

You have Update query, meaning at least you have a record already.

If the table has no record try Insert query.

I've tried doing that, even if I leave the query out of it and just put the Me.txtStartTime = Now() it still doesn't do anything either.

 

[Gasman]

I've tried doing that, even if I leave the query out of it and just put the Me.txtStartTime = Now() it still doesn't do anything either.

No, arnelgp is saying you are using an Update query, but you are not saying what record to update.?
So you would likely use an Insert query to create a record for the start and then use Update for the end, depending on your structure.? It could be two records, one for start and one for end, then you would use Insert query for both.?

 

[Mike Krailo]

Assuming this time for start and end is being added to the record on the form, did you actually check the table data to see if anything is being entered into the field StartTime? I assume this is a bound control right? Maybe it is just a refresh.

Also, if you have just added a new record, maybe the record is not saved to the table yet so it can be updated!

 

[Jolland26]

Assuming this time for start and end is being added to the record on the form, did you actually check the table data to see if anything is being entered into the field StartTime? I assume this is a bound control right? Maybe it is just a refresh.

Also, if you have just added a new record, maybe the record is not saved to the table yet so it can be updated!

I have checked and there's nothing going into the table either. I have saved the record already so there is one there, but the button just doesn't seem to add the time to either the textbox on the form or the field in the table.

 

[Jolland26]

No, arnelgp is saying you are using an Update query, but you are not saying what record to update.?
So you would likely use an Insert query to create a record for the start and then use Update for the end, depending on your structure.? It could be two records, one for start and one for end, then you would use Insert query for both.?

I understand that, what I'm saying is neither the Update or Insert query put anything into the table and running the code without the query doesn't do anything either.

 

[Mike Krailo]

Interesting, so can you manually enter a time in that field to update the table?

 

[Jolland26]

Interesting, so can you manually enter a time in that field to update the table?

Yes I can, that's why it's so confusing to me that the command button won't update anything.

 

[Mike Krailo]

I guess I don't know why you are even doing the SQL and DbExecute if this is bound field, why not just do:

Me.txtStartTime = Now()

 

[Jolland26]

I guess I don't know why you are even doing the SQL and DbExecute if this is bound field, why not just do:

Me.txtStartTime = Now()

I tried that first and that didn't do anything either, that's why I tried querying it to update my table but none of it seems to work.

 

[Mike Krailo]

Try deleting the control off the form and adding a new control on the form. That should absolutely work. Maybe there is some other corruption issue going on as well so you can try and decompile your project and try again. If you can manually enter in a date into the control and the table updates, then there is no reason that the above command will not work.

You can do a quick test by making a new form based on the same record source and I'll bet that it works fine.

 

[Jolland26]

Try deleting the control off the form and adding a new control on the form. That should absolutely work. Maybe there is some other corruption issue going on as well so you can try and decompile your project and try again. If you can manually enter in a date into the control and the table updates, then there is no reason that the above command will not work.

You can do a quick test by making a new form based on the same record source and I'll bet that it works fine.

I've just tried both deleting the control and adding a new one and creating a whole new form and neither of those things seemed to make any difference. I compile my code each time before saving and there are no errors there, so I'm really at a loss as to why it doesn't want to put the time into the textbox.

 

[Gasman]

Are we talking about Time or Date and Time?, as Now() produces Date & Time?
Why not just have the control bound to the field?

 

[Mike Krailo]

I thought I had addressed that in Post Number 8. If you can manually enter a date in the control on the form and the table updates then this should not be a problem. I would just create a new database and import all the objects into the new database and test again.

 

[Jolland26]

Are we talking about Time or Date and Time?, as Now() produces Date & Time?
Why not just have the control bound to the field?

I mean Date and Time. The control is bound to the field, but I want the field to only be updated when the user clicks the button as it's a form that will most likely have to be used more than once so the first time they use it they click the Start Time button and when they have finished using the form completely they click an End Time button and the table updates accordingly.

I thought I had addressed that in Post Number 8. If you can manually enter a date in the control on the form and the table updates then this should not be a problem. I would just create a new database and import all the objects into the new database and test again.

I suppose I could do it without a button but it just seems a but easier to me to use one.

 

[Gasman]

FWIW I've just tried that code with a new Date/Time field and it worked fine. Even updated EVERY record in the table. Something I have not done before.

 

[Jolland26]

FWIW I've just tried that code with a new Date/Time field and it worked fine. Even updated EVERY record in the table. Something I have not done before.

I did it myself in a new database quickly with a new table and it worked! I think the reason it doesn't work in my original is because in the table I am using I haven't defined a primary key, but I'm unsure what I should define it as. I have 2 tables related to this one (which is called tblEQNResponse), one of which is called tblEQNSubAccept where EQNID is a primary key. The other is tblEQNAllocation which has the primary key of AllocationID. In the tblEQNResponse I want the user to fill a form using their AllocationID, which then autofills the EQNID associated with it into a textbox. They then write their response to the EQN, having clicked the StartTime button when they start. When they have resolved the engineering issue and they are submitting the EQN for review I want them to press EndTime and then save the form. Should I have a composite key within tblEQNResponse that encapsulates EQNID and AllocationID? It's obvious to me now that this is what was holding me up, I had just forgotten to add the primary key in the Friday afternoon rush.

 

[Gasman]

My PKs have *always* been Autonumber, somethingID
That is all I ever use to link records.

However I don't think you meant to update every record, did you?, so perhaps add some criteria.?

Good luck with it anyway.

 

[Jolland26]

My PKs have *always* been Autonumber, somethingID
That is all I ever use to link records.

However I don't think you meant to update every record, did you?, so perhaps add some criteria.?

Good luck with it anyway.

No I'd just like to update each AllocationID with the time it was started and finished. Thanks very much for your help!