recordset

What am I doing wrong that I have forgotten or is stupid?

I am creating a recordset in the report

I have created a textbox called 'txtCase' in the detail section.

I am looping through the recordset:

strSql = "Select a.[case#], a.name_1, b.niin " & _
"from PIPSFF17 a, PIPSFF01_CASE b " & _
"Where b.corp = 'H' and a.[case#] =b.[case#] and a.name_1='" &
gFullName & "'"

Set rsDao = CurrentDb.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)

Do Until rsDao.EOF
txtCase = rs![case#]
rsDao.MoveNext
Loop
rsDao.Close
Set rsDao = Nothing


The only record that appears to get written is the last record in the
recordset. In other words, the detail is not showing the detail. It is
overwriting the txtCase textbox with only the last one showing obviously. I
have tried putting the code in the detail section, print, form open, and the
format section with no such luck.

What obvious mistake am I making......

Thanks,
Jason

85ascMcLaren wrote:

What am I doing wrong that I have forgotten or is stupid?

I am creating a recordset in the report

I have created a textbox called 'txtCase' in the detail section.

I am looping through the recordset:

strSql = "Select a.[case#], a.name_1, b.niin " & _
"from PIPSFF17 a, PIPSFF01_CASE b " & _
"Where b.corp = 'H' and a.[case#] =b.[case#] and a.name_1='" &
gFullName & "'"

Set rsDao = CurrentDb.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)

Do Until rsDao.EOF
txtCase = rs![case#]
rsDao.MoveNext
Loop
rsDao.Close
Set rsDao = Nothing

The only record that appears to get written is the last record in the
recordset. In other words, the detail is not showing the detail. It is
overwriting the txtCase textbox with only the last one showing obviously. I
have tried putting the code in the detail section, print, form open, and the
format section with no such luck.


What do you want it to do?

If it's supposed to construct a string of case nos for the
specified name, then you need to concatenate the value from
each record:

Do Until rsDao.EOF
txtCase = txtCase & "," & rs![case#]
rsDao.MoveNext
Loop
txtCase = Mid(txtCase, 2)

--
Marsh
MVP [MS Access]

Problem is, I am use to Crystal Reports where the field keep biulding in a
vertical placement as long as there is data to be read. I want the 'txtCase'
field to keep goinf 'down'. Just like when you use the wizard to build a
report, the fields in the detail section do what they are suppose to do -
which is keep going 'gown' the page until the recordset ( or data) is empty.
I want to do the same thing except I am creating my own code and populating
the fields in the detail section with the recordset fields, but it ain't
working. It just populates the last field. For instance, the Case text
field should go down. Lilke

123a
123b
123c
...

It is only letting the last value stick in the field unlike what a detail
section is suppose to do. I tried using something similar to what you had
except with the vbcrlf instead of the comma and that still didn't work. I
must be doing something wrong here.... Do you see what I am trying to do
(just like the wizard creates).... ??

Thanks,
Jason

"Marshall Barton" wrote:

85ascMcLaren wrote:

What am I doing wrong that I have forgotten or is stupid?

I am creating a recordset in the report

I have created a textbox called 'txtCase' in the detail section.

I am looping through the recordset:

strSql = "Select a.[case#], a.name_1, b.niin " & _
"from PIPSFF17 a, PIPSFF01_CASE b " & _
"Where b.corp = 'H' and a.[case#] =b.[case#] and a.name_1='" &
gFullName & "'"

Set rsDao = CurrentDb.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)

Do Until rsDao.EOF
txtCase = rs![case#]
rsDao.MoveNext
Loop
rsDao.Close
Set rsDao = Nothing

The only record that appears to get written is the last record in the
recordset. In other words, the detail is not showing the detail. It is
overwriting the txtCase textbox with only the last one showing obviously. I
have tried putting the code in the detail section, print, form open, and the
format section with no such luck.


What do you want it to do?

If it's supposed to construct a string of case nos for the
specified name, then you need to concatenate the value from
each record:

Do Until rsDao.EOF
txtCase = txtCase & "," & rs![case#]
rsDao.MoveNext
Loop
txtCase = Mid(txtCase, 2)

--
Marsh
MVP [MS Access]

85ascMcLaren wrote:

Problem is, I am use to Crystal Reports where the field keep biulding in a
vertical placement as long as there is data to be read. I want the 'txtCase'
field to keep goinf 'down'. Just like when you use the wizard to build a
report, the fields in the detail section do what they are suppose to do -
which is keep going 'gown' the page until the recordset ( or data) is empty.
I want to do the same thing except I am creating my own code and populating
the fields in the detail section with the recordset fields, but it ain't
working. It just populates the last field. For instance, the Case text
field should go down. Lilke

123a
123b
123c
...

It is only letting the last value stick in the field unlike what a detail
section is suppose to do. I tried using something similar to what you had
except with the vbcrlf instead of the comma and that still didn't work. I
must be doing something wrong here.... Do you see what I am trying to do
(just like the wizard creates).... ??


The vbCrLf will work, but you have to set the text box's and
its section's CanGrow property to Yes. However, this is a
complex way of doing a simple thing.

The normal way of doing that is to use an INNER JOIN in the
report's record source query to make all the needed data
available to the report. Then use the Sorting and Grouping
feature (View menu) to specify a group on the name field
(and maybe the corp field?). Put the name data in the group
header section and the case data in the detail section.
This way, you don't need any code.

--
Marsh
MVP [MS Access]