Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Before Append Access 2013

Posted on 2013-12-23
5
Medium Priority
?
462 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
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
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 earned 1500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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