Need help in updating a record in unbound

S

sohailcdc

Registered User.
Local time
Today, 12:58
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

Did you check the Name property of the control to see if you spelled it correctly?

When you type Me. you should get intellisense and the dropdown will include the names of all controls in the form's collection as well as the names of all the fields in the forms collection. If that name doesn't show up, it doesn't exist.

PS - teaching yourself a new tool is a good thing and I don't want to discourage you but unbound forms are not the Access way. If you are going to use unbound forms, you should switch to VB.net or some other language since you are loosing the RAD quality of Access by not using bound forms. A better use of your time would be to learn about properties and events and how to control bound forms so that they work the way you want them to. CLUE - concentrate on the onCurrent and BeforeUpdate events for Forms since those are critical to properly controlling a form.
 
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
600
CJ_London
C
S
Help needed: Updating subtotal only in the last record of each item in Access form
Replies
9
Views
449
Pat Hartman
P
M
Solved Update records through a recordset
Replies
11
Views
734
Pat Hartman
P
S
Solved Need help querying subform's combo box based on the main form.
Replies
13
Views
626
SkeletorPlus
S
Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
731
Mike Krailo
Mike Krailo

Users who are viewing this thread

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