Go to record from subform made from query?

[JQuinnC]

I spent $60 on a book about Access and it completely skips how to reference things... ugh.



Anyway, I have a subform that is taking data from a query to show products a client has. I want to be able to click on a line in that form (or select it and hit a button - I don't really care), and have it take me to the order details of that product.



How do I set that up? I have no idea how to reference a specific line in there and load it as the filter into a new form.

It's my first database.

Name of parent form: CurrentClientsView
Name of Query: sblProducts
Name of subform on parent form based on query: sblProductsListingSubform

[DougsGraphics]

I am not sure if what you want to do is use the subform to navigate in the parent, or to open another form and display the details of the selected data. I'm presuming its the later? If so, add a command button to the form detail section and label it as appropriate, e.g., "Open". You can use the Command Button Wizard to set it to open the appropriate form, select "Open the form and find specific data to display", link the appropriate fields, and done. This will create a macro that will do just what you want. Otherwise, you can use an event procedure and do the same thing using the DoCmd.OpenForm method with Me.my_control to reference the control you want to open the form based on (use a filter).

[vicsaccess]

one thing I've learned is that there are many ways of doing things with access depending on your knowledge and desire to know more. i have a similar setup as Doug's second scenario. my sub form is continuous. i have a invisible command button stretched over the area of data with the "curser on hover" set to hyperlink so that the user gets a cool hand as a curser anytime they move over something that will link them to another page. The on click event for this command button has this code

Private Sub cmdstart_Click()
    DoCmd.OpenForm "jobstartf", , , "workqueid=" & Me.WorkQueID
End Sub

this opens another form based on the unique id of the record i select. if you do a quick search of the openform method it will give you the syntax.
As for the book, i wouldn't fret about it. i have several but none of them are a complete reference. it takes my books, forums, google, /msdn.microsoft.com and youtube on a average day.

[JQuinnC]

I am not sure if what you want to do is use the subform to navigate in the parent, or to open another form and display the details of the selected data. I'm presuming its the later? If so, add a command button to the form detail section and label it as appropriate, e.g., "Open". You can use the Command Button Wizard to set it to open the appropriate form, select "Open the form and find specific data to display", link the appropriate fields, and done. This will create a macro that will do just what you want. Otherwise, you can use an event procedure and do the same thing using the DoCmd.OpenForm method with Me.my_control to reference the control you want to open the form based on (use a filter).

So I used this method, and get:

"A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control.

Close the OLE server and restart it outside of Microsoft Access. Then try to original operation again in Microsoft Access."

Not knowing what an OLE server is or how to restart it, I just rebooted my computer. It didn't help.

What does this mean and how do I fix it?

[JQuinnC]

I am not sure if what you want to do is use the subform to navigate in the parent, or to open another form and display the details of the selected data. I'm presuming its the later? If so, add a command button to the form detail section and label it as appropriate, e.g., "Open". You can use the Command Button Wizard to set it to open the appropriate form, select "Open the form and find specific data to display", link the appropriate fields, and done. This will create a macro that will do just what you want. Otherwise, you can use an event procedure and do the same thing using the DoCmd.OpenForm method with Me.my_control to reference the control you want to open the form based on (use a filter).

I just remade the button and it fixed the earlier problem.

New problem.

The button does not seem to be linking to the data in the form. How do I link the variable in the button to what's selected on the form?





Name of subform to select from is: sblProductsListingSubform and is based on a query.

[vicsaccess]

Glad to hear you figured it out. As for the form opening to the record, the only thing i see is the link,
sblnameofproperty=&"'"& [ID]&"'"
the two values of this where clause must match, looking at http://www.baldyweb.com/wherecondition.htm as referance

DoCmd.OpenForm "SecondFormName", , , "FieldName = " & Me.ControlName
Where SecondFormName is the name of the form being opened, FieldName is the field in that form's recordsource the restriction is based on, and ControlName is the name of the control on the current form that contains the value to be shown on the second form. As you will find throughout VBA, text and date values are treated differently. For text:

your where clause should look like the second part of this, I'm assuming its a number. so it should be the name if the ID on the form being opened wrapped in quotes and then the equivalent number on the existing form.

[JQuinnC]

Glad to hear you figured it out. As for the form opening to the record, the only thing i see is the link,
sblnameofproperty=&"'"& [ID]&"'"
the two values of this where clause must match, looking at http://www.baldyweb.com/wherecondition.htm as referance


your where clause should look like the second part of this, I'm assuming its a number. so it should be the name if the ID on the form being opened wrapped in quotes and then the equivalent number on the existing form.

Well, that looks like it should work, but....

How I could possible have a type mismatch error with two autonumbers (that are also the SAME autonumber) is beyond me. I ended up using a method to click the PropertyID inside of the query subform to take me to the record that seems to work just fine.

Someone linked me to this in another post: https://www.youtube.com/watch?v=L8nEFLVMQE8

Thanks for the help!