NotInList and 2 values ComboBox

[cluendo]

Hello, Access Users!

Situation:

I have Simply two tables - Patients and PatiensInfo. Also I have two forms with the same names.

So I use ComboBox in order to add not existing record. Everything seems ok, but here is a NotInList code:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
Dim strsql
Dim NewFirst As String
Dim NewLast As String
Dim SpacePosition As Integer

SpacePosition = InStr(NewData, " ")
If SpacePosition = 0 Then
        MsgBox "Your value has no First Name."
        Exit Sub
        End If
    NewLast = Trim(Left(NewData, SpacePosition - 1))
    NewFirst = Trim(Mid(NewData, SpacePosition + 1))
    
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, , , acFormAdd, acDialog

Forms![Patients]![First Name] = NewFirst
Forms![Patients]![Last Name] = NewLast



'strsql = "Insert Into Patients ([First Name], [Last Name]) values ('" & NewFirst & "','" & NewLast & "')"
'CurrentForm.Execute strsql, dbFailOnError

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

One question would be: how to make the form "Patients"(which will pop up as a dialog box to add new info about new record) have filled the values I entered in combo box, so I will not need to re-enter ?

Hint: there are two values(or maybe 3 as I do not know if [ID] counts as one) in the combo box - [ID] and [First Name] & ' ' & [Last Name].

 

[John Big Booty]

Welcome to the forum.

Perhaps the sample posted here will give you some pointers.

 

[cluendo]

Thank You very much for your reply!

Ok, I looked at yours code but it is not very clear for me as Im quite new in MS Access coding.

So what I did now in trying to solve my problem was:
I put this code on "On Load" event of Patients form:

Private Sub Form_Load()
Me!FirstName = Forms!Patients.OpenArgs
Me!LastName = Forms!Patients.OpenArgs
End Sub

Now, I get both of the values First and Last name in each of the [FirstName] and [LastName] fields!

I fell im very near to solve this, could you please help me as Im playing with this all day! I think I will hate Access for a while from now on lol ))

 

[John Big Booty]

OpenArgs can only hold one value. As you seem to be trying to pass both the first and last names to the new form in the OpenArgs, you will need to firstly concatenate them in then DoCmd.OpenForms command, and identify this and break them apart in the new form's On Load event.

So in your referring form, are the first and last names being entered in separate fields or the one field?

 

[cluendo]

Thanks for answer again! Yeah I just read about OpenArgs and got some idea

If You have a few seconds could You please look at my DB I attached.

There are 1 combobox which have two values FirstName and LastName
Im abit confused where and how to separate this String of value ? There is a space between like that: RawSource of ComboBox: [Last Name] & " " & [First Name]

Thanks!

 

[John Big Booty]

What version of Access is your DB? When I try to open it I get an error "Unrecognised format"

 

[cluendo]

Ohh crap...Im using 2010, and unfortunately I cannot save compatible to older versons

Ok here is a brief explanation on hows everything arranged:

Basically There are two Tables - one Patients and other PatientInfo. There are also two forms: Patients and PatientInfo.

Tables Patients and PatientInfo are related.

There is a ComboBox in PatientInfo Form which I use to enter Name and Last name of a patient. If a record exist I am allowed to enter following data in the SAME form - Attend Date, Price, etc.
If record is NOT existing, then NotInList event occurs(You can see the code above in the first post) and form Patients shows as Dialog. There I can enter new data related to the new record(Name, Last name, address, etc)

Everything Works well, but I can get Values I entered in combobox transferred to the Patient form's field [Last Name] and [First Name]


I hope You understood what I wrote

 

[John Big Booty]

