Form lookup question

  • Thread starter Thread starter Larry Berstein
  • Start date Start date
L

Larry Berstein

I have created a table with the following fields: fname, lname,
homeaddress, etc. I have a related table with vacation related data.

Could someone explain how I can create a form (with a subform utilizing
the vacation table) allowing me to find the name of the person quickly,
without having to go through all (a few hundred) of the names to get to
them. I also want to make sure that I don't change the person's name and
have been using the Locked property.


I assume it has something to do with creating a query. Is there a way of
creating that query from within a form so I don't have to manually
create the query each time I lookup a person?

I was also wondering how to easily alphabetize the table each time I add
a new person without manually going back and creating a query and
replacing the main table with the names.



Larry
 
You shouldn't worry about alphabetizing your records in
the table itself because you should never be doing data
entry in the table but through forms and you can sort in
forms.

Forms have an OrderBy property and I believe you should be
able to enter: [lname],[fname] to get it to sort by last
then forst name. Try it and see how it works.

For lookups, I would:
Add a unique, AutoNumber field (CustID) to your table
Click to expand...
(tblCustomers) as the PrimaryKey.
On your form, in addition to the first and last name
Click to expand...
controls, build a ComboBox (cboLookUp) and use this as the
RowSource:

SELECT [tblCustomers].[CustID], [tblCustomers].[lname]
& ", " & [tblCustomers].[fname] FROM tblCustomers ORDER BY
[tblCustomers].[lname],[tblCustomers].[fname]

Set the ColumnCount to 2, ColumnWidths to 0,2 (to hide the
ID field) and the BoundColumn to 1 (the combo saves the ID
as its value).

In the AfterUpdate event for the ComboBox, filter the form
for the selected customer:

Private Sub cboLookUp_AfterUpdate()

' if the combo box is empty, end the code
If IsNull([cboLookUp]) or [cboLookUp]="" Then
Exit Sub
End If

' declare and set variables
Dim strID As String
Dim strFilter As String

strID = [cboLookUp]
strFilter = "[CustID]='" & strID & "'"

' filter form
DoCmd.ApplyFilter , strFilter

End Sub

Hope this helps!

Howard Brody
 
Back
Top