recordset values displaying only *one* value on form

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = rs
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set cn = Nothing
 
Hi

I don't do a lot of ADO but your Do While loop
would seem to get you quickly to the last record
Which would then show in your form. To test - comment out the
rs.movenext with a " ' " and see what you get - bet its the first record.
 
Your current code continually overwrites the txtid value on the form, so you
see only the last item from the recordset:

Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop


Why don't you just set the rs recordset to be the form's Recordset? Although
this will result in a read-only form, will that be ok?

Or are you wanting to "fill in" the data on the form so that it's editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.

Post the form's RecordSource query/table statement, and tell us what you're
trying to do with and on this form. Is the form in Single Form view or is it
in Continuous Forms view?
 
Thank you very much, and thanks for explaining why the value simply
gets overwritten, that makes sense now.

Yes, a readonly form will be ok - if I set the forms recordset to be
the same as rs, that does not work either. This is an unbound form. So
I guess in addition to setting the forms recordset to be equal to rs,
should I also set the data source of the text field to be the same as
the rs field? I thought that's what I was doing, but that did not
work. Do you have a sample that I can use? Thank you so much!

Your current code continually overwrites the txtid value on the form, so you
see only the last item from the recordset:

 Do While Not rs.EOF
    Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
     rs.MoveNext
   Loop

Why don't you just set the rs recordset to be the form's Recordset? Although
this will result in a read-only form, will that be ok?

Or are you wanting to "fill in" the data on the form so that it's editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.

Post the form's RecordSource query/table statement, and tell us what you're
trying to do with and on this form. Is the form in Single Form view or isit
in Continuous Forms view?

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.
Click to expand...
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
     Set .ActiveConnection = cn
     .Source = qrydept
     .LockType = adLockReadOnly
       .CursorType = adOpenStatic
     .CursorLocation = adUseClient
     .Open
  End With
Click to expand...
  Set Me.Recordset = rs
Do While Not rs.EOF
   Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
    rs.MoveNext
  Loop
Click to expand...
 rs.Close
   Set rs = Nothing
  Set cn = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
To display more than one record in a form, the form's controls must be bound
to fields in the form's RecordSource (or, in your case, the form's
Recordset). So you need to set the ControlSource of each of the controls
that will display data to be the name of a field in the rs recordset -- do
this in the design view of the form and save the form.

Then your code would change to this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = rs

Set rs = Nothing
Set cn = Nothing

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Thank you very much, and thanks for explaining why the value simply
gets overwritten, that makes sense now.

Yes, a readonly form will be ok - if I set the forms recordset to be
the same as rs, that does not work either. This is an unbound form. So
I guess in addition to setting the forms recordset to be equal to rs,
should I also set the data source of the text field to be the same as
the rs field? I thought that's what I was doing, but that did not
work. Do you have a sample that I can use? Thank you so much!

Your current code continually overwrites the txtid value on the form, so
you
see only the last item from the recordset:

Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop

Why don't you just set the rs recordset to be the form's Recordset?
Although
this will result in a read-only form, will that be ok?

Or are you wanting to "fill in" the data on the form so that it's
editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.

Post the form's RecordSource query/table statement, and tell us what
you're
trying to do with and on this form. Is the form in Single Form view or is
it
in Continuous Forms view?

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/




I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.
Click to expand...
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Click to expand...
Set Me.Recordset = rs
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
Click to expand...
rs.Close
Set rs = Nothing
Set cn = Nothing- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
To display more than one record in a form, the form's controls must be bound
to fields in the form's RecordSource (or, in your case, the form's
Recordset). So you need to set the ControlSource of each of the controls
that will display data to be the name of a field in the rs recordset -- do
this in the design view of the form and save the form.

Then your code would change to this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
      Set .ActiveConnection = cn
      .Source = qrydept
      .LockType = adLockReadOnly
        .CursorType = adOpenStatic
      .CursorLocation = adUseClient
      .Open
End With

Set Me.Recordset = rs

Set rs = Nothing
Set cn = Nothing

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Thank you very much, and thanks for explaining why the value simply
gets overwritten, that makes sense now.

Yes, a readonly form will be ok - if I set the forms recordset to be
the same as rs, that does not work either. This is an unbound form. So
I guess in addition to setting the forms recordset to be equal to rs,
should I also set the data source of the text field to be the same as
the rs field? I thought that's what I was doing, but that did not
work. Do you have a sample that I can use? Thank you so much!

Your current code continually overwrites the txtid value on the form, so
you
see only the last item from the recordset:
Click to expand...
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
Click to expand...
Why don't you just set the rs recordset to be the form's Recordset?
Although
this will result in a read-only form, will that be ok?
Click to expand...
Or are you wanting to "fill in" the data on the form so that it's
editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.
Click to expand...
Post the form's RecordSource query/table statement, and tell us what
you're
trying to do with and on this form. Is the form in Single Form view or is
it
in Continuous Forms view?

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
news:7fe429cb-4421-4321-a1a2-d855b672e21b@j32g2000yqh.googlegroups.com....
Click to expand...
I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Set Me.Recordset = rs
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you SO much - I will give this a try. One question though -
inorder to to set the ControlSource of each of the controls in design
view, the RecordSource of the form needs to be set to the table or
query from the the ControlSource comes from, right? The query I need
is created at run time - if all the fields in the query do not belong
to one table, how can the ControlSource be set at design time? Thank
you again, so much for your time.
 
You can set the ControlSources in the form's design view (do this by
assigning a query with the fields to the form's RecordSource property,
assign the ControlSource values to the fields, save the form, delete the
query from the form's RecordSource, and save and close the form).

Or you can assign the ControlSource values in code.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = rs

