Make links into records on a report

[Kevin C Niven]

I'd like the user to be able to click a text box in the report and go
to the corresponding record.

For example, if my report puts out a line like this:

ID Last First Address
123 Doe John 1234 Main St.

I'd like the user to be able to click the text box with the value
"123" and go to record ID 123 on frmMyForm.

So, the way I've tried to set this up is:

Private Sub Text0_Click()

DoCmd.OpenForm "frmMyForm", , , "ID = Me.Text0.Value"

End Sub

Which does not work. My problem is with "ID = Me.Text0.Value". How
do I correctly insert the numerical value of the text box there?


Thanks,
Kevin

 

[Jeff Boyce]

Kevin

What is the nature (data type) of the ID ... yes, I can see that it contains
digits in your example, but is it being stored as numeric or text?

If numeric, then I believe you'd need to not keep the reference to the field
within the quotes, maybe something more like (untested):

DoCmd.OpenForm "frmMyForm", , , "ID = " & Me!Text0

Using the Me. construction may work some times, but I believe the "." refers
to built-in properties/methods, where the "!" refers to user-created objects
.... like a textbox. Also, the default property of a control on a form is
its Value, so including ".Value" is redundant.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Hannah]

Hi Kevin and Jeff,
I need to be able to do the same here but I don't know where to start. What
do I need to do to be able to click on the ID (123) when my report opens? My
ID is a number data type. Which property do I need to use in order to click
on this ID to take me to the form? Also, I don't see any "On Click" in my
report property for me to write the SQL statement like you've mentioned.

Your help is greatly appreciated.

 

[Gina Whipp]

Hannah,

What version of Access? Reports are images that do not allow clicking on a
text box to show any form. Though I understand Access 2007 handles that
differently.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Hannah]

Hi Gina,
I am using Access 2003. From what Jeff described, I was hoping that I can
do this directly from a report. Our users do not want to go back and forth
between reports and forms. Actually, what I would like to accomplish was to
give them a report or listing but do not want them to update or override
certain fields. If I give them access to see the query, how do I format it
to where they can not update certain fields within this query?

Thanks for your help.

 

[Gina Whipp]

Hannah,

In Access 2003 you cannot do that in/on a report. You can do what you want
from a form and then lock the fields you do not want them updating. (You
cannot lock fields in a query either.)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Hannah]

Thanks Gina. I guess our users will have to go through that next step.
There is no easy shortcut for them.

Best regards,

 

[Gina Whipp]

You're welcome...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm