Strange error using Query by Form

[thewrightdesign]

I have a form set up that has several fields to search from. I created a separate form and included the most searched fields that we use. In the search query for that form, under criteria, I have the following code:

Forms!QBF_Form!StudentID Or Forms!QBF_Form!StudentID Is Null

also for fields with a wildcard:

Like Forms!QBF_Form!LastName & "*" Or Forms!QBF_Form!LastName Is Null

This works perfectly well and returns the results I want to be seeing. The problem is, after running this search even one time, the codes above do something really strange. They completely fill up the other cells in the search query, get out of order, and eventually the search returns a memory error telling me there is no more memory for this operation.

Am I missing some code that I need to add to the search form code perhaps? Or some code to add to the query criteria code to make it stop doing that?

Also I'd like for the search fields on the form to clear after each search, but right now the last thing typed into the form stays there so you have to manually remove it before every search. I know there should be simple code to fix that.

Thanks in advance!

 

[boblarson]

Personally, for me I would just set the query to be generic - no criteria and then filter the form as needed with code like this:

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

And to reset is just

Me.Filter = vbNullString
Me.FilterOn = False

 

[thewrightdesign]

So in your example, would this still work on a form? What we need to do is to mimic what Lotus Approach can do by hitting a keystroke, bringing up a blank form that has all the same input fields as our records do, and be able to enter anything into any of the fields and have it search based on that.

Will that work for your example and if so can you guide me in how to do that? I'm fairly new to coding, as far as Access goes anyway, so not even sure exactly where I'd put all that code you listed. It looks like I'd have to include the If Len statements for every input field we want to be able to search on, is that correct? We're going to have about 20 - 30 input fields that need to be able to be searched on in different combinations.

 

[boblarson]

Yes, you would have an If Then Else statement for each field you wanted to be able to search on. But by doing so you are building ONLY the filter string that is applicable to the selected inputs and not trying to use each one including the Is Null part about the selection.

As for where to put the code it all depends on how you want the records to come up. If the users make their selections / inputs and then click SEARCH that is the simplest way as you would have this code on the click event of the button.

However, if you just want a form that matches the current form to have input for searching you can use the Filter By Form method which you would call the form by using

DoCmd.RunCommand acCmdFilterByForm

in the click event of a button on the form with the data. It then automatically changes that same form to be filter input and then you select/input what you want to search for and then click on another button where you have set the click event to apply the filter:

DoCmd.RunCommand acCmdApplyFilterSort

 

[thewrightdesign]

The problem I have with filter by form, and I mentioned this in my previous thread, is that we have two subforms linked to the main form which do not become searchable when we use the filter by form.

Still need to find a way to make sure the fields in the sub forms are searchable as well when we pull up this search form. I can make it do it by the method I described here, but like I said this strange error occurs and eventually crashes the database.

I will try the method you mention here, to see if it will still yield the results I need, but my boss is very specific about how she wants the search to work and I am struggling to get it to happen to her specifications. Mainly that she wants the ease and simplicity of the Lotus Approach we currently use, so I need to mimic that as much as possible.

I appreciate all your help and look forward to learning more.

 

[thewrightdesign]

If the users make their selections / inputs and then click SEARCH that is the simplest way as you would have this code on the click event of the button.

Bob,

I put the code on the click event of the SEARCH button and I get an error. Invalid procedure call or argument with Private Sub Searchbtn_Click() highlighted.

Here is the code I put there:


Private Sub Searchbtn_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


Did I do something wrong? I did just this small bit to make sure it works before coding for every input field.

 

[boblarson]

Did you type in this part:

Private Sub Searchbtn_Click()

or did you select the click event and click on the Ellipsis (...) to open to it? It is best to not type that part yourself but to let Access manage it.

 

[thewrightdesign]

I clicked on the ellipsis button and let Access do it itself.

 

[boblarson]

What version of Access are you using? What happens if, in the VBA window, you go to DEBUG > COMPILE? Do you get errors? If so, go through and fix it then repeat the DEBUG > COMPILE and fix the next one and so on until you don't get any errors. Then see what happens when you click the button.

 

