Need help in updating a record in unbound

S

sohailcdc

Registered User.
Local time
Today, 01:36
Joined
Sep 25, 2012
Messages
55
Hi there
I am totally beginner in Access, just trying to learn myself during my free time
I am using unbound form, to update the record, but I am unable to update the record, as when i try to run the codes, Access giving me following error

The form, first pull the customer details from list box and based on clicking the record, filled the empty fields on the form (which works perfectly), but when i change the details, I am getting following error

Need help from Gurus of Access

Run-time error '2185':
You can't reference a property or method for a control unless the control has the focus

List box code (which work perfectly)

Private Sub Updatecustomerbillto_AfterUpdate()
txtcustomerid = Updatecustomerbillto.Column(0)
txtcustomername = Updatecustomerbillto.Column(1)
txtcontactperson = Updatecustomerbillto.Column(2)
txtaddressline1 = Updatecustomerbillto.Column(3)
txtaddressline2 = Updatecustomerbillto.Column(4)
txtaddressline3 = Updatecustomerbillto.Column(5)
txtaddresscity = Updatecustomerbillto.Column(6)
txtaddressprovince = Updatecustomerbillto.Column(7)
txtaddresspostalcode = Updatecustomerbillto.Column(8)
txtaddresscountry = Updatecustomerbillto.Column(9)
txttelephonenumber = Updatecustomerbillto.Column(10)
txtfaxnumber = Updatecustomerbillto.Column(11)
End Sub

Following are the code when i press the update button (to update the information in the table "Custbillingdetail" - when i click the debug button, following red area is highlighted with Yellow and last line marked as error

Private Sub Cmdupdatecustomerbillto_Click()

Dim dbs As DAO.Database, sql As String, rCount As Integer

txtcustomerid.SetFocus
If txtcustomerid = "" Then
MsgBox "Please select a record to update"
End If
Set dbs = CurrentDb
sql = "UPDATE Custbillingdetail Set Customer_Name=" & txtcustomername.Text _
& ",Contact_Person=" & txtcontactperson.Text & ",Address_line1=" & txtaddressline1.Text _
& ",Address_line2=" & txtaddressline2.Text & ",Address_line2=" & txtaddressline3.Text _
& ",Address_city=" & txtaddresscity.Text & ",Address_province=" & txtaddressprovince.Text _
& ",Address_postalcode=" & txtaddresspostalcode.Text & ",Address_country=" & txtaddresscountry.Text _
& ",Telephone_number=" & txttelephonenumber.Text & ",Fax_number=" & txtfacnumber.Text _
& "WHERE Customer_ID=" & txtcustomerid.Text

dbs.Execute sql, dbFailOnError
rCount = dbs.RecordsAffected
If rCount > 0 Then
MsgBox "Record Updated"
Updatecustomerbillto.Requery
End If

End Sub

Can anyone help me out, where I am making mistake in syntax
Have a good one
 
Hello sohailcdc, Welcome to AWF.. :)

Well there are couple of things to look into other than just the error..
* What if no ID is chosen? The code will resume execution after prompting the MsgBox.. So to stop it we need to add a Exit Sub.
* My assumption is Name, Address and many information are Text, so you need to surround them with Single quotes..
* .Text is not required (which is also the problem of the error you are getting), just accessing the control name would suffice for the code to pick up the value..

So based on that, the following changes..
Code: 
Private Sub Cmdupdatecustomerbillto_Click()
    Dim dbs As DAO.Database, sql As String, rCount As Integer

    Me.txtcustomerid.SetFocus
    If Me.txtcustomerid = "" Then
        MsgBox "Please select a record to update"
       [B] [COLOR=Red]Exit Sub[/COLOR][/B]
    End If
    Set dbs = CurrentDb
    
    sql = "UPDATE Custbillingdetail Set Customer_Name=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtcustomername & "[COLOR=Red][B]'[/B][/COLOR]" _
            & ",Contact_Person=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtcontactperson & "[COLOR=Red][B]'[/B][/COLOR],Address_line1=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddressline1 & "[B][COLOR=Red]'[/COLOR][/B]" _
            & ",Address_line2=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddressline2 & "[COLOR=Red][B]'[/B][/COLOR],Address_line2=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddressline3 & "[COLOR=Red][B]'[/B][/COLOR]" _
            & ",Address_city=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddresscity & "[COLOR=Red][B]'[/B][/COLOR],Address_province=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddressprovince & "[COLOR=Red][B]'[/B][/COLOR]" _
            & ",Address_postalcode=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtaddresspostalcode & "',Address_country='" & Me.txtaddresscountry & "[COLOR=Red][B]'[/B][/COLOR]" _
            & ",Telephone_number=[COLOR=Red][B]'[/B][/COLOR]" & Me.txttelephonenumber & "[COLOR=Red][B]'[/B][/COLOR],Fax_number=[COLOR=Red][B]'[/B][/COLOR]" & Me.txtfacnumber & "[COLOR=Red][B]'[/B][/COLOR]" _
            & "WHERE Customer_ID=" & Me.txtcustomerid

    dbs.Execute sql, dbFailOnError
    rCount = dbs.RecordsAffected
    If rCount > 0 Then
        MsgBox "Record Updated"
        Updatecustomerbillto.Requery
    End If
