please help getting hyperlink to open subform...

[joebaich]

Hi all, thanks to those who take the time to keep reading...

I found a similar solved thread here... https://www.accessforums.net/access/...form-8521.html

But i'm pretty new at access and i can't quite figure out if my issue is the same... I have a table with a column that is populated by an update query. It populates a "Yes" value if it finds a corresponding record in another table and "0" if it doesn't. I would like to make these values hyperlinks which open a subform showing the related records, if any exist, and a blank form if they do not.

I almost had this working when populating with the ID variable rather than "yes/0", but I got stuck where there were multiple corresponding records. I could only get it to populate the first ID# and the hyperlink would open a subform showing only that single corresponding record. I thought maybe if I made it like a "Yes/No" instead of a specific ID# it might show all associated records in the subform, but i got all messed up somewhere along the way and now i can't even get back to where it was kind of working.

I started based on this piece of code I found online (see link) while trying to figure out how to do this....http://ms-access-tips.blogspot.ca/20...n-form-at.html

The code from this example on this site is as follows and is in the OnClick event for the 'Servicing' field on the 'FolioDetails' form.

On Error GoTo myError
Dim varWhereClause As String
varWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause
leave:Exit


SubmyError:MsgBox Error$
Resume Next

My main form is called 'FolioDetails' and is created from a table with the same name. The yes/no hyperlink field in this form is named 'Servicing' and has Yes or 0 as values. The form i want these hyperlinks to open is called 'Servicing' and is created from a table with the same name. All of these forms by the way are datasheet style.

The linked field between these two tables is named Area/Jur/Roll and is a 13 digit number, but is data type Text, as it could potentially start with a zero.

The goal here is to be able to click on a "Yes" hyperlink and open a subform showing all (could be multiple) related records from the 'Servicing' table. Do I need to create a new query for this form to find only the servicing records with the same Area/Jur/Roll value as the one related to the activated hyperlink for my subform to display?

Thanks again!
Joe

[June7]

The second link you posted crashes IE8, Firefox (very old version, guess I better update) just can't find the URL, Chrome gives me list of possible websites. Think I found the page http://ms-access-tips.blogspot.com/2...n-form-at.html
However, just tested the link I posted and IE still doesn't like it - won't load but at least doesn't crash.

The code provided is commonly used to open form or report filtered to a specific record, regardless of the Is_Hyperlink property of textbox. Seems to me that is just a display setting and doesn't really influence the code.

You can pass whatever criteria you want in the strWhereClause variable. Can be multiple criteria. Just concatenate as needed. Not really understanding the data structure so can't be more specific.

Advise no spaces or special characters/punctuation (underscore is exception) in names. Better would be AreaJurRoll or Area_Jur_Roll.

[joebaich]

thank for the reply June7! I'm super new at access and i guess part of my problem is i don't really understand what exactly the code is referencing and what i want it to be referencing.... for example in the code i started with from someone elses example... where they use "ID = ... i think i'm wanting to reference my Yes/0 values in my [Servicing] field in my [FolioDetails] table. Then when i click on this yes/no value, it opens a form that shows the records in my [Servicing] table that correspond to the associated Area/Jur/Roll number (which i will rename) So i guess my question is what do i want to replace "ID= with and what do i want to replace Me!productId with? would it make sense to replace ID with "Servicing =" and "&Me!Area/Jur/Roll" ?

VarWhereClause = "ID = " & Me!productId
DoCmd.OpenForm "frmProducts", , , varWhereClause

[joebaich]

Does this help?

[June7]

Yes, that sounds right. Maybe:

varWhereClause = "Servicing=True AND Area/Jur/Roll=" & Me!Area_Jur_Roll

Still have questions about data structure. Your Yes/0 field is a yes/no datatype? Is Area_Jur_Roll a text or number field?

Again, spaces and special characters not good idea, Street # should be StreetNum or Street_Num.

[joebaich]

This is the Servicing form, which is just the table in form view...

[joebaich]

These are the design views for the folio details table and form...

[June7]

Area_Jur_Roll appears to be a text type. Text criteria must be enclosed in apostrophe delimiters (dates use #)

varWhereClause = "Servicing=True AND Area/Jur/Roll='" & Me!Area_Jur_Roll & "'"

True is a constant in Access and does not require delimiters if applied to a Yes/No type field.

[joebaich]

so when use this code, i get a msg box labeled Servicing and the Servicing form opens displaying the information from the first record in the Servicing table, which isn't the corresponding information

varWhereClause = "Servicing=True AND Area_Jur_Roll=" & Me!Area_Jur_Roll
DoCmd.OpenForm "Servicing", , , varWhereClause

[joebaich]

varWhereClause = "Servicing=True AND Area_Jur_Roll=" " & Me!Area_Jur_Roll""

am i missing something?

[joebaich]

should i change the update query to update to "Yes","No"? do i need the quotation marks here?

[June7]

I see now Servicing is a text field, not Yes/No type:

varWhereClause = "Servicing='" & Me!Servicing & "' AND Area_Jur_Roll='" & Me!Area_Jur_Roll & "'"

This assumes Servicing and Area_Jur_Roll are both fields in the form RecordSource.

Area_Jur_Roll values appear to be unique in the examples you show. If the values are not duplicated in the form dataset, there is no need for the Servicing criteria. Using unique criteria means only one record will be retrieved. This corresponds to the code template that used ID criteria.

varWhereClause = "Area_Jur_Roll='" & Me!Area_Jur_Roll & "'"

[joebaich]

Area_Jur_Roll are unique in the FolioDetails table, but not in the servicing table... One foliodetail A_J_R may have multiple Servicing records with that same A_J_R and i would like the servicing form to display them all...

Also, how can i get my update query to populate Yes and No, or True and False, or even Yes and blank instead of 0?

[joebaich]

Holy cow! i just figured out how to update that field with yes/no check boxes! so does that mean it is now no longer a text field? if so, what does this mean for my quotation mark needs?

Thanks,
Joe

[joebaich]

ok, check boxes is a bad idea... they don't work well as hyperlinks... how about actual "Yes" and "No" Values?