Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

AC2007 - Table Relationship for Form


(!)

vmf's Avatar
vmf vmf is offline
Computer Specs
Member with 25 posts.
THREAD STARTER
 
Join Date: Apr 2012
Location: Texas, USA
Experience: Beginner
22-Jul-2012, 06:20 AM #1
AC2007 - Table Relationship for Form
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,615 posts.
 
Join Date: Mar 2005
Location: UK
22-Jul-2012, 11:44 AM #2
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.
__________________
OBP
I do not give up easily
vmf's Avatar
vmf vmf is offline
Computer Specs
Member with 25 posts.
THREAD STARTER
 
Join Date: Apr 2012
Location: Texas, USA
Experience: Beginner
22-Jul-2012, 08:50 PM #3
Thank you OBP. If I want to use the VBA method, what would my code look like?
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,615 posts.
 
Join Date: Mar 2005
Location: UK
23-Jul-2012, 04:55 AM #4
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's Avatar
vmf vmf is offline
Computer Specs
Member with 25 posts.
THREAD STARTER
 
Join Date: Apr 2012
Location: Texas, USA
Experience: Beginner
23-Jul-2012, 09:47 AM #5
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,615 posts.
 
Join Date: Mar 2005
Location: UK
23-Jul-2012, 10:43 AM #6
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's Avatar
vmf vmf is offline
Computer Specs
Member with 25 posts.
THREAD STARTER
 
Join Date: Apr 2012
Location: Texas, USA
Experience: Beginner
23-Jul-2012, 02:45 PM #7
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's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,615 posts.
 
Join Date: Mar 2005
Location: UK
26-Jul-2012, 05:14 AM #8
Can you post a copy of the database with some dummy data in it?
vmf's Avatar
vmf vmf is offline
Computer Specs
Member with 25 posts.
THREAD STARTER
 
Join Date: Apr 2012
Location: Texas, USA
Experience: Beginner
04-Aug-2012, 08:26 PM #9
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
File Type: docx Relationships.docx (141.8 KB, 15 views)
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