Adding SQL backend to 2007 database 3

[punky001]

I inherited a database originally created using Office 97, then migrated to Office 2003 then Office 2007. All has been working well in the database so I now want to put a SQL backend using the Access frontend. I have run into an issue and can’t figure out what the problem is. I get the error “Field cannot be updated”. Here is the code below with the problem pointing to: Me.DESC = rst!DESC


Private Sub cmbBarCode_AfterUpdate()
Dim dbs As Database
Dim rst As Recordset
Dim strSQL, DESC, strMsg As String
Dim PRICE As Integer
DoCmd.Hourglass True
Set dbs = CurrentDb()
Me.cmbBarCode = UCase(Me.cmbBarCode)
strSQL = "SELECT class,toolid,Desc, Price FROM [toolcls_Mob]"
strSQL = strSQL & " WHERE qty_tot > 0 and [barcode] = '" & Me.cmbBarCode & "' and status = 'A';"
Set rst = dbs.OpenRecordset(strSQL)
If Not rst.EOF Then
Me.CLASS = rst!CLASS
Me.ToolID = rst!ToolID
Me.DESC = rst!DESC
Me.PRICE = rst!PRICE
If IsNull(Me.ToolID) Then
Me.QTY_OL.SetFocus
Else
Me.QTY_OL = 1
DoCmd.GoToRecord , , acNewRec
End If
Else
Me.cmbBarCode.SetFocus
strMsg = "The barcode you entered does not exist or is checked out!"
If MsgBox(strMsg, vbOKOnly, "Error!") = vbOK Then
Me.Undo
End If
End If
DoCmd.Hourglass False
End Sub

I’m sure this issue is related to the SQL backend – but I’m not sure why. Can someone help please?

 

[PWise]

try

Set rst = dbs.OpenRecordset(strSQL,,dbSeeChanges)

 

[punky001]

I just tried that and still got the error "field cannot be updated" and pointing to Me.DESC=rst!DESC

 

[dhookom]

I'm not sure if this is the issue but Desc is a reserved word Allen Browne's web list. You could try wrapping the field name in []s.

Duane
Hook'D on Access
MS Access MVP

 

[punky001]

I changed the field to Desc1 in SQL table - linked back to Access - changed all queries / forms to reflect Desc1 and got the following error. "Compile error - Method or Data member not found" pointing to: me.DESC! = rst!Desc1. When I responded OK - pointed to "Private Sub cmbBarCode_afterUpdate()".

 

[dhookom]

Looks like you misspelled DESC1 as DESC!.
You should also change your code to:

Private Sub cmbBarCode_AfterUpdate()
Dim dbs As [b]DAO.[/b]Database
Dim rst As [b]DAO.[/b]Recordset

Duane
Hook'D on Access
MS Access MVP

 

[punky001]

I've made the suggested changes and have attached a new copy of the code....I'm still getting the "Compile error - Method or Data member not found" - poining to :me.DESC1.

Private Sub cmbBarCode_AfterUpdate()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, DESC1, strMsg As String
Dim PRICE As Integer

DoCmd.Hourglass True

Set dbs = CurrentDb()


Me.cmbBarCode = UCase(Me.cmbBarCode)
strSQL = "SELECT class,toolid,Desc1, Price FROM [toolcls_Mob]"
strSQL = strSQL & " WHERE qty_tot > 0 and [barcode] = '" & "' and status = 'A';"
Set rst = dbs.OpenRecordset(strSQL, , dbSeeChanges)
If Not rst.EOF Then
Me.CLASS = rst!CLASS
Me.ToolID = rst!ToolID
Me.DESC1 = rst!DESC1
Me.PRICE = rst!PRICE
If IsNull(Me.ToolID) Then
Me.QTY_OL.SetFocus
Else
Me.QTY_OL = 1


DoCmd.GoToRecord , , acNewRec
End If
Else
Me.cmbBarCode.SetFocus
strMsg = "The barcode you entered does not exist or is checked out!"
If MsgBox(strMsg, vbOKOnly, "Error!") = vbOK Then
Me.Undo
End If
End If
DoCmd.Hourglass False

End Sub

Any other ideas? I don't know what else to try.

 

[dhookom]

What is the name of the control that DESC1 is bound to?

Duane
Hook'D on Access
MS Access MVP

 

[Gammachaser]

Your statement:

Dim strSQL, DESC1, strMsg As String

is probably not doing what you think it is, and Me.DESC1 is not pointing at that variable... same thing for your variable PRICE. Me.PRICE and Me.DESC1 are looking on the form for controls called PRICE and DESC1. If you have controls with those names on your form I recommend you use a better naming convention for your controls (like txtPrice instead of PRICE) If you don't have controls on the form with those names and you are trying to fill the variables you Dimensioned in the procedure, drop the 'Me.' part.

Also

When you chain you DIM statements like you did, you will get strSQL and DESC1 as Variant types and strMsg as a string. You have to identify each one (unless you want variants.)

DIM strSQL as String, DESC1 as String, strMsg as String

will give you three string variables.

DESC1 = rst!DESC1
Price = rst!Price

will fill the variables DESC1 and PRICE.

 

[punky001]

Thanks to ALL.....I made changes and it works great - PLUS - I learned something new. You guys are the best! Stars to all!