Hi Amadea,
If I understand you properly, you want to use one form to view the data from the table and to add new information? Is your form based on InstrName table?
if you have the form showing all instructors' details and combo box where you select the instructor to view his data, I suggest you to add a button for adding new record to the InstrName and after update event to the category combo.
I will try to "draw" it
Form with record source based on InstrName
----------------------------------------------------------
__________________
Select Instructor: |________________|>|
_________ ____________
Last Name: |_________| First Name:|____________|
_________ _________________
Category: |_________|>| |Add New Instr Btn|
|_________________|
| Save Btn |
|_________________|
-----------------------------------------------------------
Combo Select Instructor based on the InstrName table as well and show all details, hope it is clear.
AfterUpdate event for Select Instructor is finding the target instructor and shows details in fields:
--------------------------------------------------
Private Sub SelectInstructor_AfterUpdate()
On Error Resume Next
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![SelectInstructor], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
Onclick event for the Add Button:
-----------------------------------------------
Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click
End Sub
You will not need to clear boxes and run query to add the record.
And checking for the integrity of data ( I will take your code and modify it
) Just don't forget to add error checking
You could put it to form event or save button click event
Private Sub Form_BeforeUpdate(Cancel As Integer)
' Check for missing required data
If IsNull(Me!txtInstr) Then
If MsgBox("Please enter an instructor name abbreviation." _
& Chr(13) & Chr(10) _
& "Example: EINSTEIN A A" _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbOk Then
CANCEL = true
Exit Sub
End If
ElseIf IsNull(Me!txtLName) Then
If MsgBox("Please enter the instructor last name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required selection is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
ElseIf IsNull(Me!txtFName) Then
If MsgBox("Please enter the instructor first name." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
ElseIf IsNull(Me!cboCategory) Then
If MsgBox("Please select the instructor category." _
& Chr(13) & Chr(10) & Chr(10) _
& "Press 'OK' to return and enter text." _
& Chr(13) & Chr(10) _
& "Press 'Cancel' to close session.", _
vbOKCancel, "A Required field is blank.") = _
vbOk Then
Cancel = true
Exit Sub
End If
'If user pressed cancel, undo all typing
cancel = true
me.undo
'After then here you could put your actions like go to the first record or last or whatever
End Sub
Private Sub Form_AfterUpdate()
msgbox "Added successful"
if me.combocategory = "F" then
'Add your second query here
.......
end if
End Sub
To activate form before and after update events you need to save the record, so you could save it after the last item or put the event to the save button
docmd.runcommand accmdsaverecord
Ok, if it is interesting but not clear let me know
