Brooke:
Here's the setup for an analogous situation, in this case for selecting an
existing salesperson or entering a new salesperson via a combo box on a form
bound to another table (in my case Customers, in your case the ProjectBilling
table) which includes a SalesPersonID numeric foreign key column:
1. the combo box is set up as follows:
ControlSource: SalesPersonID
RowSource: SELECT SalesPersonID,
FirstName & " " & LastName AS Fullname
FROM SalesPersons
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
2. To enable the user to enter a new sales person by typing the name in the
format 'Ken Sheridan' into the combo box the following code goes in the combo
box's NotInList event procedure:
Const conMESSAGE = "Salesperson's name must be entered " & _
"in format 'FirstName <space> LastName'"
Dim args As String
Dim strFirstName As String
Dim strLastName As String
Dim intSpacePos As Integer
Dim ctrl As Control
Dim strMessage As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"
intSpacePos = InStr(NewData, " ")
If intSpacePos = 0 Then
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
Response = acDataErrContinue
Else
strFirstName = Left(NewData, intSpacePos - 1)
strLastName = Mid(NewData, intSpacePos + 1)
' add named arguments
AddArg args, "argFirstName", strFirstName
AddArg args, "argLastName", strLastName
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmSalesPersons", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=args
' ensure frmSalesPersons closed
DoCmd.Close acForm, "frmSalesPersons"
' ensure salesperson has been added
If Not IsNull(DLookup("SalesPersonID", "SalesPersons", _
"FirstName = """ & strFirstName & """ And " & _
"LastName = """ & strLastName & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Salespersons table.
"
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If
End If
3. The frmSalesPersons form, which is bound to the SalesPersons table, has
the following code in its Open event procedure:
Dim args As String
If Not IsNull(Me.OpenArgs) Then
args = Me.OpenArgs
' get named named arguments
Me.FirstName.DefaultValue = """" & Arg(args, "argFirstName") & """"
Me.LastName.DefaultValue = """" & Arg(args, "argLastName") & """"
End If
' move form to new record
DoCmd.GoToRecord acForm, Me.Name, acNewRec
4. The following module, named basArgs, which was developed some years ago
by Stuart McCall and myself and whose functions are called by the above
procedures, goes is added to the database:
''''module starts''''
Private Const OFFSET As Long = 127
Private Const ASSIGNOP As String = "=="
Function Arg(buffer, idx) As Variant
If IsNumeric(idx) Then
I& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
I& = InStr(I&, buffer, ASSIGNOP) + 2
Else
I& = InStr(1, buffer, idx) + Len(idx) + 2
token$ = Chr(Asc(Mid(buffer, InStr(1, buffer, idx) - 1, 1)) + 1)
End If
Arg = Mid(buffer, I&, InStr(I&, buffer, token$) - I&)
End Function
Function Argname(buffer, idx) As String
I& = InStr(1, buffer, Chr(idx + OFFSET - 1))
token$ = Chr(idx + OFFSET)
Argname = Mid(buffer, I& + 1, InStr(I&, buffer, ASSIGNOP) - (I& + 1))
End Function
Function ArgCount(buffer) As Long
ArgCount = Asc(Right(Chr(OFFSET) & buffer, 1)) - OFFSET
End Function
Sub AddArg(buffer, Argname, argval)
If Len(buffer & "") = 0 Then buffer = Chr(OFFSET)
If IsNumeric(Argname) Then Argname = ArgCount(buffer) + 1
buffer = buffer & Argname & ASSIGNOP & argval & Chr(Asc(Right(buffer, 1))
+ 1)
End Sub
Sub AddArgList(buffer, ParamArray Tokens())
For I& = 0 To UBound(Tokens)
AddArg buffer, I& + 1, Tokens(I&)
Next
End Sub
''''module ends''''
NB: you don't need to include a control bound to the computed 'contact name'
column in the form. The name shows in that format in the combo box, but its
value, by virtue of the hidden first column, is the unique numeric ID value
for that person.
Ken Sheridan
Stafford, England
