Search Field

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

I have a main form with a record source of a customer
table. The form contains basic table fields including
Customer ID, Last Name, First Name, etc. This form
contains three sub forms which were established by
dragging and dropping those forms onto the main form and
letting Access do it's magic, according to previously
setup relationships. All went well. The sub-forms use the
Customer ID field as a record source as expected. As I
page through records on the main form (or use the record
selector bar), the main form displays the respective
fields and the sub-forms display the appropriate records.

Now I want the Customer ID field on the main form to be a
search field, so I changed it to a combo box and copied an
appropriate select statement to the record source for that
field.

If I use the drop box to select a record, the sub forms
display the appropriate records for the selected choice,
but the remaining fields on the main form, such as "Last
Name" and "First Name" do not change. They stay set with
values from the original record. What do I need to do to
the main form or the Customer ID field to get the main
form fields to reflect the selected record. Thanks!

-Howard
 
Howard said:
I have a main form with a record source of a customer
table. The form contains basic table fields including
Customer ID, Last Name, First Name, etc. This form
contains three sub forms which were established by
dragging and dropping those forms onto the main form and
letting Access do it's magic, according to previously
setup relationships. All went well. The sub-forms use the
Customer ID field as a record source as expected. As I
page through records on the main form (or use the record
selector bar), the main form displays the respective
fields and the sub-forms display the appropriate records.

Now I want the Customer ID field on the main form to be a
search field, so I changed it to a combo box and copied an
appropriate select statement to the record source for that
field.

If I use the drop box to select a record, the sub forms
display the appropriate records for the selected choice,
but the remaining fields on the main form, such as "Last
Name" and "First Name" do not change. They stay set with
values from the original record. What do I need to do to
the main form or the Customer ID field to get the main
form fields to reflect the selected record. Thanks!

-Howard
Click to expand...

I think you made this a little more difficult than it needed to be.
Access has a built in combo box "search" wizard.

Reset your form back to the it's original layout - no dropdown or the
customer name. Add a combo box to the form using the wizard, the very
first question in the wizard, choose the last option - which is create a
combo box used to search for records. Choose the appropriate table and
field and it should work like you want.
 
That was the first thing I tried. Maybe I didn't do it
exactly as you stated so I'll try the Wizard again. Please
continue to monitor this message stream and I'll reply
again soon. Thanks for everything!

-Howard
 
Hi Shaun,

This didn't work. I allowed the wizard to create the Combo
box as suggested, selecting the third (last) choice to
search records in the current form. When running, I select
the name and nothing else changes. I then set the record
source on that field to be the Customer ID. Now when I
select a name, the original Customer ID field depicts the
new selection, but the remaining main form fields do not
get altered and the sub forms do not respond to the
change. If I attempt go to another record, it warns me
that the changes I've made to the current record will
create a duplicate so it can't be changed. It seems that
selecting a name from the drop down box does not really
change the record pointer to the selected record, it just
changes the Customer ID fields on the current record to
the selected value. Does this make sence? Thanks!

-Howard
 
Howard,

Changing the recordsource of the combo won't work like you want. That
actually makes the combo box bound to the CustomerID field.

Which fields are you choosing to display when you use the wizard. You
choose search records on the current form, then you choose the customer
table, then which fields? Make sure you include the key field, as
that's what access uses to verify a match.
 
Hi Shaun,

The three fields I chose were Customer ID (key field),
Last Name, and First Name. I let the wizard not display
the Customer ID so the only the last and first name are
displayed on the drop down box. Let me know your
thoughts. Thanks!

- Howard
 
I wish I knew what was wrong, maybe someone else can give us a hand. It
seems you've set everything up correctly.
 
Thanks for trying!

- Howard
-----Original Message-----
I wish I knew what was wrong, maybe someone else can give us a hand. It
seems you've set everything up correctly.

.
Click to expand...
 
Back
Top