Strange error using Query by Form

[boblarson]

1. Open Access
2. File > NEW
3. Put in a name and select the location for it
4. Go to FILE > GET EXTERNAL DATA > IMPORT
5. Select the old database from the browsing dialog that opens up.
6. Click OPEN on the file browsing dialog
7. Hopefully a dialog will pop up which will have tabs for Tables, Queries, Forms, Reports, etc.
8. If it does, start with the tables tab and click the Select ALL button.
9. Click the next tab and click the Select ALL button. Keep doing that for all tabs.
10. Click the OPTIONS button on that dialog and select the IMPORT RELATIONSHIPS checkbox if not checked and any of the other options for anything you might have (like Import/Export Specifications).
11. Then click OK and it should start importing everything.
12. If it stops with an error message about an object it should be easy to tell which because you can go to the different items and see where it stopped importing. Then you would repeat the File > Get External Data > Import function except not selecting the objects that imported and also skipping the one which caused the error.
13. See if any others cause an error.
14. The ones you can't import are what you will have to recreate or import from a backup.
15. You would need to set your Startup Options again for the new database.

 

[thewrightdesign]

1. Open Access
2. File > NEW
....

That worked perfectly thank you. It no longer gives me that error, but shows me a different one. LOL

I know we were moving past this issue but I wanted to make sure it wasn't corrupt like you said.

 

[boblarson]

What's the new error? Oh, and if it works now, then it likely was suffering some corruption.

 

[thewrightdesign]

What's the new error? Oh, and if it works now, then it likely was suffering some corruption.

It was improper use of a . but I got the studentid cbo unbound and that went away. Now when I enter a last name into the last name box and click search... nothing happens.

Here is the code on the onclick event of the search button

Private Sub Searchfrmbtn_Click()
Dim strWhere As String
Me.Filter = vbNullString
Me.FilterOn = False

If Len(Me.LastName & vbNullString) > 0 Then
strWhere = "[LastName] Like " & Chr(34) & Me.LastName & "*" & Chr(34) & " AND "
End If
If Len(Me.FirstName & vbNullString) > 0 Then
strWhere = strWhere & "[FirstName] Like " & Chr(34) & Me.FirstName & "*" & Chr(34) & " AND "
End If
If Len(Me.StudentID & vbNullString) > 0 Then
strWhere = strWhere & "[StudentID] = " & Me.StudentID & " AND "
End If
If Right(strWhere, 5) = " AND " Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub

 

[boblarson]

Of course nothing happens. Because you are using the filter on ME which is the current form - the search form. It isn't what you want to filter. So you would have to use

With Forms!YourFormNameForFormYouWantToFilter
    .Filter = strWhere
    .FilterOn = True
End With

 

[thewrightdesign]

Of course nothing happens. Because you are using the filter on ME which is the current form - the search form. It isn't what you want to filter. So you would have to use

With Forms!YourFormNameForFormYouWantToFilter
    .Filter = strWhere
    .FilterOn = True
End With

Ok that makes sense. I'll make that change. I would need to add to that code if I am wanting it to filter on the subforms on that studentinfo form then?

 

[boblarson]

For the subform it is

Forms!ParentFormNameHere.SubformControlName.Form.Filter =

And the SubformControlName is the name of the control which HOUSES the subform on the parent form and not the name of the subform (unless they share the exact same name). The .Form. part remains just exactly as is. That tells Access you are wanting something on the actual subform as opposed to the subform control.

 

[thewrightdesign]

For the subform it is

Forms!ParentFormNameHere.SubformControlName.Form.Filter =

And the SubformControlName is the name of the control which HOUSES the subform on the parent form and not the name of the subform (unless they share the exact same name). The .Form. part remains just exactly as is. That tells Access you are wanting something on the actual subform as opposed to the subform control.

Ok, so would I need separate With / End With statements to include the two subform controls in this? Or can they all be included in the

