Multiple parameters 1

[Dophia]

Hi Everyone:
I am having trouble specifying different criteria in a query. I want the user to be able to retrieve records from a table, using a form to specify different criteria and show the results in a report. Is it possible to specify criteria from different fields in a table? For example: the database is for an animal shelter. I want the user to be able to specify a date range, then what type of animal, the breed of animal, the colour of the animal, etc.

Any guidance would be appreciated.
Sophia

 

[dhookom]

I asked you three questions and you didn't answer any of them. I understood how you built your code and which line of code caused the error.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: I thought that I did respond to your questions.

You wrote: Does the report open by itself? - I wrote that it is opened from a form, from which you pick the criteria for the report. I don't know what else you mean....unless you wanted the following, which is the "on open" "event procedure" for the report:

Private Sub Report_Open(Cancel As Integer)
'Set public variable to true to indicate that the report
'is in the Open event
bInReportOpenEvent = True

'Open form
DoCmd.OpenForm "FrmFindAnimalInfoReport", , , , , acDialog
'Cancel Report if User Clicked the Cancel Button
If IsLoaded("FrmFindAnimalInfoReport") = False Then Cancel = True

'Set public variable to false to indicate that the
'Open event is completed
bInReportOpenEvent = False
End Sub





The "on open" "event procedure" for the form, is:

Private Sub Form_Open(Cancel As Integer)

If Not bInReportOpenEvent Then
'If we're not called from the report
MsgBox "For use from the Animal Info Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub

You wrote: Does your code compile? Is there any code in the report? - I listed the code from my report. Again, I don't know what else you mean.

Can you give me a little more info?
Thanks, Sophia

 

[lespaul]

Have you compiled the code?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

I think Duane wants to know if the report opens without error when directly launched in the database window.

 

[Dophia]

Yes, I complied the code. If I open the report without going through the form, it opens up.

Any other suggestions?

Sophia

 

[dhookom]

My questions explained:
[blue]Does the report open by itself? [/blue]
Can you open the report from the database window?

[blue]Does your code compile? [/blue]
Do you get any errors when you attempt to compile your code?

[blue]Is there any code in the report? [/blue]
Can't make this any clearer.

I would try comment out the strWhere at the end

  DoCmd.OpenReport "RptAnimalInfo_Find", acPreview ', , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Yes, I can open the report from the database window and I get the same error.
Yes, I did compile the code and did not get any errors.
The code in the report is as follows:

Option Compare Database
Private Sub Report_Open(Cancel As Integer)
'Set public variable to true to indicate that the report
'is in the Open event
bInReportOpenEvent = True

'Open form
DoCmd.OpenForm "FrmFindAnimalInfoReport", , , , , acDialog
'Cancel Report if User Clicked the Cancel Button
If IsLoaded("FrmFindAnimalInfoReport") = False Then Cancel = True

'Set public variable to false to indicate that the
'Open event is completed
bInReportOpenEvent = False
End Sub
Private Sub Report_Close()
DoCmd.Close acForm, "FrmFindAnimalInfoReport"
End Sub



I still got the same error when I used what you suggested.

I may have narrowed down the problem. There was code to open the form, to choose criteria for the report, when I opened the report from the database window. When I took the "on open" event off, the report displayed all of the records. Then when I took off the following code from the form's "on open" event, it also displayed all of the records.


Private Sub Form_Open(Cancel As Integer)

If Not bInReportOpenEvent Then
'If we're not called from the report
MsgBox "For use from the Animal Info Report only", vbOKOnly
Cancel = True
End If
Form_Open_Exit:
Exit Sub
End Sub


I used the "If Not bInReportOpenEvent" from the article

http://office.microsoft.com/en-s/access/HA011170771033.aspx

on microsoft's knowledge base. I had to remove the public function, as in the article, since I would get an error message of "Complie Error: ambigious name detected:bInReportOpenEvent"

Code in Article:
Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or
' Datasheet view.
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

 

[dhookom]

Did you create a global memory variable bInReportOpenEvent?

I don't care for code in a report that opens a form to collect criteria. I always begin with the criteria form open and then open the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

I'm sorry, but I don't know what you mean by "global memory variable bInReportOpenEvent?" I even did a search on that term, to avoid being called a "slacker"! haha.

Anyway, I did what you commented about. I now open the form and then the report. I don't know why the article suggests it the other way. Also, I don't know why my database worked okay and then all of a sudden I was getting the error.

That article must have a bug in it. Although I did download the sample database with the code and it works fine. Very odd.

Any suggestions for why my database suddenly didn't work anymore?
Thank you for your help!
Sophia

 

[dhookom]

I don't know what you might have changed in the application. Some times installation or updates of other software will have adverse affects on Access apps.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DogTeacher]

Dophia, I am wanting to do the same type of query that you are doing. Could you post your final code to us? Also I tried to look up the article you referenced and the link does not work. I'd like to read that also.

Thank you.