Drop down list and report

M

meisaka

Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
 
D

Duane Hookom

Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
 
M

meisaka

Thank you Duane.

The code you provide works.

More questions, now I want create 2 drop down menus, one is Borough and one
is address, when I select a Borough from the Borough menu the Address menu
will show only the addresses belong to that Borough.

After I selected the Boro and Address, I want to have the user info report,
how do I do it?

Thanks again.


Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
 
M

meisaka

Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
 
D

Duane Hookom

Go to www.rogersaccesslibrary.com and find his small sample database that
describes how to use Cascading Combo Boxes.

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Thank you Duane.

The code you provide works.

More questions, now I want create 2 drop down menus, one is Borough and one
is address, when I select a Borough from the Borough menu the Address menu
will show only the addresses belong to that Borough.

After I selected the Boro and Address, I want to have the user info report,
how do I do it?

Thanks again.


Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

If you want to order the records, try:
SELECT [address]
FROM [AllUser]
GROUP BY [address]
ORDER BY [address];

I don't know what you mean by "tried Boro".

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


meisaka said:
Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
M

meisaka

Thanks again.

Duane Hookom said:
If you want to order the records, try:
SELECT [address]
FROM [AllUser]
GROUP BY [address]
ORDER BY [address];

I don't know what you mean by "tried Boro".

--
Duane Hookom
Microsoft Access MVP


meisaka said:
Also, when I tried "Boro" with the similar code, it returned an error message
[Microsoft][ODBC SQL Server Driver]ORDER BY items must appear in the select
list if SELECT DISTINCT is specificed. (#145)

What am I missing? THanks again.

Duane Hookom said:
Use DISTINCT rather than DISTINCTROW
SELECT DISTINCT [address]
FROM [AllUser];

The most formatting to Excel (without code) would probably be to use the
menus to send the results to Excel.

Have you considered using Excel to pull the information using MS Query?
--
Duane Hookom
Microsoft Access MVP


:

Hi, I am a little new to Access and I need some helps on my reports...

I have a table called AllUser which has Addresses and user info.

I want to create a form that allow the users to click on a drop down menu of
the addresses and display a report the user info of the selected address(es)

I tried to create combo box and entered "select distinctrow [address] from
[AllUser] but it gave me duplicated addresses.

Also, how can I export the report in a formatted excel file? For example,
preset cell width, header and footer.

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Drop Down List on a Report 1
Having a drop down combo box preceed the running of a report 5
Drop Down Box 2
Print a Report based on a drop-down menu of choices. 8
Filtering Report Data 3
Drop Down menu on report 1
Select from Drop down list 1
Selecting a report from a drop down box 3

Top