combo not working with subform

[slimjen]

All; using access 2010. I had a blank form. I want use the form to put a combo box for custid. I use a query based on a table with custid and maxdescription for the record source. I created the combo box. Then I inserted a subform with custid description and other data to appear as datasheet view. During the insertion of the subform; it suggested that I use the maxdescription as link master and address as child link which I thought was odd. I changed it to custid for both master and child links. When I view the form; I used the combo box to select one of the custid’s that I know is in the subform. It didn’t select anything. I’ve done this hundreds of times and I can’t find what’s wrong! I planned to put code to filter; but this basic wizard should work without code! My initial thought was the datatype for both master and child links are text; but I have used this datatype in links before. Please help me figure this out.

[ranman256]

You dont need the sub form. Have the form.recordsource point to the table too,( w all the fields)....when you pick the ID in combo box, just filter the form.

me.filter = "[ID]=" & me.combobox
me.filteron = true

[ItsMe]

There may be a way to use the Wizard exclusively to cause a subform to be dependent on a combo but, I am not imaging a way right now. I use VBA to change the subform's .Filter or .Recordsource property. This approach requires there not be a Main/Child link between subform and main form

Perhaps changing the Recordsource of the subform (the form object inside the subform container) to something like this. And I think maybe a Requery for the subform in the combo afterupdate (can't remember if needed).

SELECT * FROM QueryName WHERE [custid] = " & Forms!MainFormName.ComboName

[rpeare]

To be honest I am not following your description of the problem

You say your main form has the CUSTID on it and it is based on a query with 'maxdescription' if you are using an aggregate query (non-updatable) as your MAIN FORM data source I don't know if that would cause you any issues in the subform (I've never tested it but it's something to check). Secondly you say that when you added the subform it suggested maxdescription as the link, so if you are using an aggregate query in the SUBFORM you will definitely not be able to update or add any records in the subform. If this is strictly a lookup form and you are selecting a customer on the mainform and wanting to see what's related to it in the subform your main form you would have to issue a requery command once the customer is selected and the customerID combo box on the main form would have to be unbound and the criteria for the query driving the main form would have to be something like

[forms]![mainformname]![customeridcomboboxname]

then in the ON CHANGE (or similar event)
you'd have to issue a me.requery command for your form to force the data to refresh.

It might be easier to post a sample of your database as we have different impressions of what your problem actually is.

[slimjen]

Ok. I found the problem. Apparently; you have to have the same named field in both recordsource of the form and subform. My recordsource field was cus_id oppose to the subform cust_id. I don’t remember that being the case as long as the datatypes matched and it is the same data. I further thought as long as the subform and the combo box matched datatype and data it would work. Thanks again for all your suggestions.

[rpeare]

you don't have to have the same field in both form and subform, access suggests fields that are the same name, but if you type in your own and you mistype one it's not going to work, better to use the interface so you make sure you're selecting fields to link that actually exist