Loop through records, but skips the first record (1 Viewer)

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Hi everyone! I have a question i hope you call all help me out with. I'm completely stuck here :S.

The code below is working, but it skips the first record it goes through it seems.

What im trying to do: it needs to loop through "shipments" table, find if the answer is "Yes" and if so, put an icon on my report.

Problem:
It does put the icon on my report when the answer is "Yes" throughout my table somewhere, but not if the first record has the answer "Yes" answer.

What is going on here :eek: Thanks in advance! Would help a great deal!


Code:
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM shipments Where [checklistID] = " & Me![checklistID])

Do Until rs.EOF

    If rs!dg = "Yes" Then 'this is my form field
        Me("attentiondg").Visible = True
        Else
        Me("attentiondg").Visible = False
    End If
    
    If rs!t1 = "Yes" Then 'this is my form field
        Me("attentiont1").Visible = True
        Else
        Me("attentiont1").Visible = False
    End If

rs.MoveNext
Loop

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
    
 End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:04
Joined
Sep 21, 2011
Messages
14,047
Prehaps rs.MoveFirst before the loop, and test that records have been retuned before using it?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
Why not join your base table and shipmnt table in a query and use the query as the recordsource of the report.
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Hi Guys. It seemed logical to have a query because i have 2 tables running in this case.

However, a problem popped up:

I have a print button for the report, i have the code running "on print".
When i press print it gives me:

"Too few parameters. Expected 1"

What could this be? Everything seems fine. Im using Access 2016 btw
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
What is your sql in the query.
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:04
Joined
Sep 21, 2011
Messages
14,047
I'd hazard a guess it is asking for this?


checklistID


Hi Guys. It seemed logical to have a query because i have 2 tables running in this case.

However, a problem popped up:

I have a print button for the report, i have the code running "on print".
When i press print it gives me:

"Too few parameters. Expected 1"

What could this be? Everything seems fine. Im using Access 2016 btw
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Code:
SELECT drivers.checklistID AS drivers_checklistID, drivers.comeinside, drivers.company, shipments.shipmentID, shipments.checklistID AS shipments_checklistID, shipments.dg, shipments.air, shipments.t1
FROM drivers RIGHT JOIN shipments ON drivers.[checklistID] = shipments.[checklistID];

mmm...checklistID spelling is correct though :S
I keep investigating here...but i dont know
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Aaaah...ofcourse it has to be drivers_checklistID now because thats the name in the query :banghead: :)
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
So, that was it. Just the right query name.

BUT, now im still with the same problem though...
After some testing i found, it's only applying (the icons on my report) based on the Yes values of my LAST record.

Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)


Code:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM checklist_print_query Where [drivers_checklistID] = " & Me![checklistID])

Do Until rs.EOF
rs.MoveNext
    If rs!dg = "Yes" Then 'this is my form field
        Me("dgicon").Visible = True
        Else
        Me("dgicon").Visible = False
    End If
    
    If rs!t1 = "Yes" Then 'this is my form field
        Me("t1icon").Visible = True
        Else
        Me("t1icon").Visible = False
    End If
    
    If rs!air = "Yes" Then 'this is my form field
        Me("airicon").Visible = True
        Else
        Me("airicon").Visible = False
    End If
    
   If Me.comeinside.Value = "Yes" Then
        Me("insideicon").Visible = True
        Else
        Me("insideicon").Visible = False
   End If
       
    

Loop

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up
   
End Sub

It needs to go through all the records of the query.
If theres an answer "Yes" ANYWHERE in the DG, T1, Air columns, it needs to put the icon on the report.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 21:04
Joined
Sep 21, 2011
Messages
14,047
What is your code in the report?
I'd expect you would have an IIF function or some VBA code.
It would have to be at the detail level to switch on and off depending on your flags?
It sounds to me like you are just testing once somehow?
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Thanks gasman, just put it in my last reply. Didn't want to get too messy with the replys. Im sorry :S
 

Gasman

Enthusiastic Amateur
Local time
Today, 21:04
Joined
Sep 21, 2011
Messages
14,047
I think what arnelgp was getting at was to bring in the flags with the rest of your data, then test on each record.
If you put code in the report header, that will only run each time a header is produced?
You would need it on each detail line, or so I believe.


Edit: Ok, I have just spotted the sentences below the code, so makes what I have stated previously nonsense.
I'd probably would look at a Dlookup for each of the fields and set a boolean flag blnIconNeeded if any of them are Yes.?
 
Last edited:

Minty

AWF VIP
Local time
Today, 21:04
Joined
Jul 26, 2013
Messages
10,355
Arnel's suggestion and it would be mine too, would be simply move the computation into the underlying query as a calculated field.

If it can be done with a DLookup() it can be joined in the query and will be a lot more efficient.
 

bruceblack

Registered User.
Local time
Today, 21:04
Joined
Jun 30, 2017
Messages
119
Thanks guys!


Logically: it loops... so it will run though the whole statement for every record and the final change takes place at the last record. So the whole statement is based on the last record ofcourse i see now.

I dont really use flags. Im a rookie with them. I will find out what they mean though. Your solution is kinda above my head right now and dont know where to start :confused: Awesome you guys help out so much though. Its well appreciated!


Maybe i could run an If inside and If?
Something like:

Code:
        If Me("attentiondg").Visible = Flase Then
        If rs!dg = "Yes" Then 'this is my form field
        Me("attentiondg").Visible = True
        Else
        Me("attentiondg").Visible = False

        End If
        End If

Something like if its already visible, leave it there
 

Minty

AWF VIP
Local time
Today, 21:04
Joined
Jul 26, 2013
Messages
10,355
Can you post up the SQL for the reports records source ? I'm sure between us we could write you the expression required to join the data correctly.
 

static

Registered User.
Local time
Today, 21:04
Joined
Nov 2, 2015
Messages
823
You don't need to loop if you are only looking up one value.

Code:
Private Sub ReportHeader_Print(Cancel As Integer, PrintCount As Integer)

    Me("dgicon").Visible  = Nz(DLookup(True, "checklist_print_query", "dg ='Yes' and drivers_checklistID=" & checklistID), False)
    Me("t1icon").Visible  = Nz(DLookup(True, "checklist_print_query", "t1 ='Yes' and drivers_checklistID=" & checklistID), False)
    Me("airicon").Visible = Nz(DLookup(True, "checklist_print_query", "air='Yes' and drivers_checklistID=" & checklistID), False)
	
    Me("insideicon").Visible = Me.comeinside.Value = "Yes"
End Sub
 

Users who are viewing this thread

Top Bottom