Record set

[kkbharat]

Hello Everyone,



Can anyone give me information or send me in the right direction about using recordset in form/subform scenario.

Thanks,

Bharat

[ranman256]

Connect the query to the form,
then the textboxes to the fields.
the can be done in a single click,
select the query, then Make Form icon.

[kkbharat]

Hello,

Thanks for your response. Now I see that my question was a stupid one. I saw this video by Mr. Steve Bishop where in he explained Recordset in vba but he explained it using single form with no subform.

My question was if I use the same method for form / subform senario will I need to set Recordset for both the tables and if I add a record to the related table from the many side of the relationship, will the primary key value be automatically updated in the related table.

Hope I am making some sense.

Thanks & regards,

Bharat

[ssanfu]

Don't understand why you would use VBA to set the record source of a form.


Let's say you have two tables:
tblCustomers
-------------
CustID_PK Autonumber
CustFName Text
CustLName Text


tblOrders
-------------
OrderID_PK Autonumber
CustID_FK Number-Long (foreign key - link to table tblCustomers)
OrderDate

The linking (related) fields are tblCustomers.CustID_PK --->> tblOrders.CustID_FK

Make 2 queries:
qryCustomers:

SELECT CustID_PK, CustFName, CustLName FROM tblCustomers ORDER BY CustLName, CustFName;

qryOrders:

SELECT OrderID_PK, CustID_FK, OrderDate FROM tblOrders ORDER BY OrderDate;

Create 2 forms:
frmCustomers - set the record source to qryCustomers. Add the fields to the form.


sfOrders - set the record source to qryOrders. Add the fields to the form.
(sf = sub form)

I would set both forms to Continuous Forms view. Access will complain, but set both forms back to Continuous Forms view.


Save the forms. Open form frmCustomers in design view.
In the footer, drag and drop the form sfOrders. Right click on the sub form Control and select properties.
On the data tab, look for "Link Master Fields" and "Link Child Fields".
Set the "Link Master Fields" to "CustID_PK" and "Link Child Fields" to "CustID_FK".

Close and save the form frmCustomers.

Open the form frmCustomers. When you select a customer, then go to the sub form and enter one or more order dates, the Customer PK (CustID_PK) is automagically entered into the table tblOrders record(s).

No VBA required........

[kkbharat]

Hi Steve,

Sorry for the late reply. I was tied up elsewhere.

My query (related to Recordset in VBA) was out of curiosity and I was wondering about how it would work. I looked around and I got some partial understanding of it but I will need to apply and see if my understanding is correct. However your suggestion about using both the form and subform as continuous form is something very new but very useful in some of my requirements. I shall definitely try your suggestion.

Thank you very much once again and best regards,

Bharat

Don't understand why you would use VBA to set the record source of a form.


Let's say you have two tables:
tblCustomers
-------------
CustID_PK Autonumber
CustFName Text
CustLName Text


tblOrders
-------------
OrderID_PK Autonumber
CustID_FK Number-Long (foreign key - link to table tblCustomers)
OrderDate

The linking (related) fields are tblCustomers.CustID_PK --->> tblOrders.CustID_FK

Make 2 queries:
qryCustomers:

SELECT CustID_PK, CustFName, CustLName FROM tblCustomers ORDER BY CustLName, CustFName;

qryOrders:

SELECT OrderID_PK, CustID_FK, OrderDate FROM tblOrders ORDER BY OrderDate;

Create 2 forms:
frmCustomers - set the record source to qryCustomers. Add the fields to the form.


sfOrders - set the record source to qryOrders. Add the fields to the form.
(sf = sub form)

I would set both forms to Continuous Forms view. Access will complain, but set both forms back to Continuous Forms view.


Save the forms. Open form frmCustomers in design view.
In the footer, drag and drop the form sfOrders. Right click on the sub form Control and select properties.
On the data tab, look for "Link Master Fields" and "Link Child Fields".
Set the "Link Master Fields" to "CustID_PK" and "Link Child Fields" to "CustID_FK".

Close and save the form frmCustomers.

Open the form frmCustomers. When you select a customer, then go to the sub form and enter one or more order dates, the Customer PK (CustID_PK) is automagically entered into the table tblOrders record(s).

No VBA required........