Run code for every record in report

[CamtheChamp]

Hi,



I am still very new to Access so there may be a better way to do this all together. I was given a poorly designed Access database and I am trying to not have to recreate the entire thing.

I have a report where I need to create a textbox for each record that displays information based on the value of certain fields. It changes for each record so I need it to run for each record.

My code:

Private Sub DisplayFrameTypes()
Dim FrameTypeText As String
Dim engCMID As String
Dim SQL As String
Dim MyRec As ADODB.Recordset
engCMID = Me.txtEngineeringCostModelID
SQL = "SELECT qryfrmFrameTypeList.EngineeringCostModelID, qryfrmFrameTypeList.FrameType1, qryfrmFrameTypeList.FrameType2, qryfrmFrameTypeList.FrameType3, qryfrmFrameTypeList.FrameType4, qryfrmFrameTypeList.FrameType5, qryfrmFrameTypeList.FrameType6, qryfrmFrameTypeList.FrameType7, qryfrmFrameTypeList.FrameType8, qryfrmFrameTypeList.FrameType9, qryfrmFrameTypeList.FrameType10, qryfrmFrameTypeList.FrameType11 " & _
"FROM qryfrmFrameTypeList " & _
"WHERE qryfrmFrameTypeList.EngineeringCostModelID =" & engCMID & ""

Set MyRec = New ADODB.Recordset
MyRec.Open SQL, CurrentProject.Connection
If MyRec("FrameType1") = -1 Then FrameTypeText = "SGen6-Aux" & vbCrLf
If MyRec("FrameType2") = -1 Then FrameTypeText = FrameTypeText & "SGT6-2000E" & vbCrLf
If MyRec("FrameType3") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F4" & vbCrLf
If MyRec("FrameType4") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F5" & vbCrLf
If MyRec("FrameType5") = -1 Then FrameTypeText = FrameTypeText & "SGT6-5000F6" & vbCrLf
If MyRec("FrameType6") = -1 Then FrameTypeText = FrameTypeText & "SGT6-8000H" & vbCrLf
If MyRec("FrameType7") = -1 Then FrameTypeText = FrameTypeText & "SGT6-8000H(SS)" & vbCrLf
If MyRec("FrameType8") = -1 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-50)" & vbCrLf
If MyRec("FrameType9") = -1 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-55)" & vbCrLf
If MyRec("FrameType10") = -1 Then FrameTypeText = FrameTypeText & "SST6-2000H(110-46)" & vbCrLf
If MyRec("FrameType11") = -1 Then FrameTypeText = FrameTypeText & "SST6-PAC" & vbCrLf
Me.Text28.Value = FrameTypeText

End Sub

The code works fine when I call the function in Private Sub Text28_Click() for a specific record that I click. But I need it to work for each record as soon as the report is created.

Any ideas on where I should be calling this function or if I should be going in a totally different direction?

Thanks for your help!

[CamtheChamp]

I believe I just figured out the Detail_Print event does what I need. I am still not sure why it worked though.

[ssanfu]

I have a report where I need to create a textbox for each record that displays information based on the value of certain fields

I am thoroughly confused.
Are you really "creating" a textbox on the report or updating the value?

------------------------------------------
First, a couple of corrections. You have:

Dim engCMID As String

engCMID = Me.txtEngineeringCostModelID

"WHERE qryfrmFrameTypeList.EngineeringCostModelID =" & engCMID & ""

You declare engCMID as a string, but in the WHERE clause, you do not have text delimiters for engCMID. I think "engCMID" is a number (probably a Long) and Access is doing a conversion from a string to a number. So the lines should look like:

Dim engCMID As Long

engCMID = Me.txtEngineeringCostModelID

"WHERE qryfrmFrameTypeList.EngineeringCostModelID =" & engCMID

------------------------------------------
Value is the default property, so you can save typing and use

Me.Text28 = , instead of Me.Text28.Value =

------------------------------------------
You opened the recordset, but didn't close it. You should have

MyRec.Close

to close the recordset.

------------------------------------------
What is the record source for the report? If it is a query, I think I would change your code into a function and call it in the query.
The function could be:

Function FrameType(FT1 As Boolean, FT2 As Boolean, FT3 As Boolean, FT4 As Boolean, FT5 As Boolean, FT6 As Boolean, FT7 As Boolean, FT8 As Boolean, FT9 As Boolean, FT10 As Boolean, FT11 As Boolean) As String

   FrameType = ""

   If FT1 Then FrameTypeText = "SGen6-Aux" & vbCrLf
   If FT2 Then FrameTypeText = FrameTypeText & "SGT6-2000E" & vbCrLf
   If FT3 Then FrameTypeText = FrameTypeText & "SGT6-5000F4" & vbCrLf
   If FT4 Then FrameTypeText = FrameTypeText & "SGT6-5000F5" & vbCrLf
   If FT5 Then FrameTypeText = FrameTypeText & "SGT6-5000F6" & vbCrLf
   If FT6 Then FrameTypeText = FrameTypeText & "SGT6-8000H" & vbCrLf
   If FT7 Then FrameTypeText = FrameTypeText & "SGT6-8000H(SS)" & vbCrLf
   If FT8 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-50)" & vbCrLf
   If FT9 Then FrameTypeText = FrameTypeText & "SST6-1000A(104-55)" & vbCrLf
   If FT10 Then FrameTypeText = FrameTypeText & "SST6-2000H(110-46)" & vbCrLf
   If FT11 Then FrameTypeText = FrameTypeText & "SST6-PAC" & vbCrLf

   'Return
   FrameType = FrameTypeText

End Function

To use it in the query, the column would look something like:

SELECT qryfrmFrameTypeList.EngineeringCostModelID, qryfrmFrameTypeList.FrameType1, qryfrmFrameTypeList.FrameType2, qryfrmFrameTypeList.FrameType3, qryfrmFrameTypeList.FrameType4, qryfrmFrameTypeList.FrameType5, qryfrmFrameTypeList.FrameType6, qryfrmFrameTypeList.FrameType7, qryfrmFrameTypeList.FrameType8, qryfrmFrameTypeList.FrameType9, qryfrmFrameTypeList.FrameType10, qryfrmFrameTypeList.FrameType11, 
FT_Text:FrameType(FrameType1, FrameType1, FrameType3, FrameType4, FrameType5 , FrameType6 , FrameType7 , FrameType8 , FrameType9,  FrameType10,  FrameType11)

FROM qryfrmFrameTypeList

[CamtheChamp]

Awesome, thanks for the suggestions. What is the importance of doing MyRec.Close?

I haven't gotten into writing code for Queries yet, so I guess I will try that next.

[ssanfu]

If you don't close the recordset (something you opened), it can lead to memory leaks - you eventually run out of memory (RAM).

It's not code for queries, but you can use a function to do calculations that couldn't be done in a column in a query. You can do simple things like

FullName: FName & " " & LName

to create a full name. But if you want do a calculation like your example or something more complicated, write a function...