Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating new entry in Access if Entry does not already exisit

Posted on 2003-11-15
10
Medium Priority
?
231 Views
Last Modified: 2010-05-03
Ok, so I need to create a new record in an Access database using ADO if the record does not already exisit.  In other words, if "James_09_03" does not already exisit I need it to be added through the script, the variable is picked up from a form, so I just need to know what the script would look like.  Thanks!!!
0
Comment
Question by:gideonn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 1

Expert Comment

by:RocketMan801
ID: 9757019
do you mean add a field inside an existing table, or a new table?
0
 
LVL 27

Accepted Solution

by:
Dabas earned 1050 total points
ID: 9757231
Hi gideonn:
Assuming your connection already has been set properly and is named cn

Set rs = New ADODB.Recordset
rs.Open "Select * from YourTable Where YourField = 'James_09_03'", cn
If rs.BOF then 'Record not found
      rs.AddNew
Endif

....Make changes to recordset here. This will happen to the new record, or to the existing one.

rs.Update 'Save changes

-------
If you only want the changes to happen when it is a new record, then move the changes and the rs.Update inside the If - End IF

Dabas
0
 
LVL 1

Author Comment

by:gideonn
ID: 9770394
Existing table.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 27

Expert Comment

by:Dabas
ID: 9772990
gideonn:
huh?

Dabas
0
 
LVL 1

Author Comment

by:gideonn
ID: 9773238
I was answering Rockets question.
0
 
LVL 27

Expert Comment

by:Dabas
ID: 9773289
gideonn:
Thanks!

Have you tried my solution, or can you clarify if I have misunderstood your question?

Dabas
0
 
LVL 1

Author Comment

by:gideonn
ID: 9773492
No I haven't tried your answer yet, and I am sure that you have the answer.  I have been busy on some other things once I have an oppurtunity to apply your method and test to verify that it works I will post again.
0
 
LVL 1

Author Comment

by:gideonn
ID: 9897127
Alright Dabas, I don't want to sound like an idiot but then again I am confused...  Where do I place the value to be entered in the field if the previously determined value is not already there?  In your statement you have rs.AddNew but I don't know where to tell it what to enter.  Thanks!
0
 
LVL 1

Author Comment

by:gideonn
ID: 9897270
To help here is what I have written out, I had to change some stuff here and there, which may be where the problem lies however I have multiple database records being used here and the variable names are a bit different.


----------  Code  ---------
    Dim con As New Connection
    Dim rec As New Recordset
    tblDate = Format(Date, "MM/YYYY")
    REPSUP = REPSUP & "_" & tblDate
    strTable = "tblESC_Summary"
   
    ' Opens a connection to CACS Dollars Database
    conCACS.Open "Provider=microsoft.jet.oledb.4.0;" + _
    "Data Source=" & GetPath & "ESC.mdb"
   
    rec.Open "Select * from " & strTable & " Where SupervisorsName = '" & REPSUP & "'", con      
        If rec.BOF Then 'Record not found
            rec.AddNew
        End If
   
        SupervisorsName = REPSUP
   
    rec.Update
-----------  Break  ----------
0
 
LVL 27

Expert Comment

by:Dabas
ID: 9898485
gideonn:
ADO works as follows:
You first use .AddNew (and as you noticed, with nothing else) just to let ADO this is a new record, as opposed to editing an existing one

You fill in the fields with values
rec.Fields("FieldName") = Value

YOu call the Update method (This is the case both if you are adding a record or editing an existing one)

rec.Update

Thanks for the points!


Dabas
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question