1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

AC2007 - Table Relationship for Form

Discussion in 'Business Applications' started by vmf, Jul 22, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. vmf

    vmf Thread Starter

    Joined:
    Apr 26, 2012
    Messages:
    25
    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.
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,063
    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.
     
  4. vmf

    vmf Thread Starter

    Joined:
    Apr 26, 2012
    Messages:
    25
    Thank you OBP. If I want to use the VBA method, what would my code look like?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,063
    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?
     
  6. vmf

    vmf Thread Starter

    Joined:
    Apr 26, 2012
    Messages:
    25
    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.
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,063
    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.
     
  8. vmf

    vmf Thread Starter

    Joined:
    Apr 26, 2012
    Messages:
    25
    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.
     
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,063
    Can you post a copy of the database with some dummy data in it?
     
  10. vmf

    vmf Thread Starter

    Joined:
    Apr 26, 2012
    Messages:
    25
    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.
     

    Attached Files:

  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1062112