Help! Search through form :) Needs some fine tuning

[SheilaAlighieri]

Hi,

I would like to make a search system for my database. I would like my method to use 2 forms; one to gather search criteria, one to retrieve and display results.

On my search form I have two textboxes, txtAccountName and txtBudgetYear.I want the search system to assume that when one of these criteria isn't entered, the user wants to see all available records thereof.

I made a query which uses the above-mentioned criteria as parameters:
[forms]![Budget Search]![txtAccountName]
[forms]![Budget Search]![txtBudgetYear]

When the user has entered the criteria, he/she presses the action button.

I attached the following code to the button:

DoCmd.OpenForm "FormName" or

I have a few problems though. I am not sure how to make the criteria in my query so it shows all records when nothing is entered... or a specific account name for a specific budget year... or all budget years for a specific account.... or all accounts for a specific budget year Hope I am still clear!

Furthermore, it is possible that for some criteria there is no data available. Now I see a white form, which I find terribly ugly. Is there a way to show a messagebox stating "The is no data available" or something like that? This is possible with the NoData option in reports.. but I don't know how to acquire this result in a form

Thanks.

Sheila

 

[PaulBricker]

Sheila this isn't hard to do but it's not easy to explain. On the Field line for your query you will have four fields (you can add any other fields once you get this part set up)

AccountName
BudgetYear
[Forms]![Budget Search]![txtAccountName]
[Forms]![Budget Search]![txtBudgetYear]

Now the Criteria is where is gets tricky. On the first criteria line under AccountName you will have
[Forms]![Budget Search]![txtAccountName]
On the first criteria line under Forms![Budget Search]!txtBudgetYear you will have
Is Null

On the second criteria line under BudgetYear put
[Forms]![Budget Search]![txtBudgetYear]
On the second criteria line under [Forms]![Budget Search]![txtAccountName] you will have
Is Null

On the third criteria line under AccountName you will have
[Forms]![Budget Search]![txtAccountName]
and on the third criteria line under BudgetYear you will have
[Forms]![Budget Search]![txtBudgetYear]

Now in the Show check box for the two columns
[Forms]![Budget Search]![txtAccountName] and
[Forms]![Budget Search]![txtBudgetYear]
deselect the Show box so they don't show.

Try this out and see if you have any problems.

To anwser the second part of your question, in the Click event, you will need to open a recordset based on the selections in the textboxes on the form and check to see if there are any records returned. If there are, open the query, if not, open a message box letting the user know there are no matching records. If you need help with this part, post the SQL for your query and we can write it from there.
Good luck.

Paul

 

[SBendBuckeye]

Sheila,

One of the options within docmd.openform, etc is a where clause without the WHERE. So think of your code as building an SQL where clause and do the following in your click code:

Dim strWhere As String
Dim intCount As Integer

'If Account Name entered (assumed string) then process it
If Len(txtAccountName) Then
strWhere = strWhere & "[AccountNameField] = '" _
txtAccountName & "' AND "
End If

'If Budget Year entered (assumed numeric) then process it
If Len(txtBudgetYear) Then
strWhere = strWhere & "[BudgetYearField] = " _
txtBudgetYear & " AND "
End If

'Strip trailing AND if there is one
strWhere = Trim$(strWhere)
If Right$(strWhere, 4) = " AND" Then
strWhere = Left$(strWhere, Len(strWhere) - 4)
End If

'Ensure valid data has been entered
If Len(strWhere) Then
intCount = DCount("[AccountNameField]", "TableName", _
strWhere)
If intCount = 0 Then
MsgBox "Invalid Account Name and/or Budget Year " _
& "combination - No data returned", _
vbOKOnly + vbInformation, "Invalid Criteria"
Exit Sub
End If
End If

DoCmd.OpenForm "YourFormName", , , strWhere

Here is what is happening - If nothing is entered in either text box then strWhere is an empty string which will return everything. Each textbox is tested separately and a where clause is created.

You don't need the square brackets [] if your field and/or table names have no embedded spaces. If you are testing for string data then it must be surrounded by single quotes '. Similarly, dates must be surrounded by pound signs # and a numeric value by nothing. I assumed your budget year was numeric. If it is not, add single quotes around it as well.

DCount counts the number of fields returned from a given table for a given set of criteria. If it returns 0 then you have bad criteria and there is no reason to call the other form, just pop a message and exit the function. Otherwise, modify your openform command to include strWhere as above.

You are definitely on the right track! Good job and good luck!

 

[SheilaAlighieri]

Thanks guys!

I will get to work on it tonight I'll let you know how it works out.

Sheila

 

[SheilaAlighieri]

I got the second part to work So now my system displays a message when no data is found.

I have some trouble figuring the query out though. I reduced the number of criteria to the Account Name to simplify things somewhat. BudgetYear wasn't really necessary for my search. Here is my query in SQL, maybe you can be so kind to alter it so that when nothing is entered, all records are shown

PARAMETERS [Forms]![Budget Commitments Search]![txtAccountName] Text ( 255 );
SELECT [Budget Database].[Account Name], [Budget Database].[Budget Year]
FROM [Budget Database]
GROUP BY [Budget Database].[Account Name], [Budget Database].[Budget Year]
HAVING ((([Budget Database].[Account Name])=[Forms]![Budget Commitments Search]![txtAccountName]));

Thanks loads for your help so far!

Sheila

 

[PaulBricker]

PARAMETERS [Forms]![Budget Commitments Search]![txtAccountName] Text ( 255 );
SELECT [Budget Database].[Account Name], [Budget Database].[Budget Year]
FROM [Budget Database]
GROUP BY [Budget Database].[Account Name], [Budget Database].[Budget Year]
HAVING ((([Budget Database].[Account Name])=[Forms]![Budget Commitments Search]![txtAccountName] Or Forms![Budget Commitments Search]![txtAccountName] Is Null));

