Combo box / Not in list

[Guest]

I have a form w/subform on it in Access 2000. My PK is MedicalRecord, with
two text boxes Lname and Fname. On the subform I have VisitDate, Physician,
etc. What I am trying to accomplish is to have a combobox lookup on the main
form and if the combobox does not find the record, it will ask the user if
they want to add the record. When the user selects yes, a new form will
appear with the MedicalRecord.

Here is what I have done:
I have three text boxes on my main form w/one combo box. Textboxes:
txtMedicalRecord, txtLname, txtFname. I used the wizard to create the look up
combo box, cboMedicalRecordSearch. When I used the wizard, it gave me code
for the look up which is in the afterupdate field:

Private Sub cboMedicalRecordSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CMSLName] = '" & Me![MedicalRecord] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Here is what I have for NotInList
Private sub cboMedicalRecordSearch_NotInLIst( _
Newdata as string, Response as integer)
Dim strmsg as string
Dim rst as DAO.recordset
Dim db as DAO.database

strmsg = " ' " & Newdata & " 'is not in the list."
strmsg = strmsg & "Would you like to add it?"
If vbno = msgbox(strmsg, vbyesno +vbquestion, _
"New Patient") then
Response = acdataerrdisplay
else
set db = currentdb()
set rst = db.openrecordset ("tbldemographics")
rst.addnew
rst("MedicalRecord") = Newdata
rst.update
response = acdataerradded
rst.close
end if
end sub


Where and what would I add in order to add a new record when the user select
"yes" to add that new record? Hope that this makes sense. Thank you in
advance.

Ryan

 

[Guest]

I think you need to requery your form - Me.Requery
Put it as the last statement in your NotInList code

 

[Guest]

Klatuu,
The form is not adding a "new record". Still not sure what I need to do to
accomplish this.

Thank you, Ryan W

I think you need to requery your form - Me.Requery
Put it as the last statement in your NotInList code

I have a form w/subform on it in Access 2000. My PK is MedicalRecord, with
two text boxes Lname and Fname. On the subform I have VisitDate, Physician,
etc. What I am trying to accomplish is to have a combobox lookup on the main
form and if the combobox does not find the record, it will ask the user if
they want to add the record. When the user selects yes, a new form will
appear with the MedicalRecord.

Here is what I have done:
I have three text boxes on my main form w/one combo box. Textboxes:
txtMedicalRecord, txtLname, txtFname. I used the wizard to create the look up
combo box, cboMedicalRecordSearch. When I used the wizard, it gave me code
for the look up which is in the afterupdate field:

Private Sub cboMedicalRecordSearch_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CMSLName] = '" & Me![MedicalRecord] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Here is what I have for NotInList
Private sub cboMedicalRecordSearch_NotInLIst( _
Newdata as string, Response as integer)
Dim strmsg as string
Dim rst as DAO.recordset
Dim db as DAO.database

strmsg = " ' " & Newdata & " 'is not in the list."
strmsg = strmsg & "Would you like to add it?"
If vbno = msgbox(strmsg, vbyesno +vbquestion, _
"New Patient") then
Response = acdataerrdisplay
else
set db = currentdb()
set rst = db.openrecordset ("tbldemographics")
rst.addnew
rst("MedicalRecord") = Newdata
rst.update
response = acdataerradded
rst.close
end if
end sub


Where and what would I add in order to add a new record when the user select
"yes" to add that new record? Hope that this makes sense. Thank you in
advance.

Ryan