• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

Updating a recordset field after user updates a form

I have a datasheet form, based on a recordset, where a user may enter new (or revise existing) records.  Whenever a record is updated, I need to update a field in the underlying recordset that does not appear on the form.  I want to use the "AfterUpdate" event to trigger this action. (seems logical enough...)

Is there a way to refer to the recordset directly, or do I need to create a separate SQL statement / recordset / etc. to perform the update?
0
Mckenzma
Asked:
Mckenzma
  • 8
  • 5
  • 4
  • +1
2 Solutions
 
jjafferrCommented:
try
Requery
0
 
BillystyxCommented:
you can use dao recordsets to update specific fields in the table.

Billystyx
0
 
flavoCommented:
Me.recordset("MyField").value = "???"

0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
MckenzmaAuthor Commented:
Will I need an Edit / Update around this assignment statement?
0
 
BillystyxCommented:
yes
0
 
flavoCommented:
On second thoughts my post is a load of bolocks (getting into the grove of my new found country)

you will need the edit and update

me.recordset.edit
me.recordset("myField").value = "????"
me.recordset.update

But considering this in datashet mode, im not 100% which record its going to edit?? Id hope it was the current, but wouldnt be surprised if it was the first...

What is the actual need for this may i ask... there will no doubt be a "nicer" way of doing it...


Dave
0
 
BillystyxCommented:
Me.Recordset.Edit
Me.Recordset!MyField= "billystyx"
Me.Recordset.Update

Billystyx
0
 
BillystyxCommented:
yes, flavo, I think you arew right.

You would need.
str1="field1=" &ID.value
me.recordset.findfirst str1

if me.recordset.nomatch=false then
Me.Recordset.Edit
Me.Recordset!MyField= "billystyx"
Me.Recordset.Update
end if

Billystyx
and btw, I agree, why work with datasheet forms when you don't have to?
0
 
BillystyxCommented:
<off topic- I live in UK, originally from Brisbane too. Just checking up on your fascination with b*~~#!ks>

Billystyx
0
 
MckenzmaAuthor Commented:
It does actually edit the current record.  

Me.Recordset.Edit
Me.Recordset("myfield").value = "xxxxx"
Me.Recordset.Update

... works fine.

I'm using a datasheet due to the peculiarities of the end-user's request, sadly.

I will increase the points and split them, thanks to flavo and Billystyx
0
 
flavoCommented:
<off topic>

'ant that a co-inky-dink!

When did you come over?

</off topic>

0
 
flavoCommented:
:-)
Glad to help my friend
0
 
BillystyxCommented:
good luck with it, McKenzma, thanks for points:)
<offtopic>
been here for about 2 1/2 years now. No plans to go back - too hot in Brissy

Billystyx
0
 
flavoCommented:
I've been here for 2 1/2 months... too cold here ;)
Not wanting to go home any time soon, the ££ are too good compaired to back home.

Take care all

Dave
0
 
MckenzmaAuthor Commented:
Hold the presses...

The solution works fine if an existing record is being updated... but bombs if a new record is being added to an empty datasheet.  Get a "Run-Time error 3021: No current record" message.  Also, if a new record is being added to a non-empty dataset, the previous record highlighted is the one that ends up getting edited.

So flavo's initial suspicions appear to have been well-founded.

0
 
BillystyxCommented:
on a new record you need to ignore the previous code
You can do this a number of ways, one way is to set a form level variable before any subs like
Public newRec as boolean
and then when you go to a new record newRec=true
and when you save that new record,
newRec=false (this will ned to be done through buttons)
and then on the previous code
if newRec=false then
'previous code
end if



Billystyx
0
 
MckenzmaAuthor Commented:
Here's what I did for a work-around.

I added a "hidden" column to the datasheet and bound it to the field I needed to update after any user input / revisions.  I then added a "BeforeUpdate" sub to set/modify the value in this field.  (Me.{FieldName} = {Value})  This works for both the "new" and "update" cases, and avoids the extra code.

Thx again to all.
0
 
BillystyxCommented:
good work - and good luck:)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 8
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now