Me.ControlName1.ControlSource = "NameOfField1"
Me.ControlName2.ControlSource = "NameOfField2"
Me.ControlName3.ControlSource = "NameOfField3"
' and so on for all controls to be bound to fields

Set rs = Nothing
Set cn = Nothing


--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



To display more than one record in a form, the form's controls must be
bound
to fields in the form's RecordSource (or, in your case, the form's
Recordset). So you need to set the ControlSource of each of the controls
that will display data to be the name of a field in the rs recordset -- do
this in the design view of the form and save the form.

Then your code would change to this:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With

Set Me.Recordset = rs

Set rs = Nothing
Set cn = Nothing

--

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


Thank you very much, and thanks for explaining why the value simply
gets overwritten, that makes sense now.

Yes, a readonly form will be ok - if I set the forms recordset to be
the same as rs, that does not work either. This is an unbound form. So
I guess in addition to setting the forms recordset to be equal to rs,
should I also set the data source of the text field to be the same as
the rs field? I thought that's what I was doing, but that did not
work. Do you have a sample that I can use? Thank you so much!

Your current code continually overwrites the txtid value on the form, so
you
see only the last item from the recordset:
Click to expand...
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
Click to expand...
Why don't you just set the rs recordset to be the form's Recordset?
Although
this will result in a read-only form, will that be ok?
Click to expand...
Or are you wanting to "fill in" the data on the form so that it's
editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.
Click to expand...
Post the form's RecordSource query/table statement, and tell us what
you're
trying to do with and on this form. Is the form in Single Form view or
is
it
in Continuous Forms view?

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
news:7fe429cb-4421-4321-a1a2-d855b672e21b@j32g2000yqh.googlegroups.com...
Click to expand...
I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Set Me.Recordset = rs
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you SO much - I will give this a try. One question though -
inorder to to set the ControlSource of each of the controls in design
view, the RecordSource of the form needs to be set to the table or
query from the the ControlSource comes from, right? The query I need
is created at run time - if all the fields in the query do not belong
to one table, how can the ControlSource be set at design time? Thank
you again, so much for your time.
 
You can set the ControlSources in the form's design view (do this by
assigning a query with the fields to the form's RecordSource property,
assign the ControlSource values to the fields, save the form, delete the
query from the form's RecordSource, and save and close the form).

Or you can assign the ControlSource values in code.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
      Set .ActiveConnection = cn
      .Source = qrydept
      .LockType = adLockReadOnly
        .CursorType = adOpenStatic
      .CursorLocation = adUseClient
      .Open
End With

Set Me.Recordset = rs

Me.ControlName1.ControlSource = "NameOfField1"
Me.ControlName2.ControlSource = "NameOfField2"
Me.ControlName3.ControlSource = "NameOfField3"
' and so on for all controls to be bound to fields

Set rs = Nothing
Set cn = Nothing

--

        Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/


To display more than one record in a form, the form's controls must be
bound
to fields in the form's RecordSource (or, in your case, the form's
Recordset). So you need to set the ControlSource of each of the controls
that will display data to be the name of a field in the rs recordset --do
this in the design view of the form and save the form.
Click to expand...
Then your code would change to this:
Click to expand...
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Click to expand...
Set Me.Recordset = rs
Click to expand...
Set rs = Nothing
Set cn = Nothing

Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/
Click to expand...
"Sue" <[email protected]> wrote in message
Click to expand...
Thank you very much, and thanks for explaining why the value simply
gets overwritten, that makes sense now.
Click to expand...
Yes, a readonly form will be ok - if I set the forms recordset to be
the same as rs, that does not work either. This is an unbound form. So
I guess in addition to setting the forms recordset to be equal to rs,
should I also set the data source of the text field to be the same as
the rs field? I thought that's what I was doing, but that did not
work. Do you have a sample that I can use? Thank you so much!
Your current code continually overwrites the txtid value on the form,so
you
see only the last item from the recordset:
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
Why don't you just set the rs recordset to be the form's Recordset?
Although
this will result in a read-only form, will that be ok?
Or are you wanting to "fill in" the data on the form so that it's
editable?
If the latter is correct, there are other ways that are less
overhead-intensive to do this.
Post the form's RecordSource query/table statement, and tell us what
you're
trying to do with and on this form. Is the form in Single Form view or
is
it
in Continuous Forms view?
--
Ken Snell
<MS ACCESS MVP>http://www.accessmvp.com/KDSnell/

I am creating an adodb recordest and setting the recoredset property
of the form to the adodb recordset - but what's happening is that only
the last value of the recordset is displayed in the form, so even if
the recordset has 25 records, only one value (the last one) is
displayed - this happens even when the form is set to be datasheet
view- please see my code below, any suggestions will be really
helpful, thank you very much.
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = qrydept
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.Open
End With
Set Me.Recordset = rs
Do While Not rs.EOF
Forms![frmDeptSearch]!txtid = rec.Fields![EMPLID].Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set cn = Nothing- Hide quoted text -
- Show quoted text -- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

Thank you SO much - I will give this a try. One question though -
inorder to to set the ControlSource of each of the controls in design
view, the RecordSource of the form needs to be set to the table or
query from the the ControlSource comes from, right? The query I need
is created at run time - if all the fields in the query do not belong
to one table, how can the ControlSource be set at design time? Thank
you again, so much for your time.- Hide quoted text -

- Show quoted text -
Click to expand...


====
It Works!!! Thanks again, SO MUCH for all help finally it works, and
perfectly!! I set the ControlSource in code just like you said...took
me for ever to figure out - the whole of this 3 day memorial weekend
actually, thanks a LOT!
 
Back
Top