Solved

Creating new entry in Access if Entry does not already exisit

Posted on 2003-11-15
10
229 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA filters 2 81
vb6 connector to SQL Server 2 42
Problem to line 23 81
odbc driver manager data source name not found and no default driver specified 9 64
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

752 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