Get values of a field from subform in datasheet view (1 Viewer)

sv89 · Jan 11, 2011

Hi,

I'm trying to get all values of a field from a subform in datasheet view using the following code:

Code:

Dim rs As DAO.Recordset
Set rs = Me.sbfrmMySubForm.Form.RecordsetClone
   Do While Not rs.EOF
        strMyValues = "" & Forms!frmMyMainForm!sbfrmMySubform!MyField & ""
   Loop

MS Access just hangs up and ends abruptly due to the code above. Any ideas?

sv89 · Jan 12, 2011

Thanks Bob. Solved the hanging up part

I've got 4 rows in my subform datasheet but the above code returns the value in the first row 4 times.

sv89 · Jan 12, 2011

Thanks Bob

ahmed_optom · Aug 24, 2017

Sorry to revive this dead thread, but Im trying to do something similar and having trouble.

Dim rs As DAO.Recordset
Set rs = Me.Transactions_subform.Form.RecordsetClone
Do While Not rs.EOF
[Status] = "closed"
[Staff] = Parent.loggedinas
[Payment Amount] = "10"
[Payment Type] = Me.List73
[PaymentDate] = Now()
rs.MoveNext
Loop

Thats my code, the problem is it only changes the top entry in the datasheet. Any ideas?

My plan is for it to go down the datasheet and change each field accordingly.

testuser2 · Aug 25, 2017

ahmed_optom said:
Sorry to revive this dead thread, but Im trying to do something similar and having trouble.

Dim rs As DAO.Recordset
Set rs = Me.Transactions_subform.Form.RecordsetClone
Do While Not rs.EOF
[Status] = "closed"
[Staff] = Parent.loggedinas
[Payment Amount] = "10"
[Payment Type] = Me.List73
[PaymentDate] = Now()
rs.MoveNext
Loop

Thats my code, the problem is it only changes the top entry in the datasheet. Any ideas?

My plan is for it to go down the datasheet and change each field accordingly.

Change this -
RecordsetClone
to just
Recordset

as you are changing the clone of the recordset instead of the recordset to which the form is bound.

jdraw · Aug 25, 2017

Research the
Recordset.AddNew and
Recordset.Update methods

gemma-the-husky · Aug 25, 2017

your syntax isn't correct, I thinkwhen you modify a row in a recordset you need this

rs.edit to initiate the edit
rs!fieldname to address the edited field
(or rs.fields("fieldname")
rs.update to save the edit

ahmed_optom · Aug 26, 2017

Thanks guys.

It worked by removing clone.

However, in the end i used an update query, which seems to be the better method, at least for me it was easier to implement.

ahmed_optom · May 19, 2018

sorry to revive a dead thread again !

However I am trying to accomplish the same thing as sv89, however bobs answer appears to be deleted.

Basically I have a form and subform, the subform is in a datasheet view. I would like a value from the top result in the datasheet to be copied into a text box on the form.

Any ideas?

Pat Hartman · May 19, 2018

The problem of course is that when you reference a continuous or DS form from outside of the form, you only "see" the current record. This will frequently be the first record in the recordset but not always.

In the logic of a relational database, it makes no sense to duplicate a value from one child record in the parent record.

If you want to do this - and I suggest that you don't - the best place is the AfterUpdate event of the subform. This will give you the opportunity to copy a value from the subform to the main form.

ahmed_optom · May 20, 2018

Pat Hartman said:
The problem of course is that when you reference a continuous or DS form from outside of the form, you only "see" the current record. This will frequently be the first record in the recordset but not always.

In the logic of a relational database, it makes no sense to duplicate a value from one child record in the parent record.

If you want to do this - and I suggest that you don't - the best place is the AfterUpdate event of the subform. This will give you the opportunity to copy a value from the subform to the main form.

the datasheet is organised by time, and I want to know a value from the most recent entry.

eg each patient has many visits, but only one recall date. the subform shows all the visits, but I want to copy the most recent visit's recall onto the main form.

does this make sense?

Get values of a field from subform in datasheet view (1 Viewer)

sv89

Registered User.

sv89

Registered User.

sv89

Registered User.

ahmed_optom

Registered User.

testuser2

New member

jdraw

Super Moderator

gemma-the-husky

Super Moderator

ahmed_optom

Registered User.

ahmed_optom

Registered User.

Pat Hartman

Super Moderator

ahmed_optom

Registered User.

Similar threads

Users who are viewing this thread