Make label visible if first record

[DIH]

Hi all,

I have a subreport based on a query called, "qryOrderDetails".
I want to make dollar sign labels visible only on the first detail record.

Here is the code I have managed to gather from some internet searching
(I'm just starting to learn some vba)

Dim qdf As QueryDef, rst As Recordset
Set qdf = DBEngine(0)(0).QueryDefs("qryOrderDetails")
Set rst = qdf.OpenRecordset()

If rst.AbsolutePosition = 0 Then
Me.lbldollar1.Visible = True
Else
Me.lbldollar1.Visible = False
End If

Here is how I want the report to look:

QTY U/M DESC PRICE TOTAL
2 EA STAPLES $ 2.99 $ 5.98
3 EA CLIPS 1.50 4.50
1 BOX PENS 10.95 10.95

ETC.... (notice the dollar signs are visible only for the first record
for "STAPLES")

The code above however shows dollar signs for every record. I think I
need some kind of FOR EACH, NEXT statement but I'm not sure how to write
it. Any help is greatly appreciated.

Dave

 

[Evi]

it may be easier than this.

Put a text box in the section that contains your label (txtCount)
Type =1 into it
Set its Running Sum Property (on the Data tab in Properties) to Whole Group
or Over All depending on your report structure - check visually to see that
it says 1 when you want it to then make the textbox invisible in Properties

Then, in the OnFormat event of the section put

If Me.txtCount=1 Then
Me.lbldollar1.visible = True
Else
Me.lbldollar1.Visible = False
End If

Evi

 

[DIH]

Thank you so much! This works perfectly. Just for learning sake, could
you or someone write out the vba code using the recordsets and loops?

Dave

 

[Larry Linson]

I think I understand what you want to do, but what you write does not do it.
You have opened a separate recordset that has nothing to do with the Report.

Larry Linson
Microsoft Office Access MVP

 

[Larry Linson]

Thank you so much! This works perfectly. Just for learning sake, could
you or someone write out the vba code using the recordsets and loops?

Evi's code was given to you in total... it needs neither recordsets nor
loops. It goes, if you will look, in the OnFormat event of the Detail
Section of the Report. It pays to read carefully, pay close attention, and
try before asking for someone to do something you only imagine may be
needed.

Perhaps you are asking for some other "vba code" that does involve
"recordsets and loops"; if so, then please clarify what you are looking for.

Larry Linson
Microsoft Office Access MVP

 

[DIH]

Larry,

Yes, Evi's answer was exactly right and what I'm going to use (thanks
again Evi). I was just asking (and thinking to myself) how one might go
about using vba code to find what record number you were at in a query
and then act on that info in the report based on that query. Like I
wrote in my original post, I'm just starting to learn some things in vba
and was simply trying to add to my (so far) rather limited knowledge and
understanding. If I'm asking in the wrong newsgroup or left out
something else, please excuse.

Dave

 

[Klatuu]

If you are asking a general VBA question, the answer is that you have to
open a recordset. You can then use the AbsolutePosition property of the
recordset to tell you which record you are on. Now, there are a couple of
important issues here.
First, if you are using a DAO recordset, the first record is 1, but if you
are using ADO, it is 0. So in DAO, the last record number will = the number
of records in the recordset, but for ADO, it will be the number of records -1.

Also, Access does not use presistent record numbers. That is to say that
any given record will have a different number depending on the order of the
records and other records having been added or deleted. AbsolutePosition is
only an ordinal reference to the current recordset.

 

[Evi]

Hi DIH, do you mean that you want an example of Recordset code and Do Loop
so that you can see how it works?

OK, here's one where I can renumber a set of tracks when put I together my
own CD from MP3s which I have on my harddrive.
The main form (based on TblCD) contains the primary key of TblCD, CDID.
The Subform, based on TblCDTracks which has the primary key CDTrID, contains
CDID as the Foreign Key field.

I use a simple DMax code to give me the next Track Number (Num) when I enter
each track but if I change my mind and want to move the tracks around, then
I use the code below to renumber the track numbers based their order in the
query QryRenumberTracks

Private Sub cmbRenumberTracks_Click()
Dim a As Integer
Dim MyCDID As Integer
Dim MyCDTrID As Integer
Dim MyTable As String
Dim Db As Database
Dim rs As Recordset

MyTable = "QryRenumberTracks"
'Qry is sorted in Num, CDTrID order
MyCDID = Me.CDID
Set Db = CurrentDb
Set rs = Db.OpenRecordset(MyTable, dbOpenDynaset)
a = 1
rs.MoveFirst
'go to first record in MyTable
rs.Filter = "CDID =" & MyCDID
'filter MyTable so that it only shows the Opera I want
Do Until rs.EOF
'stop when you get to the end of MyTable
rs.Edit
If rs!CDID = MyCDID Then


rs!Num = a
'put the current value of a in the Num field
rs.Update
'update that field - the previous line won't work without this
rs.MoveNext
'move to the next record
a = a + 1
Else
'if the record in the query isn't for the CD in my main form
rs.MoveNext
'go to the next record
End If
Loop

rs.Close
'tidy up
Set rs = Nothing
Set Db = Nothing
Me.Requery
'requery the main form so that everything shows
'Go back to correct page in the main form in case i lose it
Me.RecordsetClone.FindFirst "[CDID] = " & MyCDID
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Recordsets can be subforms themselves

The code below is in a multichoice questionnaire's main form where the user
can only choose one answer from a set of choices. The subform is called
FrmPersonAnswerSub2. The user has typed a number (MyNum) into a text box.
The code looks for the AnsNo that is the same as the number in the textbox.
It sets the AnsChoice Yes/No field next to that answer to True and then sets
all the other choices displayed in the subform to false.

Set MySub2 = Me.FrmPersonAnswerSub2
Set rst = MySub2.Form.RecordsetClone

rst.MoveFirst

Do Until rst.EOF

rst.Edit
If rst!AnsNo = MyNum Then
rst!AnsChoice = True
Else
rst!AnsChoice = False
'set all other answers for that question to false
End If
rst.Update
rst.MoveNext

Loop

Set MySub2 = Nothing
Set rst = Nothing

Evi

 

[DIH]

Thanks again Evi. My understanding has been greatly enhanced thanks to
your expertise. Much appreciated!

Hi DIH, do you mean that you want an example of Recordset code and Do Loop
so that you can see how it works?

OK, here's one where I can renumber a set of tracks when put I together my
own CD from MP3s which I have on my harddrive.
The main form (based on TblCD) contains the primary key of TblCD, CDID.
The Subform, based on TblCDTracks which has the primary key CDTrID, contains
CDID as the Foreign Key field.

I use a simple DMax code to give me the next Track Number (Num) when I enter
each track but if I change my mind and want to move the tracks around, then
I use the code below to renumber the track numbers based their order in the
query QryRenumberTracks

Private Sub cmbRenumberTracks_Click()
Dim a As Integer
Dim MyCDID As Integer
Dim MyCDTrID As Integer
Dim MyTable As String
Dim Db As Database
Dim rs As Recordset

MyTable = "QryRenumberTracks"
'Qry is sorted in Num, CDTrID order
MyCDID = Me.CDID
Set Db = CurrentDb
Set rs = Db.OpenRecordset(MyTable, dbOpenDynaset)
a = 1
rs.MoveFirst
'go to first record in MyTable
rs.Filter = "CDID =" & MyCDID
'filter MyTable so that it only shows the Opera I want
Do Until rs.EOF
'stop when you get to the end of MyTable
rs.Edit
If rs!CDID = MyCDID Then


rs!Num = a
'put the current value of a in the Num field
rs.Update
'update that field - the previous line won't work without this
rs.MoveNext
'move to the next record
a = a + 1
Else
'if the record in the query isn't for the CD in my main form
rs.MoveNext
'go to the next record
End If
Loop

rs.Close
'tidy up
Set rs = Nothing
Set Db = Nothing
Me.Requery
'requery the main form so that everything shows
'Go back to correct page in the main form in case i lose it
Me.RecordsetClone.FindFirst "[CDID] = " & MyCDID
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Recordsets can be subforms themselves

The code below is in a multichoice questionnaire's main form where the user
can only choose one answer from a set of choices. The subform is called
FrmPersonAnswerSub2. The user has typed a number (MyNum) into a text box.
The code looks for the AnsNo that is the same as the number in the textbox.
It sets the AnsChoice Yes/No field next to that answer to True and then sets
all the other choices displayed in the subform to false.

Set MySub2 = Me.FrmPersonAnswerSub2
Set rst = MySub2.Form.RecordsetClone

rst.MoveFirst

Do Until rst.EOF

rst.Edit
If rst!AnsNo = MyNum Then
rst!AnsChoice = True
Else
rst!AnsChoice = False
'set all other answers for that question to false
End If
rst.Update
rst.MoveNext

Loop

Set MySub2 = Nothing
Set rst = Nothing

Evi

Larry,

Yes, Evi's answer was exactly right and what I'm going to use (thanks
again Evi). I was just asking (and thinking to myself) how one might go
about using vba code to find what record number you were at in a query
and then act on that info in the report based on that query. Like I
wrote in my original post, I'm just starting to learn some things in vba
and was simply trying to add to my (so far) rather limited knowledge and
understanding. If I'm asking in the wrong newsgroup or left out
something else, please excuse.

Dave