Query by Form with Sub Form

[BAzZ]

I've created two forms, my main form, and a search form which is a straight copy of the main form but called search form.

The search form has a command button which runs the "Query by form" from Microsofts web page (KB210242), for the most part it works, but on the forms they have two sub forms, I can't seem to get the search form to query anything on the sub forms.

The error message I'm getting is: Runtime error 2465: Database can't find the filed "housing" referred to in your expresion.

And this is the code it "debugs" to:

If Left(Me![housing], 1) = "*" Or Right(Me [housing], 1) = "*" Then
    where = where & " AND  [housing] Like '" + Me![housing] + "'"
Else
    where = where & " AND [housing]='" + Me![housing] + "'"
End If

I think it needs to reference the sub form somehow, I have tried using [forms]![subFormName]![housing], it comes up with the same error as above, but replaces "housing" with "forms".

Could someone please help me, as this is one of the last things I need before I can put this database into production!

Thanks

 

[pbaldy]

The proper syntax to reference a subform control would be:

Forms!MainFormName!SubFormName.Form!housing

 

[BAzZ]

Thanks for the reply, if I put in the code

Me!FormsfrmSearch!frmSearchHousing![Type of housing]

it comes up with the error I mentioned before, but it says it can't find 'forms'

this is the actual if statement I'm using, (I have several other implementations of this code but for fields on the main form.)

If Left(Me!Forms!frmSearch!frmSearchHousing![Type of housing], 1) = "*" Or Right(Me!Forms!frmSearch!frmSearchHousing![Type of housing], 1) = "*" Then
    where = where & " AND Forms!frmSearch!frmSearchHousing![Type of housing] Like '" + Me!Forms!frmSearch!frmSearchHousing![Type of housing] + "'"
Else
    where = where & " AND Forms!frmSearch!frmSearchHousing![Type of housing]='" + Me!Forms!frmSearch!frmSearchHousing![Type of housing] + "'"
End If

 

[pbaldy]

You haven't used the correct format yet

The proper syntax to reference a subform control would be:

Forms!MainFormName!SubFormName.Form!housing

To emphasize:

Forms!MainFormName!SubFormName.Form!housing

so in your case:

Forms!frmSearch!frmSearchHousing.Form![Type of housing]

 

[BAzZ]

Thanks again for the reply
If I try that it still comes up with the same error, its seems to be getting stuck on the forms part.

 

[pbaldy]

Drop the "Me" by the way (I just noticed you had it on some of them). When you start with "Forms" you're giving Access the full "path", and don't want the "Me".

 

[BAzZ]

Ok now it's got the error message saying it can't find the field 'frmSearchHousing', which is the subform, and when I click debug it highlights the top line of the IF statement.

If Left(Forms!frmSearch!frmSearchHousing.Form![Type of housing], 1) = "*" Or Right(Forms!frmSearch!frmSearchHousing.Form![Type of housing], 1) = "*" Then
    where = where & " AND Forms!frmSearch!frmSearchHousing.Form![Type of housing] Like '" + Forms!frmSearch!frmSearchHousing.Form![Type of housing] + "'"
Else
    where = where & " AND Forms!frmSearch!frmSearchHousing.Form![Type of housing]='" + Forms!frmSearch!frmSearchHousing.Form![Type of housing] + "'"
End If

The form is definitely called frmSearchHousing, which I copied and paste into the code

 

[pbaldy]

Okay, we're closing in on it. The most likely problem here is that the name of the subform can be different than the name of the subform control (the name the main form knows it as). There are a number of ways to check. In design view of the main form, click once on the subform so that the sizing boxes appear around it. Right click on the larger sizing box in the upper left corner and select properties. In the title bar of the properties window, it should say "Subform/Subreport:" and the name of the control. See if it's different than the subform's name. The name that appears here is the important one.

 

[BAzZ]

Alright, the error message has stopped, you were right about the control name and the subform's name being different. The only thing now is, if I enter some criteria into these sub forms to search by, it seems to skip over it and displays all records of the main form, and leaves the subforms blank. Any ideas?

 

[BAzZ]

I'll try and tell you a bit about the forms and the search.
Under Forms, I have a main form, two subforms, a search form based on the main form, and a results form (also based on the main form) which displays the search results, I have also copied the two sub forms and pasted them with search infront of the names.

The main form displays well with the subforms, and adding data is no problem.

When I use the search form I can search all fields that are on the main form, but with the sub forms, If I have the original sub form as part of the search form, all the fields on the search form have to be unbound, so as not to edit the underlying records, so if I do this to the subforms, they also become unbound on the main form, hence why I made copies of them. If I leave them bound, an error appears saying It can't update the ID field, which is good because I don't wont it to edit/add records.

I know that is all pretty confusing but it's a bit more of a background of what I've got going on.

 

[pbaldy]

It would be easier to see the whole code (or better yet a sample db), but it looks like you're comparing the form field to itself in your WHERE clause, rather than comparing a table field to the form field.

 

[BAzZ]

I can't attached the database due to file size restrictions, I have deleted everything that doesn't count including, the main form, copies of the sub forms, these were straight copies with all the fields unbound, deleted most of the records, deleted the embedded bitmaps, ended up deleting the qrys and pointing the sub forms directly to the tables, still not small enoguh though. Could I email you the whole thing, it is only 242 Kb when zipped? It is in Access 2000 file format

The two tabs with the sub forms are "Employment" and "Population and Housing"
Open the form "frmSearch" just click "Run Search" which opens the "Search Results" form. And notice on this form that "Employment" and "Population and Housing" subforms are blank.

I had a look at the design view of the query "qrySearch" (which is what the Dynamic Query is based on), and it doesn't include the subforms, I tried adding the sub form fields directly to "qrySearch". On the Search form I selected the drop down list's first option (on the population and housing subform), and ran the search, this seemed to display the first record four times (which is how many records are related from the sub form to the main form), but this still displays all the other records on the subform, which don't conform to what I want to search. In the code for the Run search button, I have only included the "type of Housing" field from the sub form.

 

[pbaldy]

Sent you a PM with my email address.

 

[pbaldy]

I didn't go over the whole process, but those 2 subforms are blank because of the master/child links. If you delete them, those subforms display records.

 

[BAzZ]

Deleting the link then displayed all the sub form records for every record in the main form, I fixed this by adding the communityID to the query "qrySearch".

Just need to get the Search part for the sub forms figured out...