Solved

Before Append Access 2013

Posted on 2013-12-23
5
447 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
[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
  • 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

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

740 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