With Forms!YourFormNameForFormYouWantToFilter .Filter = strWhere .FilterOn = TrueEnd With
code?

 

[boblarson]

If you have two subforms then it could be done like this:

With Forms!ParentFormNameHere
    .SubformControlName.Form.Filter = strWhere
    .SubformControlName.Form.FilterOn = True
    .Subform2ControlName.Form.Filter = strWhere
    .Subform2ControlName.Form.FilterOn = True
End With

 

[thewrightdesign]

If you have two subforms then it could be done like this:

With Forms!ParentFormNameHere
    .SubformControlName.Form.Filter = strWhere
    .SubformControlName.Form.FilterOn = True
    .Subform2ControlName.Form.Filter = strWhere
    .Subform2ControlName.Form.FilterOn = True
End With

Thanks for all the help. It's not returning an error, but neither is it returning any results. Here is what I have on the search button onclick event now:

Private Sub Searchfrmbtn_Click()
Dim strWhere As String
Me.Filter = vbNullString
Me.FilterOn = False

If Len(Me.LastName & vbNullString) > 0 Then
strWhere = "[LastName] Like " & Chr(34) & Me.LastName & "*" & Chr(34) & " AND "
End If
If Len(Me.FirstName & vbNullString) > 0 Then
strWhere = strWhere & "[FirstName] Like " & Chr(34) & Me.FirstName & "*" & Chr(34) & " AND "
End If
If Len(Me.StudentID & vbNullString) > 0 Then
strWhere = strWhere & "[StudentID] = " & Me.StudentID & " AND "
End If
If Right(strWhere, 5) = " AND " Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If
With Forms!StudentInfo
.Enrollmentsubform.Form.Filter = strWhere
.Enrollmentsubform.Form.FilterOn = True
.Gradessubform.Form.Filter = strWhere
.Gradessubform.Form.FilterOn = True
End With
End Sub

What am I missing?

 

[boblarson]

Well first off the first part where you have

Me.Filter = vbNullString
and
Me.FilterOn = False


should be the same form references as the ones below.

Other than that, I'm not completely sure. Are the subforms linked to the main form in any way (do they have fields listed in the Master/Child links property of the subform control)? If so, do the subforms really have those three fields in their record sources and are the records there before you filter?

 

[thewrightdesign]

Well first off the first part where you have

Me.Filter = vbNullString
and
Me.FilterOn = False


should be the same form references as the ones below.

Other than that, I'm not completely sure. Are the subforms linked to the main form in any way (do they have fields listed in the Master/Child links property of the subform control)? If so, do the subforms really have those three fields in their record sources and are the records there before you filter?

I've attached the database again, maybe it will help to see it. this is the new one uncorrupted and on the search form in question I marked out what input boxes are to search on which form or subform. Hope this helps.

 

[boblarson]

Okay, so this took me a bit of work to do but hopefully it will help you out. Here's what I did:

Decompiled the database to fix the subforms not displaying information on the Student Info form.
Added the rest of the Search Form code, including adding a reset button which calls a function I put into a new module.
Changed a few things in your tables including (removed datasheet definition in table, changed Year to EnrollmentYear because YEAR is an Access Reserved Word, changed the datatype of the year fields to INTEGER instead of Double which is not a good datatype to use for year as it is a floating point number and can end up with rounding errors.
I also changed the search query to use with a new frmSearchResults which then lets you double click on the student ID of any row and then it opens the student info form to that record. I had to do it that way because of the search items on the search form. It wouldn't work the way you originally had envisioned with filtering the subforms and it would become a big mess.

I may have done some other stuff too, like renaming controls so it is very easy to find them in the lists. Oh, I did change the dlookups on the subforms to reference a column in the combo box for the class as it is much more efficient than dlookups. I also changed your relationships and added a lookup table. Your relationships had links which essentially formed a circle and that is a fairly good indication that they aren't set properly. So, I fixed that.

So, hopefully this helps.