Solved

Before Append Access 2013

Posted on 2013-12-23
5
442 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now