Subform Record Selector Help

[Aph002]

Hi, I have a subform within a Customer information form, I need to be able to use the in-built record selector to select an individual record from the subform, and then press a button on the main form to open that individual record in a report.
I have absolutely no clue how to do this, I can't figure out how to filter/query only the selected record, my report still shows all records. All information I've seen online is in VBA, which unfortunately I have no clue about.
In the attached photo, the record has been selected, now the 'Overdue?' button needs to open that specific record in a report.
Thank you in advance!

[pbaldy]

Here's a VBA method:

http://www.baldyweb.com/wherecondition.htm

If you prefer a macro, it also has a wherecondition argument.

Edit: note you'd refer to the subform:

http://www.theaccessweb.com/forms/frm0031.htm

[Aph002]

I don't quite understand, as I said I have very little knowledge of Code (or macro's), this is my first database.
The link you gave says:
DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName
Where "FieldName is the field in that form's recordsource the restriction is based on"
But, What is the recordsource that the restriction is based on? I just want it to filter whether I've selected it or not.
Also, I assume this code would be placed in the code builder for the button to open the report?
Like:

[pbaldy]

In your case the record source is the report's. Instead of referring to the form the code is in with Me, you'd refer to the subform. That would get you whatever record was currently selected in the subform. VBA would go here:

http://www.baldyweb.com/FirstVBA.htm

[Aph002]

Hmm, I can't get the VBA to work, I don't really get it, perhaps macros would be simpler?
I see in the macros there's a wherecondition for RecordSelector (also one for recordsource, both seem to be on the right track) but it doesn't affect anything, surely this isn't that far off?
With the Loans Form being the subform.

[Welshgasman]

Use the builder?
If you use that, is shows RecordSelectors not what you have?

As it is the subform you need the Mainform.subformcontrolname.Form.Recordselectors (if that works, never used Recordselectors like that). Hardly ever use macros either

http://access.mvps.org/access/forms/frm0031.htm

[Welshgasman]

I cannot get your recordselector logic to work, even after constructing with the builder?
For a start, I do not believe you can use Me in a macro.?

When I use =[Forms]![frmTestTransactions].[RecordSelectors] I still get other records?

I have always used a field/fields with controls to filter a report?
So I would use the autonumber field of your Loans table and in the syntax I have shown, using the builder.?

[Aph002]

Thanks for replying.
I tried using the builder with the loan autonumber and still couldn't get anything to work. Being a built-in feature, I thought It would be much simpler, such simply filtering a report for only selected records.

[Welshgasman]

Thanks for replying.
I tried using the builder with the loan autonumber and still couldn't get anything to work. Being a built-in feature, I thought It would be much simpler, such simply filtering a report for only selected records.

Best compact, zip and upload your DB then, or create a smaller version if it is very large?

[davegri]

Try doubling up the equals:

==[Forms]![frmTestTransactions].[RecordSelectors]

[Welshgasman]

Try doubling up the equals:

==[Forms]![frmTestTransactions].[RecordSelectors]

Nope, cannot parse?

That is my code as well? not the O/Ps.?
I was just trying to see if it even worked?

[Aph002]

The Database is nearly done. The selected record from the subform in 'Customer Book Loan Form' is supposed to be opened as a report using a button, the report is supposed to be 'Overdue Report' however it's a mess due to me trying to find a solution, the 'Loaned Books' report is completed and would be better suited for troubleshooting.

Thank you.

[ssanfu]

I don't use macros, but I would have thought the macro WHERE CONDITION would need a field in the record source to compare the field value of the selected record.


EDIT... looks like I missed Post # 12



PS .... to the forum

[Welshgasman]

Are you relationships correct?
The sql for the subform is

SELECT loans.*, persons.person_id
FROM persons INNER JOIN loans ON persons.person_id = loans.issued_by;

yet PersonID is the customer? is it not

Code to open report with VBA can be

DoCmd.OpenReport "Loaned Books", acPreview, , "Person_ID=" & Me.Combo24

The Loaned Books with a macro is the Books Not button.
The Loaned books with VBA is the Overdue Books button.

You can change the reports to suit.? However the report is based on the customer in the combo?
When I tried to go to the subform via the builder with a macro it kept crashing and restarting my Access?

HTH

[Welshgasman]

Here is the code to get the actual record you are on for the customer
I cannot get the macro to work, it keeps prompting for the second parameter being Combo75, and I cannot see what is wrong. ALso you have to close the macro each time you want to get a control/field name. A right PITA

DoCmd.OpenReport "Loaned Books", acPreview, , "Person_ID=" & Me.Combo24 & " AND book_id= " & Me.Loans_final.Form.combo75