Question Use data from tables in VBA on form with different record source

[MushroomKing]

Hi everyone. I have a somewhat "general" question.
I have multiple examples where i would apply this, but i would like to keep the answers general so others can learn this basic, but useful way of going about data.

When i have a form, with a certain record source, that certain source is the data i can manipulate or get.

In some cases i have "subforms" for example.
They have their own record source (probably), but sometimes i just want to access the data from a subform on my mainform. If that makes sense.

For example:

I want a button on my main form that sends an email, i will use an event procedure. So i make a sub where i put the fields in my vba code...containing data from the subform! How would i go about this?

I could use Dlookup for each individual piece of data, but that's alot of code.

Could i use a recordset in this case? Or how does that work?

Hope to learn to code more efficient

Private Sub emailupdate_Click()
    
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM table2") 'not my recordsource, but i want data from this table, only for now...
    
MsgBox Me.somefield 

End Sub

 

[theDBguy]

Hi,

In my humble opinion, a general answer may not be an efficient way to achieve the desired effect when compared to a specific answer. But to answer your question, yes, you could use a recordset to reference the record source of a form to access its data and use them somewhere else.

 

[MajP]

If you want data from the current record on the subform simply reference that fields/controls by name

Me.MySubFormControlName.Form.SomeFieldOnSubForm.Value

If you want the recordset of the subform go directly to the subform

dim rs as dao.recordset
set rs = Me.MySubFormControlName.Form.recordset

 

[MushroomKing]

Thanks for the reply guys!

Well, it's multiple fields i would like. A set of data, that has the same value in the ID field on my main form (relation).

I don't know the shortest way, to get a few records of data from another recordsource (or, the recordsource of my subform in this case into my main form)

 

[MajP]

A set of data, that has the same value in the ID field on my main form (relation).

Assuming your main form is linked to the subform by the ID then the below code only returns the records visible in the subform. So if 3 records are linked by ID 1 then you get just the three records.

dim rs as dao.recordset
set rs = Me.MySubFormControlName.Form.recordset

 

[theDBguy]

Thanks for the reply guys!

Well, it's multiple fields i would like. A set of data, that has the same value in the ID field on my main form (relation).

I don't know the shortest way, to get a few records of data from another recordsource (or, the recordsource of my subform in this case into my main form)

You can also do the same with the following SQL statement:


Set rs = db.OpenRecordset("SELECT * FROM OtherTable WHERE FK=" & Me.ID)

 

[Cronk]

....i just want to access the data from a subform...

The code in your parent form can access the data in the relevant sub form text boxes without opening any new data set

Use

strEmail = me.sfSubForm.Form!txtEmail

where sfSubForm is the name of the sub form control on your main form and txtEmail the name of the text box containing the email address data.
Similarly for any other sub form controls.