How do you get a form to post data from a text box to a specific record in a table

[MarkVenes]

OK I am a newbie, I am learning as I go and I more the most part I have been able to find my answers on the net within 2 to 3 hours. This one is stumping me.

I have time card form that when I have an employee that wants to log an event a form that I created called PINForm pops up and asks for their pin prior to performing the action that the button on the TimeCardForm is designed to do. The problem I am having is that the data entered into the PINForm only updates the first record in the Employees table. I have a combo bod that I use for the employee to find their record first, but the data still gets entered into the first record of the Employees talble no matter who is selected from the combo box on the TimeCardForm. Here is my code so far.

Whats the idea: In my head I have the program checking the pin number against a preset pin number in the "Employees" table. The TimeCardForm has its Record Source as the "Emloyees" table. The PINForm has it's Record Source as the "Employees" table as well.

This is where the form is called from to check the PIN number (right now since I am in testing phase I am not allowing it to complete it's function)

- From the TimeCardForm -

Private Sub StartNew_Click()
Dim dbs As Database
Dim UserName As String
Dim EmpID As Long
Dim CurDate As Date
Dim PIN As String
Dim PauseTime As Variant
Dim start As Variant

Set dbs = CurrentDb

' A value of 1 indicates that a time card has been initiated
If ([TimeCardStarted] = "1") Then
' Checks to see if the time card is for todays date
If ([TimeCardDate] = Date) Then
' When there is a time card started tells the employee what to do
MsgBox "You have already started a Time-Card for today!", , "Please Check Your Selection and Try Again"
' Prevents the sub from adding a new time card
GoTo Exists
Else: MsgBox "You Failed to Log-Out on your last work day!" & vbCrLf & "Please see the Office Manager" & vbCrLf & "Or you may not receive proper pay for the day!", , "See The Office Manager!"
GoTo AddTimeCard:
End If
End If
If ([TimeCardStarted] = "2") Then
GoTo AddTimeCard
End If

' Not sure why but this is the only place this works when an employee has not selected who they are from the combo box
MsgBox "Please select your Employee Number first", , "EMPLOYEE NUMBER REQUIRED"

GoTo Exists

AddTimeCard:

DoCmd.OpenForm "PINForm", acNormal, "", "", , acNormal
' Needed time for the data to be recognised
PauseTime = 7
start = Timer
Do While Timer < start + PauseTime
DoEvents
Loop
' Not sure if needed but couldn't hurt
Me.Refresh
If [PINNumber] = [PINCheck] Then MsgBox "it did work" ----- Error checking here


' trying to reset the data entered from the PINform to something other than their actual pin number
[PINCheck] = "9999"

' this goto gets deleted once I figure out the problem
GoTo Exists

DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO EmpTimeCardsTbl(EmpID,UserName,InForTheDay,CurDate ,EmpLast,EmpFirst)VALUES (EmpID,UserName, Now(),date(),LastName,FirstName);"
DoCmd.Hourglass False
DoCmd.SetWarnings True
Me.TimeCardStarted = "1"
[TimeCardDate] = Date
Me.Refresh

Exists:

End Sub


This is what I have from the PINForm

Option Compare Database

Private Sub Text0_AfterUpdate()
Me.Refresh
DoCmd.Close
End Sub

Any ideas? I had been working with Dlookup for a while and was so F'n confused that I went a different route.

I am sure it probably something in the book on page 1291 that I have not gotten to yet, but hey if you have a moment and you do not mind giving me some advice - great.

[June7]

Need to concatenate variables in the sql statement. Reference to form control is variable. Example showing number, text, date values:

CurrentDb.Execute "INSERT INTO tablename(numberfieldname, textfieldname, datefieldname) VALUES(" & Me.comboboxname.Column(0) & ", '" & Me.comboboxname.Column(1) & "', #" & Me.comboboxname.Column(2) & "#)"

Combobox column index begins with 0 so if the data is column 1, the index is 0.

With CurrentDb.Execute don't have to SetWarnings.

[MarkVenes]

What I need to do is Update a field in the employees table with data typed into the unbound text box from the PINForm WHERE the EmpID from the Employees table is equal to the EmpID that is currently displayed in the combo box on the TimeCard Form. MY latest attempt has been this: Since the PINForm Source record is the Employee table it should recognize that the EmpID at the end of the statement as referring to the EmpID in the Employee table? Yes? So I have the get the statement to recognize the EmpID from the TimeCard form as the value to compare in the WHERE statement. The error message I am getting at this point is "Run time Error '424' Object required" I am thinking I have the Forms!TimeCards!EmpID format wrong or something.

Private Sub Text0_AfterUpdate()
dbs.Execute "Update Employees set PINCheck = '" & Text0 & "' WHERE Forms!TimeCards!EmpID = EmpID ;"
Me.Refresh
DoCmd.Close
End Sub

Need to concatenate variables in the sql statement. Reference to form control is variable. Example showing number, text, date values:

CurrentDb.Execute "INSERT INTO tablename(numberfieldname, textfieldname, datefieldname) VALUES(" & Me.comboboxname.Column(0) & ", '" & Me.comboboxname.Column(1) & "', #" & Me.comboboxname.Column(2) & "#)"

Combobox column index begins with 0 so if the data is column 1, the index is 0.

With CurrentDb.Execute don't have to SetWarnings.

[MarkVenes]

OPPS the Test Box on the PINForm is bound to the PINCheck field of the Employees table and here are my newest attempts. OH and the TimeCards form is open and the Combo box has the proper EmpID selected and displayed on the form.

' BAD dbs.Execute "Update Employees set PINCheck = Text0 WHERE Forms!TimeCards!EmpID = EmpID ;"
' BAD dbs.execute "Update Employees set PINCheck = '" & Text0 & "' WHERE Forms!TimeCards!EmpID = EmpID ;"
' BAD dbs.Execute "Update Employees set PINCheck = '" & Text0 & "' WHERE Forms!TimeCards!EmpID.value = EmpID ;"
' BAD dbs.Execute "Update Employees set PINCheck = '" & Text0 & "' WHERE Forms!TimeCards!EmpID!Value = EmpID ;"

All still give me the ERROR Object Required.

Now I am thinking I need to use some kind of Dlookup or Select statement first any ideas? Also should I unbind the text filed from the PINCheck field?

[MarkVenes]

OK I figured it out but only partially. All the above was over thinking. In my original form the doCmd.Openform statement needed to direct the form to input the data into a specific record. Now of course I am having trouble with the WHERE statement Here is what I have and it is not working yet.

DoCmd.OpenForm "PINForm", acNormal, , "EmpID =" & EmpID, , acNormal

I will keep trying to figure out the syntax it seems to be the solution but I am not a programmer yet. Some day I hope to be.

[June7]

Where is this OpenForm code executed? You do show proper concatenation in OpenForm statement but where is the EmpID criteria coming from?

The UPDATE syntax in the prior post does not show concatenation of criteria. The correct syntax would be:
dbs.Execute "UPDATE Employees SET PINCheck = '" & Text0 & "' WHERE EmpID=" & Forms!TimeCards!EmpID

Value is default property of data controls and therefore is not necessary to type. Also, would be .Value not !Value.

I just reviewed the op again. I am confused by:
The TimeCardForm has its Record Source as the "Emloyees" table. The PINForm has it's Record Source as the "Employees" table as well.
Why are these two forms bound to same table? Both forms are open at same time?

Want to provide db for analysis? Follow instructions at bottom of my post.

Link at bottom of my post is a good tutorial on debugging techniques.

[MarkVenes]

Want to provide db for analysis? Follow instructions at bottom of my post.

Link at bottom of my post is a good tutorial on debugging techniques.[/QUOTE]


WOW, that is a really generous offer, and at some point I may take you up on it. I am working 8 to 10 hours a day learning MS Access 2010 by doing and searching for the answers to all my questions as they arrive. I learn better that way. So far I have 13 tables, 4 Queries, and 25 forms including one Navigation form. I am 100% certain that I have made over 100 errors in DIM statements, that I have not Concatenated as I should have, but everything is working as I want it to so far. I have been able to fix syntax errors even if it has taken me four hours for one stupid line of code. I have probably done a lot of newbie mistakes, but as time goes on I am sure I will learn enough to fix some of them myself. When the time is right, and I am sort of proud of my work I would be honored to have you take a look at it. I expect you will probably groan, and say "This is all wrong" and I will not take it personally. I am just learning after all.

Thank you again for your generous offer. I will be taking you up on it some time in the future.

Mark