Combo Box help please

[Dermot]

I have created........
A table of contacts "tblContactDetails"
A Query "qryContactDetails"
A Query qryParameterSurname
A Report "rptQueryParameterSurname2"
A form with a 2 column ( First / Last Name) combo box in it "cboCantactNames"

Questions
1. I can anyone advise me how I get a contacts details report to be
displayed when I click on a contacts name in the drop down list in the combo
box.

2. Do I need to use VBA to do this, can you post a "commented" example
please that I could examine.

3. Any other tips relevant would be appreciated

Thanks

 

[KARL DEWEY]

Build a query that uses the combo box as criteria --
[Forms]![YourFormName]![YourComboBox]

Use the query for record source for the report.

Build a macro that open the report.

In the property for the combo box - On Double Click put the macro. Open the
pull down and select the macro from the list.

Make your selection and then all you have to do is double click the combo
box to run the report.

 

[Allen]

Assuming your report's recordsource has the fields FirstName and
LastName, then these two lines of code should work:

If cboContactNames.ListIndex < 0 Then Exit Sub
DoCmd.OpenReport "rptQueryParameterSurname2", acViewPreview, ,
"FirstName=" & Chr(34) & cboContactNames.Column(0,
cboContactNames.ListIndex) & Chr(34) & " And LastName=" & Chr(34) &
cboContactNames.Column(0, cboContactNames.ListIndex) & Chr(34)

The last parameter of OpenReport contains the where clause, limiting
the report to show only the records that meet that condition. The
Chr(34) is the double quote character. Surrounding the names with
double quotes ensures that names with an apostrophe (like O'Neil) will
not cause an error.

For more examples in VBA code, see the tools at
http://www.aislebyaisle.com/access/tools3.htm

 

[Dermot]

Hi Karl,
Thanks for your reply.
I have tried what your suggested but don't get the results I expected.
Correct my error please.....
I built my query fro the record source (tblContacts)
In the "Surname Field" in the criteria field I entered
[Forms]![frmMyContacts]![combo7]
Created the open report macro and set the double click property as you
suggested.
What Happens now.......
A Enter Parameter Value Box opens with the text
"[Forms]![frmMyContacts]![combo7]" in it...........
exactly as it is typed.....I know this is wrong and must have entered the
criteria in the wrong place....can you explain what I might have done wrong?

I would like to include both the First and Second name Column to both
effect the relevant report to appear in the combo Box. (as I have several
contacts with the same surname)......not sure how I go about this either.

I hope I have explained my problem, well.
If you need further information let me know.
Thanks
Dermot

Build a query that uses the combo box as criteria --
[Forms]![YourFormName]![YourComboBox]

Use the query for record source for the report.

Build a macro that open the report.

In the property for the combo box - On Double Click put the macro. Open the
pull down and select the macro from the list.

Make your selection and then all you have to do is double click the combo
box to run the report.

I have created........
A table of contacts "tblContactDetails"
A Query "qryContactDetails"
A Query qryParameterSurname
A Report "rptQueryParameterSurname2"
A form with a 2 column ( First / Last Name) combo box in it "cboCantactNames"

Questions
1. I can anyone advise me how I get a contacts details report to be
displayed when I click on a contacts name in the drop down list in the combo
box.

2. Do I need to use VBA to do this, can you post a "commented" example
please that I could examine.

3. Any other tips relevant would be appreciated

Thanks

 

[Dermot]

Thanks for the reply Allen.
Thanks for the interesting link too.
Is the code you supplied for the combo box?

I am also interested to know where I went wrong In the posting above when I
tied to do what Karl explained to me?
Can you explain to me where I went wrong?
Thanks
Dermot