Recordsets

[LittleMan22]

Here's what I'm trying to do (this code needs to appear in the on-click event procedure of a button in a form).

I need to
a) open a recordset (Policies)
b) Access searches for the entry (unique entries) that matches the policy ID# displayed on Forms!frmClients!subFormPolicies.
c) Access updates the field 'status' within this entry to say "CHANGE"
d) the recordset saves and closes

How do I got about writing this code?

Thanks in advance,
Ryan.

 

[gwinn7]

DAO answer...

Public Function UpdateField(Polid as Long) as Boolean

Dim rs as Recordset

On Error Goto Handler:

set rs = Currentdb.OpenRecordset("SELECT * FROM Policies WHERE [PolicyID] = " & Forms!frmClients!subFormPolicies.Polid, dbOpenDynaset)

If Not rs.BOF then
rs.Edit
rs("Status&quot = "CHANGE"
rs.Update
End if

rs.Close
set rs = Nothing
UpdateField = True
Exit Function
Handler:
UpdateField = False
End Function

Gary
gwinn7

 

[gwinn7]

Oops! After "Subformpolicies", you need to put "form". Like this...

...!subformpolicies.Form.Polid

Gary
gwinn7

 

[tlbroadbent]

I would create and execute an UPDATE query rather than opening a record set. Here are two possible solutions.

Create and save query: qryUpdateStatusChanged

Update Table Set Status="Change"
Where ID=Forms!frmClients!subFormPolicies!PolicyID

Add code in click event to execute saved query.

DoCmd.OpenQuery("qryUpdateStatusChanged&quot

------------------------------
Alternate: Create and execute dynamic query in click event.

Dim sSql as string
sSql = "Update Table Set Status='Change'" & _
"Where ID=" & Me.PolicyID
DoCmd.RunSQL(sSql) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[LittleMan22]

qwinn7 quick question: How do I call your function?

 

[gwinn7]

You could stick it in a Module or the form itself. You can call the function from anywhere within your project/database.

Gary
gwinn7