Syntax error when using DoCmd.OpenForm

[lmcc007]

I have a command button that when clicked it will open a form called Check
Addresses. Below is the code:

Private Sub cmdEditAddress_Click()
Me.Requery

DoCmd.OpenForm "frmCheckAddresses", , , "CompanyID=" &
CompanyID

End Sub

The above code works fine when there is an address (or one of the address
fields is filled out), but when there is no address I get an error instead of
a blank Check Address form. The error message is:

Run-time error ‘3075’:

Syntax error (missing operator) in query expression ‘CompanyID=’.

I want a blank form so I can add an address if the address field is blank.

How shall I do that? Any help will be appreciated.

Thanks!

 

[Allen Browne]

If the CompanyID is null (blank), there's no value to concatenate into the
WhereCondition, so it becomes just:
CompanyID =
which is clearly not valid, and so you get the syntax error.

To avoid that, test for Null, and then handle that case differently. Perhaps
you could filter the from so that there are no records to show. It would
then open the the new record. Like this:

Dim strWhere As String
If IsNull(Me.CompanyID) Then
strWhere = "(False)"
Else
strWhere = "CompanyID = " & Me.CompanyID
End If
DoCmd.OpenForm "frmCheckAddresses", _
WhereCondition:=strWhere

The WhereCondition string is an expression that evaluates to True or False
for each record. By setting it to the value False, no records match, and so
you end up with the new record.

 

[lmcc007]

Thanks a lot, Allen!

For some reason I keep getting confused about the WhereCondition. Oh well,
sometimes it take me a while to get something.

Thanks!