Not In List: SQL adds info, need more information

J

Jenna B

I have a combo box based on a query that will allow a user to select a name
from the list. If the name is not in the list, the Not In List event will run
the following code, and add the name that they entered to tblContacts.
However, I need more information (such as phone number, job title, etc.) and
I want the form to open to that specific record so that they can finish
adding information. I have successfully gotten the form to open, but not to
the record that was just added.

I greatly appreciate your help!
_____________________________________
Private Sub Combo39_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub

Msg = " ' " & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Would you like to add this person as a contact?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Contact...")
If i = vbYes Then
strSQL = "Insert Into tblContacts ([ContLName]) values ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
DoCmd.OpenForm "frmNewContact", , , , acFormEdit
Else
Response = acDataErrContinue

End If

End Sub
 
F

fredg

I have a combo box based on a query that will allow a user to select a name
from the list. If the name is not in the list, the Not In List event will run
the following code, and add the name that they entered to tblContacts.
However, I need more information (such as phone number, job title, etc.) and
I want the form to open to that specific record so that they can finish
adding information. I have successfully gotten the form to open, but not to
the record that was just added.

I greatly appreciate your help!
_____________________________________
Private Sub Combo39_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub

Msg = " ' " & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Would you like to add this person as a contact?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Contact...")
If i = vbYes Then
strSQL = "Insert Into tblContacts ([ContLName]) values ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
DoCmd.OpenForm "frmNewContact", , , , acFormEdit
Else
Response = acDataErrContinue

End If

End Sub
Click to expand...

In this case, use the NotInList event to notify the user that the name
was not in the list and to double-click on the combo box if they wish
to add it.

MsgBox "Double-click this field to enter a new name.", vbInformation,
Me.Name
Response = acDataErrContinue
=============

The "frmPatients" listed below is the name of the form into which the
new patient data is entered. Change it's name and control names to
whatever your actual names are.
Code the combo box Double-click event:

Private Sub cboClient_DblClick(Cancel As Integer)
On Error GoTo Err_cboClient_DblClick

Dim lngClient As Long
If IsNull(Me![cboClient]) Then
Me![cboClient].Text = ""
Else
lngClient = Me![cboClient]
Me![cboClient] = Null
End If

DoCmd.OpenForm "frmPatients", , , , , acDialog, "GotoNew"
Me![cboClient].Requery

If lngClient <> 0 Then Me![cboClient] = lngClient

Exit_cboClient_DblClick:
Exit Sub

Err_cboClient_DblClick:
MsgBox "Client Error #: " & Err.Number & " " & Err.Description, ,
Me.Name
Resume Exit_cboClient_DblClick

End Sub
===

Next, code the form frmPatients Load event:

If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "GoToNew" then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End If
 
J

Jenna B

Thanks for replying! That is an acceptable solution.

However... I was looking for a solution like this: the Not In List event
would prompt them to add the name to a new record, and when they double
clicked on the name/combo box, it would take them to the record that was just
automatically created so that they could add further information. That way,
they are only typing the name once, and not having to re-enter information.

But, thanks for the info. I do appreciate it. I'll use that as a temporary
solution.

fredg said:
I have a combo box based on a query that will allow a user to select a name
from the list. If the name is not in the list, the Not In List event will run
the following code, and add the name that they entered to tblContacts.
However, I need more information (such as phone number, job title, etc.) and
I want the form to open to that specific record so that they can finish
adding information. I have successfully gotten the form to open, but not to
the record that was just added.

I greatly appreciate your help!
_____________________________________
Private Sub Combo39_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub

Msg = " ' " & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Would you like to add this person as a contact?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Contact...")
If i = vbYes Then
strSQL = "Insert Into tblContacts ([ContLName]) values ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
DoCmd.OpenForm "frmNewContact", , , , acFormEdit
Else
Response = acDataErrContinue

End If

End Sub
Click to expand...

In this case, use the NotInList event to notify the user that the name
was not in the list and to double-click on the combo box if they wish
to add it.

MsgBox "Double-click this field to enter a new name.", vbInformation,
Me.Name
Response = acDataErrContinue
=============

The "frmPatients" listed below is the name of the form into which the
new patient data is entered. Change it's name and control names to
whatever your actual names are.
Code the combo box Double-click event:

Private Sub cboClient_DblClick(Cancel As Integer)
On Error GoTo Err_cboClient_DblClick

Dim lngClient As Long
If IsNull(Me![cboClient]) Then
Me![cboClient].Text = ""
Else
lngClient = Me![cboClient]
Me![cboClient] = Null
End If