[thewrightdesign]

I'm using 2003.

I get the very same error, invalid procedure call or argument, but it doesn't highlight anything so there's nothing to fix and run it again. How frustrating.

 

[boblarson]

See what happens if you import everything into a brand new database shell. It may be some corruption occurring.

 

[thewrightdesign]

Same error after importing to a new shell and even completely recreating a short version of the search form. Feel like I'm really missing something here.

 

[boblarson]

Can you post your database so I can take a look. I'm not seeing why it would be doing that just from what you've posted.

 

[thewrightdesign]

Here is the database I've been working with, thanks for taking a look at it!

edited to removed db since it had flaws I needed to fix. see next post.

 

[thewrightdesign]

Can you post your database so I can take a look. I'm not seeing why it would be doing that just from what you've posted.

I made some changes, enforced refrerential integrity on the relationships and changed the code in question to correct where it was pulling from. (changed me. to StudentInfo. since the search form is unbound and the fields pull from the studentinfo table)

 

[boblarson]

So far, you have

1. You don't change ME to StudentInfo. It SHOULD be ME as that refers to the current class object, which in this case is the Search Form. And we aren't checking for length of the name in the StudentInfo table. We are checking to see if we have anything in our search form's control to do the search with.

So change them all back like:

If Len(Me.LastName & vbNullString) > 0 Then

2. Why do you have combo boxes on the search form for almost everything? That isn't necessarily a bad thing but you do have that for almost everything and they don't have any record sources to pull the possible options.

3. Your Asset table (and the Counseling table) are still a little suspiciously non-normalized. You have different fields like WritingSkills, ReadingSkills, NumericSkills, EnglishScore, ReadingScore, etc. Those should not be fields. They should be rows of data. That way you can easily aggregate the data but, more importantly, you can add or edit any of the available items since things do change over time. And that way you would not have to modify/add fields or remove fields from the tables and then modify queries, forms, and reports whenever a change is made. You would simply add it, edit it, or remove it from the table as data.

 

[thewrightdesign]

I changed it all back to me. still have just the first three fields so once I get those to work I can do the coding for the rest. I used combo boxes because that's what everyone keeps telling me to use. Every instructional for doing a query by form says it, and it's what I was told to do in this forum as well even though I said earlier I didn't think combo boxes were necessary since we really won't ever be choosing search criteria based on a drop down list.

I realize a couple of the tables still need work, but honestly right now I am more focused on getting this search function to work. Since it won't be pulling off either of those tables I can fix them later.

Did you see any reason why it kept giving me the error it did?

 

[boblarson]

The error you were getting, at this point is irrelevant. We are pursuing a different path and so there is no point in even trying to go there.

However, the next thing I have to say is that you have this search form, but you have it trying to filter it but it isn't a form to filter. You are going to be wanting to filter the StudentInfo form I believe. Is that correct? So, having Me.Filter and Me.FilterOn in this code is not valid as this Search form is not bound to anything.

So that's the next question to find out the answer from you before continuing.

 

[thewrightdesign]

Ok. What I want exactly is that we can do a ctrl-F while on the StudentInfo form, have it bring up a blank form (much like filter by form does) where we can type in any criteria into any of the fields and have it return results on not just the form but the two subforms there as well. Ideally we should be able to enter a partial string and have it return results... by that I mean if I type in White into last name it returns all records where student last name either is or contains white. Not returning white only.

I also need to be able to do a search on the two subforms, so that I can search on school name, semester, year and return all enrollments for that period, or I can search on a specific class name (using either class prefix or class name) and return all records of students that have been in that class. Ideally, I want to do this from the main page, studentinfo, without having to navigate to either enrollments or grades forms to do these searches. I want to do it from the front screen.

I hope this clarifies what I'm looking for so that maybe a solution can finally be found for this.

 

[thewrightdesign]

See what happens if you import everything into a brand new database shell. It may be some corruption occurring.

Bob,

Can you tell me how to do this without recreating all my forms, queries, etc?

Thanks.