Subform Recordset - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Subform Recordset

Richard Pitassy

2 years ago

I am a new to manipulating data with recordsets and after 3 days of videos and experimenting, I am stuck.

I want to use a command button on my main form to create a recordset of the records in my subform.

The main form is based on a Transactions table that contains a TransId key field. The subform is based on a TransDetail table that contains a TransDetailId key field and a foreign key TransId field. The forms are linked on TransId.

In English, I want to SELECT only the records from the TransDetail table WHERE TransId = TransId of open Transaction form. I have tried multiple variations of the WHERE clause but no luck.

Would very much appreciate some guidance on this. Thanks!

	John Davy @Reply 2 years ago
Hi Richard Do you want the subform to show only the records that relate to the main form? Just checking to make sure I understand what you are trying to do. John

	John Davy @Reply 2 years ago
Hi Richard If I understand your question correctly, I believe a subform inside your main form will show you the records that you desire. I created an example of this scenario for you. If this is what you need, then drop a note and I will give you a link to download it. John

	Richard Pitassy OP @Reply 2 years ago
Thanks John: The answer to your first reply is yes and yes I would very much like to get the link.

	John Davy @Reply 2 years ago
Hi Richard You can download a small Access file here. https://1drv.ms/f/s!AkKOeOjAYbyVhaZ0J8Yh5FvKf_yDKg?e=d0Zh3k Tear it apart and see how it is done. If you have questions post them on the forum HTH John

	Richard Pitassy OP @Reply 2 years ago
Many thanks John. Checking it out now.

	Richard Pitassy OP @Reply 2 years ago
John: I've downloaded the db but when I try to view your code it comes up blank. Where should I be looking?

	John Davy @Reply 2 years ago
Hi Richard. Did you open the file? Did you open the form in design mode? Look at how the link is made john

	John Davy @Reply 2 years ago

Richard Pitassy OP @Reply

2 years ago

Hi John: I'm sorry I didn't make my question clear. I get that form/subform link and my Trans form and Trans subform have the link based on the TransId field. What I'm having trouble with is the syntax of an SQL Statement that will produce a Recordset of only the records from the TransDetail table WHERE TransId = TransId of open Transaction form. This is what I've got so far.

Private Sub RecordsetBtn_Click()    

    Dim db As Database

    Dim rs As Recordset

    Set db = CurrentDb

    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTransDetail WHERE TransId = TransId")

    

    rs.MoveFirst    

    Status "----------------"

    Status rs!Barcode

    Status rs!QtySold

    

    rs.Close

    db.Close

    

    Set rs = Nothing

    Set db = Nothing    

End Sub

When I run that, I'm getting the first record in the TransDetail table, not the first record in the subform. (I understand that to get all the records in the subform I'm going to need to create a loop in the code).

I hope this makes sense. Thanks

	Kevin Robertson @Reply 2 years ago
Try one of these options: `Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTransDetail WHERE TransID=" & TransID) Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTransDetail WHERE TransID=" & Parent!SubformName.Form!TransID) Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTransDetail WHERE TransID=" & Forms!ParentFormName!SubformName.Form!TransID)`

	Kevin Robertson @Reply 2 years ago
Value From a Form Value From Subform

	Richard Pitassy OP @Reply 2 years ago
Thanks Kevin!

	Richard Pitassy OP @Reply 2 years ago
The first 2 did not work BUT the 3rd one works!! . . . WHERE TransID=" & Forms!ParentFormName!SubformName.Form!TransID) - is a winner. Thanks again! Dick

	Marc Lievens @Reply 2 years ago
Or use Me.Parent!TransID in the subform

	Marc Lievens @Reply 2 years ago
Or if you only want to use the recordset of the subform in the subform: Dim rs As DAO.Recordset Set rs = Me.RecordsetClone

	Richard Pitassy OP @Reply 2 years ago
Thanks!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View