Moving to a new record in a sub-subform using VBA

[Dave - MS Newsgroups]

I have a form (frmHouse) with a subform (Options) that also has a subform
(Materials). I need to add default records (read from a query) to the
Materials sub-subform. I can read the query into a recordset and get the
first record on the sub-subform populated properly. The problem I'm having
is moving to the next record on the sub-subform (which is a new record) so I
can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

Thanks for any insights.

Dave

 

[Allen Browne]

Dave, you could AddNew to the RecordsetClone of the form, but it might be
easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?), so
just type a dummy number such as 99 into the Field row of the query for now,
and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample statement
you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new record
into the string in place of the 99. The code will look something like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

 

[Dave]

Allen -- Thanks for the reply.

I need to digest what you are suggesting to see if I understand all that is
going on. Will do so tomorrow.

Dave

Dave, you could AddNew to the RecordsetClone of the form, but it might be
easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?), so
just type a dummy number such as 99 into the Field row of the query for
now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample statement
you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new record
into the string in place of the 99. The code will look something like
this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

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

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

I have a form (frmHouse) with a subform (Options) that also has a subform
(Materials). I need to add default records (read from a query) to the
Materials sub-subform. I can read the query into a recordset and get the
first record on the sub-subform populated properly. The problem I'm
having is moving to the next record on the sub-subform (which is a new
record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

 

[Comcast Newsgroup]

Allen,

Would you know the syntax for the statement to create a RecordsetClone for
the query that is the recordsource for the sub-subform. I can't figure it
out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc. than
I can count -- none work. All give me runtime errors.

Thnaks.

Dave

Dave, you could AddNew to the RecordsetClone of the form, but it might be
easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?), so
just type a dummy number such as 99 into the Field row of the query for
now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample statement
you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new record
into the string in place of the 99. The code will look something like
this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

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

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

I have a form (frmHouse) with a subform (Options) that also has a subform
(Materials). I need to add default records (read from a query) to the
Materials sub-subform. I can read the query into a recordset and get the
first record on the sub-subform populated properly. The problem I'm
having is moving to the next record on the sub-subform (which is a new
record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

 

[Allen Browne]

Try:
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone

Don't forget to use the Update method after AddNew.

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

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

Allen,

Would you know the syntax for the statement to create a RecordsetClone for
the query that is the recordsource for the sub-subform. I can't figure it
out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc.
than I can count -- none work. All give me runtime errors.

Dave, you could AddNew to the RecordsetClone of the form, but it might be
easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?), so
just type a dummy number such as 99 into the Field row of the query for
now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample
statement you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new
record into the string in place of the 99. The code will look something
like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

I have a form (frmHouse) with a subform (Options) that also has a subform
(Materials). I need to add default records (read from a query) to the
Materials sub-subform. I can read the query into a recordset and get the
first record on the sub-subform populated properly. The problem I'm
having is moving to the next record on the sub-subform (which is a new
record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

 

[Comcast Newsgroups]

Allen,

Me.RecordsetClone gives me the recordset for the main form. I'm trying to
get the recordset for the sub-subform (frmHouse --> Options -->Materials).
Am I making sense?

Dave

Try:
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone

Don't forget to use the Update method after AddNew.

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

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

Allen,

Would you know the syntax for the statement to create a RecordsetClone
for the query that is the recordsource for the sub-subform. I can't
figure it out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc.
than I can count -- none work. All give me runtime errors.

Dave, you could AddNew to the RecordsetClone of the form, but it might
be easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?),
so just type a dummy number such as 99 into the Field row of the query
for now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample
statement you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new
record into the string in place of the 99. The code will look something
like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

I have a form (frmHouse) with a subform (Options) that also has a
subform (Materials). I need to add default records (read from a query)
to the Materials sub-subform. I can read the query into a recordset and
get the first record on the sub-subform populated properly. The problem
I'm having is moving to the next record on the sub-subform (which is a
new record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

 

[Douglas J Steele]

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for how
to refer to form and subform properties.

You'll need something like:

Dim rsClone As DAO.Recordset

Set rsClone = Me!Subform1.Form!Subform2.Form.RecordsetClone

where Subform1 and Subform2 are the names of the containers that hold the
subforms.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,

Me.RecordsetClone gives me the recordset for the main form. I'm trying to
get the recordset for the sub-subform (frmHouse --> Options -->Materials).
Am I making sense?

Dave

Try:
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone

Don't forget to use the Update method after AddNew.

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

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

Allen,

Would you know the syntax for the statement to create a RecordsetClone
for the query that is the recordsource for the sub-subform. I can't
figure it out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc.
than I can count -- none work. All give me runtime errors.

Dave, you could AddNew to the RecordsetClone of the form, but it might
be easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is it?),
so just type a dummy number such as 99 into the Field row of the query
for now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample
statement you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new
record into the string in place of the 99. The code will look something
like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

