form question

[Guest]

I need some help with the following. I am new to this and may have not gone about things right so feel free to tell me so

I have a table Customers with LastName, Firstname and MiddleInitial fields, I have created a form with a unbound cbx with a row source as follows

SELECT tblCustomers.CustomerID, [tblCustomers.LastName] & "," & [tblCustomers.FirstName] & " " & [tblCustomers.MiddleInitial] AS Fullname FROM tblCustomers;

the entries are expected to follow the format of Smith, James

I have a notinlist procedure on the cbx as follow

Private Sub FullName_NotInList(NewData As String, Response As Integer
Dim db As DAO.Databas
Dim rs As DAO.Recordse
Dim strMsg As Strin

strMsg = "'" & NewData & "' is not an available Customer Name " & vbCrLf & vbCrL
strMsg = strMsg & "Do you want to add the new Name to the database?
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it.

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo The
Response = acDataErrContinu
Els
Set db = CurrentD
Set rs = db.OpenRecordset("tblCustomers", dbOpenDynaset
On Error Resume Nex
rs.AddNe
rs!FullName = NewDat
rs.Updat

If Err The
MsgBox "An error occurred. Please try again.
Response = acDataErrContinu
Els
Response = acDataErrAdde

End I

rs.Clos
Set rs = Nothin
Set db = Nothin

End I
End Su

I know the line rs!FullName = NewData is not correct. If I replace FullName with LastName the entry appears in my Customers table with Smith, James A in the LastName field as expected. My question is how can I get the name to split back into the appropriate fields on the customers table

Thanks!

 

[Allen Browne]

Steve, I don't think you can do this with the NotInList event. It is pretty
sensitive about what data is in what field.

Would it be satisfactory to place a button beside your combo to
DoCmd.OpenForm "frmCustomer", DataMode:=acFormAdd
and then in the AfterInsert event of that form Requery your combo.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I need some help with the following. I am new to this and may have not

gone about things right so feel free to tell me so.

I have a table Customers with LastName, Firstname and MiddleInitial

fields, I have created a form with a unbound cbx with a row source as
follows;

SELECT tblCustomers.CustomerID, [tblCustomers.LastName] & "," &

[tblCustomers.FirstName] & " " & [tblCustomers.MiddleInitial] AS Fullname
FROM tblCustomers;

the entries are expected to follow the format of Smith, James A

I have a notinlist procedure on the cbx as follows

Private Sub FullName_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available Customer Name " & vbCrLf & vbCrLf
strMsg = strMsg & "Do you want to add the new Name to the database?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCustomers", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!FullName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing

End If
End Sub

I know the line rs!FullName = NewData is not correct. If I replace

FullName with LastName the entry appears in my Customers table with Smith,
James A in the LastName field as expected. My question is how can I get the
name to split back into the appropriate fields on the customers table.

 

[Guest]

Allen

Can I include the open form somewhere in the notinlist event code?

 

[Allen Browne]

You can OpenForm in dialog mode, but unless the NewData goes into the bound
column, this won't satisy the requirements of NotInList.