Solved

Creating new entry in Access if Entry does not already exisit

Posted on 2003-11-15
10
230 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 350 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses
Course of the Month9 days, 14 hours left to enroll

623 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