[vmf]

I need to track, for each work day, the time an employee arrives at work and the time he leaves so I created a table:

Name - PK
ProdDate - PK
ArrivalTime
DepartTime

That employee will have many records for the same ProdDate in another table that captures the detail of work done for that day and this list may be recorded on multiple data entry sheets.

I created a data entry form with the main form based on the Arrival/Departure table and a subform based on the detail table.

The data entry operator will enter the name, date arrival & departure times in the main form, then the detail in the subform.

My problem is that these data entry sheets may not be all entered for the same person at the same time, so once a record is entered in the main form, if additional detail records need to be entered at a later time that day, a primary key error is displayed because there is already a record for that name, date combination in the main table.

I was trying to avoid repeating arrival/departure times in my detail table but I have to be able to add records in the detail table for existing records in the Arrival/Departure table at different times during the day.

How should I set up my forms and relate my tables in order to accomplish this?

Thank you for your help.

[OBP]

Do you have a "Find" Combo?
You could use it with the Name and Date for the data entry user to quickly find name and date if it has already been entered.
The alternative is to have VBA code to check that the Name & Date as they are entered and have not already been entered and if they have take the user to the already entered record.

[vmf]

Thank you OBP. If I want to use the VBA method, what would my code look like?

[OBP]

You would need to open a recordset or a recordsetclone,
when the new data is being entered are all the previous records in the form, or just a new record?

[vmf]

In the Main form after the user enters the name and date, when he tabs to the next field, ArrivalTime, if there are records for that name/date combination, the records are displayed in the subform. When the user moves to the subform a primary key error is displayed.

Thank you for your help.

[OBP]

I think a Recordsetclone of the main form to check if the name exists would do it, you could get the basic VBA by creating a "Find" Combo, it creates the recordsetclone and uses the findfirst to find the record and then uses the bookmark to go to it.
You would just need to add some code to provide a message to say the name is already in the database and clear the entries before going to the record.

[vmf]

Hi OBP, thank you for your advice. I would love to implement this solution but my VBA skills are minimal so I will need more help... I did create a "Find" combo box and looked at the VB behind it:

Private Sub Combo44_AfterUpdate()
On Error GoTo Combo44_AfterUpdate_Err
DoCmd.SearchForRecord , "", acFirst, "[ProdDate] = " & "#" & Format(Screen.ActiveControl, "mm/dd/yyyy") & "#"

Combo44_AfterUpdate_Exit:
Exit Sub
Combo44_AfterUpdate_Err:
MsgBox Error$
Resume Combo44_AfterUpdate_Exit
End Sub

I really don't know how to proceed from here. I used the date & name combination when creating the Find combo box, but I don't see the name in the vb code, just the date. Would I put this code in the "OnExit" event of the date field? And could you provide an example of how to display an error message and clear the current form entries?

Thank you again for your help.

[OBP]

Can you post a copy of the database with some dummy data in it?

[vmf]

Hi OBP, I apologize for not replying to your latest post but I have been working so many hours that I haven't had a chance to do anything else with my database. I have attached a word doc with print screens of my relationships and form and the error message I am getting. I don't know if I have the relationships set up correctlty or not. We are currently using this database without the arrival and departure times and it works well. But now I need to start capturing work arrival and departure times so that an employees total hours at work can be compared to the time he actually spent producing product on a machine. Your suggestion of a recordset clone and finding a record sounds like a good solution, however, I don't know how to code it.
Thank you for your help.