I have a form (frmHouse) with a subform (Options) that also has a
subform (Materials). I need to add default records (read from a query)
to the Materials sub-subform. I can read the query into a recordset and
get the first record on the sub-subform populated properly. The problem
I'm having is moving to the next record on the sub-subform (which is a
new record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the

form.

 

[Dave - MS Newsgroups]

Thanks

Dave

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
how
to refer to form and subform properties.

You'll need something like:

Dim rsClone As DAO.Recordset

Set rsClone = Me!Subform1.Form!Subform2.Form.RecordsetClone

where Subform1 and Subform2 are the names of the containers that hold the
subforms.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,

Me.RecordsetClone gives me the recordset for the main form. I'm trying
to
get the recordset for the sub-subform (frmHouse -->
Options -->Materials).
Am I making sense?

Dave

Try:
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone

Don't forget to use the Update method after AddNew.

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

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

Allen,

Would you know the syntax for the statement to create a RecordsetClone
for the query that is the recordsource for the sub-subform. I can't
figure it out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc.
than I can count -- none work. All give me runtime errors.

Dave, you could AddNew to the RecordsetClone of the form, but it
might
be easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is
it?),
so just type a dummy number such as 99 into the Field row of the
query
for now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample
statement you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new
record into the string in place of the 99. The code will look something
like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

I have a form (frmHouse) with a subform (Options) that also has a
subform (Materials). I need to add default records (read from a query)
to the Materials sub-subform. I can read the query into a recordset and
get the first record on the sub-subform populated properly. The problem
I'm having is moving to the next record on the sub-subform (which is
a
new record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.

 

[Dave - MS Newsgroups]

Well, after hours of staring at a computer screen, I finally got my routine
to do what I wanted. The problem seems to have been threefold:

1. A record had to be present in the parent table before I could enter one
in the child table and the IDs had to be the same (not surprising).
2. I was using the name Option for one of my fields and with some
constructs in the SQL statement, Access asked me for a parameter when I
executed the SQL statement. I think it was because Option is a reserved
word.
3. The syntax for a SQL statement is not straight forward.

Dim strSQL As String
Dim strNewItem As String
strNewItem = a_varSelectedOptions(varItem)

' This does not work
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1, strNewItem)"

' This works fine
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1, 'NEXT')"

' This is what I needed -- took me forever to figure out the "'&
strNewItem & "' construct
strSQL = "INSERT INTO tblProductionOptions (ProdOrdID, ProdOption)
VALUES (1,'" & strNewItem & "')"

DoCmd.RunSQL strSQL

Thanks for your help.

I learned a great deal from this exercise -- even if it was frustrating

Dave

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
how
to refer to form and subform properties.

You'll need something like:

Dim rsClone As DAO.Recordset

Set rsClone = Me!Subform1.Form!Subform2.Form.RecordsetClone

where Subform1 and Subform2 are the names of the containers that hold the
subforms.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Allen,

Me.RecordsetClone gives me the recordset for the main form. I'm trying
to
get the recordset for the sub-subform (frmHouse -->
Options -->Materials).
Am I making sense?

Dave

Try:
Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone

Don't forget to use the Update method after AddNew.

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

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

Allen,

Would you know the syntax for the statement to create a RecordsetClone
for the query that is the recordsource for the sub-subform. I can't
figure it out.

dim rsClone as Recordset
Set rsClone = Me.????

I've tried more variations of exclamation points, periods, Form!, etc.
than I can count -- none work. All give me runtime errors.

Dave, you could AddNew to the RecordsetClone of the form, but it
might
be easier to convert your query into an Append query and avoid the loop.

Open the query in design view.
Change it to an Append query (Append on Query menu).
When Access asks which table, specify the subform's table.
You probably need to pass the value of a foreign key (HouseID is
it?),
so just type a dummy number such as 99 into the Field row of the
query
for now, and indicate the field this should go to.

Now switch the query to SQL View (View menu.) There's the sample
statement you need to generate in your code.

In the AfterInsert event of the main form, place the code to append the
records to the subform. You will concatenate the HouseID of the new
record into the string in place of the 99. The code will look something
like this:

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO Materials ... " & Me.[HouseID] & " ...
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[NameOfYourSubformHere].Requery
End Sub

I have a form (frmHouse) with a subform (Options) that also has a
subform (Materials). I need to add default records (read from a query)
to the Materials sub-subform. I can read the query into a recordset and
get the first record on the sub-subform populated properly. The problem
I'm having is moving to the next record on the sub-subform (which is
a
new record) so I can add the second and subsequent records.

As far as I can see the active form is the main form (frmHouse) and the
DoCmd.GoToRecord property adds a new record to the main form.

How do I get VBA to move to a new record in the sub-subform so I can
populate it and then go to another new record and so on.

I am using a Do WHILE Not EOF to read the query and populate the form.