Question in Access 2007 on Forms and SubForms

[DataGuy0811]

Hi Everybody,

I have dveloped an Access 2007 database that will allow users to order products. I have two tables that will hold data. They are: Product and Product Orders.
The Product table will hold the Code, Product, Description, Price, and Available Units for Sale. I have linked these two tables together by using a primary key on the Code field.
I have created this link through a select query. This select query called productOrdersRec will hold all placed orders by the user. I have created an entry level form that will ask the user for a Vendor Name and Store No. Once these two pieces of data are entered in the textboxes, the Order Cases button is selected which opens the InvoiceNewSubform which hold two subforms: The ProductOrdersRec Subform and the PullByStoresTS-VAP Subform1. These two subforms I have Link Master Fields and Link Child fields using the MyUserID which is populated based on who is logged in on the PC. The subforms are loading with data as they are suppose to, but we need the Vendor and StoreNo to save with each order. The vendor and StoreNo are coming into the ProductsOrdersRec Subform, but before we save the record we need to have the Vendor and Store No to save somewhere so we will know what orders we took for which vendor and Store No. For example we have entered ALLStateCompany and Store 006. This vendor has ordered A6005, A6001, B6008, and C6789. We need for this information to store somewhere in a table so we can always have up to date information on what was ordered by each vendor and the store No.

How can I do this? What kind of wuery would I need to do this? I am new to Access 2007.

dataguy0811

 

[SkipVought]

hi,

In Excel, Data > Get External Data > From Other Source > From Microsoft Query...

UNCHECK Use Query Wizard

Select MS Access Database* and drill down to your db

Add you tables and JOIN on the keys. populate the QBE grid and ! Execute the query. Add whatever criteria etc.

File > Return data to Microsoft Excel

Select the TOP LEFT cell for the resultset and click OK


All you need do now is RIght-Click REFRESH to get a new set of data. You can also configure to refresh on open, for instance.

Format columns as resuired (a ONE TIME task)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DataGuy0811]

Skip,

I have never used this before. I was unaware you could do this.

How do I send you a copy of my access database? I see attach, but do not know what to put in there to send it.

New to all of this.

 

[SkipVought]

We were all new at some point.

Did you follow the steps?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DataGuy0811]

Skip, Yes I followed the steps.

This would work if I had all of my data in the tables. I have a form coming in where the user adds a Vendor Name and Store Number. For example, User has added AllStatebeverage to the Vendor name and Store Number 5. The Licensee name AllStateBeverage and Store Number 5 comes into the subform ready for orders. User enters A6001 with a Qty 5, All fields of the record populate, but when I save the record, the Licensee Added and Store Number is not saving to the Product Orders table. I need a way to capture the Licensee name and Store Number becuase it is being lost when the record is saved and added to the database. We were told to setup the database where the user enters the Licensee name and Store Number in one time only. i can see their rationale for this becuase a user might have 50 ordres on AllStateBeverage and Store Number 5. To keep the 50 records together with Licensee name and Store 5, the user would have to data entry key the Licensee name 50 times and the Store number 50 times. We have the Licensee name and Store Number coming on on the subform, but we are losing the Licensee Name and Store Number when the record is asaved and added. the Licensee name and STore Number is not writing out to the table.

That is my problem. I need a away to capture this data so that it is not lost. Becuase I might want to quyery the tables for data in a report

 

[SkipVought]

So the user adds something, from where?

Do you understand you database structure?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DataGuy0811]

Skip,

I am using a Main Form and Subform. I am not moving the Licensee Name and Store Number each time with a sale by one code or a group of sales by several codes. Let me explain, how they want the database to work as a Order Mechanism. The user starts in with the Initual Order Form, the user types their Licensee Name which we have named Vendor, the user also types in the Store Number that will go with a sale by code or group of sales by codes. We can have one order for a code or we can have infinite amount of sales by several codes. We could have 50 orders for Licensee Name, "ALLBEVERAGECOMPANY" and "Store Number 23" for example.

ANyhow once the user enters in the Licensee Name and Store Number in the Initual Order Form, the Order Cases button is selected. The Intual Order Form remains open and another form loads called the InvoiceNewSubform which has a Main Form called "Product Orders Rec Subform. The Licensee Name and Store Number comes into the Products Order Rec subform automatically and the two field names populate the fields in the Product Orders Rec Subform until all orders the user wants to make is completed. This subform calculates the Total of the sale and Available balance remaining by each code that is ordered. When the user finishes entering sales by codes for each Licensee Name and Store Number, the Save Records button is selected. The subform called the PullByStoresTS-Vap subform1 loads with the sales that have been entered in the ProductOrdersRec Subform above. However, the Licensee Name and STore Number does not get loaded here because it is lost.

