Query not returning certain data...

[mgillespie21234]

Scenario: I have 2 tables, one called Location and the other called PC Inventory. They both have columns called sites. The query is set to search both tables by site name and return the PC's at that site that match the search criteria.

The search form has a drop down menu with all the site names. When you choose a site name from the last, the after update event does a re-query to a subform on that same form. The source object of the subform is set the query.

[Forms]![PC Search]![Combo6] - this is the criteria on the query that point to the drop down menu.
--------
Private Sub Combo6_After Update()
Me.Child19.Requery
End Sub

This is the requery that point to the subform (child19)
--------

The problem is that some selections from the list don't return any results to the subform. But there is clearly records that meet the criteria in the table. Example: ABC @ Maryland returns all the PC's at the site. DEF @ Maryland doesn't return any PC's at the site, but there are records with that site name.



Even if go into the table copy the site name that isn't working and enter into the search field it doesn't return the results. Been banging my head for 2 weeks on this, any help is greatly appreciated. Please, if you need any more information or have any questions, I will be happy to provide them!!!

[ranman256]

1. FYI, when you put a combo or text box on the form, you can NAME it something else that makes sense, instead of the default COMBO5. In properties, other tab, name. Like: cboSite
this is readily understandable in a query when you look at the criteria.

2. Is the sub form props: LINK CHILD FIELD, LINK MASTER FIELD , set properly? To Combo6 on both?

3. And actually, I think you must refresh the subform, not the child..so: Me.Child19.form.Requery

[ssanfu]

You can't (well you shouldn't ) link the two tables on "Sites". If "DEF @ Maryland" is in one table but not in the other table, "DEF @ Maryland" will not be in the recordset (the query).

You can select all records from one table (Location) and only the matching records from the other table (PC Inventory), but if you want to see a record from either table, you have two options:

1) Fix the table structure
2) Use a union query. You must type the union query - query wizard can't create it.

The union query would look like:

SELECT sites From Location
Union
SELECT sites From [PC Inventory]

You should read up on the syntax for union queries. This is off the top of my head - I don't remember exactly what the syntax is.

[mgillespie21234]

The form is unbound so i get the error "Cant build a link between unbound forms"

It work for some of the sites in the list though thats what i don't understand.

I made the change to the refresh and no difference.

[mgillespie21234]

I really only need to see the records from one table and that is PC inventory. My goal is to for the user to select the site from the drop down list and it populate the PC inventory from that site in the subform.

[ssanfu]

Then the query should use the combo box to filter the records.

It would be easier to have the combo box in the form header and the records in the detail section.


Care to post your dB for analysis?

[mgillespie21234]

Well that solved my delima... eliminating the second table and just running the query on one table

[ssanfu]

Excellent...