OnNonInList and concatenate fields

[thart21]

Could someone please tell me what I should be looking for to find out how to use the Not In List event to add to a concatenated field? I can only find discussions related to just adding one field.

I have a combo box filled by tblStaff "SELECT [staffid],[last] & ", " & [first] FROM tblStaff ORDER BY [last].

I can successfully get it to add [last] but not [first] using this code found in this forum

Private Sub EngDwgOwner_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_ErrorHandler

' database and recordset object variables
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblStaff")
' using the recordset object
With rs
.AddNew
.Fields("last") = NewData
I have tried .Fields("last"), ("first") = New Data



.Update
.Close
End With
Response = acDataErrAdded

Else
Me.EngDwgOwner.Undo
Response = acDataErrContinue End If

Exit_ErrorHandler:

Set rs = Nothing
Set db = Nothing
Exit Sub

Err_ErrorHandler:
' MsgBox Err.description, vbExclamation, "Error #" & Err.Number
Resume Exit_ErrorHandler



End Sub

Thanks,

Toni

 

[Fizzio]

You can do this via code but it is a bit more tricky.

Essentially, if you want to add more than one field, you need to split NewData into its separate parts and then add the 2 parts separately.

eg if you type Smith, John into the combo, the code would need to do the following

Split "Smith, John" into "John" & "Smith" eg

dim strFirst as string, dim strLast as string
strFirst = trim(right(NewData),instrrev(NewData,",")-1))
strLast = trim(left(NewData),instr(NewData,",")-1))

Then add your data
{Recordset Stuff}....

With rs
.AddNew
.Fields("last") = strLast
.Fields("first") = strFirst

You will need to add error checking just in case someone types Smith John or another permutation of the name.

hth

 

[Mile-O]

If you create longer concatenated strings separated by a delimiter then there is a function (called GetPart()) that I posted in the Code Repository forum...somewhere.

 

[thart21]

Thanks for your help! With your help, this is what I came up with that works perfectly!


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strFirst As String
Dim strLast As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Staff")
strFirst = Right(NewData, Len(NewData) - InStr(1, NewData, ",") - 1)
strLast = Left(NewData, InStr(1, NewData, ",") - 1)

With rs
.AddNew
.Fields("last") = strLast
.Fields("first") = strFirst
.Update
End With

Response = acDataErrAdded