Updating a record using VBA on form 3

[MA04]

Hi all,

Not sure how to do this but what i want to do is update a record, which would add a value to a field from a textbox to an existing record.

I have a command button on the form which when pressed should carry this out. The table i want to update to is called: Address. The field i want to update to is called Code. I want the code to check if field: A2 of table Address is equal to textbox A2 and field A4 is equal to textbox A4, if so then update field: Code, of that record being viewed with the value in textbox Code.

I have code that will add a new record but not update to a field in an existing record, this is my code:

Dim Db As Database
Dim rec As Recordset
Set rec = CurrentDb.OpenRecordset("Address")
                    rec.AddNew
                         rec("Code") = Me.Code
                    rec.Update
Set Db = Nothing

Any help appreciated, thank in advance,
M-.

 

[ZmrAbdulla]

See thread702-1030355

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[Crowley16]

you use the .Edit command instead of the .AddNew command to tell it to edit the CURRENT record, i.e. you should have a record selected already else it'll spew errors at you...

--------------------
Procrastinate Now!

 

[MA04]

Thanks for the help guys, this is my code seems to do the trick but see if you spot any problem spots,

Dim Db As Database
Dim rec As Recordset
Dim strSQL As String
strSQL = "SELECT Address.Code FROM Address WHERE A4 = " & A4.Value & "A1 = " & A1.Value
Set rec = CurrentDb.OpenRecordset("Address")
                    rec.Edit
                        rec(("Code") = Me.Code
                    rec.Update
Set Db = Nothing

Thanks,
M-.

 

[ZmrAbdulla]

Set rec = CurrentDb.OpenRecordset("Address")
rec.Edit
rec[b][COLOR=red]([/color][/b]("Code") = Me.Code
rec.Update
Set Db = Nothing

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[MA04]

Thanks Zameer that was bit of a typo,

however everything is not as well as it seems, the update code i have updates field code of the first record, and regardless of the record always updates the first record does not seem to update any other record but first one,

any ideas on what the problem may lay?
M-.

 

[jimirvine]

Hi, you may find that getting VBA to execute the SQL directly is faster and more efficient than manipulating an open recordset:
******************
Dim strSQL as string
strSQL = "Update Address SET Code = '" & Me.Code & _
"' WHERE A4 = " & A4.Value & "A1 = " & A1.Value

CurrentDB.Execute strSQL
***********************
Note** you may have to tidy up the actual SQL a bit as I had to write that freehand (no DBMS on my box today )
I'm assuming that "A1.Value" A1 is a control on the form. You'd be better explicitly declaring that: i.e. Me.A1 (Note that you don't really need .Value)
Also I noted that you didn't close Rec. You should, if you decide to keep with the DAO method of the update.
HTH
Jim

 

[ZmrAbdulla]

Set rec = CurrentDb.OpenRecordset([b][COLOR=red]strSQL[/color][/b])
rec.Edit
rec[b][COLOR=red]([/color][/b]("Code") = Me.Code
rec.Update
Set Db = Nothing

________________________________________
Zameer Abdulla
Visit Me
Hold your child's hand every chance you get.
A time will come when he or she won't let you.

 

[HarleyQuinn]

Not sure if this is the problem but I would change the SQL statement to something like:

strSQL = "SELECT Address.Code FROM Address WHERE A4 = '" & A4.Value & "' AND A1 = '" & A1.Value & "';"

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[Crowley16]

the way a recordset works is by creating a sort of virtual table with a pointer to a record. Whenever you edit/add a new record to the recordset, it will be done TO the record being pointed to.

if you want to edit a record, you first have to point to it. There have already been lots of suggestions to do this, however if you feel you are confident, I'd suggest using a sql statement...

--------------------
Procrastinate Now!

 

[MA04]

Hi Crowley,

Not solved problem but I believe that may be the proble. I have the table Address bound to my form and have a textbox in which i enter the postcode field A4, the corresponding field is found in textbox A4 which has field A4 as its cotrol source. Now the problem i believe is that in the navigational property of my form at the bottom the record no. has not changed instead of 1 it numbers all. Here is code of textbox in which user eneters value for field A4.

    Me.RecordsetClone.FindFirst "[A4] = '" & Me![EnterPostcode] & "'"

Any ideas,
M-.

 

[Crowley16]

why are you cloning the recordset?

just use the find function, which should give you a bookmark, and then move to that bookmark.

--------------------
Procrastinate Now!

 

[RoyVidar]

I would take a closer look at jimirvine's sugestion. Executing a query is better than recordset approaches.

[tt]strSQL = "update Address set

 = " WHERE A4 = '" & _
         me!A4.Value & "' and A1 = " & me!A1.Value
currentdb.execute strSql, dbfailonerror[/tt]

Should this not work, it's probably because of datatypes. Text fields need single quotes, numerics none, dates hash (#). Here illustrated with single quotes on the A4 field.

But if the controls are bound to the table, why do you wish to update programatically? It will probably cause some errors, you'll at least need to save the current record prior to trying to do anything programatically (docmd.runcommand accmdsaverecord)

If the Code control is bound to the Code field (you should use a naming convention on controls, btw, to disambiguate references), running the above savecommand should do the whole trick, shouldn't it?

Roy-Vidar

 

[HarleyQuinn]

Roy - I noticed that you (as I showed in my post) put the AND in (undoubtedly as second nature) but didn't tell MA04 that he/she was missing this from their SQL statement. Just to bring to MA04's attention for future queries.

Harleyquinn

---------------------------------
For tsunami relief donations

 

[MA04]

Hi Roy and all the guys,

Thanks for the help, the problem was a "schoolboy error" of sorts, what the problem was, was that i did not bind the code textbox to the code field.

M-.