form question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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.

steveh said:
I need some help with the following. I am new to this and may have not
Click to expand...
gone about things right so feel free to tell me so.
I have a table Customers with LastName, Firstname and MiddleInitial
Click to expand...
fields, I have created a form with a unbound cbx with a row source as
follows;
SELECT tblCustomers.CustomerID, [tblCustomers.LastName] & "," &
Click to expand...
[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
Click to expand...
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.
 
You can OpenForm in dialog mode, but unless the NewData goes into the bound
column, this won't satisy the requirements of NotInList.
 
Back
Top