Use VBA to open a form from a main menu where one of the fields is null/blank

Question

I'm new to VBA and beginning to understand the basics, however, I'm having trouble opening a form from the main menu (which is just a form without a record source) where one of the fields is null/blank. I'm using a button with an on-click event to make this happen.

I'm able to open a form by setting the field to a specific value to open all the records that have that value. For example, the below code works:

DoCmd.OpenForm "frm_Projects" , , , "Status='Active'"

However, I have a notes field in a form and when that field is null/empty, I want it to display only those records. The other fields can be filled out.

I've tried the following:

(1) Loads a blank form as if I'm trying to add a new record

DoCmd.OpenForm "frm_Project", , ,"txt_Notes='Is Null"

(2) Gives me a "Run-time error '2450':"

If IsNull([Forms]![frm_Project]![txt_Notes]) Then
   DoCmd.OpenForm "frm_Project"
Else
   MsgBox "Some text"
End If

June7 June7 · Accepted Answer · 2019-08-27T00:39:29

Don't use equal sign with Is Null. Also, the solo apostrophe is not appropriate.

DoCmd.OpenForm "frm_Project", , , "txt_Notes Is Null"

The second code is missing closing paren.

If IsNull([Forms]![frm_Project]![txt_Notes]) Then

However, cannot reference field of form that is not yet open. Need a way on main form (or open a 'search' form) for user to choose which criteria they want to use for opening form or report. Or if you don't want user to have any input, code needs to query the data source to see if any records meet the requirement. A DCount or DLookup domain aggregate function can accomplish that.

If DCount("*", "your table or query name", "txt_Notes Is Null") > 0 Then
    DoCmd.OpenForm "frm_Project", , , "txt_Notes Is Null"
Else
    DoCmd.OpenForm "frm_Project"
End If

This assumes empty string is not allowed in field. I NEVER allow empty strings in text fields. Null and empty string are not the same thing. If empty string is a possibility, code will have to be adjusted. To handle both Null and empty string: "Nz(txt_Notes,'')=''"

Use VBA to open a form from a main menu where one of the fields is null/blank

1 Answers