Using a form to input information for a report

[slenish]

Hi everyone,

Well i have been playing with this for a few days and so far im getting no where. What I have is a form I created that you input a little data in order to pull a report. I have a report and query that already work great so im trying to intergrate it in to the report. Right now its set up where if you open the report you have to type in a date range, location name, and shift number what i want to do is make it so I can type that information in to a form i have set up and the query will go off of the information you type in the form instead of the little pop up boxes asking you to type it in.

One thing I did was for the shift number I set up 5 toggle buttons that would represent numbers 1 - 5 so when I press a toggle button i want that to register to the query as shift number, then you type in a location name and date range in text boxes.

I found a website that seems to have the information im looking for but been playing with it for a couple days now and im not sure what im doing wrong.

Any help would be great thanks

here is the site if found,
http://office.microsof t.com/en-us/access/HP051874111033. aspx

[TheSmileyCoder]

You can pass the variables in the openarg argument when opening the report(And then process it in within the report), you can base the reports query criteria directly upon the controls in the form (the form must then stay open) or you can use the form to setup a filter string, and pass that filter string to the report when opening it.

In most cases I would go with using the filter string.

Dim strCriteria as String
'Example of input checking, repeat for location and shift
If Me.txtDateRange & ""="" Then
  Msgbox "You must enter a date",VbOkOnly+vbExclamation
  Exit Sub
End If

strCriteria="ID_Location=" & Me.ComboBox_Location & " AND dtDate=#" & Me.txtDate & "# AND ID_Shift=" & Me.ComboBox_Shift
Docmd.OpenReport "MyReport",acPreview,,strCriteria

This code is assuming you are using a combobox for selecting the location and shift (And you should never use a normal textbox for entering location!)

[slenish]

Hi TSO,

Nice example!! Seems to be exactly what I was looking for. Just having one problem. Not sure where to put this. You said to use the form to set up a filter string but where would i put this? In the form commands? Or in the print command button? Or should I put it in a macro and then reference the macro when the button is pressed?

Thanks again!

[TheSmileyCoder]

I would put it in the print command button. If you want the report to print directly, without previewing, jsut replace acPreview with acNormal.
I myself have completely stopped using macros, as I seem to find it confusing to use part VBA code and part macros. Besides I find VBA to be 1000x more powerfull.

[slenish]

Hi TSO,

Thanks for writing back so quickly. Ok i plugged in the code and adjusted it but i am getting either a syntax error, type mismatch error, or a debug error. I put the code in to the on click event of the command button.

here is what I have,

strCriteria = "Agency = " & Me.Agency & " AND Date = me.BeginningDate #" & Me.EndingDate & #"  AND Shift =" & Me.Toggle6

Not sure where the problem is. Im thinking it might be with the date. Just wondering do i need to break the date field in to two boxes on the query for this to work with a start and end date? right now i have it set up as a where expression and it works when you just type it in the little pop up boxes.

I have another question that i just thought of. On my report i have a date field set up with

=[Enter Start Date] & " - " & [Enter End Date]

should i change that to look at the form as well something like

[Forms]![Form1]![BeginningDate]![EndingDate]

Just wondering if i should change part of the report and/or the query such as the criteria so that it also looks at the boxes on the form? Or should the code in the onClick command do everything?


Yeah i am not much of a macro user myself. I feel like they take to long to reference most of the time. From my experiences so far if you have VBA code right in the properties it seems to work a lot better. :)

Thanks again for helping me with this.

[slenish]

...........

[NeoPa]

Slenish,

You need to understand that a thread is not a general conversation. You need to post separate questions in separate threads otherwise everything gets into an unholy mess.

[NeoPa]

slenish:

strCriteria = "Agency = " & Me.Agency & " AND Date = me.BeginningDate #" & Me.EndingDate & #"  AND Shift =" & Me.Toggle6

Not sure where the problem is.

There are so many it's hard to list them all. I suggest you look at strCriteria after you've set it and see what it contains. This will give you a much better understanding of where the problems are. See below for more on this.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[slenish]

Hi NeoPa,

Sorry I was not meaning for my post to run on as a conversation also both posts go with the same original question just trying to figure out if there is more that I need to do from other angles than just typing in some code for the on click command.

Thanks for the info about using the Debugging. I will plug that in and see if it can show me anything more that im not seeing.

Thanks again

[NeoPa]

To be consistent it would need to be more like :

=[Forms]![Form1]![BeginningDate] & ' - ' & [Forms]![Form1]![EndingDate]

[NeoPa]

slenish: sorry both posts go with the same question i can just put them together

No.

That is neither correct nor acceptable. These are clearly separate questions and if we find such abuse of threads in future they will be edited and you will pick up a site infraction. I hope I'm being clear.

If you have any confusion about what is and is not acceptable then please read the site rules (FAQ: Posting Guidelines). These can be found from the site FAQ which is available on every page.

-Administrator.

[NeoPa]

I can see that you have edited your original response to make it clearer. As a general rule we are a little flexible with this. I'm not sure this really falls into that category, but we'll let it slide for now. Please be careful in future though - now you understand how much of a problem this can be and how serious we are about enforcing it.

[slenish]

Well i have been playing with the code more and trying to get this to work. Now I have it to accept the information from the form but on my report all of the info comes out in errors.

What I did was I put a few lines of code in to the query critera to make it look at the form and the boxes with the user input. Its seems to understand that something has been typed in but there is no output except the date for some reason.

here is what i have in the date, agency, shift critera

[Forms]![ReportMenu]![BeginningDate] & '-' & [Forms]![ReportMenu]![EndingDate]

[Forms]![ReportMenu]![Agency]

[Forms]![ReportMenu]![Toggle'*']

I also still have what TSO said to use in the form in the on button click along with on the report itself where the date field is i used the same code (as above) which could be why that one is working.

Also I will stive to be more politically correct in my posts going forward

Thanks

[NeoPa]

Here are my guesses as to what you're really looking for in the three posted examples. I hope this proves fruitful.

1. Between [Forms]![ReportMenu]![BeginningDate] And [Forms]![ReportMenu]![EndingDate]

2. [Forms]![ReportMenu]![Agency]

3. Like [Forms]![ReportMenu]![Toggle] & '*'

Let me know if any of these help.

[slenish]

Here are my guesses as to what you're really looking for in the three posted examples. I hope this proves fruitful.

1. Between [Forms]![ReportMenu]![BeginningDate] And [Forms]![ReportMenu]![EndingDate]

2. [Forms]![ReportMenu]![Agency]

3. Like [Forms]![ReportMenu]![Toggle] & '*'

Let me know if any of these help.

Hi NeoPa,

I have to say you are the man! Worked like a charm. My only problem now is trying to get the toggle buttons to equal different values for the shift. I set up one like this

dim value as string
value = 1
if toggle = true then 
toggle = value
else 
toggle = false
end if

not sure if i did that right or not because it puts a number one in the box for shift that it understand the value but instead of pulling just the information for shift 1 it pulls all shifts which I want for a different toggle button. Any ideas?

Thanks again for helping me with this