Manipulate Data in Subform

[grupyville]

Im a newbie when it comes to programming but have been using Google and as a result have been lurking on these boards, without an account, until today. Im stuck and figured this would be the best place to ask for help.

I used the Northwind database that accompanies Access as a platform and adjusted the project to meet my needs. I deleted all the data that was in the database and restructured the tables and forms.

I'm stuck at the creating an invoice portion.

I have a master form ORDERS and child form ORDERS DETAIL that are linked by ORDERS ID. When this form is loaded a table-making query runs that selects basically all the data (sale date, product, cost, additional cost etc) involved. This essentially captures all sales into a table called InvoiceData.

On the ORDERS form, I have two unbound combo boxes. One simply selects the billing company information and puts its data into corresponding text boxes. The other selects the Customer and at the same time, it launches a query that filters the data in InvoiceData table to display only the sales for that particular customer.

Once this is done, I have two text boxes that basically re-filter the data in InvoiceData table so only sales within a 2 week period are displayed or whatever dates the user enters.

Within the form the ORDERS DETAILS subform remains empty. I have created the subform based upon the InvoiceData table.

I really dont know what Im doing wrong as the three queries run accordingly outside of the forms, so I know my query filters work properly as InvoiceData ends up containing the proper data but on the form itself, I cant get it do change or display after each query runs.




I may not be using proper programming structure but Im basically trying to help my dad save a ton of time from doing paper work by using a program.

I would be more than happy to attach the file, as Im sure there might be a bunch of other errors on the invoice portion but I would greatly appreciate any insight on how I can basically get the subform to show the data accordingly and change itself whenever a query is being executed.

Thanks for anyone that takes the time to help!

 

[PNGBill]

The SubForm has record source InvoiceData.

Change the record source to the query that works and you should see your data. May well be read Only.

 

[grupyville]

The SubForm has record source InvoiceData.

Change the record source to the query that works and you should see your data. May well be read Only.

The data is read-only and I dont mind this at all as I only want to display the invoice details and the calculations for the particular invoice.

What I was hoping to achieve was the subform to display InvoiceData (table) and have the two queries change the data on the subform after each action.

For example, once you select the customer, the subform will show all products purchased by that customer. When the next parameter (date) is entered only that information is then viewed.

This is what I cant accomplish currently and move forward. I have been stuck at this for countless days so far.

 

[PNGBill]

If you can do this with queries, then have the queries as the data source for the form / subform.

A form or subform can only have one Record Source (be it a query or table)
You can have many subforms and each can have it's own Record source and these can be linked by master Child fields so the data reflects any changes. May require requery etc.

You can have Unbound Text Box Controls on any of your forms and these can display what ever data you like.
These can display data that reflects the data shown on any of your Open Forms.
To do this you can use expressions and functions. DLookup is one.

Get your form working as best you can and then ask for help to have an Unbound Text Box control to display certain data. When resolved, you should be able to replicate this to any other such controls.

Some of these controls can be calaculated values using the Bound and Unbound controls already on your open forms.

 

[grupyville]

I wanted to say thank-you for your replies as its most appreciated.

I cleaned up my issue and set the subform data source to one single query, which now combines the work of two previous queries that I had used before I created my initial post.

I do think there might be a relationship issue with my tables, which may be hindering the invoice portion. I tried again to use the Orders form but for whatever reason the subform isnt displaying my data as I would have hoped.

If I launch the subform on its own, it starts the query and does display the data and calculations outside of the parent form fine.

I would be ever grateful if you or anyone else would be so kind to look at my attached mdb file and provide some insight.

 

[PNGBill]

which form is the main form you are having problems with??

 

[PNGBill]

Your Form Orders has only one Control called CompanyName.

One of your SubForms to this Customer Orders Subform1 has Master Child link as CustomerID for both.

How can this be when the main form has no control called CustomerID.

Include CustomerID in the main form and maybe the master child link will have a chance of working.

 

[PNGBill]

Just noticed. not even a reference to CustomerID in the subform.

A Link requires the two Data fields to be the same. Don't need the same name but must have the same data.

One form can have CustomerID and the other can have CustomerNumber but the data in each of the records must be "12678" or whatever the customer number is.

Same with any link be it text, Numeric or even date. You have to have Apples joined. Not Apples joining with Oranges.

Your main form has Customer name and the subform has OrderID (twice) and Total price (twice)

 

[PNGBill]

Relationships....

Why Customer Table and Company Table - are they different ?Surely a customer is just that, be it a person or a company. One table only unless you have a good story to tell.

Customer joined to Orders one to many - correct.

Orders Table - just CustomerID. Remove Company, unless good story above
Add OrderDate, remove TotalPrice, add DeliveryAdd (maybe), add OrderStatus (Order, Invoice)

Where is your OrderDetails table?? this is joined to the Order table. One Order to many orderdetails.
Order details carry the line items in your order.
OrderID, ProductID, OrderQty, OrderPrice.

Products Table. Joined One Product to many Orderdetails.
Remove CustomerID, Employee, Address, City, Province, PostalCode, and all the other fields
Add ProductPrice, SupplierID (maybe), SellingQty, StandardPack plus any other Product fields.

