Filter report using VBA

[daSpud]

Thanks for the info on visibility. I found that Me.Lot_No works just fine to test and in the msgbox statement. 1 more hurdle passed.



I want to be able to construct my own address line (these are labels). Currently the label is as follows: https://www.screencast.com/t/mhes0me3929

The top line prints the data from the table and the other lines are prompted as parameters and I just ignore them in my testing, but now I have enough info to get serious about specifying my own strings to go there (thanks to your help). The table contains various address information and I need to parse it out depending on the content of some of the other table fields. The current application has a whole bunch of logic crammed into one of the tiny boxes on the query they are using. I am trying to move the whole thing to VBA so I need to know how to define strings that I can put on the label/report and fill with data in VBA.

[June7]

How would you want to 'fill with data in VBA'? Can't directly set Value property of textbox on report.

[pbaldy]

I'll get out of the way, though you can set the value of a textbox on a report with VBA. It would look similar to what I said for a textbox control source:

Me.TextboxName ="Dear " & [FirstName] & ", thank you for blah"

[daSpud]

Yep, I can't do it directly to the textbox.

In other systems I can put a variable name on a report detail section and it prints whatever is in that variable at the time for formatting. I had hoped that Access had something similar so I can look at the table entry and format what I want to appear on the label.

I want to put something on the report that says "use the current value of this when you format the line". And I will have given this the text I want to be there. It works for fields in the table and I was hoping for a field somewhere not in the table that I could manipulate with VBA. Maybe I can't do this in Access?

[daSpud]

I'll get out of the way, though you can set the value of a textbox on a report with VBA. It would look similar to what I said for a textbox control source:

Me.TextboxName ="Dear " & [FirstName] & ", thank you for blah"

This is exactly what I wanted and it works! THANK YOU

[daSpud]

...Can't directly set Value property of textbox on report.

Me.Text8.value = "blah blah" worked perfectly. It also works without the .value

When I originally tried it the textbox was bound to =Trim[name] and when I put an unbound textbox on the report I could assign the value I wanted.

[June7]

Well, dang! I could have sworn I have gotten "Cannot set value after report open is initialized" (or similar wording) error message in past when trying to set Value property of textbox on report with VBA so I quit trying. Wish I could remember circumstances that led me down that path.


Yes, Value is default property of data controls.

[pbaldy]

This is exactly what I wanted and it works! THANK YOU

Happy to help!

[daSpud]

To answer the question of why I want to do things in VBA, I found this:

Temp_Address_1: IIf(DatePart("m",Now())=1 And [Here_Jan]="Y" Or DatePart("m",Now())=2 And [Here_Feb]="Y" Or DatePart("m",Now())=3 And [Here_Mar]="Y" Or DatePart("m",Now())=4 And [Here_Apr]="Y" Or DatePart("m",Now())=5 And [Here_May]="Y" Or DatePart("m",Now())=6 And [Here_Jun]="Y" Or DatePart("m",Now())=7 And [Here_Jul]="Y" Or DatePart("m",Now())=8 And [Here_Aug]="Y" Or DatePart("m",Now())=9 And [Here_Sep]="Y" Or DatePart("m",Now())=10 And [Here_Oct]="Y" Or DatePart("m",Now())=11 And [Here_Nov]="Y" Or DatePart("m",Now())=12 And [Here_Dec]="Y",Null,[Second_Address_Line_1])

for each of the fields Address1, address2, city, state, zip and country. In VBA I can set the current month number and make the test of the "here" fields one time and construct the proper address. Part of this problem is poor database design, but I cannot do anything about that.

[daSpud]

Setback -- the Me.fieldname only works for fields ACTUALLY on the report layout. Even though the the field name prompt window comes up when you type "Me." and it has all the record source table names in it you get a not found error when it runs.

When I discovered that Me.Lot_No worked Lot_No was a field in the report detail section. Once I took it out and put in the text boxes I wanted to format, I lost the ability to reference it.

Is there some kind of a BIND statement that lets me reference all the fields in a table in VBA?

[daSpud]

Got around it by defining text boxes with all the needed fields concatenated and put on the report 0 high. Would still like to know if there is an easier way.

[isladogs]

The "Me.ControlName' notation is for controls on the currently used object whether it's a form or report.

So the control does need to be on the form/report though it can be shrunk as you've done or hidden by setting visible=false

Alternative methods include using public variables or tempvars.
It's largely a matter of personal preference