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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave - MS Newsgroups - 09 Sep 2005 00:47 GMT

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

Reply to this Message

Allen Browne - 09 Sep 2005 04:05 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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
[quoted text clipped - 10 lines]
>
> I am using a Do WHILE Not EOF to read the query and populate the form.

Reply to this Message

Dave - 09 Sep 2005 04:18 GMT

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.
[quoted text clipped - 35 lines]
>>
>> I am using a Do WHILE Not EOF to read the query and populate the form.

Reply to this Message

Comcast Newsgroup - 09 Sep 2005 05:16 GMT

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

Reply to this Message

Allen Browne - 09 Sep 2005 05:29 GMT

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

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 47 lines]
>>>
>>> I am using a Do WHILE Not EOF to read the query and populate the form.

Reply to this Message

Comcast Newsgroups - 09 Sep 2005 08:32 GMT

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
[quoted text clipped - 53 lines]
>>>>
>>>> I am using a Do WHILE Not EOF to read the query and populate the form.

Reply to this Message

Douglas J Steele - 09 Sep 2005 12:03 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Allen,
>
[quoted text clipped - 61 lines]
> >>>>
> >>>> I am using a Do WHILE Not EOF to read the query and populate the form.

Reply to this Message

Dave - MS Newsgroups - 09 Sep 2005 15:07 GMT

Thanks

Dave

> See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
> how
[quoted text clipped - 88 lines]
>> >>>> I am using a Do WHILE Not EOF to read the query and populate the
> form.

Reply to this Message

Dave - MS Newsgroups - 10 Sep 2005 01:32 GMT

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
[quoted text clipped - 88 lines]
>> >>>> I am using a Do WHILE Not EOF to read the query and populate the
> form.

Reply to this Message