Employees Table can be linked to Orders table. Add EmployeeID to Orders Table.
Join is one Employee to many Orders.

Change table names to something like TblCustomers, TblOrders etc.
Queries will be QryOrders, QryCutomers etc.
FrmOrders, FrmCustomers etc.
This will assist when you look at database objects to clearly see a difference otherwise the name Orders is used how many times??

 

[grupyville]

which form is the main form you are having problems with??

The main form Im having issues with is Orders and Orders Subform. The other forms, all work fine, and the Customer Subform doesnt work now as there are no orders made.

 

[grupyville]

Your Form Orders has only one Control called CompanyName.

One of your SubForms to this Customer Orders Subform1 has Master Child link as CustomerID for both.

How can this be when the main form has no control called CustomerID.

Include CustomerID in the main form and maybe the master child link will have a chance of working.

I havent worked on this form yet, and its not the one giving me an issue. As to the structure behind this, I dont know as it was created by the sample database, Northwind and not me.

 

[grupyville]

Relationships....

Why Customer Table and Company Table - are they different ?Surely a customer is just that, be it a person or a company. One table only unless you have a good story to tell.

He has 2 different company names and with 2 different mailing addresses and as a result 4 different options are required. I dont understand but it is the situation he is dealing with - else it would have saved me so much time.

Orders Table - just CustomerID. Remove Company, unless good story above
Add OrderDate, remove TotalPrice, add DeliveryAdd (maybe), add OrderStatus (Order, Invoice)

Where is your OrderDetails table?? this is joined to the Order table. One Order to many orderdetails.
Order details carry the line items in your order.
OrderID, ProductID, OrderQty, OrderPrice.

I figured it would be good to see which company does what business so thats why Company was added to Orders table. I have OrderDate associated to Product table - PickupDate given nature of his business. He needs to know the date of the dispatch which is related to the product. The price is associated to the customer, which is where the product is being delivered too, the only real price associated to the product is additional costs that might be applied, otherwise its the same price to the customer, regardless of the product being delivered. Its just the nature of his business, otherwise the standard business module would have worked as per your reply.

Products Table. Joined One Product to many Orderdetails.
Remove CustomerID, Employee, Address, City, Province, PostalCode, and all the other fields
Add ProductPrice, SupplierID (maybe), SellingQty, StandardPack plus any other Product fields.

This would work for basically all other businesses but his...being in his field these wouldnt work, IMO.

Employees Table can be linked to Orders table. Add EmployeeID to Orders Table.
Join is one Employee to many Orders.

I had this entered before but he doesnt need to know which driver delivers the product as long as the product is there. the final invoice has no bearing on this but within the dispatch table, a record of the employee is kept.

Change table names to something like TblCustomers, TblOrders etc.
Queries will be QryOrders, QryCutomers etc.
FrmOrders, FrmCustomers etc.
This will assist when you look at database objects to clearly see a difference otherwise the name Orders is used how many times??

I plan to do this once I get the data working - this was going to be my final step. I simply changed the names quickly within the sample database for now as temp names. Its more work but figured most ppl would be familiar with the sample database and thats why I kept the names they used.



Thanks for looking at the attachment as its definitely appreciated. So, are your replies and inquiries. I will try my best to reply back ASAP and would love if you could help me further with the Orders and Orders Subform.

Thanks again sir!

 

[PNGBill]

Please understand it is not the job of a Table to supply you data for your reports and forms. They may from time to time be able to do this but that is more of a chance event then a normal expectation. Apart from SubForms for Data Entry which are specific to a limited number of table fields.

You will get your data for reports (Invoices etc) from Queries. This is why it is fundamentally wrong to include fields from other tables in a table, unless it is the Link Field.

OrderTable will have CustomerID as the Link Only.

No way can you explain Order Date being on Product Table.
Why have an Order Table and a Product Table if you are going to have the fields repeated?

Clear your head. Start to think about how you can reduce the number of fields in your tables so that apart from One Link field there are No Repeats.
All fields should be directly related to the table purpose.
Customer Table will hold the Exclusive data of a Customer. This would be Name, Address and any other details Your Customers require.
All Customer Tables will have the first three fields the same. CustomerID, CustomerName and CustomerAdd - in one form or the other and then it may change for different applications.

Products Table again Must have ProductID, ProductName, ProductPrice and then may change for different applications but No Way can it have CustomerID IF you also have an Orders Table.
IF there is only ever One Customer to One Product, then why have a Customer and a Product Table. Just add the Product / Service as a field to the Customer Table.
But... If the Product was ever repeated for any other Customer, then you should have a Products Table AND IF A Customer Ever used More Then One Product, then again, you should have a Products Table.

What ever you may wish to have as a record eg Total Price, this can be obtained by way of Query and should never just be added to a tables field just in case.

You didn't explain the difference between Company and Customer.

