Solved

Before Append Access 2013

Posted on 2013-12-23
5
446 Views
Last Modified: 2014-01-06
Hi,

I run 2 simple queries using a form button that updates 2 tables. Basically it imports data from an Excel spreadsheet in to a table called Header and a table called Pricer Details.

The header table contains Header ID, Company info, etc., and the Pricer Details contains data relating to actual product linked to the Header ID .

However, I don't want either query to run if the Header already exists in the first table, so I need to figure out how to tell access to go no further if the Header ID value is already entered.

The form button syntax is shown below.

Thanks.

Private Sub Update_Click()

DoCmd.SetWarnings (WarningsOff)

DoCmd.Close

DoCmd.OpenQuery ("HeaderDetailsAppendQry")

DoCmd.OpenQuery ("PricerAppendQry")

DoCmd.OpenForm ("HeaderDetails"), acNormal

DoCmd.SetWarnings (WarningsOn)

End Sub
0
Comment
Question by:LJShepherd
  • 3
  • 2
5 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39735806
Where is the code you are using to either link or import the Excel header file?

Does that spreadsheet contain multiple headers?

Any chance the Price data would need to be updated for a particular header, so that process should run even if the header already exists?

Would be helpful if you could provide the names of your tables and fields (at least the pertinent fields)?
0
 

Author Comment

by:LJShepherd
ID: 39735823
Hi,

Two excel worksheets are linked to the database, and the queries are run against those worksheets, to append the data in 2 underlying tables, called Header Details and Pricer. They are linked by Ref and Quote Ref respectively.

The Pricer data would not need to be updated for a particular header, even if the header already exists.

Table structure is shown below.

Thanks



Header Details tbl Structure
Pricer tbl Structure
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39735838
So, does the Excel file that contains "Header data" contain a single record, or multiple records?

Does it include all of the fields in your "Headers Details tbl" or only some?  Which field(s) do you want to use to determine whether a particular record already exists in "Headers Details tbl"?

Can you provide the SQL you are currently using for query: "HeaderDetailsAppendQry"

Depending what the records look like, I'm thinking that should have your linked Excel table joined (Left) to your Headers Details table, and you should only be selecting those records where there is no match in the join, but I'm not 100% certain on the syntax of that without knowing which fields define a " if the Header already exists in the first table" condition.
0
 

Author Comment

by:LJShepherd
ID: 39735882
Hi the excel file Header Data is a single record. In the Header Details tbl, all fields apart from the Archive field are taken from this spreadsheet.

The field used to determine if the record exists is Ref, so if that exists go no further, if it doesn't, add it, then process the second query.

SQL is shown below.

Thanks

INSERT INTO HeaderDetails ( Ref, [Date], Init, Customer, [Cust Ref], Terms, Description, [Currency], Contact, email, tel, [BPL Contact], [BPL Tel], [BPL email], Certification, C2, C3, C4, C5 )
SELECT Header.Ref, Header.Date, Header.Init, Header.Customer, Header.[Cust Ref], Header.Terms, Header.Description, Header.Currency, Header.Contact, Header.[e-mail], Header.tel, Header.[BPL Contact], Header.[BPL Tel], Header.[BPL Mail], Header.Certification, Header.C2, Header.C3, Header.C4, Header.C5
FROM Header;
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39736484
So, what you need to do is check to see whether the value in [Header].Ref exists in the [HeaderDetails] table.  I would create a query that joins the two tables on the [Ref] field.  If the record already exists, then this will return 1 (hopefully not more than one) record.  If it doesn't exist, the query would return zero records.

SELECT HeaderDetails.Ref
FROM [HeaderDetails]
INNER JOIN [Header] ON [HeaderDetails].[Ref] = [Header].[Ref]

Save that as something like "qry_HeaderMatch"

Then you could use a DCount statement to count the number of record in that recordset.  The whole thing would look like:
Private Sub Update_Click()

    DoCmd.SetWarnings (WarningsOff)   '<=Delete this line (see explanation below)

    DoCmd.Close                                        '<= Don't know what you are trying to close here

    IF DCOUNT("REF", "qry_HeaderMatch") = 0 Then

        Currentdb.Execute "HeaderDetailsAppendQry", dbFailOnError
        Currentdb.Execute "PricerAppendQry", dbFailonError

    End If

   'I'm not sure if this should be inside if/end or outside, depends on whether you 
   'want to open the form if the header already exists.
    DoCmd.OpenForm ("HeaderDetails"), acNormal

    DoCmd.SetWarnings (WarningsOn)              '<= Delete this line too

End Sub 

Open in new window

Using DoCmd.SetWarnings (On/Off) has a lot of potential problems.  The worst of which is that you turn the warnings off, don't properly execute error handling, and end up exiting a procedure without setting the Warnings back on.  It is preferable to use the Execute method to deal with action queries (Insert, Update, Delete) as you can add the dbFailOnError option which will raise an error if an error is encountered in your action query.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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