The problem we are having is that the Licensee Name and Store Number for each sale by one code or a group of sales by several codes is not saving with each record. We are losing the Licensee Name and Store Number because it never appends or saves to the Product Order Table which is where we want it to be. The Product Orders table should contain and hold all sales by one code or a group of sales by several codes. For example, we need for the Licensee name to be listed four times for "four' sales by different codes along with the Store Number for each. There are fields for Licensee Name and Store Number in the Product Orders Table, but the Licensee name and STore Number is not saving to it.

When we complete an order the field Completed is marked with a check mark indicating that the sale has completed in the Product Orders table, the Licensee name and Store Number is being lost. We need these two field names to store in the Product Orders Table so that reports can be made using the table with Parameter Queries.

I hope this explains more what we are trying to do. The Licensee name and Store Number is being moved to the subforms each time, but we are losing the information on these two field names when the Saved Records button and Complete button is selected.

The Main Form has two subforms which have link Master fields and Link Child fields set for MyUserID which is the person signed in to the PC.

 

[SkipVought]

You're in Excel. No Form & Subform!

You must join the two tables. Talk to me about the STRUCTURE of your two tables.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Please post your Select Query.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DataGuy0811]

Skip,

What I am trying to tell you is this. The Product Orders table in Access will not contain all of the data that I need. I can bring into Excel the tables from Access like you said do following instructions, but the tables you are adding is not going to have all of the data we need. The Licensee Name and STore no is missing with the orders. When an order is being place, the Licensee Name and Store No in Access now is not being posted to the Product Orders Table. I do not want to even get Excel involved with this. I have my select query already setup in Access with the two tables Product and Product Orders with the primary key as Code. I need to somehow in Access have an append query done some how that will append the Licensee name and Store No to the Product Orders. We have all of our data coming in right in the two subforms of the Main Form. The applicatiln is processing orders and doing all of my totals right. We have accomplished this objective. I just need to go back and view the table, Product Orders and have the Licensee name and STore No that was input with each order be there with the order. Am I making sense. There is no problem with my data structure. You can't do in Excel without having all of your data.

dataguy0811

 

[SkipVought]

You some user is adding data to the report that is not in any table and never gets added to a table?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DataGuy0811]

No, Skip.

The application is taking orders, but the user wants to see the Licensee Name and Store Number that he or she has added to the subform each time. The user only has to type in a code and Qty in the Product Orders Rec SubForm and all of our data is here, even the Licensee Name and Store No comes in from the Initual Order Form, but when the data is saved, the Licensee Name and Store No is not being saved. We are losing these pieces of data for the order when the user selects the Save Orders event button. The Licenseename and Store No is coming in from another form called the Initual Order Form automtaically on the Product Orders Rec Subform.

The user is entering the Licensee Name and Store No in the first form called the Initual Order Form and the Order Cases event button is selected, the Licensee Name and Store No automtaically shows in the Product Orders Rec subform on the InvoiceNewForm. The InvoiceNewForm contains two subforms which are linked with LinkMaster and Link Child fields with myUSerID. The reason we have used myUSerID, is that we want only the person who is logged in to see their data being put in and not 5 or 6 other peooples data at the same time. You have data going to just one table, the Product Orders Table. So the Product Orders table needs to have every Licensee Name and Store No a user has added for each order placed. I do not know how to tell you this any simpler.

We need a way to do this as orders are saved. The Licensee Name and SToreNo is coming into the fields on the subform like this: We are referencing another form's textbox fields like this:

=[Forms]![Initual Order form]![Vendor] This is the control source on the Products Orders Rec subform inside the InvoiceOrder form. This data is showing as the Licensee Name.
=[Forms]![Initual Order form]![StoreNo] This is the control source on the Products Orders Rec subform inside the InvoiceOrder form. This data is showing as the Store Number.

 

[DataGuy0811]

Hi Everybody,

I have attached a copy of my Access 2007 database, Call TradeShowNew When the user selects The Vendor/Store Number with the mouse and then selects the Product/Price/QtyAvailable, I need for the selected Row to turn "Red" when the QtyAvailable to be sold is "0". When the QtyAvailable is "0" this means there are no products available in inventory to sell to the user. I also want the Row selected on the Product/Price/QtyAvailable to turn "Yellow" when the QtyAvailable is > 0 and < 12. This indicates to the user that the product avilable for sale is now 11 or less and no products can be ordered on this code. How can I do this. I have the database coloring the whole background when these two scenarios happen.

I need only for a particular row to turn Red or Yellow based on the Qty Available and not the entire list of product/Price/QtyAvailable.

I almost have this database done. But I have hit a snag on this issue.

Any help is very much appreciated.

DataGuy0811

 

[SkipVought]

@DataGuy0811,

No one but YOU has access to your C drive.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]