423,688 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,688 IT Pros & Developers. It's quick & easy.

Access 2010 runtime error 3201 cannot add or change record

P: 13
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub CmdClockIn_Click()
  4. Dim strSQL As String
  5. Dim str_foundPW As String
  6. Dim dbsFloor As DAO.Database
  7. Dim rstTime As DAO.Recordset
  8.  
  9. Set dbsFloor = CurrentDb
  10. Set rstTime = dbsFloor.OpenRecordset("TblTimeSlip")
  11.  
  12. If Me.PasswordEntry & vbNullString = "" Then
  13.     MsgBox "You must provide your password.", vbOKOnly, "Required Data"
  14.         Me.PasswordEntry.SetFocus
  15.     ElseIf Me.PasswordEntry <> str_foundPW Then
  16.         MsgBox "Incorrect Password.", vbOKOnly, "Required Data"
  17. 'Create a new record because this will be the first entry done of the day in the table
  18.     ElseIf Me.PasswordEntry = str_foundPW Then
  19.         rstTime.AddNew
  20.             rstTime("EmpID") = "[EmpID] ='" & Me.UserNameSelect & "'"
  21.             rstTime("FName") = DLookup("[EmpFName]", "EmployeeInfo", strSQL)
  22.             rstTime("LName") = DLookup("[EmpLName]", "EmployeeInfo", strSQL)
  23.             rstTime("DateWorked") = Format(Now(), "mm/dd/yyyy")
  24.             rstTime("TimeIn") = time()
  25.             rstTime.Update
  26.         Exit Sub
  27. End If
  28.  
  29.  
  30. End Sub
  31.  
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
Attached Files
File Type: pdf Report1.pdf (100.2 KB, 276 views)
Aug 22 '13 #1
Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,272
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.
Aug 22 '13 #2

zmbd
Expert Mod 5K+
P: 5,285
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_employees] numeric(long), 1:M with tbl_employee
[timepunch_fk_punchtype] numeric(long), 1:M with tbl_punchtype
[timepunch_dateandtime] 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 whooooooooffffff 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.
Expand|Select|Wrap|Line Numbers
  1. '(Note: A lot of code is being omitted here i.e. the event header)
  2. '
  3. Dim zdb As DAO.Database
  4. Dim zpunchrs As DAO.Recordset 'punch recordset
  5. Dim zstrpunchtime As String
  6. 'more type classing as needed.
  7. '
  8. 'the on timer event has been updating the displayed time in
  9. 'the correct formating for data entry.
  10. 'so just to be sure that the time is "frozen" for this punch
  11. 'let's just store it for the moment and format it for 
  12. 'the time date field.
  13. zstrpunchtime = "#" & me.z_lbl_currentime.caption & "#"
  14. '
  15. 'code to validate that the controls are not empty
  16. 'set the focus back to that control if it is
  17. 'I'd use a for each loop checked against the control name
  18. '
  19. 'Call to the employee validation function
  20. 'returning true if the employee badge and pin are found
  21. 'in the correct pairing.
  22. '
  23. If fnc_employeegood Then
  24.    Set zdb = CurrentDb
  25. '
  26. 'now, note, we are only going to add a record;
  27. 'thus, there's no need to read the table into memory
  28. 'just look at the table, create the new record, and 
  29. 'get out.
  30. '
  31.    Set zpunchrs = zjdb.OpenRecordset("tbl_timepunch",dbOpenDynaset,dbAppendOnly)
  32.    '
  33.    'Add the new record
  34.     zpunchrs.AddNew
  35.     zpunchrs![timepunch_fk_employees] = _ 
  36.       me.z_cmbbx_employee.Value
  37.     zpunchrs![timepunch_fk_punchtype] = _
  38.       me.z_cmbbx_employee.Value
  39.     zpunchrs![timepunch_dateandtime] =  _
  40.       zstrpunchtime
  41.     zpunchrs.Update
  42. Else
  43.    Msgbox "Either the Employee Badge Number" & _
  44.       vbcrlf & " or the PIN entered is invalid" & _
  45.       vbcrlf & "If you think they are correct" & _
  46.       vbcrlf & "please contact any supervisor and" & _
  47.       vbcrlf & "the HR departement.", vbOKOnly+vbCritical, _
  48.       "Unable to Log Punch"
  49. End If
  50. '
  51. 'Clean up
  52. 'if you set it, clear it.
  53. 'if you open it, close it.
  54. If Not zjpunchrs Is Nothing Then Set zjpunchrs = Nothing
  55. If Not zjbrs Is Nothing Then Set zjbrs = Nothing
  56. '
  57. '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.
Aug 24 '13 #3

Post your reply

Sign in to post your reply or Sign up for a free account.