parameter query opening a form

[Guest]

I have a button which on clicking runs a macro which opens another form. This
form is a result of a parameter query which brings data to the form according
to the parameter selected. Now if the parameter entered does not have any
record in the underlying table, currently a blank form opens. What i want is
if the parameter entered does not have any record in the table a message box
pops up to the user that you entered a invalid entry and then the blank form
does not show up at all. How to do this.

 

[Guest]

Hi Pitu,

Instead of having the user enter a parameter, can you have them select from
a combo box? You can cancel the form's open event procedure if the
recordcount is zero:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

If Me.Recordset.RecordCount = 0 Then
MsgBox "There are no records."
Cancel = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Open..."
Resume ExitProc

End Sub


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have a button which on clicking runs a macro which opens another form. This
form is a result of a parameter query which brings data to the form according
to the parameter selected. Now if the parameter entered does not have any
record in the underlying table, currently a blank form opens. What i want is
if the parameter entered does not have any record in the table a message box
pops up to the user that you entered a invalid entry and then the blank form
does not show up at all. How to do this.

 

[Guest]

I added the code into the form open event and it did work with user entering
parament just the way i wanted. Can you do the same while opening a report
witha button click which runs a macro to open a report. It also needs to
give a message box if no records exist to open in the report else open the
one that user enters parameter. Thanks.

 

[Guest]

Hi Pitu,

You can use the NoData event procedure in a report to accomplish this goal.
You don't do it the same way as you did on the form, because there's no need
to access the recordcount for the report's recordsource.

Use this in the report's code module:

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "There is no data for the selected criteria.", _
vbInformation, "No Data Available..."
Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Report_NoData..."
Resume ExitProc
End Sub


Now, I think you may be in trouble trying to get this to work using a macro.
I tend to avoid macros like the plague, because you cannot trap for errors
and handle them gracefully. If the report is cancelled, this will generate
error 2501, which you can trap for in VBA code. I'd replace the macro with a
click event procedure for the command button. Something like this:

Private Sub cmdPreviewAllItemsbyGroup_Click()
On Error GoTo ProcError

DoCmd.OpenReport "rptAllItemsbyGroup", View:=acViewPreview

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report cancled.
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdPreviewAllItemsbyGroup_Click event
procedure..."
End Select
Resume ExitProc

End Sub


Replace "rptAllItemsbyGroup" with the name of your report.

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I added the code into the form open event and it did work with user entering
parament just the way i wanted. Can you do the same while opening a report
witha button click which runs a macro to open a report. It also needs to
give a message box if no records exist to open in the report else open the
one that user enters parameter. Thanks.

 

[Guest]

Hi Tom,

Thanks a lot. It works and thanks for the macro suggestion. I din't knew that.

 

[Guest]

Glad to hear that you got it working.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Hi Tom,

Thanks a lot. It works and thanks for the macro suggestion. I din't knew that.