Loop Through UnBound Subform's records

[Andibevan]

What method would I use to loop through all the records of an unbound
continuous sub-form?

I understand that you can only use recordsetclone when the subform is bound
to a table or query.

Any pointers would be appreciated.

TIA

Andi

 

[Allen Browne]

If your continuous subform is unbound it will have only one row.

A continuous unbound subform isn't.

 

[Andibevan]

Thanks Allen - I've now bound my continuous subform to the following query:-

SELECT [tbl_Sign-off_Documents].[Sign-off Group],
[tbl_Sign-off_Documents].[ArchiveID], [tbl_Sign-off_Documents].[File Name],
[tbl_Sign-off_Documents].[Version] FROM [tbl_Sign-off_Documents] WHERE
((([tbl_Sign-off_Documents].[Sign-off Group]) Like "" &
[Forms]![frm_Sign-Offs_Add_New]![TXT_Group_No]));

The form is called frm_Sign-Offs_Add_New
The Subform is called frm_Sign_Offs_Add_New_Subform

I have 3 questions:-

(1) How do I loop through the recordset of the subform (e.g. when I have a
number of records that I want to add / have added)

(2) What vba command do I need to use in order to save the new record on the
main form (frm_Sign-Offs_Add_New)

(3) What vba command do I need to use in order to save the new record on the
subform (frm_Sign_Offs_Add_New_Subform)?
[tbl_Sign-off_Documents].[ArchiveID] is looked up based on a combo box that
is on the continuous form

Hope all that makes sense, I would really appreciate some help on any of
these aspects.

Thanks Andi

 

[Allen Browne]

Answers in-line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen - I've now bound my continuous subform to the following
query:-

SELECT [tbl_Sign-off_Documents].[Sign-off Group],
[tbl_Sign-off_Documents].[ArchiveID], [tbl_Sign-off_Documents].[File
Name],
[tbl_Sign-off_Documents].[Version] FROM [tbl_Sign-off_Documents] WHERE
((([tbl_Sign-off_Documents].[Sign-off Group]) Like "" &
[Forms]![frm_Sign-Offs_Add_New]![TXT_Group_No]));

The form is called frm_Sign-Offs_Add_New
The Subform is called frm_Sign_Offs_Add_New_Subform

I have 3 questions:-

(1) How do I loop through the recordset of the subform (e.g. when I have a
number of records that I want to add / have added)

The bound subform automatically adds its records to the table.

If you want to programmatically add a record,use the RecordsetClone of the
subform. Assuming the code goes into the subform's module, the code would
look like this:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.AddNew
rs!ArchiveID = 990
rs![File Name] = "C:\MyFile.txt"
rs!Version = "abc"
rs.Updaate
Set rs = Nothing

(2) What vba command do I need to use in order to save the new record on
the
main form (frm_Sign-Offs_Add_New)

Access saves the record in a bound form automatically by default, as soon as
you do anything that requires it to be saved, such as:
- clicking in the subform,
- moving to another record,
- applying a filter
- changing the sort order,
- closing the form,
- closing Access,
- requerying the form,
and so on.

To explicitly save it in code, set the form's Dirty property to No:
If Me.Dirty Then Me.Dirty = False

(3) What vba command do I need to use in order to save the new record on
the
subform (frm_Sign_Offs_Add_New_Subform)?
Same.

[tbl_Sign-off_Documents].[ArchiveID] is looked up based on a combo box
that
is on the continuous form

Hope all that makes sense, I would really appreciate some help on any of
these aspects.

Thanks Andi

 

[Andibevan]

Many thanks for your assistance with this one.

Andi