Question Use data from tables in VBA on form with different record source (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 11:52
Joined
Jun 7, 2018
Messages
100
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 :)


Code:
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 11:52
Joined
Oct 29, 2018
Messages
21,358
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

You've got your good things, and you've got mine.
Local time
Today, 14:52
Joined
May 21, 2018
Messages
8,463
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

Registered User.
Local time
Today, 11:52
Joined
Jun 7, 2018
Messages
100
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

You've got your good things, and you've got mine.
Local time
Today, 14:52
Joined
May 21, 2018
Messages
8,463
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

I’m here to help
Staff member
Local time
Today, 11:52
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Tomorrow, 05:52
Joined
Jul 4, 2013
Messages
2,770
....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

Code:
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.
 

Users who are viewing this thread

Top Bottom