Link to home
Get AccessLog in
Avatar of Richard Comito
Richard ComitoFlag for United States of America

asked on

Re-query a list box in a sub form when I select a different value from a list box in the main form.

I have a form that is t_name and in this form I have a List box.  When I select a value in the list box I have the results come up in a subform called t_address_subform, in the subform I have a different list box that pulls from a query design view.  I was just working on making sure that the subform list box pulled only the information on a specific name_id.  With help from EE this is now working when I load the form the first time.  But when I try and select a value from the list box in the t_name form the subform list box does not change.  I have tried to do a Click event with the list box in the main form that would make the subform list box requery.  But when I try to find the list box in the me.t_address_subform, I cannot find the list box.

My goal is to update this subform list box every time I make a new selection from the main form list box.

Should I be trying to use an event that is on the subform or is there a better way to do this?
SOLUTION
Avatar of danishani
danishani
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
You need to place this in the After Update event your first Listbox.

HTH,
Daniel
SOLUTION
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
ASKER CERTIFIED SOLUTION
Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Richard Comito

ASKER

Hi Everyone,

Thanks for all the replies.  I have tried all the above suggestion, with no luck.  I have tried them in both the Click event and the AfterUpdate event.

When I use this code.

Private Sub List7_Click()
    Forms!t_name!t_Address_subform.Form!List47.Requery
End Sub

Open in new window



I get this error.

 User generated image
With the other code I get no refresh in the subform at all.

Thx,

Gabicus
Are you sure you have the correct spelled name of your Subform?

HTH,
Daniel
remember the string your code is looking for is the name of the sub-form-CONTROL placed on the main form, NOT the actual name you gave the sub form
open the main form in design view and check the property page for the name of the control (the rectangle "hosting" the sub form)
Is the listbox on the main form a multi-select?  If so, then the listbox technichly has no value, so the query syntax mentioned by ErezMor will not work.  Try setting that property to No.  

first off, you should institute some form of naming convention.  Forms are normally named something like frm_Names, not t_name (which might indicate a table, but probably not a form).  To refer to a control on a subform, you use a syntax similar to:

Forms("MainFormName").subFormControlName.Form.NameOfControlOnSubform

The "subFormControlName" is not the name of the subform that you find in the SourceObject property of the subform control (which is actually the name of the form when you created it and before you drug it onto your form).  It is the value that is displayed in the Name property, when you click on the border of the subform.  I usually name my subform controls like:  sub_MainFormName_Purpose

Hi Everyone,

Thanks for the replies.  Just really quick, I know the naming convention is bad.  This is just a test DB.  I have not worked in access in over 8 years and I have forgotten must of what I did back then.  I spend more time in SQL 2005 and I am more comfortable with TSQL then VB.  What I am doing right now is working on a "simple" project for our Production Department.  All they need is Database to keep track of the Dancers and Choristers. Only one person will be using this DB at a time.  This is why I went with Access.  Basic DB and self contained UI.

I think that it is not the code, it is in the way I have set up the forms and query.  If I should change from using the query designer and just go with a Record Set, I am fine with that. I am emending some screen shot of how I have set everything up.  I hope that this will help in troubleshooting what I did wrong.

 User generated image
 User generated image
 User generated image
 User generated image
 
Private Sub List7_Click()
    Forms!t_name!sub_address.Form!li_address.Requery
End Sub

Open in new window


Thank you again for your help and advice.
if we're into debugging through images ;) change the tab of the properties window for the subform control to "ALL" so we'll see the control's name (in the "Data" tab as it is, you only see the underlying object name, not the sought-after control-name)
looking again i found it!!!
your object (the form used as subform) name is "t_Address_subform"
BUT THE NAME OF THE subform control on the main form CONTAINING the above form is actually "sub_address"

this is the name access is looking for when referencing a sub form, not the actual  "t_Address_subform"

change either of them so they match (my coding method - might opposed to some ee's is calling an object ALWAYS the same in all places so i never fall into such pitfalls...

Good Idea!  I made the changed to  t_Address_subform.  But I am still getting the same result and that is the subform and list box are not repopulating.  I am thinking that it is something that I did when setting this up that is overriding my code or there is a conflict with what I am trying to do.

Would the Linked Master Fields or Link Child Fields in the sub form be an issue?
I got it!!!  It was the criteria that I was using in the Query Designer.

I had this for the criteria:

[Forms]![t_Name]![name_id]

but when I changed the criteria to:

[Forms]![t_name]![List7].[Value]

the requery code worked like a charm.

Thank you again to everyone that help!!!  I am going to split the point because everyone gave basically the same answer.