Combo box headache

[Jen]

Hi again guys,
This must be my 3rd question in 2 weeks so sorry for being a pain.
This database which i'm trying to finish (started by someone else) is driving
me nuts.

My problem is that i'm trying to display employee records on a form, based
on a selection i make in a combo box. The combo box is on another form (which
is a pop-up form) accompanied by a command button which opens the form with
the records to be displayed. The form with the employee records is linked to
a query which is in turn linked to a table which contains the actual records.
The combo button itself is linked to the Employee Name field on the query via
an Expression.
Anyway, when i select a name and click the button, the form opens up
ok but it's blank. Any ideas

Thanks lads,
Jen

 

[Jeff Boyce]

Jen

That seems a bit more complicated than necessary. Opening form1 to use a
combobox, then clicking a button to open form2 to display the record for the
selection in the combobox is more convoluted than you need.

Instead, consider the following approach:

* create a query that returns all of the records
* create a form based on that query
* add a combobox (unbound) to the header of that new form
* base that combobox on the list you'd be using in your form1 above, but
make sure the bound field is the record ID field
* modify the query that feeds this new form to point to the combobox on the
form to get its value for the recordID field (note: the form must be open
for this to work)
* add an AfterUpdate event to the combobox on the form -- add: Me.Requery
* close the form & the query.

Now, when you first open the form, it looks to the query for its records.
The query looks back at the form and sees nothing in the combobox (you just
opened the form, right?), so loads ... no record in the form. You pick from
the combobox the record you want to see and tab/enter. The combobox's
AfterUpdate event fires, requerying the form. The form looks to the query,
the query looks to the combobox and sees a recordID, returns that record,
and the form loads with that record (i.e., the selected records data).

Will that do what you need?

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[golfinray]

Your combo should be set up to search the filed you want. That part is ok.
Now put some code in to make it work. Go into properties of the form, go to
events, go to the afterupdate event, and click the little button out to the
right. Start the code builder. Type:
Me.filter = "[thefieldyouwanttolookup] = """ & Me.combo# & """"
Me.filteron = true

The combo# will be listed, like combo6 or combo20

 

[Jen]

Hi Jeff,
Thanks for your reply...although for some reason it keeps disappearing
??. I tried your suggestion and it works perfectly...thank you. I've one
other slight problem in that my Combo Box looks up a field which contains
peoples names. So, say there were 5 records for John Smith, i'd like John
Smith to only be listed once in the Combo Box instead of 5 times. Is this
possible ?

Thanks,
Jen

 

[Jen]

Bugger, i should have looked before asking that last question....you just add
'Distinct' to the row source of the combo box right . Well that's
everything sorted so thanks for your help Jeff it's very much appreciated.

Jen
xoxo

 

[Jeff Boyce]

Jen

That's good ... ?!and bad?!

Are you totally convinced that you will only ever deal with a single "John
Smith" ... there's more than one of them out there, you know...<g>.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.