DoCmd.OpenForm "frmPatients", , , , , acDialog, "GotoNew"
Me![cboClient].Requery

If lngClient <> 0 Then Me![cboClient] = lngClient

Exit_cboClient_DblClick:
Exit Sub

Err_cboClient_DblClick:
MsgBox "Client Error #: " & Err.Number & " " & Err.Description, ,
Me.Name
Resume Exit_cboClient_DblClick

End Sub
===

Next, code the form frmPatients Load event:

If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "GoToNew" then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End If
Click to expand...
 
F

fredg

Thanks for replying! That is an acceptable solution.

However... I was looking for a solution like this: the Not In List event
would prompt them to add the name to a new record, and when they double
clicked on the name/combo box, it would take them to the record that was just
automatically created so that they could add further information. That way,
they are only typing the name once, and not having to re-enter information.

But, thanks for the info. I do appreciate it. I'll use that as a temporary
solution.

fredg said:
I have a combo box based on a query that will allow a user to select a name
from the list. If the name is not in the list, the Not In List event will run
the following code, and add the name that they entered to tblContacts.
However, I need more information (such as phone number, job title, etc.) and
I want the form to open to that specific record so that they can finish
adding information. I have successfully gotten the form to open, but not to
the record that was just added.

I greatly appreciate your help!
_____________________________________
Private Sub Combo39_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String

If NewData = "" Then Exit Sub

Msg = " ' " & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Would you like to add this person as a contact?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Contact...")
If i = vbYes Then
strSQL = "Insert Into tblContacts ([ContLName]) values ('" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
DoCmd.OpenForm "frmNewContact", , , , acFormEdit
Else
Response = acDataErrContinue

End If

End Sub
Click to expand...

In this case, use the NotInList event to notify the user that the name
was not in the list and to double-click on the combo box if they wish
to add it.

MsgBox "Double-click this field to enter a new name.", vbInformation,
Me.Name
Response = acDataErrContinue
=============

The "frmPatients" listed below is the name of the form into which the
new patient data is entered. Change it's name and control names to
whatever your actual names are.
Code the combo box Double-click event:

Private Sub cboClient_DblClick(Cancel As Integer)
On Error GoTo Err_cboClient_DblClick

Dim lngClient As Long
If IsNull(Me![cboClient]) Then
Me![cboClient].Text = ""
Else
lngClient = Me![cboClient]
Me![cboClient] = Null
End If

DoCmd.OpenForm "frmPatients", , , , , acDialog, "GotoNew"
Me![cboClient].Requery

If lngClient <> 0 Then Me![cboClient] = lngClient

Exit_cboClient_DblClick:
Exit Sub

Err_cboClient_DblClick:
MsgBox "Client Error #: " & Err.Number & " " & Err.Description, ,
Me.Name
Resume Exit_cboClient_DblClick

End Sub
===

Next, code the form frmPatients Load event:

If Not IsNull(Me.OpenArgs) Then
If Me.OpenArgs = "GoToNew" then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
End If
Click to expand...
Click to expand...

Shouldn't be a problem.
Just make a few changes to the code.

Private Sub cboClient_DblClick(Cancel As Integer)
On Error GoTo Err_cboClient_DblClick

Dim strName as String
strName = Me![ComboName].Text

Dim lngClient As Long
If IsNull(Me![cboClient]) Then
Me![cboClient].Text = ""
Else
lngClient = Me![cboClient]
Me![cboClient] = Null
End If

DoCmd.OpenForm "frmPatients", , , , , acDialog, "GotoNew" & "/" &
strName
Me![cboClient].Requery

If lngClient <> 0 Then Me![cboClient] = lngClient

Exit_cboClient_DblClick:
Exit Sub

Err_cboClient_DblClick:
MsgBox "Client Error #: " & Err.Number & " " & Err.Description,
Me.Name
Resume Exit_cboClient_DblClick

End Sub
===========

Then code the frmPatients Load event:

If Not isNull(Me.OpenArgs) Then
If Left(Me.OpenArgs,InStr(Me.OpenArgs,"/")-1) = "GoToNew" then
DoCmd.RunCommand acCmdRecordsGoToNew
Me![ControlName] = Mid(Me.OpenArgs,InStr(Me.OpenArgs,"/")+1)
End If
End If

Change [ControlName] to whatever the actual name of the control is.
The form will open with the new name already entered. Simply fill in
the rest of the fields as needed.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

item not found in this collection 1
NotInList Issue 1
Add Record with combo box 13
Need Help Handling Apostrophes with New Data 9
Need help modifying code 0
Update Combo Box when Not In List and update related info in form 1
Help with Not in the list (combobox) 1
open args syntax 4

Top