Is "Mobile" a reservered word? (1 Viewer)

[Garrett!]

I have a form that has things like the date, a dealer name, the quote number, make, model, and cost for a quote that my salesmen create. There is a button on this form called Change Dealer that allows the salesmen to change the name of a dealer. Change Dealer button will open another form with a combobox that lists the dealer names. So the salesmen could change it from Stock Trailer to Trailerworld for example and the dealer name would change on my quotes form. This works unless the first word in the dealer name is "Mobile". I I tried to create a variable but I got the 94 invalid use of null error. If I add a blank space before Mobile in the table then the dealer name will change on the quotes form. I also tried to add a blank space in my string with no luck. So is "Mobile" a reserved word? This only seems to happen with that word. My code is:

Option Compare Database
Option Explicit



Private Sub cmdSave_Click()

On Error GoTo Err_ErrHandle

Dim sSql As String


    sSql = "UPDATE tblQuotes SET [DealerName] = '" & Me.DealerID.Column(0) & "'"
    sSql = sSql & " WHERE QuoteNum = " & Form_frmCustomQuote.QuoteNum
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSql
    DoCmd.SetWarnings True
    
    Form_frmCustomQuote.Requery
    
    DoCmd.Close
    
Exit_ErrHandle:
    Exit Sub
    
Err_ErrHandle:
    MsgBox Err.Number & Err.Description
    Resume Exit_ErrHandle
    
End Sub

Thanks!

 

[Uncle Gizmo]

I don't think that "mobile" is currently a reserved word, but it's one I would give a high probability of becoming a reserve word one day. Looking at your question it appears you are passing "mobile" in through an SQL statement.

I can't see that this would cause the problem you describe. You might have a problem passing quotes "" and commas ''' through an SQL statement because that can affect the actual SQL. This is called an SQL injection attack.

The reserved word problem is for elements of your VBA code things like field names in tables, and table names. This is where you need to avoid "reserved words". This is one good reason for using a naming convention, the convention itself destroys any chance that the word could ever be considered a real word. For example you could call A Field in your table "Date" that is a reserved word. If you used a naming convention you might well call the field "fDate" destroying any possibility of it causing conflict later.

 

[gemma-the-husky]

sSql = "UPDATE tblQuotes SET [DealerName] = '" & Me.DealerID.Column(0) & "'"
sSql = sSql & " WHERE QuoteNum = " & Form_frmCustomQuote.QuoteNum

this looks odd though. setting dealername to dealerid.column(0). column(0) is usually numeric. doesn't have to be, but often is.


try this instead, which should give you more info. And/Or don't turn the warnings off while testing.

 on error goto fail
 currentdb.execute sSql,dbfailonerror
 exit sub
  
  
 fail:
 msgbox "error with statement: " & vbcrlf & _
      ssql & vbcrlf & vbcrlf & _
      "Error: " & err & "  Desc: " & err.description

------
[edit]

this is nothing to do with mobile being a reserved word. this might affect the field name, but it won't affect data.

the most likely issue is that you are enclosing the data within apostrophes.

either you are using the wrong apostrophe character, or your string has an apostrophe within it.

 

[Garrett!]

Well I figured it out. I don't know why this worked but it did. My combobox was based on a query called qryDealers. In the query I only had one field, DealerName. I added another field to the query so I could filter some of the dealers out. Once I added a second field I could change the name to a dealer with "Mobile" in the name. I don't know why this fixed it but it did. Thanks for the help.