Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 78 votes, 5.00 average. Display Modes
Old 04-04-2012, 02:09 PM   #1
Mist
Newly Registered User
 
Join Date: Mar 2012
Location: Johannesburg, South Africa
Posts: 66
Thanks: 32
Thanked 0 Times in 0 Posts
Mist is an unknown quantity at this point
Send a message via Skype™ to Mist
Arrow Append record if exist or Update if not exist

My question is this: How do I go about creating a procedure that will interrogate each table (on completion of the 'common_fields' form) and APPEND the record if the ID isn't found in the destination table, or UPDATE the destination table's record if the ID is found?

Scenario background:
I have an Access 2007 database comprising two tables at present (probably 5 by the time I'm finished). In certain situations the input into some fields in one table will need to be replicated in a similar set of fields in the other table. I cannot join the tables as it will result in more than 255 fields.

Hence, I have created a third table containing the fields common to both these tables and want the user to fill in the common information in the third table's form. I then propose to APPEND this data to each of the two tables, if the form's ID doesn't exist in the destination table, or UPDATE the existing data in the destination table if it already exists - before presenting the user with a partially completed form for each of these two tables with only the blank fields to complete and possibly edit some fields before saving.

__________________
_______
Nigel
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Mist; 04-04-2012 at 02:10 PM. Reason: clarity
Mist is offline   Reply With Quote
Old 04-04-2012, 03:54 PM   #2
sparks80
Physicist
 
Join Date: Mar 2012
Location: West Sussex, England
Posts: 223
Thanks: 8
Thanked 62 Times in 60 Posts
sparks80 is on a distinguished road
Re: Append record if exist or Update if not exist

First of all I would suggest you create the append and update queries that would save the common data into the other two tables. You will probably need to include a parameter for the record ID that you wish to append/update.

Then the vba code can use the dcount function to test if the record exists in the destination table. If it does exist then perform update query, if not perform the append query. I'll write some sample code and get back to you.
sparks80 is offline   Reply With Quote
The Following User Says Thank You to sparks80 For This Useful Post:
Mist (04-04-2012)
Old 04-04-2012, 04:19 PM   #3
sparks80
Physicist
 
Join Date: Mar 2012
Location: West Sussex, England
Posts: 223
Thanks: 8
Thanked 62 Times in 60 Posts
sparks80 is on a distinguished road
Re: Append record if exist or Update if not exist

OK here is some sample code, although I haven't had time to test it.
You will need to go through and enter the names of the actual objects - tables, queries and fields.
The code below will pass a parameter called "FieldID" to the query. You can use this to restrict the update query to the relevant record:

So at the top of the SQL code you will need to include:
PARAMETERS FieldID Long; .........

And in the where condition:
WHERE Your_RecordID_FieldName = [FieldID]

Code:
Sub UpdateOrAppend(lngFieldID As Long)
    Dim cn As New ADODB.Connection
    Dim cmd As Command
    Dim p As ADODB.Parameter
    
    Set cn = CurrentProject.Connection
    
    cmd.CommandType = adCmdStoredProc
    
    ' Check if record with specified ID exists in Table1
    If Nz(DCount("*", "Table1", "ID_Field_Name=" & lngFieldID), 0) > 0 Then
        ' If record found then perform update query
        cmd.CommandText = "Table1_UpdateQueryName"
    Else
        ' If record not found then perform append query
        cmd.CommandText = "Table1_AppendQueryName"
    End If
    
    ' Create parameter to pass to Update or Append query
    Set p = cmd.CreateParameter("FieldID", adBigInt, adParamInput, 0, 0)
    cmd.Parameters.Append p
    cmd.Parameters("FieldID") = lngFieldID
    
    cmd.Execute
    
    ' Now you will need to repeat the same code for table 2 ....
    



    ' Finally release objects
    Set cmd = Nothing
    Set p = Nothing
    Set cn = Nothing
    
End Sub
To call the code:

UpdateOrAppend(12345)
where 12345 is the record ID in the "common fields" table

sparks80 is offline   Reply With Quote
The Following User Says Thank You to sparks80 For This Useful Post:
Mist (04-04-2012)
Old 04-04-2012, 10:05 PM   #4
Mist
Newly Registered User
 
Join Date: Mar 2012
Location: Johannesburg, South Africa
Posts: 66
Thanks: 32
Thanked 0 Times in 0 Posts
Mist is an unknown quantity at this point
Send a message via Skype™ to Mist
Re: Append record if exist or Update if not exist

Many thanks Sparks80, you seem to have it exactly, much appreciated. I'm gonna setle down and try it now!
__________________
_______
Nigel
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Mist is offline   Reply With Quote
Old 05-16-2012, 09:51 AM   #5
Mist
Newly Registered User
 
Join Date: Mar 2012
Location: Johannesburg, South Africa
Posts: 66
Thanks: 32
Thanked 0 Times in 0 Posts
Mist is an unknown quantity at this point
Send a message via Skype™ to Mist
Question Re: Append record if exist or Update if not exist

Quote:
Originally Posted by sparks80 View Post
OK here is some sample code, although I haven't had time to test it.
You will need to go through and enter the names of the actual objects - tables, queries and fields.
The code below will pass a parameter called "FieldID" to the query. You can use this to restrict the update query to the relevant record:

So at the top of the SQL code you will need to include:
PARAMETERS FieldID Long; .........

And in the where condition:
WHERE Your_RecordID_FieldName = [FieldID]

Code:
Sub UpdateOrAppend(lngFieldID As Long)
    Dim cn As New ADODB.Connection
    Dim cmd As Command
    Dim p As ADODB.Parameter
 
    Set cn = CurrentProject.Connection
 
    cmd.CommandType = adCmdStoredProc
 
    ' Check if record with specified ID exists in Table1
    If Nz(DCount("*", "Table1", "ID_Field_Name=" & lngFieldID), 0) > 0 Then
       ' If record found then perform update query
        cmd.CommandText = "Table1_UpdateQueryName"
    Else
        ' If record not found then perform append query
        cmd.CommandText = "Table1_AppendQueryName"
    End If
 
    ' Create parameter to pass to Update or Append query
    Set p = cmd.CreateParameter("FieldID", adBigInt, adParamInput, 0, 0)
    cmd.Parameters.Append p
    cmd.Parameters("FieldID") = lngFieldID
 
    cmd.Execute
 
    ' Now you will need to repeat the same code for table 2 ....
 
 
 
 
   ' Finally release objects
    Set cmd = Nothing
    Set p = Nothing
    Set cn = Nothing
 
End Sub
To call the code:

UpdateOrAppend(12345)
where 12345 is the record ID in the "common fields" table
----------------------------------------------------------------
In applying the above (thanks Sparks) I get a "runtime error 91" (see attached) and I'm also not certain what you mean by: "So at the top of the SQL code you will need to include: PARAMETERS FieldID Long; .........

I have been teaching myself as much as possible re- Access & VBA and my Append and Update Queries work just fine - I'm using Access 2007. Thanks for your inputm it's been very helpful i.t.o. my understanding. I will appreciate your comments on the above.
Attached Images
File Type: jpg AppendOrUpdate.JPG (58.3 KB, 604 views)
File Type: jpg AppendOrUpdate2.JPG (25.4 KB, 389 views)
__________________
_______
Nigel
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Mist is offline   Reply With Quote
Old 05-16-2012, 03:29 PM   #6
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,320
Thanks: 80
Thanked 1,415 Times in 1,335 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Append record if exist or Update if not exist

There is no need for deciding between append and update queries.

Simply use an Update query with an OUTER JOIN from the source to destination table on the common fields.

Unmatched records will be appended and matching records will be updated.
Galaxiom is offline   Reply With Quote
Old 07-30-2013, 02:25 AM   #7
syedadnan
Access Lover
 
Join Date: Mar 2013
Posts: 315
Thanks: 123
Thanked 2 Times in 2 Posts
syedadnan is on a distinguished road
Re: Append record if exist or Update if not exist

Quote:
Originally Posted by sparks80 View Post
First of all I would suggest you create the append and update queries that would save the common data into the other two tables. You will probably need to include a parameter for the record ID that you wish to append/update.

Then the vba code can use the dcount function to test if the record exists in the destination table. If it does exist then perform update query, if not perform the append query. I'll write some sample code and get back to you.
Regards,

Mr Spark Rare you on board,

I am using this way to be in touch with you as i have seen that you are having marvellous expertise in access , i am hiting head from last few days as me is a newbie at access 2007.. just looking to get help in producing automatic fee voucher at month start which consist of all student in student table .. this i need badly.. please contact at adnansafdar1979@yahoo.com or reply here to so i will send you a file or disscuss further

syedadnan is offline   Reply With Quote
Old 01-18-2016, 02:50 AM   #8
CoffeeGuru
Newly Registered User
 
Join Date: Jun 2013
Location: Dunstable, Bedfordshire, UK
Posts: 121
Thanks: 28
Thanked 1 Time in 1 Post
CoffeeGuru is on a distinguished road
Cool Re: Append record if exist or Update if not exist

Quote:
Originally Posted by Galaxiom View Post
There is no need for deciding between append and update queries.

Simply use an Update query with an OUTER JOIN from the source to destination table on the common fields.

Unmatched records will be appended and matching records will be updated.
Eh..How does that work I just get an error..

UPDATE <Destination table> A
INNER JOIN <Source table> B ON A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd
SET A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd

This works as an INNER JOIN but if I Just change it to OUTER JOIN I get Syntax error in UPDATE Statement.
__________________
Thanks,
Martin

Windows 7 Professional, MS Access 2010, SQL Server 2012
-----------------------------------------------------------------
If the above post has made you cringe at my inexperience, please help me out of my hole. You were in it once. Cheers.
CoffeeGuru is offline   Reply With Quote
Old 01-18-2016, 03:51 AM   #9
JANR
Newly Registered User
 
Join Date: Jan 2009
Location: Norway
Posts: 1,623
Thanks: 7
Thanked 165 Times in 158 Posts
JANR has a spectacular aura about JANR has a spectacular aura about
Re: Append record if exist or Update if not exist

Access do not support a full outer join. Change til to either Right Join or Left Join.

In your case I think you need Left Join

Also use your real table names marked in blue

Code:
UPDATE <Destination table> As A
LEFT JOIN <Source table> As B ON A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd
SET A.aaa = B.aaa AND A.bbb = B.bbb AND A.ccc = B.ccc AND A.ddd = B.ddd
JR
JANR is offline   Reply With Quote
Old 01-18-2016, 06:07 AM   #10
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Append record if exist or Update if not exist

janr, you only need to join on pk/fk keys, no need for each field and it is not LEFT JOIN, its RIGHT JOIN.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
append , iif , insert , update

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
append new records to table where record does not exist on table. flect Queries 3 08-13-2008 10:24 PM
Append if Record Doesnt Exist, Else Update proballin Queries 1 02-20-2008 02:36 AM
Append Only Where The Record does not Exist belly0fdesire Modules & VBA 1 05-30-2006 11:28 AM
Append records where non exist CraigBFG Queries 0 08-18-2005 07:34 AM
append query for vals that don't exist aphelps Queries 2 12-31-2003 10:18 AM




All times are GMT -8. The time now is 08:57 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World