This should do it Sheila. Let me know if you want the SQL for the BudgetYear also. With this SQL, I can just write it for you.

Paul

Paul

 

[SheilaAlighieri]

That would be very kind of you!
Then maybe I will put in the Budget Year after all.

 

[SheilaAlighieri]

Oh Paul. Something weird happens. When I leave the txtAccountName field empty on my search form, I still don't get all results. Is there an error in my code:

Dim strWhere As String
Dim intCount As Integer

'If Account Name entered (assumed string) then process it
If Len(txtAccountName) Then
strWhere = strWhere & "[Account Name] = '" & txtAccountName & "' "
End If

If Len(strWhere) Then
intCount = DCount("[Account Name]", "Budget Commitments Search", _
strWhere)

If intCount = 0 Then
DoCmd.Beep
MsgBox "No data returned.", _
vbOKOnly + vbCritical, "Error"
Exit Sub
End If
End If

DoCmd.OpenForm "Budget", , , strWhere


One more small thing I am sorry to ask so much of you but this has me dazzled as well. In my search result form I make use of subforms. I use two of them. The second one is linked to the first and makes use of the requery function on current. So when a line is selected in subform1, subform2 mentions the coinciding data. When I make use of a search form no line in subform1 is selected, leaving subform2 empty. Is there a way to make the database select the first rule in subform1 on open?

Sheila

 

[PaulBricker]

Sheila, I have the second parameter for txtBudgetYear set to Text (255). If it's something else, you will have to change that. Also, look for typo's. I reread it a couple of times but you might see something. The last thing is, you will have tow fields that you don't need to see. You can open the query in design view and uncheck the Show box. That will hide the unnecessary columns.

Paul

PARAMETERS [Forms]![Budget Commitments Search]![txtAccountName] Text ( 255 ), Forms![Budget Commitments Search]![txtBudgetYear] Text (255);
SELECT [Budget Database].[Account Name], [Budget Database].[Budget Year], [Forms]![Budget Commitments Search]![txtAccountName], Forms![Budget Commitments Search]![txtBudgetYear]
FROM [Budget Database]
GROUP BY [Budget Database].[Account Name], [Budget Database].[Budget Year]
HAVING ((([Budget Database].[Account Name])=[Forms]![Budget Commitments Search]![txtAccountName]) And ((Forms![Budget Commitments Search]![txtBudgetYear]) Is Null)) Or ((([Budget Database].[Budget Year]) = Forms![Commitments Search]!txtBudgetYear) And ((Forms![Budget Commitments Search]![txtAccountName]) Is Null)) Or ((([Budget Database].[Account Name]) = Forms![Commitments Search]![txtAccountName]) And (([Budget Database].[Budget Year]) = Forms![Commitments Search]![txtBudgetYear])) ;

 

[SheilaAlighieri]

Paul,

When I make the query this way, it doesn't give me any results. It doesn't ask me to enter any parameter either. Shouldn't it do that?

Sheila

 

[PaulBricker]

The form Commitments Search has to be open and the values in the two textboxes on the form. It won't prompt for anything. Then use a button to open the query.

Paul

 

[SheilaAlighieri]

Hi Paul,

I now use this code:

Private Sub OK_Click()
On Error GoTo Err_OK_Click

DoCmd.Close acForm, "Budget Commitments Search"

DoCmd.OpenForm "Budget Commitments"

Exit_OK_Click:
Exit Sub

Err_OK_Click:

Resume Exit_OK_Click

End Sub

But this gives the following error:

This action will reset the current code in break mode.
Do you want to stop running the code?

WHen I have this figured out my database will be finished. SO I promise I will stop bothering you then

 

[PaulBricker]

Sheila, if I'm reading your code correctly, switch the two lines.

DoCmd.OpenForm "Budget Commitments"
then
DoCmd.Close acForm, "Budget Commitments Search"

By closing Budget Commitments Search first, you are interrupting the code and that's why you are getting the error message.

Paul

 

[SheilaAlighieri]

Paul,

It doesn't make any difference. I still get the same error . Are you sure that SQL is correct?

Sheila

 

[danvlas]

Try ftp://ftp.artrom.ro/SearchForm.zip to create the filter visually on the fly.

I made it just to have a search/filter utility for myself, but you may find it useful.

Good luck,

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[PaulBricker]

Sheila, the error you are getting is a VBA error and not an SQL error. First, just put this in the click event for the button.

Docmd.OpenForm "Budget Commitments", acViewNormal

See if the Form opens with the correct information you need. If it does, then the problem is we are closing form Budget Commitments Search to soon and that's what is generating the error. Let me know how it goes just opening the main form and we'll go from there.

Paul

 

[SBendBuckeye]

Sheila,

You don't need to close your first form, just open the second one in dialog mode. That will pause your first one until the second form is closed or hidden. You also need square brackets around your form name because it has embedded spaces. Try something like this in your click event:

me.visible = false
'open second form and pause until it finishes
DoCmd.OpenForm "[Budget Commitments]", , , , , acDialog
me.visible = true

Good LucK!

 

[PaulBricker]

Actually you don't want the brackets around the form name even though there are spaces in the name. Because the argument is enclosed in quotes, the OpenForm method will look for a form called [Budget Commitments] and that will cause an error.

Paul

 

[SBendBuckeye]

Thanks, Paul and sorry Sheila for the bad information.

 

[SheilaAlighieri]

Hi Daniel,

You tool looks great. It gives an error in some instances though.

Syntax error in FROM clause.

Why does this happen?

Sheila