How to have Combo Box act like Find Record button

  • Thread starter Thread starter Paul R in Oregon
  • Start date Start date
P

Paul R in Oregon

Access 2002/2003

frmChooseManuscript is a form used to choose a manuscript (a record)
from the tblManuscript table. The single record chosen is then the
basis for a printout/report.

User begins by running the report. On open, a macro in the report
opens frmChooseManuscript which has a combo box based on
qryChooseManuscript. This query displays MsNumber and MsTitle from
tblManuscript. The MsNumber (column 1) is the bound column in the
combo box.

After choosing a manuscript using the combo box, the MsNumber remains
displayed in the combo box field. User hits OK button which makes
frmChooseManuscript invisible so combo box value is still available for
another query which uses results to select record for report.
Invisible form is then closed.

I followed example in Help section to do this. It works great even if
a little clunky because I followed example and used macros. I even did
the workaround for the Access 2003 bug regarding a form's visible
property.

BUT, now the user wants me to display the MsNumber, Title, Authors, and
Issue (all from same tblManuscript) in fields on frmChooseManuscript
after combo box choice is made so user can be sure he chose the right
manuscript before hitting OK button.

To frmChooseManuscript I added new fields txtMsNumber, txtTitle,
txtAuthors, and txtIssue.

PROBLEM: How do I populate these fields based on choice in combo box?
I was able to do txtMsNumber with macro in an event in the combo box to
SetValue based on value in combo box. But how do I populate the other
three?

Because I've already used macros, I'd rather stay with macros but VB
code would be accepted too if that's the best or only way or you don't
like macros.
 
ASSUMING that all the data you need is in the same table as your MsNumber....

Simply add ALL the fields you want to the combobox, but select 0" for the
width of the fields you don't want displayed. Then you'll have the data
in-hand once the user has selected their choice. You can then populate
several "unbound" fields in your form via the VBA command:

me.WhateverField = me.MsNumber.column(x)

where WhateverField is another (unbound) field, and "x" is the column number
(starting at zero) of the data you want from the MsNumber combo box.
 
Back
Top