Value on sub forms still - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Value on sub forms still

Gary Orros

4 years ago

Sorry to ask a similar question as before but I still can't get my head around this and have been stuck here for a while.

I have attached some screenshots in case that helps and I'm hoping someone could point me in the right direction.

I'm a locksmith and trying to hold customers and lock details in a database. I have a main form based on the customer with a cylinder subform which holds the locks they own. This is a continuous form and works fine but the bit I can't get to work the way I need is the 2nd subform, which is the key history. This is stored in a separate table and linked to the locks via an ID number (CylinderID).

What I would like to achieve is to have the current record with the selector in the cylinder subform, and reference the key history subform. So if the cylinder has a record it will be displayed in the history subform. This does kinda work but I have to manually tell it which CylinderID to display every time I move a customer. I did watch the video you suggest about "after update" and "on click" But I just can't access to put the ID in for me. I have also tried query but still, have to add the ID.

Thanks for reading and I hope it makes sense

I thought at first the reason I get the parameter value screen is because maybe both subforms don't have the same cylinderID. This didn't either and I have added it to both subforms, making it not visible but still no joy.

	Gary Orros OP @Reply 4 years ago

	Gary Orros OP @Reply 4 years ago

	Gary Orros OP @Reply 4 years ago

	Gary Orros OP @Reply 4 years ago

	Scott Axton @Reply 4 years ago
Check out the Nested Continuous Subforms video. I think that may give you a path forward. It will give you an idea how to make the connections.

	Gary Orros OP @Reply 4 years ago
Thanks Scott, I have seen that one but as my forms are not in each other I didn't think it would be the same thing. I will rewatch this as probably missed something, thanks for the help.

Kevin Robertson @Reply

4 years ago

Use the OnCurrent event of the cylinder subform to change the record source of the history subform.

Private Sub Form_Current()



    On Error Resume Next

    

    Forms!MainForm!HistorySubform.Form.RecordSource = "SELECT * FROM KeyHistoryT WHERE CylinderID=" & CylinderID

    

End Sub

You will need to remove the Link Master/Child Fields in the history subform as this doesn't work between subforms. This is why you are getting Enter Parameter Value.
Change MainForm and HistorySubform to the actual names of your forms.

	Gary Orros OP @Reply 4 years ago
Wow, I can't believe it's working. Thanks so so much, Kevin, you have no idea of how many hours and videos I've watched trying to solve it. It's been driving me nuts. Thanks again

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View