Here's a partial solution, use the following code in the On Load event of second form;

    If Not IsNull(OpenArgs) Then
        DoCmd.GoToRecord acDataForm, "FRM_PatientName", acNewRec
        Me.PFName = Left(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
        Me.PLName = Right(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
    End If

This code will only work where the name has a single space in it. You will need to work a method for dealing will names such as Ronald Mc Donald or Jean Michel Jarre.

 

[cluendo]

Ok than You very much! It kind of worked! Now I have codes

NotInList:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
Dim strsql
Dim NewFirst As String
Dim NewLast As String
Dim SpacePosition As Integer

SpacePosition = InStr(NewData, " ")
If SpacePosition = 0 Then
        MsgBox "Your value has no First Name."
        Exit Sub
        End If
   ' NewLast = Trim(Left(NewData, SpacePosition - 1))
    'NewFirst = Trim(Mid(NewData, SpacePosition + 1))
    
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, OpenArgs, , acFormAdd, acDialog, NewData

'Forms![Patients]![First Name] = NewFirst
'Forms![Patients]![Last Name] = NewLast

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

Code OnLoad event of second form:

 If Not IsNull(OpenArgs) Then
        DoCmd.GoToRecord acDataForm, "Patients", acNewRec
        Me.LastName = Left(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
        Me.FirstName = Right(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
    End If

Now I confused with the buttons. There are two buttons - one is on the PatientInfo form which do basically Save and NewRecord function.

Other button is n the PupUp dialog form Patients. When I add new record I just click the button Save&Close. How to make the original form's combo box have that values ehich I entered in Dialog Patients form?

 

[cluendo]

Who can answer my question ?

 

[vbaInet]

Who can answer my question ?

It's only been a 3 hour gap between your posts and you realise it's a Sunday today (well at least in most countries). If you exercise some patience John will be here at some point today or tomorrow to answer your question.

Unless someone else wants to chime in.

 

[cluendo]

I know It is Sunday Anyway big Sorry for my impatience, I just need to finish somehow by today, so it is a big headache at the moment if u know what I mean..

 

[vbaInet]

I've attached an example I once did for someone on here which is pretty much what you're asking. It should be straightforward to follow.

 

[cluendo]

Thank You !

One problem, I only see "tblAccounts" in your DB. Maybe because im using Access 2010 ? If its the case, could you please copy the code of not in list event ? It would be very helpful!

I kind of near to finish the coding, but as Im quite new in it, I found alot of NotInList examples, but all of the are differently written in VB. So its quite hard to adapt to my requirements... It is strange how there are no example/sample databases of proper NotInList event with a bound dialog form. Is it not a common problem ?

 

[vbaInet]

Pasting the code won't help you. You need to see how it works to understand it.

You were seeing only the table because the Navigation Pane was set to display Tables only. You just needed to change it to All Access Objects. I've changed the filter so look at the attached again.

 

[John Big Booty]

Maker the highlighted change to your code;

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
Dim strsql
Dim NewFirst As String
Dim NewLast As String
Dim SpacePosition As Integer

SpacePosition = InStr(NewData, " ")
If SpacePosition = 0 Then
        MsgBox "Your value has no First Name."
        Exit Sub
        End If
   ' NewLast = Trim(Left(NewData, SpacePosition - 1))
    'NewFirst = Trim(Mid(NewData, SpacePosition + 1))
    
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", acNormal, OpenArgs, , acFormAdd, acDialog, NewData

'Forms![Patients]![First Name] = NewFirst
'Forms![Patients]![Last Name] = NewLast

[B][COLOR="Red"]Me![YourComboName].Requery[/COLOR][/B]

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

 

[cluendo]

Ok it is actually very helpful, thank you for your attachment. But what is not clear At The Moment is :

1. The code provided by John -

If Not IsNull(OpenArgs) Then
        DoCmd.GoToRecord acDataForm, "Patients", acNewRec
        Me.LastName = Left(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
        Me.FirstName = Right(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
    End If

Is partially working. What it does - Fills both "First Name" and "Last Name" of the bounded form. What it does not - it not working with some names which are different length.

Here are some pictures:

Here you can see all forms and Tables:

Here You can see the MAIN form:

Here I enter data which is not in thelist, so PopUp dialog occurs( NOTE THE NAME I HAVE ENTERED) :

Here you can see the dialog form to enter the details of the new record:
Look how it represents the Last name?

Finally if I click on "SAVE AND CLOSE" what I get is a Blank combo insetad of the record:

Maybe someone will get an Idea of what I want to achieve..

 

[cluendo]

And here is the new code for my NotInList:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
  
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", , , , acFormAdd, acDialog, NewData

Me![PatientLook].Requery

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

John, I change that now, but It didnt work. Any Ideas ?

 

[John Big Booty]

Make the highlighted adjustment to your code;

If Not IsNull(OpenArgs) Then
        DoCmd.GoToRecord acDataForm, "Patients", acNewRec
        Me.LastName = Left(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")[B][COLOR="Red"]+1[/COLOR][/B]))
        Me.FirstName = Right(OpenArgs, Len(OpenArgs) - (InStr(OpenArgs, " ")))
End If

 

[John Big Booty]

And here is the new code for my NotInList:

Private Sub PatientLook_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_PatientLook_NotInList
  
Dim intAnswer As Integer
  
intAnswer = MsgBox("Would you like to add this value to the list?", vbYesNo, vbQuestion)
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "Patients", , , , acFormAdd, acDialog, NewData

Me![PatientLook].Requery

Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
  
Exit_PatientLook_NotInList:
Exit Sub
  
Err_PatientLook_NotInList:
MsgBox Err.Description
Resume Exit_PatientLook_NotInList
End Sub

John, I change that now, but It didnt work. Any Ideas ?

How does it not work