Reporting a limited group of Records (1 Viewer)

[rwpetrie]

How can I have a report, when it is opened, to prompt the user for a value (which in my case is a Job #) and then the report use that as the filtering factor to show only those records? I assume that it will take some correlation between a Macro, a query, and the report, but can't figure out how to do it. Thank you in advance for any assistance!

 

[S]

Try this code on your report "On open" event:

Dim Que As String
Que = InputBox("Input Job #")
Que = "select * from tablename where ([fieldname] = " & Que & ");"
Me.RecordSource = Que

Set the table as report source.
Hope this helps you, Bye, S.

 

[rwpetrie]

You'll have to excuse me, I have not worked much with VB, but I think I see what you are saying. Although I can't get the second "Que=" statement written correctly. Could you show me exactly how it should be written? The Query I want to use is called "Cost Query" and the Field name is "Job #" Thanks again for your help, I just found this site, and I can't believe how helpful we can all be to each other. I love the internet!

 

[S]

Dont use the query, just change the word "tablename" with the real name of the table your "cost query" works on; for example having a table named "Mary":
Dim Que As String
Que = InputBox("Input Job #")
Que = "select * from Mary where ([job #] = " & Que & ");"
Me.RecordSource = Que

 

[S]

If [Job #] isn't a numeric field but is a text ones you must add "" between Que (string from inputbox); Chr(34) is Ansi code to generate ""; try this code if you have an Runtime error using the previus post code.

Dim Que As String
Que = InputBox("Input Job #")
Que = "select * from Mary where ([job #] = " & Chr(34) & Que & Chr(34) & ");"
Me.RecordSource = Que

Bye, S.