Need help with lookup on table/form

[sparky5]

Hi all. I have a contact dB with 2 contacts (main & alternate). I would like to be able to pull any record containing either the main or alt last name up. Right now the unbound combo box works fine pulling up the first contact name but I need to be able to pull up a record if the second contact name matches as well. I am trying to use 1 unbound combo box to look at both fields (LastName1 & LastName2). Is it possible and how should I set it up?
I appreciate any help!

 

[RichO]

When you say "pull a record" are you opening a report with the selected criteria? A filtered form? Query? What type of code are you using to pull the record based on the name selection?

It would help to know this in order to give you a more accurate answer.

 

[sparky5]

need help with form lookup

I apologize for an unclear representation of my problem to begin with, but here is the basic problem: I have a table of contacts (based on the contact management dB template in Access). It is a database to track real estate properties. The table is set up to hold two different first and last names as contacts. On the main data entry / record viewing form I have an unbound combo box to do a search for a property based on the last name of the contact. It works fine finding the the last name of the first contact (LastName1), but I would also like it to find a record if I input a name stored as the second contact (LastName2). The only thing I can think of so far is to create another unbound combo box that searches the second contact name. I don't really want to do that because I think it looks crappy and it's extra work for the users.
Again, any help would be greatly appreciated!

 

[RichO]

When you make a selection in the combo box, what happens next? Does a report open? Does another form open? Does the current form populate with the selected record?

The reason I ask is that we need to pinpoint where Access pulls the information based on your selection. From there that can be changed to include the second contact name as well.

 

[sparky5]

Hi. Basically, the form I'm working with has a bottom section for data entry / display and a top section for searching for a particular record. So I would like to have the users search for a property (which is the unique part of the record) by entering the contact last name which should fill in the bottom half of the first matching record. It does this fine so far with the data stored in "LastName1" but does not work if I type in a name stored as "LastName2". The basis of the dB is that there may be a contact (owner) with more than one property. I realize that in a perfect world the contacts would be stored in a seperate table pointing to various properties they owned, but what I am dealing with is all lumped into one table. I appreciate any help on this.

 

[RichO]

It's hard to pinpoint what you need to do because there are a few different ways to accomplish what is currently being done on your form and in order to be able to point you in the right direction to include the second name field, I guess I would need to know how the record is being pulled into the form.

Can you post the code that runs after selecting a name from the combo box?

This may be in the after update event of your combo box or beneath a command button if that is being used.

 

[sparky5]

The code I have is the following:
'*********************************
Private Sub Combo210_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PropertyID] = " & Str(Nz(Me![Combo210], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
'***********************************
I used a wizard when I put the combo box on the form, so this code was supplied by Access. Like I said it works fine getting records based on one of the last names, but I would like to get records based on either last names. The row source property of the combo box is a query of the two last name columns from the contacts table. I'm not sure if I'm querying it correctly or not. Again, any help is greatly appreciated.

 

[sparky5]

Forget that last bit about the query. That was something I was playing around with that didn't work in the end. The SQL statement (provided by Access) is:

SELECT Contacts.PropertyID, Contacts.[Last Name1], Contacts.[Last Name2]
FROM Contacts;

It creates two columns, one for each last name. I suppose if there was a way to query the last names and put them into one column of data that the combo box could look at...
Well, obviously I'm stuck here. Any help is appreciated.

 

[RichO]

OK try this:

First change the Row Source of your combo box to:

SELECT LastName1 FROM Contacts UNION SELECT LastName2 FROM Contacts;

This will bring up the names from both last name fields in a single column. Also in the properties for the combo, set the Column Count to 1 and make sure the Bound Column is 1.

Now change the code to look like this (the red line is the only one changed):

Private Sub Combo210_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
[COLOR=DarkRed]rs.FindFirst "[LastName1] = '" & Me![Combo210] & "' OR [LastName2] = '" & Me![Combo210] & "'"[/COLOR]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[sparky5]

Still no luck I'm afraid. After making those changes, I got two "Enter Parameter Value" msgbox pop ups for both the LastName1 and LastName2 values. Do they need to be in [] in the SQL statement or some other minor adjustment to the SQL statement? This is quite a difficult problem - any help is appreciated.

 

[RichO]

OK I notice that in your example you have a space between Last and Name [Last Name1] in the field name. Insert spaces in the field names in the code and try again.

 

[sparky5]

Works like a charm, my friend. I feel like a moron. The only field names that have spaces are the name fields. I don't know why I did that. I usually don't include spaces just for that reason. I appreciate your help tremendously. I still have a ways to go, so if I'm stuck again, look for another newbie level question from me somewhere on this board. Thanks again.

 

[Mile-O]

Private Sub Combo210_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName1] = '" & Me![Combo210] & "' OR [LastName2] = '" & Me![Combo210] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

A couple of things about that code:

1. Dim rs As Object
   
   Be explicit. Use the following. Dim rs As DAO.Recordset
   
   
2. Me![Combo210] . Use Me.[Combo210] - this is early binding and is more appropriate.

 

[RichO]

That code was created by the combo box wizard.

Can anyone explain why Access does it that way if there's a better way?

 

[Mile-O]

Can anyone explain why Access does it that way if there's a better way?

Because Access Wizards, for some reason known only to Microsoft, haven't been updated since Access 95 and are well known for producing inefficient and/or outdated code.

As a rule, never use wizard code.