Setting textbox values on a report using vba

[Markb384]

Hi guys

Brief caveat, I don't understand the full power of Access, I'm sure, and I know even less about reports.

I want to generate a report once a user makes a selection from a combobox and presses a button. I have the report already made, and it is only for printing, so I have the following vba code to display it to the user in print preview.

    'open report page in print preview mode    
DoCmd.OpenReport "Calculation Report", acViewPreview

Based on the user choices from prior, my aim is to populate the report (unbound textboxes) using data from a specific record (single date and equipment ID) but from several different tables for different tests performed.. The returned values then need to be subjected to some calculations which use data from "reference" tables etc. and deposited back on the form in another textbox. Doing such a thing on a form is a doddle and using DLookup I can quickly and easily make the functions I need.

The part I am struggling with the most is the most basic part, setting the value of a textbox on the report and retrieving the value back in vba. Using testdata, I don't get any errors when it runs, but I don't get any values appearing on the report either. My current code is below, but I have also tried using an intermediary variable as well to no avail (although the value of the intermediary is fine). teststring is just some criteria to find the right record, and that works fine in other parts of the code.

[Reports]![Calculation Report]![text_hd_norm_field] = CStr(Nz(DLookup("[Norm Field Size]", "[Results_Hutt_Dig]", testString)))

I don't understand why this isn't working, does anyone have any idea where I am going wrong.

[ItsMe]

You can format a report using VBA. The VBA can reference a public variable or an open form to determine formatting criteria. If a textbox within the report is bound you should update the table and not the report.

[NTC]

ummmmm well it's difficult to interpret your post; but in general - - - reports are great at sums/counts/calcs in the footer section of bound fields (that have a record source) ; and one can do some code in the OnFormat event of a report's section. so those aspects of what you seek - if they are do-able - would be done one of those 2 points.

reports are not objects for elaborate on-the-fly situational calculations: so otherwise all calcs/data must be pre-prepped via query(s) and put together into a final query/table that is the report's record source....

[Markb384]

Thank you NTC. Fundamentally, my post was asking how to retrieve and dump information into an unbound textbox in a report, the rest of the post was just background on why I need to do that. If I understand your reply correctly, it seems like reports can't be used for the purpose I need, as least using the method I am currently trying (which is effectively like a limited spreadsheet).

Looks like I have two options...

- Create a form in a similar style to my report and apply coding like I described in my OP

- Add another table which temporarily holds the calculated data and dump that into the report instead

[ItsMe]

You can use the format event handler to populate unbound controls, using variables. Take a look at the property sheet of the group header/footer, or detail, where your control resides.

[Markb384]

HI itsme, for time constraint purposes, I have decided to proceed with a form as I know how to handle those, particularly as the main reason I was using a report was for chart reasons, which are no longer needed.

Thank you anyway

[ItsMe]

I don't understand how a form is going to save time if you need a report. If a report suits the need, use a report. Displaying variables in a report is not a big deal.