End Sub
However I have not surrounded txtcustomerid as I believe it would be number.. if not.. enclose them between the single quotes as well..
 
Thanks Paul

after updating the information provided by yourself
now, i am getting
"Compile Error", Method or data member not found
and upon clicking the debug button
txttelphonenumber highlighted with Blue

i attached the database for your review its in 2003 (please don't convert it, since i don't have access to new version)

Regarding customerid, it's alphanumeric

Private Sub Cmdupdatecustomerbillto_Click()

Dim dbs As DAO.Database, sql As String, rCount As Integer

txtcustomerid.SetFocus
If Me.txtcustomerid = "" Then
MsgBox "Please select a record to update"
Exit Sub
End If
Set dbs = CurrentDb
sql = "UPDATE Custbillingdetail Set Customer_Name='" & Me.txtcustomername & "'" _
& ",Contact_Person='" & Me.txtcontactperson & "',Address_line1='" & Me.txtaddressline1 & "'" _
& ",Address_line2='" & Me.txtaddressline2 & "',Address_line2='" & Me.txtaddressline3 & "'" _
& ",Address_city='" & Me.txtaddresscity & "',Address_province='" & Me.txtaddressprovince & "'" _
& ",Address_postalcode='" & Me.txtaddresspostalcode & "',Address_country='" & Me.txtaddresscountry & "'" _
& ",Telephone_number=" & Me.txttelephonenumber & "',Fax_number='" & Me.txtfacnumber & "'" _
& "WHERE Customer_ID=" & Me.txtcustomerid


dbs.Execute sql, dbFailOnError
rCount = dbs.RecordsAffected
If rCount > 0 Then
MsgBox "Record Updated"
Updatecustomerbillto.Requery
End If

End Sub
 

Attachments

Code: 
& ",Telephone_number=[COLOR=red][B]'[/B][/COLOR]" & Me.txttelephonenumber & "'

Missed a single quote

JR
 
Thanks

Any help

Now I am getting Runtime error '3075'
Syntax error in string in query expression 'Customer_ID='11111111'
"11111111" or Customer_ID is a text field

when i click the debug it say dbfailonerro = 128

here's the code
Private Sub Cmdupdatecustomerbillto_Click()

Dim dbs As DAO.Database, sql As String, rCount As Integer

txtcustomerid.SetFocus
If Me.txtcustomerid = "" Then
MsgBox "Please select a record to update"
Exit Sub
End If
Set dbs = CurrentDb
sql = "UPDATE Custbillingdetail Set Customer_Name='" & Me.txtcustomername & "'" _
& ",Contact_Person='" & Me.txtcontactperson & "',Address_line1='" & Me.txtaddressline1 & "'" _
& ",Address_line2='" & Me.txtaddressline2 & "',Address_line3='" & Me.txtaddressline3 & "'" _
& ",Address_city='" & Me.txtaddresscity & "',Address_province='" & Me.txtaddressprovince & "'" _
& ",Address_postalcode='" & Me.txtaddresspostalcode & "',Address_country='" & Me.txtaddresscountry & "'" _
& ",Telephone_number='" & Me.txttelephonenumber & "',Fax_number='" & Me.txtfaxnumber & "'" _
& "WHERE Customer_ID='" & Me.txtcustomerid


dbs.Execute sql, dbFailOnError
rCount = dbs.RecordsAffected
If rCount > 0 Then
MsgBox "Record Updated"
Updatecustomerbillto.Requery
End If
 
That is because the table has the Customer ID set as Text type.. As I had mentioned earlier..
pr2-eugin said:
However I have not surrounded txtcustomerid as I believe it would be number.. if not.. enclose them between the single quotes as well..
Click to expand...
So based on that.. Just change it to..
Code: 
& "WHERE Customer_ID='" & Me.txtcustomerid & "[COLOR=Red][B]'[/B][/COLOR]"
Also you should always get in the habit of using Debug.Print, a very helpful command..
 
You must log in or register to reply here.

Similar threads

M
working with unbound field in a form
Replies
8
Views
917
CJ_London
C
S
Help needed: Updating subtotal only in the last record of each item in Access form
Replies
7
Views
765
shobhanauto
S
M
Solved Update records through a recordset
Replies
10
Views
1,217
MadPiet
M
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
1,149
SkeletorPlus
S
Mike Krailo
Solved Need help with before update code logic?
Replies
10
Views
1,092
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom