Command Buttons (Access 2002)

Topic

New Reply

I have a form that has command buttons that are used to open other forms; I tried to develop one master form to display certain record types. Certain times the command button will pressed but the table will not contain any records; thus the form will be blank. Is there a way to have an error message state “there are no records for the selected type”? And not open the form?

Reply | Quote

Replies

You could use DCount to determine the number of records that will be displayed. If this is 0, put up a message box and don’t open the form. How exactly you should use DCount depends on the way the forms are opened: do you just open them, or do you pass a where-condition?

Reply | Quote

I just open the form, no where conditions?

Reply | Quote

Say that FormA is based on TableA (it could also be a query, that isn’t important here). The On Click code of the command button that opens FormA could look like this:

Private Sub cmdOpenFormA_Click()
If DCount("*", "TableA") = 0 Then
MsgBox "There are no records to display.", vbInformation
Else
DoCmd.OpenForm "FormA"
End If
End Sub

Substitute the correct names.

Reply | Quote

Do you have any examples of using where condition? What if I wanted the form to only open up if certain values existed in Field A within TABLE A?

Reply | Quote

If you search for OpenForm and/or WhereCondition in this forum, you’ll find many examples, such as post 513,200, post 497,573, post 496,798 etc.

WhereCondition is used to limit the records displayed in a form to those satisfying a certain condition, not to open the form only if a specific condition is met.

Reply | Quote

How would I control the opening of a form based on a value with in a table? Or better stated if no records existed for the value of the command button the form should not open; however there are records wiithin the table.

Reply | Quote

The idea could be like this:

Private Sub cmdOpenFormA_Click()
Dim strCondition As String
' The restriction for the records to be displayed
strCondition = "..."
If DCount("*", "TableA", strCondition) = 0 Then
MsgBox "There are no records to display.", vbInformation
Else
DoCmd.OpenForm FormName:="FormA", WhereCondition:=strCondition
End If
End Sub

The contents of strCondition must be determined by you; it could be something like

strCondition = "[CompanyID] = 37"

or

strCondition = "[LastName] Like 'R*'"

or

strCondition = "[InvoiceDate] > #09/01/2005#"

Reply | Quote