The names and addresses should be held in a separate table, Customers say,
which you can look up when a reservation is made. In the Reservations table
you should have a CustomerID foreign key column which references the
CustomerID primary key column of the Customers table. The CustomerID should
be a unique number and in the Customers table can be an autonumber, but that
in the Reservations table should be a normal long integer number data type.
Whether you need to store the address data for the customer in the
Reservations table depends whether you want previous reservations to reflect
the customer's new address should they change address, or whether you want
previous reservations to show the customer's address at the time the
reservation was made. If the former you don't need any address columns in
the Reservations table, if the latter you do.
Whichever way you do it your reservations form would have a combo box,
cboCustomerID bound to the CustomerID column with a RowSource property along
these lines
SELECT CustomerID, FirstName & " " & LastName,
Address1, Address2, City, PostCode FROM Customers
ORDER BY LastName, FirstName;
and othervproerties like this:
BoundColumn 1
ColumnCount 5 (i.e. the number of columns returned by the above
RowSource query)
ColumnWidths 0cm;8cm;0cm;0cm;0cm (this hides all but the names in the
second column. If you enter it in centimetres on a system set up to use
inches Access will automatically convert it)
Add unbound controls to the form with references to the combo box's hidden
columns as follows:
ControlName RowSource Property
txtAddress1 =cboCustomerID.Column(2)
txtAddress2 =cboCustomerID.Column(3)
txtCity =cboCustomerID.Column(4)
txtPostCode =cboCustomerID.Column(5)
To enter a customer you can either select a name from the list, which is
sorted by name, or enter the name in the format ken Sheridan, i.e. the first
name, a space, then the last name. Note that only one space is allowed with
it set up as in this example. If the name is in the list it will go to it as
you type. If not then you need to cater for adding a new customer. This is
done by means of code in the combo box's NotInList event procedure as follows:
Private Sub cboCustomerID_NotInList(NewData As String, Response As Integer)
Const conMESSAGE = "New customer not added."
Const conFORMCANCELLED = 2501
Dim ctrl As Control
Dim strMessage As String
Dim strCriteria As String
Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list of customers?"
If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
On Error Resume Next
DoCmd.OpenForm "frmCustomers", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
Select Case Err.Number
Case 0
' no error
Case conFORMCANCELLED
Response = acDataErrContinue
Exit Sub
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, Error
End Select
' ensure frmCustomers closed
DoCmd.Close acForm, "frmCustomers"
' ensure new customer was added
strCriteria = "Firstname & "" "" & Lastname = " & _
"""" & NewData & """"
If IsNull(DLookup("CustomerID", "Customers", strCriteria)) Then
MsgBox conMESSAGE, vbInformation, "Warning"
Response = acDataErrContinue
Exit Sub
End If
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If
End Sub
You also need to include the following code in the Open event procedure of
the frmCustomers form which is a form bound to the Customers table and which
the above code opens to enter the customer's address data:
Private Sub Form_Open(Cancel As Integer)
Const conMESSAGE = "Name must be a first and " & _
"last name separated by a space."
Dim intSpacePos As Integer
If Not IsNull(Me.OpenArgs) Then
intSpacePos = InStr(Me.OpenArgs, " ")
If intSpacePos > 0 Then
' parse name into first and last names
' and set as DefaultValue properties of
' first and lastname controls
Me.FirstName.DefaultValue = _
"""" & Left(Me.OpenArgs, intSpacePos - 1) & """"
Me.LastName.DefaultValue = _
"""" & Mid(Me.OpenArgs, intSpacePos + 1) & """"
Else
MsgBox conMESSAGE, vbExclamation, "Warning"
Cancel = True
End If
End If
End Sub
The above routines were in fact developed by me some years ago for a
database of pedigree dogs I did for someone and was for entering breeders
names, but the modus operandi can be applied to many situations. If you find
the above difficult to follow mail me at the address below and I'll send it
to you as a simple little demo file:
kenwsheridan<at>yahoo<dot>co<dot>uk
The above assumes you want previous reservation records to reflect the
customer's current address if changed. If you want their old addresses to
remain with the old reservations, however, all you need to do is include
columns for Address1, Address2 etc in the Reservations table and instead of
using unbound text boxes on the form as above, bind them to the columns in
question. In the AfterUpdate event procedure of the cboCustomerID combo box
assign values to the bound controls like so:
Me.txtAddresss1 = Me.cboCustomerID.Column(2)
Me.txtAddress2 = Me.cboCustomerID.Column(3)
Me.txtCity = Me.cboCustomerID.Column(4)
me.txtPostCode = Me.cboCustomerID.Column(5)
Ken Sheridan
Stafford, England
