Empty Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a parameter query that is invoked from a command button on a form. The
query asks for the description of an item.

Another form then displays each record from the query (I have a command
button that displays the next/previous records in the record set).

This works fine when the query returns records. However, if there are no
records returned, the form is displayed with headings only, because it is
bound to the query.

How can I

a. Display a message box indicating that there is no data to match the query
b. prevent the blank form from being displayed.

I would prefer a solution based on a macro, if possible.

Thanks for any help!
 
Use a Condition in the macro to test if the query will return any records.
Use the DCount function to get the number of records. Set up the expression
so that the form is not opened if the DCount returns a value of 0.

MacroName
Condition: DCount("*", "QueryName") = 0
Action: MsgBox
Message: There are no records to be displayed.

Condition: . . .
Action: StopMacro

Condition: (blank)
Action: OpenForm
Form Name: NameOfForm
 
Ken Snell is correct. I had the same issue with a form prompting or allowing
the user to enter a search criteria value (field or as in this case two
fields). These fields were the primary key of the table. The first form
prompted and received requirements, then, using a macro, opens the next form
in update mode.

This is the code I used in the first form in the After Update Event. The
table name was TST_FR_CASE_RECORDS. The fields being evaluated in the table
were CASE_NUM_YR and CASE_NUM. The reference to Me.xxxx is the field on the
input form that holds the value that I wanted to compare on (search for).
On successful match found (DCount > 0) it would issue message records found,
then pass control to a macro named Search_By_Case. If no match found, resets
focus to the input form, first input field. The macro code executed on
successful find follows the after update event code below:

Private Sub unbtxt_SEARCH_CASE_NUM_AfterUpdate()
If DCount("*", "TST_FR_CASE_RECORDS", "[CASE_NUM_YR]= " &
Me.[unbtxt_SEARCH_CASE_YR] & " And [CASE_NUM]= " &
Me.[unbtxt_SEARCH_CASE_NUM]) > 0 Then
MsgBox " Matching Records found "
DoCmd.RunMacro "Search_By_Case"
Else
MsgBox "No Records To Show", vbOKOnly, "No Records"
Cancel = True
Me.Form!unbtxt_SEARCH_CASE_YR.SetFocus
End If

End Sub
-----------------------------------------
Macro Search_By_Case:

Action = OpenForm
Form Name = Fr_CR_U
View = Form
Filter Name = left blank due to fields being passed via 1st input form
Where condition =
([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_YR]
And
[TST_FR_CASE_RECORDS]![CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_NUM])
Data Mode = Edit
Window Mode = Normal
---------------------------------------------------------------------------------------
Upon successfully finding the records, control is passed to form Fr_CR_U,
allowing updates, viewing, printing etc. Upon closing, returned to the 1st
form so a new search criteria value could be applied.

This site rocks and rules.... It's been a great help for me.

HTH

Robert Nusz,
DPS, State of Oklahoma
 
Back
Top