Combo box

  • Thread starter Thread starter Joe Paterno
  • Start date Start date
J

Joe Paterno

I've created a form with a drop down menu combo box. When the user
uses the drop down menu and selects a record (in this case serial#) I
want a query to pop up displaying all the selected records and the rest
of the fields in the table.

I've created a select query that has the follwoing syntax entered in
the criteria for serial# : "Forms![Form1]![Combo0].OnClick". Form1 is
the name of my form and Combo0 is the name of my combo box. So far all
this does is display an empty record of the query. What am I missing?
Thanks in advance for your help.
 
try

[Forms]![Form1]![Combo0]

leaving off the ".OnClick" in the query's criteria row.

a note of caution: allowing the user to open a query directly may expose
the table data to direct editing, which you won't be able to control.
suggest you bind the query to a subform or pop-up form, and display the
returned dataset that way, instead.

hth
 
Thanks again Tina. When I take away the .onclick, now the query
doesn't pop up at all. Is there something I need to do in the design
view of the form1 to get it to link together?

Thanks for your note of caution, something I will definitely take into
consideration.
 
HA, I figured it out. You were right, I just created a macro to open
the query.

Thanks again Tina, you're godsend.
 
1.) I would create a query; under its criteria for SerialNumber (use instead
of Serial#,
since the # sign confuses Access with dates), put in
"Forms![Form1]![Combo0]"
without the quotation marks OR "Me![Combo0]".

Next, create a SPREADSHEET FORM (read-only) based
on that query. Let's call this form "frmYourRecordsAsSpreadsheet"

Next, using the combo box's AfterUpdate event:

Sub Combo0_AfterUpdate()
Docmd.Openform "frmYourRecordsAsSpreadsheet", acFormDS, , ,
acFormReadOnly
End Sub

Depending on the version of Access you are using, it may be "acReadOnly" -
see the "OpenForm Method" in Access HELP.

OR

2.) Create a query; under its criteria for SerialNumber (use instead of
Serial#,
since the # sign confuses Access with dates), do NOT put anything. The next
procedure assumes that the Serial Number field is actually a NUMBER; NOT
text!

Sub Combo0_AfterUpdate()
Docmd.Openform "frmYourRecordsAsSpreadsheet", acFormDS, , "SerialNumber
= " & Me![Combo0], acFormReadOnly
End Sub

3.) If the SerialNumber is a TEXT field: (Note the single quotes, then
double quotes)

Sub Combo0_AfterUpdate()
Docmd.Openform "frmYourRecordsAsSpreadsheet", acFormDS, , SerialNumber =
&
' Me![Combo0]' ", acFormReadOnly
End Sub

3.) You should get into the habit of giving your objects recognizable names:
e.g., frmMainMenu; frmReports, etc. "frm" for forms
qrySerialNumbersForComboBox "qry" for queries
qryOrdersThisPastMonthForRpt "qry" for queries
cboSerialNumbers (instead of Combo0) "cbo" for
combo boxes
rptOrdersThisPastMonth - "rpt" for reports
and so on.

---Phil Szlyk
 
Back
Top