SheilaAlighieri
Technical User
Hi all,
I would like to make a two criteria search for my form. I was thinking of copying the example in the Northwind sample database. Here it works as follows. When you open the report 'Sales by Year' a form is opened, named 'Sales by Year Dialog'.
On this form you enter two fields which form the criteria for the query used in the Sales by Year report.
Only difference is, I would like to use it for a form, and in my case it won't show dates, but a name and a year "yyyy" respectively. The Field names are 'AccountName' and 'Year'
The result has to show the record of a specific name and year. But when I open the form Access asks for my parameters seperately and doesn't make use of my Sales by Year Dialog form. Can anyone check my code and see where I've gone wrong.
Or is there perhaps an easier way to achieve the same result? I want to make a search field which is based on two criteria. So the user can search quickly for a specific year and account. I use the query with the criteria as the record source for my form.
I copied the following in my Sales by Year Form:
Option Compare Database
Option Explicit
Public blnOpening As Boolean
Private Sub Form_NoData(Cancel As Integer)
' Display a message if user enters criteria for which there are no records.
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
strMsg = "No Data for Date Range"
intStyle = vbOKOnly
strTitle = "Error"
MsgBox strMsg, intStyle, strTitle
Cancel = True
End Sub
Private Sub Form_Open(Cancel As Integer)
' Open Sales by Year Dialog form.
' IsLoaded function (defined in Utility Functions module) determines
' if specified form is open.
blnOpening = True
Dim strFormName As String
strFormName = "Sales by Year Dialog"
' Open form.
DoCmd.OpenForm strFormName, , , , , acDialog
blnOpening = False
End Sub
Private Sub Form_Close()
' Close the Sales by Year Dialog form.
Dim strFormName As String
strFormName = "Sales by Year Dialog"
DoCmd.Close acForm, strFormName
End Sub
This is the code I added to my sales by year dialog form:
Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.
Private Sub Cancel_Click()
' This code created by Command Button Wizard.
On Error GoTo Err_Cancel_Click
' Close form.
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
End Sub
Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
' If Sales by Year form is not being opened for previewing or printing,
' cause an error. (blnOpening variable is true only when report's Open event
' is being executed.)
If Not Forms![Sales By Year].blnOpening Then Err.Raise 0
' Hide form.
Me.Visible = False
Exit_OK_Click:
Exit Sub
Err_OK_Click:
strMsg = "To use this form, you must open the Sales by Year form."
intStyle = vbOKOnly
strTitle = "Open from From"
MsgBox strMsg, intStyle, strTitle
Resume Exit_OK_Click
End Sub
These are the parameters and criteria in my query
Parameter Data Type
Forms![Sales by Year Dialog]!AccountName Value
Forms![Sales by Year Dialog]!Year Value
Criteria
[Forms]![Sales by Year Dialog]![AccountName]
[Forms]![Sales by Year Dialog]![Year]
Thanks,
Sheila
I would like to make a two criteria search for my form. I was thinking of copying the example in the Northwind sample database. Here it works as follows. When you open the report 'Sales by Year' a form is opened, named 'Sales by Year Dialog'.
On this form you enter two fields which form the criteria for the query used in the Sales by Year report.
Only difference is, I would like to use it for a form, and in my case it won't show dates, but a name and a year "yyyy" respectively. The Field names are 'AccountName' and 'Year'
The result has to show the record of a specific name and year. But when I open the form Access asks for my parameters seperately and doesn't make use of my Sales by Year Dialog form. Can anyone check my code and see where I've gone wrong.
Or is there perhaps an easier way to achieve the same result? I want to make a search field which is based on two criteria. So the user can search quickly for a specific year and account. I use the query with the criteria as the record source for my form.
I copied the following in my Sales by Year Form:
Option Compare Database
Option Explicit
Public blnOpening As Boolean
Private Sub Form_NoData(Cancel As Integer)
' Display a message if user enters criteria for which there are no records.
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
strMsg = "No Data for Date Range"
intStyle = vbOKOnly
strTitle = "Error"
MsgBox strMsg, intStyle, strTitle
Cancel = True
End Sub
Private Sub Form_Open(Cancel As Integer)
' Open Sales by Year Dialog form.
' IsLoaded function (defined in Utility Functions module) determines
' if specified form is open.
blnOpening = True
Dim strFormName As String
strFormName = "Sales by Year Dialog"
' Open form.
DoCmd.OpenForm strFormName, , , , , acDialog
blnOpening = False
End Sub
Private Sub Form_Close()
' Close the Sales by Year Dialog form.
Dim strFormName As String
strFormName = "Sales by Year Dialog"
DoCmd.Close acForm, strFormName
End Sub
This is the code I added to my sales by year dialog form:
Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.
Private Sub Cancel_Click()
' This code created by Command Button Wizard.
On Error GoTo Err_Cancel_Click
' Close form.
DoCmd.Close
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
MsgBox Err.Description
Resume Exit_Cancel_Click
End Sub
Private Sub OK_Click()
On Error GoTo Err_OK_Click
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
' If Sales by Year form is not being opened for previewing or printing,
' cause an error. (blnOpening variable is true only when report's Open event
' is being executed.)
If Not Forms![Sales By Year].blnOpening Then Err.Raise 0
' Hide form.
Me.Visible = False
Exit_OK_Click:
Exit Sub
Err_OK_Click:
strMsg = "To use this form, you must open the Sales by Year form."
intStyle = vbOKOnly
strTitle = "Open from From"
MsgBox strMsg, intStyle, strTitle
Resume Exit_OK_Click
End Sub
These are the parameters and criteria in my query
Parameter Data Type
Forms![Sales by Year Dialog]!AccountName Value
Forms![Sales by Year Dialog]!Year Value
Criteria
[Forms]![Sales by Year Dialog]![AccountName]
[Forms]![Sales by Year Dialog]![Year]
Thanks,
Sheila
I think Shyam got my problem exactly right. But I am not sure how to build the query and where to place the code. I would greatly appreciate some more information. If you have a sample file available, it would be even more wonderful. I will provide you with my mail address in case you have one.