Executing query on a specific record in a continuous form 1

[BTilson]

I have a form which lists labor ticket transactions (clock in time, clock out time, etc) for our employees. I have added a button in the continuous form which updates the employees efficiency to 90% for the day. However, whenever I try to click the button, it always runs the query for the first displayed record, regardless of which record I click the button for.

Does anyone have any idea how I can make this work properly?

Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PWise]

What is the code behand the buttton?

 

[dhookom]

I doubt "anyone" has any idea what your code or SQL looks like. If you want some help, you might want to post some details about your current specs. Where's the button located? What is the primary key value on the subform? What are your table structures? ....?

Duane
Hook'D on Access
MS Access MVP

 

[BTilson]

Sorry bout that, guess some specifics would've been helpful.

Here is the SQL of the query I am running:

UPDATE SYSADM_LABOR_TICKET SET SYSADM_LABOR_TICKET.HOURS_WORKED = forms!ticketBrowser!HOURS_EARNED.value/0.9
WHERE (((SYSADM_LABOR_TICKET.TRANSACTION_ID) Like [forms]![ticketBrowser]![TRANSACTION_ID].[value]));

Here is the code behind the "Make 90%" button:

Private Sub Command17_Click()
Me!HOURS_WORKED.Value = Me!HOURS_EARNED.Value / 0.9
DoCmd.Requery
DoCmd.SetWarnings False
DoCmd.OpenQuery "updateVisTicket"
DoCmd.SetWarnings True
End Sub

"updateVisTicket" is the query shown above. I have also attached a screenshot of the form.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[PWise]

for one
change query to

UPDATE SYSADM_LABOR_TICKET SET SYSADM_LABOR_TICKET.HOURS_WORKED = forms!ticketBrowser!HOURS_EARNED/0.9 WHERE SYSADM_LABOR_TICKET.TRANSACTION_ID = [forms]![ticketBrowser]![TRANSACTION_ID];

 

[dhookom]

Aren't you updating the value twice with

Me!HOURS_WORKED.Value = Me!HOURS_EARNED.Value / 0.9
'...
DoCmd.OpenQuery "updateVisTicket"

Duane
Hook'D on Access
MS Access MVP

 

[BTilson]

@dhookom

Yes, you are absolutely correct. The whole reason this came about was because I was pulling data from our main database to the local access app in the interest of speed. For some reason, all but the simplest queries bog our main DB down like crazy. Then, this morning, my boss came to me and requested that the local changes be written back to the main DB, which was not previously a requirement. The idea of using these two lines is that the first one updates the fields in the local database, while the query I run applies those same changes to the main remote DB.

@pwise

Thank you! I didn't realize that that would make such a difference. The query went from taking maybe 10 seconds to run to being virtually instantaneous.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[dhookom]

I would do all of this with code like:

Private Sub Command17_Click()
    Dim strSQL as String
    ' set SQL statement to update remote db 
    'assuming TRANSACTION_ID is numeric
    strSQL = "UPDATE SYSADM_LABOR_TICKET " & _
        " SET HOURS_WORKED = " & Me.HOURS_EARNED/0.9 & _
        " WHERE TRANSACTION_ID = " & Me.TRANSACTION_ID
    Me!HOURS_WORKED.Value = Me!HOURS_EARNED.Value / 0.9
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[BTilson]

Excellent! Works perfectly.

Thanks so much for your patience and help!

Brooks Tilson
Database Development
Tilson Machine, Inc.