If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
Syntax error when using DoCmd.OpenForm
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. |
#3
|
|||
|
|||
Syntax error when using DoCmd.OpenForm
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. |
Thread Tools | |
Display Modes | |
|
|