Access form to create report based on selection from combo box

[SamsFriend]

I have imported data from a text document and created tables from each import.

I have a form with a combo box with a list of choices. The choices are the same name as the tables created from the imports. The names are related to dates of the data dump.

When the user selects a date (the imported file name) I would like a report to be generated using the data in the (user chosen) imported table.

How do I make a report based on the selection and how do I tell the report which table to pull the info from and then display it?

I will also need to further refine the report according to a user defined job number to only show records whith that job number.
Any advice would be greatly appreciated.

Thank you

 

[JBinQLD]

How about this as a starting point?

Create a query based on one of your tables, doesn't matter which, as you'll see.

Bind your report to the query you just made, Eg. qryMyQuery

On your form where the user chooses a table add something like this to your report open button

Dim db As Database
Dim qd As QueryDef
dim txtChosenTable as string

'adjust next line to set variable to your users chosen file/table - I've hard coded a value
txtChosenTable="Table1"

'open the query from VBA
Set db = CurrentDb
Set qd = db.QueryDefs("qryMyQuery")
    
'Change the SQL of the query   
qd.SQL = "SELECT " & txtChosenTable & ".* FROM " & txtChosenTable & ";"

'tidy up
Set qd = Nothing
set db=nothing

'Now open the report which is based on a query now based on all values in the users chosen table...

docmd.openreport "MyReport",acpreview

Hope that helps,

JB

 

[SamsFriend]

I'm sorry I have never used VB but really appreciate your help.

I put this onto the button event but have done something wrong as it is coming up with errors.

****************************************************

Private Sub btn_Hours_Click()

Dim db As Database
Dim qd As QueryDef
Dim txtChosenTable As String

'adjust next line to set variable to your users chosen file/table - I've hard coded a value
'cmb_Hours is the name of the combo box
txtChosenTable = "cmb_Hours"

'open the query from VBA
Set db = CurrentDb
' This line below is a problem
' qry_Hours is the name of the query
Set qd = db.QueryDefs("qry_Hours")

'Change the SQL of the query
' This line is also a problem
qd.SQL = "SELECT " & txtChosenTable & ".* FROM " & txtChosenTable & ";"

'tidy up
Set qd = Nothing
Set db = Nothing

'Now open the report which is based on a query now based on all values in the users chosen table...

' rpt_Hours is the name of the report
DoCmd.OpenReport "rpt_Hours", acPreview

End Sub

I think I have bitten off more than I can chew but trying

Sam's Friend

 

[JBinQLD]

the error is in this line:

txtChosenTable = "cmb_Hours"

Change it to

msgbox(me!cmb_Hours)
txtChosenTable=me!cmb_Hours

i've just dropped the msgbox in there so you get a visual of the control contents. Provided that shows the name of your imported table. Presuming that;s ok you can of course remove the line, it's just a very loose debugging technique

JB

 

[SamsFriend]

Thank you but that line still comes up as an error. Also the report I have made is only showing 1 ine of the query.

 

[JBinQLD]

does the msgbox fire? What does it say?

 

[SamsFriend]

No message box. I have fiddled a bit and can now get one of the combo boxes to work i.e. I select a date and click the print report button and it shows the report but it only has one line of the report. I will do the same with the other combo box. I just need to work out how to filter the data now to show the date picked from the first combo box and the job number from the second combo box. Is there a way of telling it, if a value is repeated, only show it one time in the combo box? Thank you JBinQld

 

[JBinQLD]

Look into SELECT DISTINCT.....

Glad you're making progress mate!

JB