Is there a better way in dao to add a record in the recordset ?

ACCESS 2010  VBA


The Linked table "trans_time_entry" has quite a few records in it.

Is there a better way in dao to add a record in the recordset ?

Dim R As DAO.Recordset

If Me.Combo26.Text = "Closed" Then
             Set R = CurrentDb.OpenRecordset("SELECT * FROM [trans_time_entry]", dbOpenDynaset, dbSeeChanges)
                 R.AddNew
                 R![NSC_ID_Ref] = Me.NSC_Id
             Forms!nsc_dataentry.txtReOpened.SetFocus
             
             If Forms!nsc_dataentry.txtReOpened.Text = "" Then
                R![opened_at] = date_Now 'Forms!NSC_DataEntry.txtReOpened
             Else
                R![opened_at] = Forms!nsc_dataentry.txtReOpened 'd
             End If
                          
                R![InProgress_or_Closed_at] = date_Now
                R.Update
                R.Close
                Set R = Nothing
                Rvs = False

Open in new window


Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
1. The .text property is available when the control has the focus.  It is usually used in the Change event to monitor character by character typing if you need to do that.  When you are working with an entire field, you should be using the .value property which is the default property and so can be omitted.
Forms!nsc_dataentry.txtReOpened.Text
becomes
Forms!nsc_dataentry.txtReOpened  AND eliminates the need to set focus to the control.  If you are using old code samples that reference the .text property, they will always set focus to the control FIRST.  In A2016, the .text property seems to be available even when the control no longer has the focus.  But to be consistent and not restrict the app to only the newest version of Access, don't use the .text property unless the code is in an event where the control still has the focus.
2. When you are referencing fields on the form in which the code is running, use the "Me" qualifier instead of the external reference method.
Me.nsc_dataentry.txtReOpened
3. Always give your controls meaningful names BEFORE you write code in their events or start referencing them.  Who knows what Me.Combo26 Is?
4. Last but not least, This is Access, rather than fighting with it, use "Access" methods including bound forms rather than writing DAO or insert queries to add data.  If you are logging changes, I would simply run an append query in the AfterUpdate event of the form rather than using DAO to open a recordset.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You may open one record only. And, as Pat mentions, be careful using the Text property:

Dim R As DAO.Recordset

If Me!Combo26.Value = "Closed" Then
    Set R = CurrentDb.OpenRecordset("SELECT TOP 1 * FROM [trans_time_entry]", dbOpenDynaset, dbSeeChanges)
    R.AddNew
        R![NSC_ID_Ref].Value = Me!NSC_Id.Value             
        R![opened_at].Value = Nz(Forms!nsc_dataentry!txtReOpened.Value, date_Now)
        R![InProgress_or_Closed_at].Value = date_Now
    R.Update
    R.Close

    Set R = Nothing
<snip>

Open in new window

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<You may open one record only>>

 Or you may see this done as:

 <<"SELECT * FROM [trans_time_entry] WHERE 1 = 0">>

 which gives you an empty recordset.

Jim.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

aikimarkCommented:
If you're only adding rows to a single table, then you should open the recordset as a dbOpenTable type.  Just pass the table name as the first parameter (no "select from").

If you have a lot of rows to add, use transactions to batch them up.
FordraidersAuthor Commented:
Thanks all !!
PatHartmanCommented:
Since four people offered four DIFFERENT suggestions, did you use ANY of them?  Accepting all answers is as bad as abandoning the question since the point of the closure is not to give participation points to everyone who offered an opinion but to reward the person who ACTUALLY provided the answer and to guide people who find the question later using a web search.   You have left everyone in the dark and diminished the value of the work of the person who did provide the answer.  Sometimes it takes a village and in that case it makes sense to choose more than one answer.  Sometimes none of the answers led to a solution.  In that case, accept one of your own answers for no points and write a comment giving folks the solution that did work or the information that you were not able to solve the problem.

There is a reason why utopias don't work.  They sound good on paper.  They are all warm and fuzzy but if everyone gets the same reward whether they contribute or not, eventually nobody does anything.  Who wants to clean up the garbage when there are better jobs to be done or you don't even have to work at all.   Accepting one answer over others does not offend the experts.  They all know that sometimes another answer will be more suitable.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.