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.microsoft.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]

@NeoPa
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

[NeoPa]

I'm pleased that helped :)

Unfortunately I read the rest a couple of times but could make no sense of it. I have no idea what you're asking.

[slenish]

@NeoPa
Ah sorry i might not has said that very clear.

Let me try again :)

What I have are 5 toggle switches. I want each one to represent a number value so what will happen is if you press toggle1 it will = 1 so when I hit the run report button it knows to pull the information for shift 1 if I hit toggle button 2 it will = 2 and pull the information for shift 2, and so on. The last toggle button will = 0 for all shift numbers and pull the information for all of them together. Any idea how i can get that to work right? I have it working for 1 toggle button but the way i did it is definatly not the best way.

I set up one button to have a value of 1 then I set the query for shift to look at that one toggle button then I set the report to also look at that toggle button. Just trying to figure out how can i set it so the query and the report will just look at any toggle button I press and not only 1.

Hope this is a little clearer

*UpDate*

Ok I figured out how to get this to work, but im still having some trouble. What I did was in the query critera i used the OR operator and then typed the next toggle button. Plus in the onClick command of each toggle button I set them to a value such as 1, 2, 3.

example

[forms]![ReportMenu]![Toggle6] Or [Forms]![ReportMenu]![Toggle7] 
 

but the problem im getting now is trying to make it pull all the information. Before if I left the Shift critera blank and the Agency name blank it would pull all of the information. Now when I try to set a Toggle buttons value to "" and leave the Agency field blank i just get errors.

Any ideas?

[OldBirdman]

Toggle Buttons, like Radio Buttons, can be items in a Frame. Each button will have a value that is assigned to the frame when the button is pressed. The frame will also set all the other buttons to "False", so only one can be pressed at a time. The Frame can be made borderless, flat, and transparent if you do not want it to show. The captions on the buttons can be descriptive: Day, Swing, Graveyard, All, ...
If the "All Shifts" button is pressed, then the shift element is not used in the SQL statement.

[NeoPa]

I suspect OB's answer is what you need here Slenish. Let us know how you get on :)

[slenish]

Thank you OldBirdman and NeoPa,

Well im still having trouble with this. I got one problem fixed which was getting my agency drop down to accept a blank value and it would pull all the information but im still having a problem with the "All Shifts" toggle button.

Here is what i tried. I tried to set the All Shifts button to have a value of "*". I also set up an effect were if you press one toggle button all of the other toggle buttons disapear and other information pops up, but if you press the toggle button again it should reset the form and all the buttons come back. For some reason with the value set to "*" it will not reset the form but as soon as i put a number value in for the button it works fine. I thought maybe it I set the toggle value to "*" then it would pull all information for all shifts which seems to work with the Agency. I would just make adjustments in the query. So im still having touble with this. Not sure how to set the SQL statement to not be used if the button is pressed. Any further examples would be great.

appreaciate the help!!

[NeoPa]

I've read this a number of times and still find nothing I can work with.

If you're asking a question please give enough context for the question to make sense in an Access environment. Telling me you're using "*" doesn't give me any understanding unless I have some context. At this stage of the thread I don't want to have to reread all the other posts to work out your meaning. It is your responsibility to post a question that is understandable. Not ours to research so much that we can turn what you say into something that makes sense.

[slenish]

@NeoPa
Hi NeoPa,

Wanted to let you know I finally got this to work earlier today. I found out with toggle buttons in order for them to work right you have to set them as a number value. So what I did was set the All Shifts Toggle to equal a value of 5. Then I created a new column on my table for employee names that was called all shifts that held the value 5 so when the button was clicked it would look at the table for the number 5 and know to pull all the records. Which was really cool. Took me a while to get it but yeah i did it! :D

[NeoPa]

Clearly communicating via a forum can be difficult at times, but nevertheless I'm pleased you managed to find your solution. It's often more gratifying that way anyway, as I'm sure you know :)

[slenish]

Yeah it is hard to always communicate exactly what you are trying to do on forums with out seeing the actual program. It is a nice feeling when you figure it out on your own, frustrating during the process but a relief in the end. :) thanks again for the support

[ADezii]

@slenish
After making certain assumptions on the Form and Control Names, the SQL would look similar to:

SELECT tblTest.TheDate, tblTest.Location, tblTest.[Shift Number]
FROM tblTest
WHERE tblTest.TheDate Between [Forms]![Form1]![txtStartDate] And [Forms]![Form1]![txtEndDate] 
AND tblTest.Location=[Forms]![Form1]![txtLocation]
AND tblTest.[Shift Number]=[Forms]![Form1]![txtShiftNumber];

[OldBirdman]

I don't think the last code addresses the "All Shifts" issue. Perhaps better is:

Dim strSQL As String
If [Forms]![Form1]![txtShiftNumber] = 5 then
  strSQL = ";"
Else
  strSQL = " AND tblTest.[Shift Number]=[Forms]![Form1]![txtShiftNumber];"
End If
strSQL = "SELECT tblTest.TheDate, tblTest.Location, tblTest.[Shift Number] " & _
"FROM tblTest " & _
"WHERE tblTest.TheDate Between [Forms]![Form1]![txtStartDate] And [Forms]![Form1]![txtEndDate] " & _
"AND tblTest.Location=[Forms]![Form1]![txtLocation]" & strSQL