Find Student ID beforeupdate (1 Viewer)

[Springs61]

Hi
I hope i have posted this correctly!
I urgently need help, after 5 days trawling Internet and playing with Dlookup I am no further forward.
Problem:
I have a table called Attendance were information about the Student and the class they are attending is collected.

Another table called Student, this hold information about the student.

I am using a Form called Attendance to input my information.
What I am trying to achieve is:
BeforeUpdate on a text box:
when you input Student_ID it looks at the Student Table to see if the Student ID is valid before the information is updated to the Attendance Table.
I can supply a copy of database.:banghead:

 

[R. Hicks]

I suggest the you use a combobox to select the student ...
This would stop any possible typos by the user.
You simply need to use a query as the Row Source for the combobox that includes the desired fields to be displayed for the user to see in the dropdown ...
I advise you to store the StudentID from the Student table instead of the students literal name in the Attendance table. You can return the student's literal name and any other information in the table for the specific student by including the student table in a query with the correct relational join ....

R.

 

[Springs61]

Hi R
Many thanks for your quick response, shortly after I posted the question I did exactly as you said and put in a combo box. It records the Student ID and shows the name, you cannot add anything and works perfectly.
Once again many thanks.
J

 

[R. Hicks]

No problem .. you are welcome ..

R.

 

[Springs61]

Hi R
Sorry to trouble you, I have my database working 99.5% the last bit I am struggling with. I want to check that all fields in the form input are filled in and that if someone changes the login time i can keep a hidden field showing the difference. I have the last bit working fine, my problem is combining the two together.

I have got a rough idea of the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
BeforeUpdate_Err:

confirm_pick = MsgBox("Please Check All Entries Are Complete! Yes will continue to next if data correct. NO will close Form and nothing will be saved!", vbYesNo, "Confirm?")
If confirm_pick = vbNo Then
Me.Undo
End If

On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "COMPLETE" Then
If Len(ctl & vbNullString) = 0 Then
MsgBox "Please complete all fields before saving this record.", _
vbInformation, "Missing Data"
Cancel = True
Exit Sub
End If
End If
Next ctl

' this from here down works perfectly.. it is trying to combine the above and below is my problem...

On Error GoTo BeforeUpdate_Err
' Set bound controls to system date and time.
DateModified = Date
TimeModified = Time()

BeforeUpdate_End:
Exit Sub
MsgBox Err.Description, vbCritical & vbOKOnly, _
"Error Number " & Err.Number & " Occurred"
Resume BeforeUpdate_End


End Sub