Solved

Creating new entry in Access if Entry does not already exisit

Posted on 2003-11-15
10
228 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
Technology Partners: 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!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

749 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