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
Solved

Insert select query result into a table?

Posted on 2008-10-30
8
3,054 Views
Last Modified: 2011-10-19
I'm making a form for speeding up the process of creating commercial invoices.  I attached screenshots for visualization.

The invoice entry form has an underlying table and each line item has it's own field.  (IE: Line Item 1, Line Item 2, etc).  My goal is that when you click the pencil button next to the corresponding line item, it will pull up another form (which is based on a select query) that allows the user to enter a SKU number and the description of the product is pre-populated.  From there I want the user to be able to click "Add Line Item" to add the product details into the appropriate field (ie: Line Item 1)

How can I achieve this?

I barely know anything about coding so please be as descriptive and simple as possible.

Thanks
invoice-form.jpg
add-item.jpg
0
Comment
Question by:paintb4707
  • 4
  • 4
8 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 22840792
It would serve you well to examine the Invoice forms associated with the sample mdb that came with you copy of Access as well as the associated tables.  The mdb is named Northwind.mdb and is normally located in:

C:\Program Files\Microsoft Office\Office\Samples\

for Access 2000.  For Access 2003 swap Office for Office11



0
 

Author Comment

by:paintb4707
ID: 22840847
Just to further explain my question, I basically want to know how to code the "Add Line Item" button so that it adds the description of the product into it's appropriate field in the invoice table.  The select query already works as intended and the user is able to input a SKU to populate the Description field but I don't know how to take this result and insert it into a table.
0
 

Author Comment

by:paintb4707
ID: 22841291
I looked at the Northwind database.  I think it's a nice idea using a drop-down but I feel that it would be too time consuming.  There are THOUSANDS of products that we manufacture and it would just be much faster if you could enter the SKU number instead.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 44

Expert Comment

by:GRayL
ID: 22842957
You do have a table tblInvoices and another table tblLineItems.  Each record in tblLineItems carries a foreign key from tblInvoices - correct?  That way you could have an invoice with one line item or a hundred - it would not matter.  Are we on the same page?   If not, I would recommend you have another look at Northwind.

As to the reason for the dropdown, you could limit the SKU's to the dropdown, and virtually eliminate typo's.  With auto complete on, you would start to type the name, and very shortly you would narrow it down to a few from which you would click on the correct choice.  A SKU should never be typed except when entering new items.  Just my 5 ¢.
0
 

Author Comment

by:paintb4707
ID: 22843741
I'm only a beginner in Access, picking it up as I go along so I don't really follow what you're saying.  I'm looking at the Northwind database and I don't really see how the line items correspond to each invoice record.  I don't really understand how you can create an invoice and say these items will be listed for this record and another set of items will be listed for the next record.

I'm not trying to make some huge complicated database.  I think it would just be easier for me if I could accomplish my original goal, by taking the results from the select query and importing it into a line item field from the invoice table.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 350 total points
ID: 22844109
There is one Invoice record and in another table, InvoiceDetails, there may be several LineItem records, one for each line item.

Invoices
======
InvoiceNo - primary key, may be autonumber
CustomerID - foreign key from Customers table
InvoiceDate - date time
etc.

InvoiceDetails
==========
DetailID - autonumber
InvoiceNo - foreign key from Invoices - this is where the invoice number gets repeated
DetailDesc
UnitCost
Number
etc.

In Northwind, the two tables are Orders and OrderDetails.  For some strange reason there is no DetailID field.  If you click on the Order field in OrderDetails, and sort it ascending, you will see several records containing the same order number.  Within each group, the order is the Item Number.  You do not need to store that number,  your form/report can be coded to generate it automatically.  If you open the form Orders and print the first record, you will see the three items in that order.  In this case you see the ProductID.    Some companies insist on seeing something called an Item Number, and if that is your case, sobeit.  Do an Inner Join on the invoice number in these two tables and immediately you have all the data needed for your invoice.

This is all part of what is know as a One-Many relationship.  This is fundamental to creating an Invoice (0ne) with several line items (Many).  As a beginner, you would do your self a service to spend several hours/days poring over the various features of Northwind.  It is a sound design, has been with the Access family of releases since virtually day one with very little change.  
0
 

Author Comment

by:paintb4707
ID: 22853715
Thank you.  I figured out how that works and made a separate table for the line items then insert a subform for it.

I have two questions now.

One being... (as of right now) I have tblProducts with SKU and Description fields.  Now I made the Description field of tblLineItems to be a lookup of tblProducts.  SKU being the first column and Description being the second.  I made it so that it stores the Description (column 2) but when I actually go in and choose a product it inserts the SKU and not the Description like it's supposed to.  Why is this?

My second question is... I'm waiting for a more complete list of products to add to tblProducts.  The complete list will include Total KG and Price per 1000.  How can I make it so that when you choose a product in the Description field it populates the appropriate KG and Price?

Thanks in advance
0
 
LVL 44

Expert Comment

by:GRayL
ID: 22882581
Why would you want to store the description field in tblLineItems when it is already in tblProducts?  A no-no in db design.

I think you need another table tblProductsPkgs in which your products are broken down into packages.  Here is where you need the SKU, PgkID, TotKg, and PricePerK
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

861 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