You don't have to but if you want assistance and if something doesn't make sence, then you will get more help by explaining same.
You can sometimes combine tables that are similar by just adding One Field to the table that Differentiates the data.
Add a Field PrivateComp and the Enter either "Private" or "Company" for each record in this table.
You can then use two queries. One will select all records with Private and the other will select all records with Company. You then use these Queries for your respective Forms if there is a need.
Forms can also be used to filter such Data.
You can have a Company Form with a button on it to list either Private or Company customers as you click away.

 

[PNGBill]

The main form Im having issues with is Orders and Orders Subform. The other forms, all work fine, and the Customer Subform doesnt work now as there are no orders made.

No sample data supplied - this doesn't make it easier to assist.

Please add non sensitive data and re attach.

Compact and repair, if not already done, first.

 

[PNGBill]

The main form Im having issues with is Orders and Orders Subform. The other forms, all work fine, and the Customer Subform doesnt work now as there are no orders made.

Your Master Child Link is OrderID but there is no OrderID filed in your Orders Subform.
finalInvoiceSub needs to be amended to get OrderID as a field included and then you will have a chance of getting the subform to populate.

 

[grupy]

Im going to try your suggestions from above and fix the database and its structure and hopefully post in a few days again with my results.

 

[grupyville]

You didn't explain the difference between Company and Customer.

You don't have to but if you want assistance and if something doesn't make sence, then you will get more help by explaining same.

Ill provide more details in regards to the business, which may help in outlining my rationale for the tables I have choosen.

The business is a Funeral Home Service company. They basically deliver bodies to funeral homes, whether it be from an accident, home, hospital or whereever. This is why the "product" being the individual deceased, will only go to a single funeral home, which will be the "customer". The customer as a result can have multiple bodies delivered but depending upon the delivery the prices charged to the funeral home will vary. For example, if the body is delivered from a house or crime scene, the price the funeral home normally charges will be increased, therefore there is an additional cost associated to that particular product. For the most part, there is a set standard charge applied for each product, based upon the contract negotiated with the customer, funeral home.

As for the company table, its essentially a table that consists of two different company names with the same billing addresses. But since the original company is being bought, the mailing address has to be updated to the new address for both company names BUT we're forced to use the old mailing address while it goes through a transition period of one year. This is why there are 4 entries for the company table, two company names using 2 different mailing addresses.

From my view point, I think the tables in terms of populating the product works, the assigning of the product to a customer, that works but I just now need to get an automated number applied to the customer in an invoice and save the products for that invoice and that will allow me to create a report.

this is where im getting stuck. is there a better solution of creating an invoice using the data structure i have, albeit it may not be of standard but provided it captures the proper data, and creates an invoice -- it doesnt really matter to us if it doesnt follow exact standard methods or best practices...

anyhow, i look forward to your or anyone elses replies.

 

[PNGBill]

You should have a ProductTable and each Delivery Option is a Product with it's relevant Price. Their may be only a limited number of Products but still by having this table it allows for options to change and as inflation continues, prices to be updated easily.
A bit of head scratching and the different delivery choices can be identified and priced and added to as a new option is identified.
Even think about a new section of the database that Builds your Sku Prices. ie, how does your business calculate a service price? Crime Scene etc. Break this down to have the components make up the final Product price - stage two.
You refer to delivery but as delivery is your Product then it really isn't Delivery anymore it is just a SKU item like any other business.

You have one Customer - can you really be sure in 5 years time there will still only be one customer?. What if one is phasing out and another is growing.
You should have a CustomerTable.
The customer is changing hands - owners.Does this mean the Registered Company Name is changing?
Why not have two companies. (not 4) The old company and the new company.
XYZ Funeral Home and XYZ Funeral Home 2010. They will have the same delivery address but different Postal Addresses.
They can have all details, even the Name exactly the same except for Postal and Primary Key. This way, your Invoices and Reports won't really show any difference unless you include Postal Address.
You just Edit the Mailing Address on the day it changes.
You can include the mailing address as a field in the Sales Records if it is important to keep a history of which address applied at the time but normally not an issue.

Your explanation is clear but nothing proves your business is really any different to any other and can be a reason to deviate from a normal database.

We have a Finance Company and initially every Loan was calculated out as requested. I spent one day with excel and calculated the most popular options and then entered these into a table.
A query and Report produces a Loan Schedule (Price List)
And when we make a sale (new application) we just select a loan option from the loans table.
Saves us hours of work each day.

Your delivery service business would be a very simplified version of our loan business in that your delivery options may well be less then our loan options but also, our loan options take into account many more variables (fields) - I guess.

When you buy a Special Meal Pack from McDonald's their sytem knows that the pack consists of Fry's, Burger etc and records the componets sold while you just see one item on your receipt.
On the other hand, when your car is serviced you may get a long list of Items on your invoice including parts, labour, rags, misc (always). They often don't Package the invoice but show the breakup.
Your Delivery charge can either be like McDonald's Package Special or the Service Centre itemised invoice.
With the package option, you can have the database do this (stage two) or just use excel and then make the SKU (Product) from the excel result.

Trust this long winded story assists and I repeat, your business model doesn't explain the need to break the database rules.