Update current record without creating a new record

[Marie851]

I am new to access and I am sure I am missing something simple. I am creating a time task tracker and I have everything working the problem I am having is when the Start Time command button is clicked it logs everything like it is suppose to, but it creates a new line. I need it not to create this new line because when you click the command button to Stop Time it logs it on the new line. I need it log the Stop Time on the same record in the Stop Time field. The below is the code.



I tried to attach the database it wouldn't let me.

Can anyone help me with this? Please.

Private Sub cmbStartTime_Click()

   Dim CountTime As Long
   CountTime = DCount( Employee", "qryClockIn_ChckSysIN_1")
   
   'Dim CountTime2 As Long
   'CountTime2 = DCount( Employee", "qryClockIn_ChckSysIN_2")

   If (CountTime > 0) Then
      MsgBox "You need to complete previous Clock Out", vbOKOnly

   ElseIf (CountTime2 > 0) Then
      MsgBox "You need to complete previous Clock Out", vbOKOnly

   ElseIf IsNull(Me.cmbxEmply) = True Then
      MsgBox  Select Employee from drop down list!", vbOKOnly

   Else

      Me.frmClckIn_SUB.SetFocus
      Me.frmClckIn_SUB!Time_Start.SetFocus
      DoCmd.GoToRecord , , acNewRec
      'Me.frmClckIn_SUB!Employee = Me.cmbxEmply
      Me.frmClckIn_SUB!Time_Start = Now
      Me.frmClckIn_SUB!Task = cmbxTask
      MsgBox "You have successfully clocked in at " & Now(), vbOKOnly
      RunCommand acCmdSaveRecord
      Me.frmClckIn_SUB.Form Refresh
      Me.frmClckIn_SUB!Time_Stop.SetFocus
      'DoCmd.GoToRecord , , acFirst

      Me.Refresh

   End If
End Sub

Private Sub cmbStopTime_Click()
   Dim CountTime3 As Long
   CountTime3 = DCount("Employee", "qry_ClckOut_ChckSysOUT")

   If IsNull(cmbxEmply) = True Then
      MsgBox "Select Employee from drop down list!", vbOKOnly

   ElseIf (CountTime3 = 0) Then
      MsgBox "You have no open Clock In records. Please return to Clock In.", vbOKOnly

   Else

      If Me.Dirty Then Me.Dirty = False
      Me.Requery
      Me.frmClckIn_SUB!Time_Stop = Now()
      MsgBox "You have successfully clocked out at " & Now(), vbOKOnly
      Forms!frmClockin.frmClckIn_SUB.SetFocus
      'Forms!frmClockin.frmClckIn_SUB!cmbxEmply.SetFocus
      DoCmd.GoToRecord , , acFirst
      Forms!frmClockin.frmClckIn_SUB.Requery
      Forms!frmClockin.frmClckIn_SUB.Form.Refresh

   End If

End Sub

[RuralGuy]

Run the Compact and Repair on your db and then zip it up. There is a 2MB limit to ZIP files here. It should then let you attach it to a post.

[Marie851]

Run the Compact and Repair on your db and then zip it up. There is a 2MB limit to ZIP files here. It should then let you attach it to a post.

Its not the size, when I click the manage downloads a window pops up and just spins.

[June7]

Probably the Refresh action of the ClockIn procedure causes focus to move to first record or if this form is set for Data Entry Yes then the Refresh causes form to refilter the recordset and the record just created with ClockIn is no longer available.

I am not seeing any code that finds and moves to the existing record.

[RuralGuy]

Its not the size, when I click the manage downloads a window pops up and just spins.

What browser are you using?

[Micron]

I think your method is the problem. For the stop time, you will have to run an update query that will update the table field with the stop time for the record you want.
Something like UPDATE tblYourTable SET tblYourTable.YourField = Now() WHERE tblYourTable.EmployeeField = cmbxEmply AND...;
I suspect you need more WHERE criteria, else you'd update all the records in the table for that person, which is not what you want. So you MUST test suggestions like this on copies of your tables or database. As an aside, you have redundant code AFAIC;
- it doesn't look to be necessary to refresh right after requerying
- setting Dirty to False will save the record, so ensure there is a need to refresh/requery (not sure if it's a multi record form or if you're just going to close it anyway).
- why set focus for a form, then a control, for a form that already has the focus (Me)?

[Marie851]

My browser is Chrome

[Marie851]

Thank you .. I will give your suggestions a try. I am new at access and self taught with vba, I know enough vba to accomplish what iI need in most cases :-\

[RuralGuy]

Its not the size, when I click the manage downloads a window pops up and just spins.

Testing the "Manage Attachments". I just downloaded and installed Chrome and the Manage Attachments seem to work as I would expect. Do you have the latest updates? I have [Version 47.0.2526.111 m] and I'm posting this with chrome.

[Marie851]

Testing the "Manage Attachments". I just downloaded and installed Chrome and the Manage Attachments seem to work as I would expect. Do you have the latest updates? I have [Version 47.0.2526.111 m] and I'm posting this with chrome.

it worked.. thank you .. here is the database I created. I have tried prior suggestions, with the exception of creating a qry to go to the current record (not sure how to do that).

[June7]

You wouldn't build a query to find existing record, you run code to do that. Or use the intrinsic search/filter tools.

I would have a single form in Continuous view and arrange data controls to look like Datasheet. Controls to input search criteria must be UNBOUND and would be in the form header section. Here is example of a form with search feature http://www.allenbrowne.com/ser-62.html

[RuralGuy]

Marie: I have some questions. Both your mainForm and the SubForm are using the same table as their RecordSource (not a good idea). To top that off, the MainForm ComboBoxes (cbos) are bound to field in that table. If you want to use the cbos to do lookups then they should be *unbound*. You can use the table as the RecordSource for both forms but thay should both be queries of the table. You're spending a lot of time and code changing the fields on the SubForm when you have the MainForm sitting on that very record.

[Marie851]

Marie: I have some questions. Both your mainForm and the SubForm are using the same table as their RecordSource (not a good idea). To top that off, the MainForm ComboBoxes (cbos) are bound to field in that table. If you want to use the cbos to do lookups then they should be *unbound*. You can use the table as the RecordSource for both forms but thay should both be queries of the table. You're spending a lot of time and code changing the fields on the SubForm when you have the MainForm sitting on that very record.

I apologize, I am very new at this. I am more of an excel person. I have an excel task tracker that my employees use, it's a user form that writes back to an excel workbook (pretty simple). My boss wanted it moved to access (she sent me to classes and they were just very basic stuff) and wanted my employees to see their start and stop times that is why I put the table on the form. I did not know a query could update a table, but I will give it a try. thank you for your time.

[RuralGuy]

No reason to apologize, I just wanted to know your plan. You don't really need a SubForm to do what you are trying to do. Just put the two *unbound* comboboxes in the header of the form and display the rest of the query in the Detail section of the form.

[Marie851]

thank you, I will let you know if this works.