Access 2010 runtime error 3201 cannot add or change record

[Rinis]

Keep in mind that I'm new to VBA with access. so try to break it down as much as possible.

I just got some fantastic help here earlier so I hope for the same luck. I'm doing a basic timeclock design. When the employee opens the form they are given a combobox (UserNameSelect ), a textbox (PasswordEntry) and 4 command buttons: cmdClockIn, CmdLunchOut, CmdLunchIn, CmdClockOut.

the person selects their username from a dropdown menu and proceeds to enter their password in the textbox. They click Clock in and it will automatically create a new record and fill in their employee id, first name, Last name, The date, and the time of clocking in.

This is where my error comes in. When it tries to update the database it throws the error Run-time error '3201':
You cannot add or change a record because a related record is required in table 'Employee Information'.

I'm very confused as to what this is getting at. There are no fields in the employee information table that are required. I'm wondering if it has to do with my relationships and the way they are setup. Anyway any insight possible is greatly appreciated and I'm sure its some simple thing I'm overlooking. Anyway here is my code:

Option Compare Database

Private Sub CmdClockIn_Click()
Dim strSQL As String
Dim str_foundPW As String
Dim dbsFloor As DAO.Database
Dim rstTime As DAO.Recordset

Set dbsFloor = CurrentDb
Set rstTime = dbsFloor.OpenRecordset("TblTimeSlip")

If Me.PasswordEntry & vbNullString = "" Then
    MsgBox "You must provide your password.", vbOKOnly, "Required Data"
        Me.PasswordEntry.SetFocus
    ElseIf Me.PasswordEntry <> str_foundPW Then
        MsgBox "Incorrect Password.", vbOKOnly, "Required Data"
'Create a new record because this will be the first entry done of the day in the table
    ElseIf Me.PasswordEntry = str_foundPW Then
        rstTime.AddNew
            rstTime("EmpID") = "[EmpID] ='" & Me.UserNameSelect & "'"
            rstTime("FName") = DLookup("[EmpFName]", "EmployeeInfo", strSQL)
            rstTime("LName") = DLookup("[EmpLName]", "EmployeeInfo", strSQL)
            rstTime("DateWorked") = Format(Now(), "mm/dd/yyyy")
            rstTime("TimeIn") = time()
            rstTime.Update
        Exit Sub
End If


End Sub

I apologize for this being over 20 lines, but I wanted to make sure I got my entire elseif statement in. I omitted everything else that was working. Like I said at this point I'm wondering if my problem is even in my code or if it's related to my relationships. If it appears that the code is fine and it's something else in my database please let me know so I can go elsewhere for help in finding a solution.

If you happen to have any other suggestions please let me know I'm open to any and all ideas, though I don't want to have this thread go too far off topic. Once again any help or insight is greatly appreciated. Thank you

[Rabbit]

That error code means that you have linked the table TblTimeSlip to the table Employee Information. This means that the linked field's values must first exist in Employee Information before you can use those values in TblTimeSlip.

The error doesn't necessarily mean something is wrong with your VBA. You either linked the wrong field(s), used the wrong values in those fields, or you need to add a new record to Employee Information before you try to use it in TblTimeSlip.

[zmbd]

Rabbit is exactly correct on this.

Just for those that follow, this has a lot to do with Database Normalization and Table Structures.

The Kids are either outside or down for a nap so I have a little time to give you an example of how I would handle this... hopefully this will inspire you.

So, let's do something very simple here and note that I am omitting a lot of things here in that I have no real need of time clock at home :) :
PK = Primary Key
FK = Foreign Key

tbl_employees
[employees_pk] autonumber
[employees_badge] text(20), indexed, required, no duplicate
[employees_fname] text(20) required
[employees_lname] text(20) required
[employees_pin] text(8) required
[employees.... ] the remaining fields

tbl_punchtype
[punchtype_pk] autonumber
[punchtype_Text] text(20) required

tbl_timepunch
[timepunch_pk] autonumber
[timepunch_fk_em ployees] numeric(long), 1:M with tbl_employee
[timepunch_fk_pu nchtype] numeric(long), 1:M with tbl_punchtype
[timepunch_datea ndtime] date, required

Now the tbl_punchtype table isn't strictly needed; however, instead of command buttons, I'd use a combobox and this table as the recordsource for it. I'd also have just two values, IN and OUT; however, you might need to have a more detailed reason for the punches. Say you have a company policy where you need to be able to prove that the employees have taken their breaks or lunch, in which case, this design allows you to add such detail without having to re-write a ton of code.

