Accessing data from a subform

[eellenoff]

I know that I can access the data on a subform using syntax like

me.subform.form.field

What I'm trying to do now, is to write a function that will take the field name as a string and do the logical equivalent of:

private function getField (field as String)
    dim ctl as control
    For Each ctl In Me.subform
        If ctl.name = field then getField = ctl.value
    Next ctl
end function

Is there a cleaner way to do this?

 

[Rank Am]

Hi, not sure what you are trying to achieve here but

me.subform.form.field

does not refer to the form data, forms and report objects don't have fields they have controls. Fields belong to tables and queries, so the syntax is wrong - it should be .form.control.name - bear in mind though there is no relationship between ctrl names and field names.

To access the data behind a form you need to create a recordset object in memory based upon the subforms recordset.
The easiest way, but limited, is to use the subforms recordestclone method

Dim rs as dao.recordset
set rs = me.subform.form.recordsetclone

will create a readonly copy of the forms recordsource(the table or query) data in memory.

if the table had field name "fld_PK" then you can refer to this by rs("fld_PK")
If you want to manipulate data you will need to create a seperate recordset object from an SQL Statement. how you do this is dependent on many factors, are there parameters etc. Search the forums for DAO recordset or ADO recordset there are plenty of posts.

Regards

Jon

 

[eellenoff]

Excellent, thanks a lot. It was being able to access the fields as a map that I was looking for.