New to VB 1

[btrini10]

Hi
I am trying to filter a report based on a form through VB code. The following is the code that I assume will work and obviously it is not. Could someone please point out the errors in the code. Thx

stDocName = "Audit Committee copy"
stDocName1 = "Aud com rep selection"
strWhere = "Reports!stDocName!StatusBox = Forms!stDocName1!Staus_box"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

 

[PaulBricker]

It's hard putting different variables inside this type of string. Try it this way if Status_box is a string datatype

stDocName = "Audit Committee copy"
stDocName1 = "Forms!Aud com rep selection!Status_Box"
strWhere = "StatusBox = "' & stDoc1 & "'"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere


If Status_box is true/false or numeric then try this.

stDocName = "Audit Committee copy"
stDocName1 = "Aud com rep selection"
strWhere = "StatusBox = " & stDocName1
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Paul

 

[PaulBricker]

Sorry typo in the first example. I reversed my ' & "
It should be

strWhere = "StatusBox = '" & stDoc1 & "'"

Paul

 

[btrini10]

Hey Paul, thx for the suggestion. There still seems to be an issue with the recognition of the report field,"StatusBox". When I run the code it asks for this value. Do i need to specify that this field is on the report?

 

[btrini10]

Got it Paul. Thanks a lot.

 

[btrini10]

Hey Paul
I am not getting any errors, but the code is not doing what it is supposed to. I am not getting any records on my form. If I change it from being "equal" to being "not equal" it works and gives me every record. I am assuming one of the variables has a space or something in it that is making it different. Could you suggest how I could find the issue?
Thx

 

[PaulBricker]

If the code is running all the way through without error then the names are probably correct. If there was a problem with a name or a variable, you would get a parameter box popping up and asking you for the value, like you were seeing after I had posted my revision to your code.
The first thing I would suggest is to see what value is being returned for the field

Forms![Aud com rep selection]!Status_box

Is the field named Status_box or Staus_box. Your original post had it as Staus_box. I assumed that was a typo because the field on your report is StatusBox. Whichever it is, add a line like this in the code.

stDocName = "Audit Committee copy"
stDocName1 = "Forms!Aud com rep selection!Status_Box"
Debug.Print stDocName1
strWhere = "StatusBox = "' & stDoc1 & "'"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

That will print the value of the variable, stDocName1, to the debug window (known as the [blue]immediate window[/blue]). If that window isn't open in the vba window, on the menu bar go to View...Immediate Window. It should add the window to the bottom of your vba window. Check to see that you are putting a value into the strWhere clause that will actually return records for your report.

Give it a try and post back with the results.

Paul

 

[btrini10]

Good morning Paul, Hope you had a great weekend. Back to my little issue
The following is now the code that I have.

stDocName = "Audit Committee copy"
stDocName1 = "Forms!Aud com rep selection!StatusBox"
Debug.Print stDocName1
strWhere = "StatusName = '" & stDoc1 & "'"
Debug.Print strWhere
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

My field was named Staus_Box, but I changed it for consistency. The two debug statements are giving me

Forms!Aud com rep selection!StatusBox
StatusName = ''

The form name and field name are both correct. I tried hard coding one of the values into the "stdoc1" field "started" and it works, so I know "StatusName" is working. I am not sure why "stdoc1" is not giving a value.
I have given my forma caption, but I dod not think this is the issue as I have also tried changing the name in the code to match the caption.

 

[btrini10]

Hey Paul
Ok I got it. The assignment to stDocName1 should just be StatusBox without any reference to the form. Also, I was using stDoc1 in the strWhere and I changed it to the full stDocName1 and it worked.
Thanks again for all your help and have a great day.