• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

Creating new entry in Access if Entry does not already exisit

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
gideonn
Asked:
gideonn
  • 5
  • 4
1 Solution
 
RocketMan801Commented:
do you mean add a field inside an existing table, or a new table?
0
 
DabasCommented:
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
 
gideonnAuthor Commented:
Existing table.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
DabasCommented:
gideonn:
huh?

Dabas
0
 
gideonnAuthor Commented:
I was answering Rockets question.
0
 
DabasCommented:
gideonn:
Thanks!

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

Dabas
0
 
gideonnAuthor Commented:
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
 
gideonnAuthor Commented:
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
 
gideonnAuthor Commented:
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
 
DabasCommented:
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now