Understanding VBA Code attached to a Report?

[ralphralph07]

Hi

I have a report that worked fine in a previous version but since updating no longer prints the required record.

I've tried reviewing the code below but I can't seem to find how it sets which record to print? What i ideally need is to replace the current code to prompt the user for the labRef rather than access doing it automatically?

I've tried setting a parameter on the Result Parameter Query but this hasn't helped.

I've looked the parameterquery but i cant find this in the database?

Sub print_analysis(LabREF As String, Site As String, suite As String, stype As String, repname As String, Parameterquery)

'   This routine sets all of the test parameters used in suites to
'   invisible, then resets those used by the suite to visible.


'*********  SET VARIABLE AND PARAMETERS *****************
Dim mydb As Database, st As Recordset, res As Recordset, testname As String
Dim myc As Control, tgrp As String, resgrp As String, mysql As String
Dim current_group As String, lab_ref  As String
Dim equivalence  As String, testvalue As Double
Dim testuom As String, comments As String, print_pos As Integer


Set mydb = CurrentDb()
Set st = mydb.OpenRecordset(Parameterquery)
Set res = mydb.OpenRecordset("Results Parameters")



'**********    PREPARE REPORT TITLE   *******************
Call prepare_title(repname, stype, Site)

'*********** SET BOXES TO "M" AND MAKE INVISIBLE ***********
For j = 1 To 44
Reports(r_name)("box" + Str$(j)).Visible = False
Reports(r_name)("box" + Str$(j)).Caption = "M"
Reports(r_name)("eq" + Str$(j)).Visible = False
Reports(r_name)("eq" + Str$(j)).Caption = "M"
Reports(r_name)("val" + Str$(j)).Visible = False
Reports(r_name)("val" + Str$(j)).Value = "M"
Reports(r_name)("uom" + Str$(j)).Visible = False
Reports(r_name)("uom" + Str$(j)).Caption = "M"
Reports(r_name)("com" + Str$(j)).Visible = False
Reports(r_name)("com" + Str$(j)).Caption = "M"
Next

'*********** SET BOTH RECORDSETS TO FIRST RECORD ***********
res.MoveFirst
st.MoveFirst
current_group = "General"



'**********  CYCLE THROUGH BOXES UP TO 44 ******************
For I = 1 To 44
    print_pos = I
  

check_eof:
If st.EOF Then                           'check for last result record
    If res.EOF Then
        Exit Sub
        End If
End If

If res.EOF Then GoTo print_standard


If res![Lab Ref No] = LabREF Then
    GoTo process_labref
    Else
    res.MoveNext
    GoTo check_eof
End If

process_labref:
If st.EOF Then GoTo print_results

resgrp = res!t_grp
tgrp = st!t_grp
tpos = st!f_pos
respos = res!f_pos


If resgrp > tgrp Then GoTo print_standard
If resgrp < tgrp Then GoTo print_results
If resgrp = tgrp Then GoTo compare_positions
MsgBox "error in group sorting"


compare_positions:
    If tpos > respos Then GoTo print_results          'compare print positions
    If tpos < respos Then GoTo print_standard         ' and print the lowest
    If tpos = respos Then GoTo print_both
    MsgBox "error in position sorting"


print_results:
    If res!t_grp > current_group Then
        print_pos = print_pos + 1
        I = I + 1
    End If
    equivalence = res!Equiv
    testvalue = res!Value
    testuom = res!UOM
        
        If IsNull(res!p_comm) Then
            comments = " "
        Else
            comments = res!p_comm
        End If

    current_group = res!t_grp                          'set current group to
    testname = res!T_name
    Call PrintResultValues(LabREF, repname, testname, equivalence, testvalue, testuom, comments, print_pos)
    res.MoveNext                              ' one being printed
    GoTo last_line

print_standard:
    If st!t_grp > current_group Then
        I = I + 1
        print_pos = print_pos + 1
    End If
    testname = st!T_name
    current_group = st!t_grp    'set current group to one being printed
    Call Printst(repname, testname, print_pos)     'PRINT st TEXT
    'MsgBox "current group is now " & current_group
    st.MoveNext                                    'MOVENEXT st
    GoTo last_line


print_both:
    If res!t_grp > current_group Then
        I = I + 1
        print_pos = print_pos + 1
    End If
    current_group = res!t_grp          'set current group to one being printed
    testname = res!T_name
    equivalence = res!Equiv
    testvalue = res!Value
    testuom = res!UOM
        
        If IsNull(res!p_comm) Then
            comments = " "
        Else
            comments = res!p_comm
        End If
    
    Call PrintResultValues(LabREF, repname, testname, equivalence, testvalue, testuom, comments, print_pos)
    res.MoveNext
    st.MoveNext
    GoTo last_line
        
        
last_line:
    'If resgrp > current_group Then             'Check for new group and
     '   If tgrp > current_group Then I = I + 1   'leave space if true
    'End If

check_nomatch:
    If res!EOF = True Then
        If st!EOF = True Then
          Exit Sub
     End If
    End If

skip_record:
Next

Exit Sub

End Sub

 

[ck1999]

The labref variable is being sent from another procedure that calls this procedure!

if you want to use a user input
place
labref=inputbox("Enter Lab Reference Number")
before '********** CYCLE THROUGH BOXES UP TO 44 ******************


ck1999

 

[ralphralph07]

Hi

Thanks for the information but i've tried this but it doesn't prompt - i've attached the procedure that calls this procedure as this might help! (I hope as i'm going round in circles...)

I'd be happy to get it to work how it did before we moved it from 97

Private Sub PageHeader0_Print(Cancel As Integer, PrintCount As Integer)
Dim tsuite As String, tsite As String, stype As String, r_name  As String
Dim lab_ref As String, parameter_query  As String
On Error Resume Next

r_name = "report analyses"
tsite = [Site]
tsuite = [t_suite]
stype = [s_type]
lab_ref = [Lab_Ref_No]

'WRITE REPORT HEADER INFORMATION

Reports(rname).[Site].Visible = False
Reports(rname).[slash].Visible = False
Reports(rname).[Enquiry].Visible = False
Reports(rname).[cont_enq_text].Visible = False
Reports(rname).[Producer].Visible = False
Reports(rname).[producer_text].Visible = False

If stype = "W" Or stype = "A" Then
    parameter_query = "standard parameters-W"
    Else
    parameter_query = "standard parameters-noneW"
End If

Call print_analysis(lab_ref, tsite, tsuite, stype, r_name, parameter_query)



End Sub

Any help greatly received as turning greyer by the hour!

Ralph

 

[ck1999]

according to this the labref number for your report is [Lab_Ref_No] field. Does this field not have the correct value of what you want to print?

ck1999

 

[ralphralph07]

There is a labref field pointing to the correct field in the table but when the report prints - it prints nothing.

There is a filter I've just found

 ([complete]=yes  and [Lab ref no] between [Lab Ref From] and [Lab Ref To])

But i cant see how it knows what lab ref to print?

Ralph