Lookup records using Combo Box

[J Taylor]

I am building a database for my homeowners association. There is one
contact per street address.

I have one table with street names.

I have another table with street numbers that has a lookup field for street
names. I used a query to combine street numbers with street names in a third
field (street addresses).

I have another table with contact names and info that has a look field for
street addresses.

No problem so far.

I am building a form to use for viewing/editing the data. I put a combo box
for the street addresses. Using the wizard, I made it a look up control so
selecting the street address returns the contact info on the form. However,
when you scroll through the remaining data, the combo box is not refreshed.
It is "unbound". When I ran the wizard again, and this time, selected it to
be the field, it is refreshed when I scroll through the data, but does not
perform the lookup function.

I want it to do both: be bound to the field so it scrolls with the data,
and performs the lookup function. Is there a property I can set or do I
need to code it?

When I took the unbound lookup field and bound it to my field, it scrolled
and also looked up, but stored a number (the primary key) in the displayed
field ruining my data.

 

[Klatuu]

It cannot be a bound column. To do so would cause it to change the value in
the current record when you want to do a lookup.

There are a couple of ways to do this. The way I usually do this is to put
the search combo in the form header and make in invisible. Then in the
footer I have a Search command button that when clicked makes the combo
visible and sets the focus to it. After the combo has found the requested
record and made it the current record, I set the focus to another control
and hide the combo. To display the address, I just use a bound textbox, but
I also have it set as enabled = no and locked = yes. I use the search
combo's Not In List event to add new records.

The other way is use the form's current event to sync the value in the combo
to the value in the current record's address field.
Me.MyCombo = Me.[AddressField]

 

[J Taylor]

It cannot be a bound column. To do so would cause it to change the value
in the current record when you want to do a lookup.

There are a couple of ways to do this. The way I usually do this is to
put the search combo in the form header and make in invisible. Then in
the footer I have a Search command button that when clicked makes the
combo visible and sets the focus to it. After the combo has found the
requested record and made it the current record, I set the focus to
another control and hide the combo. To display the address, I just use a
bound textbox, but I also have it set as enabled = no and locked = yes. I
use the search combo's Not In List event to add new records.

The other way is use the form's current event to sync the value in the
combo to the value in the current record's address field.
Me.MyCombo = Me.[AddressField]

Thanks. You'd think they would make that a property instead of having to
code it.

 

[Klatuu]

I don't understand what you are saying. What property?

It cannot be a bound column. To do so would cause it to change the value
in the current record when you want to do a lookup.

There are a couple of ways to do this. The way I usually do this is to
put the search combo in the form header and make in invisible. Then in
the footer I have a Search command button that when clicked makes the
combo visible and sets the focus to it. After the combo has found the
requested record and made it the current record, I set the focus to
another control and hide the combo. To display the address, I just use a
bound textbox, but I also have it set as enabled = no and locked = yes.
I use the search combo's Not In List event to add new records.

The other way is use the form's current event to sync the value in the
combo to the value in the current record's address field.
Me.MyCombo = Me.[AddressField]

Thanks. You'd think they would make that a property instead of having to
code it.