Updating Subform Based on Combo Box Selection (1 Viewer)

[thabounceisback]

Hey All,

I have created a sub-form that is linked to parent form through a field that is determined by a combo box. I am trying to get the subform to requery the data anytime a new combo box selection is made.

I have tried using the "after update" event for the form using code:

[sub form name].requery

However, it is not working properly.

Also, the sub form is tabbed, if that has anything to do with it.

I appreciate any help I can get!

Best,
Kurt Amend

 

[thabounceisback]

I have now tried the requery code on nearly every event in the form. I am completely lost. Any ideas?

 

[JANR]

Try:

Me![SubFormName].Requery

or:

Me![SubformName].Form.Requery

JR

 

[thabounceisback]

What event should this code be attached to? And what object?

I have tried it on both the afterupdate and onchange event for the combo box on my parent form with no luck.

 

[JANR]

I have created a sub-form that is linked to parent form through a field that is determined by a combo box.

If you have set the master/child reationship then a requery shoulden't be neccesary. Access will handle this for you. Perhaps you should check that this relationship is setup correctly.

As for the event, it's put in the AfterUpdate event of the combobox.

JR

 

[thabounceisback]

If you have set the master/child reationship then a requery shoulden't be neccesary. Access will handle this for you. Perhaps you should check that this relationship is setup correctly.

As for the event, it's put in the AfterUpdate event of the combobox.

JR

Access handles the update, but it not immediate like I need it to be. If you change a selection, it will not update the subform until you either exit the form or change records. I need user's to be able to see updates as soon as they change the option in the combo box.

 

[vbaInet]

Where is the code attached to your combo box? That could be the problem.

 

[thabounceisback]

Where is the code attached to your combo box? That could be the problem.

I just used a parent/child relationship as built by access to create the sub-form.

I just want that relationship to work differently.

So I have attached the Me! [Form Name].Requery code to the AfterUpdate and OnChange event of the combobox.

I am open to trying other methods. I just need the subform to update immedietely after a combobox change.

 

[vbaInet]

But the combo box isn't filtering or searching records within the subform, so all you're doing is refreshing the data. You can use the Filter and FilterOn properties to filter your subform based on the combo box's value.

 

[thabounceisback]

But the combo box isn't filtering or searching records within the subform, so all you're doing is refreshing the data. You can use the Filter and FilterOn properties to filter your subform based on the combo box's value.

I am a little confused. What code should I use on the subform's On Filter event?

And wouldn't the filter still only be triggered as it is being done without the code?


I am trying to get the subform to update every time the value in the combobox is changed by a user. So, to me, it would make sense to use the event properties of the combo box.

What am I missing?

 

[vbaInet]

What is the name of the field that is unique to the combo box and subform? E.g. CustomerID?

 

[thabounceisback]

What is the name of the field that is unique to the combo box and subform? E.g. CustomerID?

The field in the combobox on the form is "Description," and the field on the subform is "DescriptionKey". If they need to have the same name, I can fix that.

 

[vbaInet]

The names need not change. What is the name of your subform control? And what is the name of your Subform? Go to design view, click on the subform control once, look at the name property. That's the name of the subform control. Also what's the name of your combobox?

 

[thabounceisback]

The names need not change. What is the name of your subform control? And what is the name of your Subform? Go to design view, click on the subform control once, look at the name property. That's the name of the subform control. Also what's the name of your combobox?

Subform Control: Description Key
Subform: Crosswalk subform
Combobox:Combo11

 

[vbaInet]

Combo box After Update event:

If Combo11.ListIndex > -1 and Combo11.value & "" <> "" Then
     Me![Description Key].Form.Filter = "[DescriptionKey] = '" & Combo11.value & "'"
     Me![Description Key].Form.FilterOn = True
End If

Try that and see what records appear on your subform.

By the way, is the "DescriptionKey" field in your subform a string data type?

 

[thabounceisback]

Combo box After Update event:

If Combo11.ListIndex > -1 and Combo11.value & "" <> "" Then
     Me![Description Key].Form.Filter = "[DescriptionKey] = '" & Combo11.value & "'"
     Me![Description Key].Form.FilterOn = True
End If

Try that and see what records appear on your subform.

By the way, is the "DescriptionKey" field in your subform a string data type?

I get Run-Time Error 2465: Access can't find Field 'Description Key' referred to in your expression.

I noticed that the code used does not have any reference to the subform. Is this a problem?

The DescriptionKey field is an Autonumber data type.

 

[vbaInet]

That's one way of referencing your subform. The .Form is calling the form attached to the subform control. If it's throwing up that error then you have given me the wrong name for your DescriptionKey field. Check your subform's record source to get what the actual name is.

 

[thabounceisback]

That's one way of referencing your subform. The .Form is calling the form attached to the subform control. If it's throwing up that error then you have given me the wrong name for your DescriptionKey field. Check your subform's record source to get what the actual name is.

No, I think you misread that. It doesn't say anything about the "DescriptionKey" field. It says it can't find "Description Key" field. Which is the control name for the text box in my subform which contains the "DescriptionKey" data.

I am confident that that is the name of the control.

 

[vbaInet]

So your combo box is actually embedded in your subform and not your main form?

 

[SOS]

The problem is likely that in this code from vbaInet:

If Combo11.ListIndex > -1 and Combo11.value & "" <> "" Then
     Me![Description Key].Form.Filter = "[DescriptionKey] = '" & Combo11.value & "'"
     Me![Description Key].Form.FilterOn = True
End If

you should be using a PERIOD instead of BANG (!) for

Me.[Description Key]

because the BANG is USUALLY (not always, but USUALLY) used for FIELDs and not controls. The controls use the period.