requery subform in MS Access

[ctaylor]

I am trying to simplify data entry in a database.

Basically I want to have a user select something analagous to a customer name from a combo box and display all the records related to that customer within a subform. By displaying the records within a subform (datasheet view) the data entry person can see the customer's order history and continue adding records from the same screen.

I have tried the following based upon the CLICK event of the combo box and have not been able to get the subform to display relevant information relevant to the value selected within the combo box:

1 - [forms]![frmDataEntry]![frmSubform].Refresh
2 - DoCmd.ShowAllRecords
3 - docmd.requery frmSubform
4 - frmSubform.Requery
5 - DoCmd.RepaintObject acForm, "[Forms]![frmDataEntry]![frmSubform]"
6 - [forms]![frmDataEntry]![frmSubform].Requery
7 - me.refresh


Any thoughts?

[ctaylor]

I figured it out - at least in my circumstance...

1 - I stored the value selected within the combo box
2 - I ran the query selecting customer records against that stored value
3 - I deleted the subform from the form and recreated it. This time there was no defined relationship between the combo box and the subform.

This allowed me to requery the subform and display results without being constrained by a relationship between the subform and the combo box.

When this was done I was able to just specify the DoCmd.Requery to get things to work.

When there was a defined link between the combo box and the subform MS Access would not allow me to requery the subform becouse of this link.

thanks everyone

[Whir]

Hey, that's a good one. I'm going to write that down. I can put this to use. Thanks ctaylor!

[ctaylor]

WHIR

glad you like it.

the actual database has a main form where command buttons in the margin set and requery the contents of the subform. the subform which would be used for data entry also contained a subform showing the "order history"

just remember that if you are writing VBA coding to autocomplete values within the subform, that you need to remember what controls will exist within the specified instance of the subform. When the subform displayed changes, the controls and control names displayed within the subform will change and your VBA code will need to explicitly apply to "instantiated" controls. (The access wizards cannot help you with the contents of a subform so you will have to code this part manually.)

[Creosote]

You can actually do this with way less overhead--if you've initially opened the recordset to all records you might use.

Example:


Private Sub Combo9_Change()
Dim str As String
str = Combo9.Text
searchField.SetFocus
DoCmd.FindRecord str
End Sub



Finds the specific records based on the combo's value.