Compare Multiple Fields from Current Record and Populate Text Box

[JohnsonG]

My first time creating an Access report, so I apologize if this is a simple task. I have experience with writing VBA for Excel, but I'm not familiar with the means or methods here.



Above is a screenshot of my report. Most text boxes have the Control Source as a field from a table (TAG_NO, EDESC, etc..). These work as expected when I view a print preview of the report. However, the Services Required text box needs to compare multiple fields from the current record and then populate the text box. I was thinking maybe I need to create a BeforeUpdate event for the text box or something along those lines and compare the fields for the current record. However, I'm clueless how to code this. Any help would be greatly appreciated. Let me know if you need anything else.

[ranman256]

the screenshot is unavailable, but if you need to populate other textboxs, then your query needs to link to the tables that HAVE the data, then bring down the needed fields into the query.

[JohnsonG]

Ranman thanks for your response! I tried to reattach the image to my original post, because it will make more sense. I did successfully bring in the table with the data I need, and I linked many of the fields to existing text boxes. There's just the one text box I need to compare multiple fields that's highlighted in the screenshot.

[ranman256]

Not sure what you mean by 'compare'. you dont comare in a report.
do you mean link the field to a table and get the result?

[JohnsonG]

I've linked many fields to a table and it works as expected. However, one of the text boxes I report needs to have logic based on 5 different fields from the linked table. Is there a way I can create vb code to do this?

[JohnsonG]

I was able to figure out a way to accomplish what I needed to get done. I set the Control Source equal to a function I created. I then placed 5 invisible text boxes and linked them to the fields I needed to compare. Without placing these invisible text boxes linked to the fields in my report, the function wouldn't work.



Here's the code of the function:

Public Function GetServicesReqd() As String
Dim servreqd As String

servreqd = ""

If Me.NO_SERVICES_REQD = "Yes" Then
servreqd = ""
GoTo leavefunct
End If

If Me.BASE_REQD = "Yes" Then
servreqd = servreqd & "B"
End If

If Me.CONTROLS_REQD = "Yes" Then
servreqd = servreqd & "C"
End If

If Me.ELECTRICAL_REQD = "Yes" Then
servreqd = servreqd & "E"
End If

If Me.FREEZE_PROT_REQD = "Yes" Then
servreqd = servreqd & "F"
End If


leavefunct:
GetServicesReqd = servreqd

End Function