Not In List Event

[daklem]

I have a subform which shows demographics for a physician associated with a patient on the main form. There is a combo box to select a physician's name which then fills in the physician's demographics if the physician already exists. If a new name is entered in the combo box, the NotInList event opens a physician entry form to add the new physician's information. I would like to have the form open to a new record and add the data typed in the combo box to the Last Name field. The code I have opens the form to the first record of the table. How would I modify it to open to a new record and add the name.

Here is the code.

Private Sub cboPhySelector_NotInList(NewData As String, Response As Integer)

Dim dbs As Database
Dim rst As DAO.Recordset
Dim strMsg As String
Dim strTitle As String

strTitle = "Physicians"
strMsg = "Would you like to add " & NewData & " to the " & strTitle & "?"
Response = MsgBox(strMsg, vbOKCancel, strTitle)
If Response = vbOK Then
DoCmd.OpenForm "frmPhyEntry"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPhysicians&quot
rst.AddNew
rst![PhyLastName] = NewData
rst.Update
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If

End Sub


I have tried this:

DoCmd.OpenForm "frmPhyEntry", , , , acFormAdd

and the form opens to a new record but the data entered by the user does not get inserted.

Thanks

 

[Eupher]

Opening the form with the acFormAdd argument is the right way to open to a new record. To add the physician's last name, add this line of code:

Forms!frmPhyEntry!PhyLastName = Me!cboPhySelector

Now, having said that, I'm concerned about your NotInList code. It looks like it's adding a new record to your physicians table, then after that, you want to open your data entry form to add a new record. Double whammy. If you do it that way, you'll end up with 2 records for that physician (although the first one will only have a last name entered). So I would either:

1) Keep the code that adds the physician's last name, then open the data entry form, filtered to that last name to complete entry of the record - possible problem here is you may have more than one physician with the last name, so simply filtering on the last name might return multiple records.

2) Scrap the code that opens the recordset and adds the physician's last name, just use the OpenForm command and the line of code above to open your form to a new record with the new last name automatically entered, then requery your combo when closing the data entry form so the new name will show up in the list.

HTH...

Ken S.

 

[daklem]

Thanks Ken,

Let me be a little more specific. There are 3 tables:

tblPatients
PatientID (auto) (PK)
PtLastName
PtFirstName

tblPhysicians
PhysicianID (auto) (PK)
PhyLastName
PhyFirstName

tblPtPhyDetail
DetailID (auto) (PK)
PatientID (FK)
PhysicianID (FK)

Both the Patient and Physician tables are associated with the PtPhyDetail table in a one to many relationship. This allows each patient to have many physicians (different types) and each physician to have many patients.

The Main form is Patient demographics with a sub form for physicians. The physician subform is frmPtPhyDetail where the cboPhySelector is placed and another subform based on tblPhysicians. This way when a physician is selected from the combo box his information is filled in on the form and the tblPtPhyDetail data is entered in the table automaticaly.

What was suggested in the above response by Eupher does open the appropriate form to a new record and it enters data in the last name field but it is the PhysicianID number that is added not his last name. This happens because the bound column of the combo box is set to the PhysicianID as that is what I want stored in the tblPtPhyDetail.

Any other suggestions. Or is there a better way to set up the forms?

 

[Eupher]

You can use column notation to insert the physician's last name from the combo. The physician ID is the first column in the combo, but it's hidden (i.e. Column Width of 0), right? So change the code to:

Forms!frmPhyEntry!PhyLastName = Me!cboPhySelector.Column(1)

Remember that column notation is zero-based, so the first column (the hidden ID column) is Column(0), the second is Column(1) and so on.

HTH

Ken S.

 

[daklem]

Thanks Ken,

Sounds like that might work. I am not at my database right now but I will try your suggestion and get back to you.

 

[daklem]

Doesn't work right. This is what I have right now:

Private Sub cboPhySelector_NotInList(NewData As String, Response As Integer)
strTitle = "Physicians"
strMsg = "Would you like to add " & NewData & " to the " & strTitle & "?"
Response = MsgBox(strMsg, vbOKCancel, strTitle)
If Response = vbOK Then

DoCmd.OpenForm "frmPhyEntry", , , , acFormAdd
Forms!frmPhyEntry!PhyLastName = Me!cboPhySelector.Column(2)
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If

End Sub

I use column(2) because the Last Name is in the third column.(the second column is an expression using LastName & "," & FirstName)

What happens is when a new name is entered in the cboPhySelector, the frmPhyEntry opens, however, there is nothing in the last name text box and the built in Access error message stating "enter a item in the list" pops up. After closing the error message, a name can be entered in the frmPhysicianEntry and the form can be closed, but the new entry does not show up in the combo box untill the main form is closed and then re-opened.

 

[Eupher]

Okay, let's sort this out...

First, you're taking the Response argument and using it as your MsgBox variable, probably not a good idea. Second, I think you must add code to put the new item in the combo list, if only temporarily, else the error message will fire again. Then, once you're done entering all the particulars in the data entry form, you should requery the combo to reflect the new data. So...

Private Sub cboPhySelector_NotInList(NewData As String, Response As Integer)
    strTitle = "Physicians"
    strMsg = "Would you like to add " & NewData & " to the " & strTitle & "?"
    If MsgBox(strMsg, vbYesNo, strTitle) = vbYes Then
        Me!cboPhySelector.RowSource = "SELECT PhysicianID, PhyLastName & ', ' & PhyFirstName " _
        & "AS Name, PhyLastName FROM tblPhysicians " _
        & "UNION SELECT '" & NewData & "', '" & NewData & "', '" _
        & NewData & "' FROM tblPhysicians;"       
        DoCmd.OpenForm "frmPhyEntry", , , , acFormAdd
        Forms!frmPhyEntry!PhyLastName = Me!cboPhySelector.Column(2)
        Response = acDataErrAdded
    Else
        Me!cmbPhySelector.Undo
        SendKeys "{F4}"     'optional, if you don't want the combo to automatically dropdown
        Response = acDataErrContinue
    End If
End Sub

Notice in the UNION query you have to have an equal number of fields on both sides of the union, so the NewData field is referred to 3 times.

Then in the close event of the frmPhyEntry form, you should test whether your first form is still open, and if it is, reset and requery the combo's row source:

Private Sub Form_Close()
If Application.CurrentProject.AllForms!frmPhySelector.IsLoaded Then
    Forms!frmPhySelector!cboPhySelector.RowSource = "SELECT PhysicianID, " _
        & "PhyLastName & ', ' & PhyFirstName " _
        & "AS Name, PhyLastName FROM tblPhysicians " _
        & "ORDER BY PhyLastName & ', ' & PhyFirstName;"
    Forms!frmPhySelector!cboPhySelector.Requery
End If
End Sub

Give that a try and see if that's what you're looking for.

HTH...

Ken S.