I might make a binary index on the first and last names to prevent duplicated employee names... or add a third field form the middle intial/name. It is concevable to have 2 or more people named "John Smith" working for you; however, I doubt that the middle name would also be the same... what do I know... I'm a chemist and all of my stuff have unique names so that we don't unknowningly make stuff that goes booom or whoooooooofffff f in the lab. (note the unknowningly, sometimes the snap-n-pop is the fun part!)

The pin code I would store as either the MD5 or SHA2 digests you can find some information on these in the Bytes > Sitemap > Microsoft Access / VBA Insights documents.

The [employees_badge] is the company's current tracking (the company I work for is on it's 2nd such incarnation and if we get a new time clock system... more than likely a third).

The form would have: label with the current date and time, combobox for the employee badge, combobox for the punch type, a textbox for the pin, and finally a command button to commit the punch.
Note: The comboboxes would be my defacto lookup functions. These would have two columns with the first being the primary key for to the related table, and the second being the human readable. The first column would be bound to the control.
I would use the ontimer event to update the label with the current time every minute.
Events would happen as follows:
Employee makes the correct selections in the CBO, enters the Pin, and clicks the Punch CMDBTN.

THe CMDBTN would the have the following

Capture the currently shown time to local variable.
Call to the Sub module where the employee validation happens, I would use a function that returns a true/false
Then the logic to create a new record.

As this code in various forms is readily available all over the net, here is a simple code based on the above and DAO object:
NOTE: This is partially aircode. I have made no attempt to debug the following for syntax errors. Even then, I'm fairly certain that what code is here is correct and will function correctly when used in context.

'(Note: A lot of code is being omitted here i.e. the event header)
'
Dim zdb As DAO.Database
Dim zpunchrs As DAO.Recordset 'punch recordset
Dim zstrpunchtime As String
'more type classing as needed.
'
'the on timer event has been updating the displayed time in
'the correct formating for data entry.
'so just to be sure that the time is "frozen" for this punch
'let's just store it for the moment and format it for 
'the time date field.
zstrpunchtime = "#" & me.z_lbl_currentime.caption & "#"
'
'code to validate that the controls are not empty
'set the focus back to that control if it is
'I'd use a for each loop checked against the control name
'
'Call to the employee validation function
'returning true if the employee badge and pin are found
'in the correct pairing.
'
If fnc_employeegood Then
   Set zdb = CurrentDb
'
'now, note, we are only going to add a record;
'thus, there's no need to read the table into memory
'just look at the table, create the new record, and 
'get out.
'
   Set zpunchrs = zjdb.OpenRecordset("tbl_timepunch",dbOpenDynaset,dbAppendOnly)
   '
   'Add the new record
    zpunchrs.AddNew
    zpunchrs![timepunch_fk_employees] = _ 
      me.z_cmbbx_employee.Value
    zpunchrs![timepunch_fk_punchtype] = _
      me.z_cmbbx_employee.Value
    zpunchrs![timepunch_dateandtime] =  _
      zstrpunchtime
    zpunchrs.Update
Else
   Msgbox "Either the Employee Badge Number" & _
      vbcrlf & " or the PIN entered is invalid" & _
      vbcrlf & "If you think they are correct" & _
      vbcrlf & "please contact any supervisor and" & _
      vbcrlf & "the HR departement.", vbOKOnly+vbCritical, _
      "Unable to Log Punch"
End If
'
'Clean up
'if you set it, clear it.
'if you open it, close it.
If Not zjpunchrs Is Nothing Then Set zjpunchrs = Nothing
If Not zjbrs Is Nothing Then Set zjbrs = Nothing
'
'error traping code and the end module

Notice that I didn't record the actual employee name, badge number, nor the text for punch type in table tbl_timepunch? Instead, I stored the value for the PK from the tbl_employee and tbl_punchtype as a FK in the tbl_timepunch table. When we go to pull the information for a report or other reasons, we can refer to these tables and pull the required information.

As for the employee PIN... my company uses the employee badge as the PIN. We're supposed to keep this information to ourselves; however, in my position as one of the senior staff I know all of the employee badges as does any member of Management. Having a PIN I think is a really good idea, so long as it is not stored except as a digest as I noted earlier. MOST management types and I think people in general are trust worthy; however, people get angery with each other and other issues, and the next thing you know, people are making some poor choices and messing with the timecards to get others in trouble... seen it happen!

The remaining queries and code I leave for the user.