basic: How do I simply get data from a field on a report using VBA?

[XenoZephyr]

I apologize, I feel that I should be able to figure this out but I'm missing something. I've searched online and on this forum and couldn't find the answers though I think I'm not using the correct language because I think this should be a very simple answer.



I have an Access 2010 database. We have a student records table, with fields like FirstName, LastName, and DatabaseID and also fields for Permanent Address and Current Address, and a true/false field of SameAsPerm to indicate if both addresses are the same.

From a form, we have a button that opens up the report in order to print it
strReportName = "rptStudent"
strCriteria = "[DatabaseID]=" & Me![DatabaseID]
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

On the report I need to check if SameAsPerm is true, then use the Permanent Address else use CurrentAddress, but when I try to reference the fields that should be there, all I get is errors.

Do I have to write code to create a recordset or a recordsetclone and then reference a field within that?? If the report has the data, I want to get to it directly. Thanks, I appreciate any help!




When I try to reference a field within the Report_Load sub I get errors
for example
ID = Me![DatabaseID] gives error saying that it can't find that field referenced within your expression

My report data source is set to SELECT Students.* FROM Students, and I'm only referencing fields within that Students table.

Am I running into problems because it's on the Load section?

What is the easiest straightforward way to read information from your table within a report? I have to think this is really straightforward and so I'm very confused as to why I can't get this to work at all.

[RuralGuy]

You need to include a report control for each field you want to reference. Have you?

[pbaldy]

I would probably have textboxes with control sources like:

=IIf(SameAsPerm = True, PermanentAddress, CurrentAddress)

You could do something similar in the report's source query.

[XenoZephyr]

No, I am trying to use unbound fields and then populate them, for example. I have a txtbox called txtFullName and I put the code:
txtFullName = me![FirstName] & " " & me![LastName] and it says that it can't find the fields FirstName and LastName...even though the recordsource of the report includes all of the fields from that table...

So I need to have a txtfield on the report and then set it to visible = false if I don't want it to show up? That seems bizarre and inefficient...?

[pbaldy]

No offense, but it seems "bizarre and inefficient" to use unbound fields and then populate them with code. The report's record source includes the data, so why use unbound textboxes?

It looks like Allan understood the question better than I did, and I didn't realize he had already answered (my apologies), so I'll get out of the way.

[RuralGuy]

I think you are doing a marvelous job on this one Paul. Please continue.

[XenoZephyr]

Sorry I wasn't trying to insult anyone by saying bizarre and inefficient, it was not a comment on anyone's programming. I said it because I'm just very confused by the difference between a Form and a Report. On a form, I can say ID = Me![DatabaseID] and it works fine, I can get data from any field that I want. On a report though it doesn't work so I felt like I was doing something wrong and/or missing something.

My reason for doing it this way is because we have 2 versions (Permanent vs Current) of 5 fields, Add1, Add2, City, State, and Zip. Plus we have Country for the Permanent address but not for Current because we know that's within the USA. So I wanted to check check once and make all the assignments in a batch instead of checking 5 times. The final output needs to be in the proper format of
Add1
Add2
City, State, Zip
Country

But skip the space for Add2 if that's blank and skip the space for Country if it's not needed...I can do that easily with building it with VBA code, but if I do it by controls, then I'll need to figure out a way to have Add2 somehow not on the report so that there's not a weird space there. How can I check to see if Add2 is null if I have to include a txtbox including Add2 regardless? That seems like a catch-22 and leaves me lost and confused. I hope I don't sound argumentative, I appreciate all the feedback, I'm just frustrated because this seems like it should be a very basic step in Access and I'm very confused by it. Plus I'm fairly certain I had it working at one point, directly getting data from the fields and made changes and when I thought I undid everything I couldn't get it working again. But it's possible I'm thinking of something else.

[pbaldy]

I've got this in the format event of the section containing the control on a report:

  Me.txtCustName = (Me.CustAttn + vbCrLf) & _
                   (Me.CustomerName + vbCrLf) & _
                   (Me.CustomerAddress1 + vbCrLf) & _
                   (Me.CustAddress2 + vbCrLf) & _
                   (Me.CustAddress3 + vbCrLf)

But as Allan mentioned, the referenced fields have controls on the report that are hidden. It does seem goofy to need them, but I've had the same experience he has.

[pbaldy]

I should have added that the mix of "&" and "+" is a trick that handles the potentially Null fields.

[XenoZephyr]

Got it all working, thanks again for all the help! I don't know enough that this is a path I would have found on my own!

[pbaldy]

No problem, we were happy to help!