Print Current record of a subform

[MrM121]

Hi,

I have a main form with a contact log subform in datasheet view (the subform is in datasheet view!). I have set the mechanism up correctly so that new logs can be added, and then displaying the last record, but that isn't my problem.

My problem is that I want to be able to output the details of the currently selected SUBFORM record to a report, but I am unsure how to do it.

In the table that contains the subform data, there are 4 fields, a date field for the date of the record, a message field, an ID number for the record (autonumber), and a reference code, which is linked with the main form. So obviously I want to be able to take the ID field data and stick it in a query. However, on the subform I have only put the date and message fields (as the ID and Reference fields aren't needed).

Does anyone know how to identify the content of the ID field in the table, even though it is not on the form?

I need to be able to click a button on the main form, and print the current record of the subform in datasheet view.

Thank you,

Nick

 

[PHV]

What is the RecordSource of the subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MrM121]

The recordsource is simpy a table, linked by the Reference field on a one-to-many relationship.

 

[PHV]

So the needed fields are in the subform's Recordset (I guessed that).
You may try to play with the Recordset("Name of ID field").Value property of your subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

I found, I don't always have to have a field on a form, in order to reference it. If not so, drag it on, & hide it, either by its visible property (can't remember if it needs to be visible also), or by its forecolor, background color etc properties.

And I believe, by default, the first record of any form, is the referenced record, or secondly , the one with the focus.
Even if it's a continuous form.

So, try this

Dim iID as Integer
iID = Me!subForm!fieldID

DoCmd.OpenForm "subfrmName", acViewNormal, , "fieldID = " & iID

maybe try preview first, to check...

DoCmd.OpenForm "subfrmName", acViewPreview, , "fieldID = " & iID

Hope this helps, good luck!

 

[MrM121]

In the end I used the following code:

dim rs as DAO.Recordset

set rs = me.childname.recordsetclone

if not rs.recordcount = 0 then
rs.bookmark = me.childname.form.bookmark
msgbox rs!ID
endif
rs.close

Worked a treat - I just need to try and configure the datasheet to force the selected record to change when the vertical scroll bar is used, as only 1 record at a time is visible (it is a contact log after all).

Nick

 

[Zion7]

Good work Nick, I do wish I thought of that also. I don't really have a grip on bookmarks & recordset clones yet.
But again, it may not be necessary, just because all that is needed, is just to reference the right control, because it will always return the current record. If none selected, then the first.
Either way Nick, it's a good tool to know & thank-you for posting your resolution.
As far as the vertical scroll bar being able to navigate through records, ...not that I'm aware of. Obviously you've considered putting navigation buttons on the subform?

Good Luck!

 

[MrM121]

You have to remember that I am using a subform in DATASHEET view, and so using controls is pretty hard, lol.

You're welcome for the code post though.

Nick

 

[Zion7]

Yes, I imagine that would be pretty difficult, LOL!

(What the *&^% was I thinking)?