Main Form - Sub Form Challenge 1

[FabHead]

This has been a problem for me for quite a while, but I'm sure someone has done something similar before.

I have frmMaintAction (Main form) and subfrmMaintPartsUsed (Sub Form). The forms are Linked by a field called MaintID.

The Sub Form is in Continuous Forms view (It is all the Maintenance Parts Used during a Maintenance Action).

My Problem:
I have a button on the main form called FSR (Field Service Report) which will copy information from the main form and put it in an excel template (this works fine)....BUT, I also need to copy all the Maint Parts Used to the excel template (this is where I am stuck)

I would greatly appreciate help in the code for accessing records in the Sub Form that are linked to the Main Form while in the Main Form.

Hope this makes sense.
Thanks,
Rick

 

[SoloDroid]

Hello Rick,
If you are experienced with ADO and VBA coding, you can access the subform's recordset in the following format:

Forms![Name of Parent Form].[Name of Subform].Form.Recordset

This will enable you to extract the records in the subform. You could use the

GetRows()

or

GetString()

methods of the Recordset. Or you can simply loop through the recordset, extracting 1 record at a time:

Dim objRSet As ADODB.Recordset
Set objRSet = Forms![Name of Parent Form].[Name of Subform].Form.Recordset
While Not objRSet.EOF
    MyVariable = MyVariable & ", " & objRSet![Field Name]
    objRSet.MoveNext
Wend

That should give you something to go on.

Good Luck,
Robert
Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

 

[FabHead]

Thanks Robert,
That recordset for the Sub Form was kicking my A$$.
I'll work on what you suggested and let you know.
Rick

 

[FabHead]

Robert,

Luck would have it I got the old 'Type Mismatch' error at the:
Set objRSet = Forms!MaintAction!MaintPartsUsed.Form.Recordset

This is really turning out to be way harder then it should be. I'll keep trying different things, thanks for any idea's
Here is my unworking code.

Dim objRSet As ADODB.Recordset
Dim PN As String
Dim ID As String
ID = Me.MaintID
'strSQL = "SELECT * FROM MaintPartsUsed_Query WHERE MaintID = """ & ID & """"


Set objRSet = Forms!MaintAction!MaintPartsUsed.Form.Recordset
objRSet.MoveFirst
While Not objRSet.EOF
While objRSet!MaintID = ID
PN = objRSet!PartID
Wend
objRSet.MoveNext
Wend

 

[SoloDroid]

Hmmm... perhaps the Recordset is not of type ADODB.Recordset after all. Simply Dim objRSet as a variant and she should work.
i.e. drop the "

As ADODB.Recordset

" part:

    Dim objRSet
    Dim PN As String
    Dim ID As String
    ID = Me.MaintID
    'strSQL = "SELECT * FROM MaintPartsUsed_Query WHERE MaintID =  """ & ID & """"
    
    
    Set objRSet = Forms!MaintAction!MaintPartsUsed.Form.Recordset
    objRSet.MoveFirst
    While Not objRSet.EOF
        While objRSet!MaintID = ID
            PN = objRSet!PartID
        Wend
        objRSet.MoveNext
    Wend

Robert Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

 

[SoloDroid]

Going on a hunch, I did a little testing and it appears that a form's recordset is of type DAO.Recordset. This is interesting to me because I am using Microsoft Access 2002 and was under the impression that Microsoft abandoned DAO (Data Access Objects) after Access 97, in favor of ADO (ActiveX Data Objects).

This appears to be a half-truth because Access 2002, by default, sets a VBA reference for the Microsoft ActiveX Data Objects 2.1 library, however, it does *NOT* set a reference to the Microsoft DAO 3.6 Object library, of which the form's recordset is a member of.

For maximum efficiency, I would ignore my above post of using a variant. Instead, set a reference to the "Microsoft DAO 3.6 Object library" and declare

objRSet

of type

DAO.Recordset

.
i.e.

Dim objRSet As DAO.Recordset

Robert Professional, affordable, Access database solutions and assistance

http://www.SoloDroid.com

 

[FabHead]

Robert,
I was hoping to just use ADO throughout my database. I'll set a reference to "Microsoft DAO 3.6 Object library" and give it a try later tonight....gotta go earn a paycheck.

Rick

 

[FabHead]

Robert,
That did the trick.
Should fine tune it in the next day or so....I'll post code so others can see.

Really strange you have to use DAO to get it to work.
Thanks for the help!

Rick