Report hyperlink to record in Form

[Lmartinrn]

I am hoping you can help me out.



I have a database of patient records. A report has been generated in which you see the patient's name, medical record number, etc.

I am trying to hyperlink from the medical record number to the record that it corresponds to on the form. Is this possible? I have tried different combinations of commands in a macro and have not been successful in getting it the link to take me to the specific record.

Please help!

[pkstormy]

In the report's OnClose event, you can add code like the following to open a form based on what the report's MedRecNum #.

Dim MID as variant
MID = Reports!myReportName!MedRecNum
docmd.close acreport,me.name 'now that the MedRecNum is stored in the MID variant, you can close the report
Docmd.close acform, "myFormName" ' close the form if open
docmd.openform "myFormName",,,"[MedRecNum] = '" & MID & "'" '(note: using the "where" parameter of the openform command. Use "[MedRecNum] = " & MID & "" if the MedRecNum field is a number versus text field type.)

or you can set the focus to the MedRecNum field on the (open) form and use docmd.findrecord command in the OnClose event of the report:

dim MID as variant
MID = Reports!myReportName!MedRecNum
docmd.close acreport,me.name
Forms!myFormName.setfocus
Forms!myFormName!MedRecNum.setfocus
docmd.findrecord MID

Both of the above would be coded into the OnClose (or possibly another event) of the report. Note that you may not need the docmd.close acreport, me.name code above. It may give you an error since the report is already closing.

[Lmartinrn]

I admit I am Access Niave. I was hoping for a way to add this information into MicroBuilder but I can't seem to get there without a syntax error even when I put it into Code Builder...

Please help as this is the last piece of the puzzel that I need to complete this request.

[pkstormy]

Trying to code in events based on a report event is not always the best route. Ideally, you'd have a search type FORM which would find 1 specific record and open that FORM with only that recordID (ie. or your autonumber field value). You'd then have a "Print Preview" button on the form which opens the REPORT based on the same recordID value. Thus when you close the report, you still have the form open on that specific record.

I don't do any macro type coding (hate the MacroBuilder) since vba is much easier and versitile to code in. The last macro I designed was over 15 years ago and it's sad to see MSAccess keep trying to blend macros and vba coding together.

To go to the active record on a report involves coding on an event (probably the OnClose event) of the report itself. In the design of the report, you'd select [Event Procedure] for the OnClose event of the Report itself (you may want to get familiar with using [Event Procedure] on form, report, and different controls on the form and reports. There's an Event for just about everything (such as an OnClick, OnDoubleClick, OnLostFocus, OnGotFocus, OnMouseMove, etc... event for a button or field, the form or report itself.)

For what you're trying to do entails capturing the Record Identifier (ie. your autonumber field in the table which you should have on your report) when the report is closing and then use that value to open the form (or goto a record on an active form) which would be based on the same table the report is based on. (For the report, I think it's Private sub Report_OnClose() in vba code after you select [Event Procedure] and then the 3 dots for the REPORT OnClose event). Try putting your code there.

A simple test to make sure the event is firing when the report closes and is grabbing the correct value is to put this code in the OnClose event of the report:

Private sub Report_OnClose()
Dim MID as variant
MID = Reports!MyReportName!MyRecordID
Msgbox MID
end sub

If this works ok, I'd then add in the rest of the code to go to the record on the form. It's been a while since I've designed any MSAccess reports (I use a different quicker method) so forgive me if this doesn't work. You may want to repost your question if you get no other answers.