Adding to a record using VBA


I would like to know how I can add to a record using VBA.  What I am doing is using a DLookUp

Dim Result
Result = DLookup("[LicenseNumber]", "MGNameAddressPhone", _
             "[LicenseNumber]='" & Me.LicenseNumber & "'")
If Result = 1 Then

Open in new window

And if that returns a record (only one will return since the field can't have duplicates) I want then to add to that record data that is on my form.  Not sure how I can call that through vba to add to that specific record that DLookUp found or not?  It would be a separate field or fields within that table.
Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?
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.

Barry CunneyCommented:
DoCmd.RunSQL "UPDATE MGNameAddressPhone SET [SeparateField] = 'Found' WHERE [LicenseNumber] = '" & Me.LicenseNumber & "'"
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
You would want to use a recordset, do a find, and if found,you would need to add in any data yourself field by field to the form.

I think though before going through that, you explain your setup briefly (is the form bound or not, controls?, etc) and what exactly is happening at this point; what has the user done to trigger this, what should happen, etc.

Ernest GroggSecurity Management InfoSecAuthor Commented:

so here is what I have:

1.  User opens a data entry form, When a control is NOTINLIST I have a adding form come up (this is the form that I am asking about here).  this form is Bound to a Table and they add the data necessary.  It opens in Data Entry mode
         DoCmd.OpenForm "myForm", , , , acAdd, acDialog, NewData   (new data is the TwicNumber above)

2.  They add to this form as necessary.  Primary fields are License Info.  

3.  they click to save and then returns and refreshes form with current added data.

But since I found a slight issue with the way multiple users are entering data.  I need to check for the License Info first or else this ends up bringing up my msgbox on the first form:  Please try again and they can't add the data on the second form.

One person can enter License Info from another location because of some running background checks on persons, but when they enter the data they don't have certain information such as what the person here is entering onto the form when it gets prompted that that info is not yet in the table.  So since the License info can't be duplicated, I need some way to search and enter the data if the record is found seamlessly so the user just thinks the data is entered.  Else if no License info is found it just saves the record as normal on the 2nd form.

Primarily if there is no license info it saves and returns to form fine with no issues, it only becomes an issue if the main data entry group knows of the person ahead of time (which they do on occations on a scheduled visit, they just won't have the data that gets scanned into the system, which is the twic card, so they have to preclear a person to steamline their entry), but on unscheduled they don't know so all data is new.

sounds complicated but not really.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
OK, along the lines of what I thought.

You would do something along the lines of:

 Dim db as DAO.Database
 Dim rst as DAO.Recordset

Set db = CurrentDB()
Set rst = db.Openrecordset("MGNameAddressPhone")

rst.FindFirst  "[LicenseNumber]='" & Me.LicenseNumber & "'"

If rst.NoMatch then
   ' License not already on file, so we don't want to do anything.
   ' License is on file.  Fill in fields with data.
   Me.<myControl> = rst![<myFieldName>]
   ' Repeat above line as needed for each field as required.
End If

Set rst = nothing.

and probably do this in the AfterUpdate event of the license control, or in the OnLoad event of the form as soon as it opened (this would allow you to fill in the fields before the user does anything.

 and of course you should add error handling to that.


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
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.