Solved

Before Append Access 2013

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

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 48

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 48

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

751 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