Combo Box, Not In List event, Access 2010 (1 Viewer)

[cluendo]

Hello colleagues,

I'm struggling with Combo Box and Not In List event. I want to be able to "add new record of First Name and Last Name if it is not in the list.
Here is the picture of how my form looks:

I have two tables: "Patients" and "Visit" - these are related as one to many(one patient can have many visit dates)
I also have Two forms - "Main" and "Patient_ex" .

So far my code for the Not In List event is:

Private Sub Combo19_NotInList(NewData As String, Response As Integer)
      
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim Msg As String
Dim NewID As String

On Error GoTo Err_combo19_NotInList

    ' Exit this subroutine if the combo box was cleared.
    If NewData = "" Then Exit Sub

    ' Confirm that the user wants to add the new customer.
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
        ' If the user chose not to add a customer, set the Response
        ' argument to suppress an error message and undo changes.
        Response = acDataErrContinue
        ' Display a customized message.
        MsgBox "Please try again."
    Else
        ' If the user chose to add a new customer, open a recordset
        ' using the table.
        Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Patients", dbOpenDynaset)
        ' Create a new record.
        Rs.AddNew
        ' Save the record.
        Rs.Update

        ' Set Response argument to indicate that new data is being added.
        Response = acDataErrAdded

    End If

Exit_combo19_NotInList:
       Exit Sub
Err_combo19_NotInList:
       ' An unexpected error occurred, display the normal error message.
       MsgBox Err.Description
       ' Set the Response argument to suppress an error message and undo
       ' changes.
       Response = acDataErrContinue


End Sub

As you can see in the picture, the code half works.
It doesn't save the new record with values Last Name and First Name.

Moreover I do not want to open a new form in order to add the new record details, but instead I would like to be able to add the new record details in the same form as the combo box(as you see in the picture).

my goal is:
1. When I enter new Name, Access detects that it is not in the list (Quite working)
2. If the name is NOT in the list, then the Values I entered (Last and first name) goes to the Text Field in the Patient_ex form so that I would no need to enter them again. Then I could update the new entered record and save it.(saving buttons works).

Guys, please, help me! I really struggling with VB...

Also, can I achieve same thing using only macros? (Access 2010)

Thank you!

 

[cluendo]

By the way can I use Queries instead of the table in the combo box RowSource?
Because I assume it would be easier to save the two values(Last name and first name) If I would make the query which would contain "Patient Name" field with BOTH first and last name?

 

[cluendo]

Now I am wondering, maybe I don't event need to save the record straight away? Maybe it is better to pass the The values "First Name" and "Last Name" I've entered from the combobox to the Text Fields "First Name" and "Last name" and only then press the save button to save record?

The thing is I do not know how to pass the name "John Ellis", I mean how to separate Name and Last name and put them into different text fields?

I know that I need to use something like this, using openargs:

  Me.LastName = Left(OpenArgs, InStr(OpenArgs, " ") - 1)
 Me.FirstName = Mid(OpenArgs, InStr(OpenArgs, " ") + 1)

Who can advise me what to do ??

 

[cluendo]

Please, someone!

 

[vbaInet]

What you're attempting from a design perspective isn't ideal. How will you split a name like this:

Sir Harold Thomas Smith

?

 

[cluendo]

Thank you for the answer.
Yeah I know what you mean. But I couldn't think of another approach to this.

Do you have any ideas?
If not, at least can you give me a hint or something how to make my method to work(just with the last name and first name) ?

Thank you very much!

 

[vbaInet]

Before I can advise, what does your combo box actually list?

 

[cluendo]

My ComboBox lists FirstName and LastName from the table "Patients".
RowSource looks like this:

SELECT Patients.ID, Patients.[LastName] & " " & [FirstName] AS Expr1 FROM Patients;

 

[vbaInet]

Right, so you need two textboxes that you will use for entering the LastName and FirstName. Set focus to one of these textboxes in the NotInList event. Or re-direct the user to the Patients entry form.

 

[cluendo]

Thank you for your help.

But is it possible to pass the FirstName and LastName(a new entry which is not in the list) to pass to the text fields "First Name" and "Last Name", so that I would not need to enter them again?

I am not sure how to do that.

 

[vbaInet]

I have already given you an instance where it will not be possible so why bother pursuing it further? It's not a code issue, it's more a logical problem.

 

[cluendo]

Ok. I understood then.

So now which approach is better you think:
1. To open a new entry form when NotInList
or
2. To set focus text field "First Name" on the same form where combo box is ?

For the 2 version the code is:

Private Sub Combo19_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Patient_ex_NotInList
  
Dim intAnswer As Integer
  
intAnswer = MsgBox("New Patient detected. Do you want to add it to the database?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then

FirstName.SetFocus


Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_Patient_ex_NotInList:
Exit Sub
  
Err_Patient_ex_NotInList:
MsgBox Err.Description
Resume Exit_Patient_ex_NotInList


End Sub

But I get an error that "First name cannot be focused"

 

[vbaInet]

Open the data entry form for Patients.

 

[cluendo]

Ok, now my code looks like this:

rivate Sub Combo19_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Patient_ex_NotInList
  
Dim intAnswer As Integer
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
  
intAnswer = MsgBox("New Patient detected. Do you want to add it to the database?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
 [B][COLOR="Red"]Set Db = CurrentDb
        Set Rs = Db.OpenRecordset("Patients", dbOpenDynaset)[/COLOR][/B]
DoCmd.OpenForm "AddNew", , , , acFormAdd, acDialog, NewData
FirstName.SetFocus

[B][COLOR="red"]Response = acDataErrAdded[/COLOR][/B]
Else
[B][COLOR="red"]Response = acDataErrContinue[/COLOR][/B]
End If
  
Exit_Patient_ex_NotInList:
Exit Sub
  
Err_Patient_ex_NotInList:
MsgBox Err.Description
Resume Exit_Patient_ex_NotInList

End Sub

Can you check the bold lines in code I highlighted?
Do I need them, because I'm not sure.

the new "AddNew" form opens, I can save the records, but when I close the "AddNew" form I get the dialog box "New patient detected, add new?" again.

How to do that after I close the "AddNew" form, the combo box requeries and jumps to the newly added record?

Thank you for your help mate!

 

[vbaInet]

You need to refresh the combo box after you add the record to the AddForm. Use the Requery method of the combo box.

I don't know what you're doing with the recordset code so I don't see why you need it.

 

[cluendo]

Ok, but where to put the combo9.requery code?
And as I said before, when I save and close the AddNew form, the NotInList event occurs again.

 

[vbaInet]

And as I said before, when I save and close the AddNew form, the NotInList event occurs again.

Which is why I mentioned you should Requery the combo box after saving the data.

You requery it in the After Update event of the form.

Forms!FormName!ComboboxName.Requery

Amend the bits in red.

 

[cluendo]

Now I get the usual error when using requery:
"Run-time error '2118'"
"You must save the current field before you run Requery action"

My save button have this macro:

 

[vbaInet]

I can't see any macro. Convert your macro to code and paste it here.

 

[cluendo]

Ok I deleted my macro and made a code for save and close button:

Private Sub cmdSaveClose_Click()
On Error GoTo Err_Saveclose_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.Close

Exit_Saveclose_Click:
Exit Sub

Err_Saveclose_Click:
MsgBox Err.Description
Resume Exit_Saveclose_Click
End Sub

still same error...