Syntax error when using DoCmd.OpenForm

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!

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lmcc007" wrote in message
...
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.

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!

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"lmcc007" wrote in message
...
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.