Viewing a calculated query field on a form

[klarae99]

Hello, I am working on an Access 2003 Database for inventory control. I am setting up a form (frmProducts) for the viewing and adding of product information. I have several tables that this information is coming from:

tblProd with fields: item(PK), CatID(FK), List Price and other not fields not related to this question

tblCat with fields: CatID(PK), Category, Tax Rate

I have a query:qryListPr ice with fields tblProd.ListPri ce and tblCat.tax and a calculation field List Price to calculate the price including tax.

I would like to be able to view the list price on my frmProducts. I have created an unbound textbox on the form and made its control source =[qryListPrice]![List Price] but when I view the form the field shows #Name?. I know that I can do the calculation right in the field, but I will use the list price in many of my reports and thought it would be better to have all references to list price calculated from the same place.

Can anyone help me get the calculated value of the field to reflect the query calculation, or is it a better idea to simply do the calculation in the text box? Any and all help and advice is greatly appreciated.

[FishVal]

Hi, klarae.

To make it you need to set Form.RecordSour ce to [qryListPrice], then calculated[List Price] field will appear in ControlSource dropdown. Certainly you need to retrieve to the query all fields bound to the form controls.

Regards,
Fish

P.S. Not sure whether it is a better idea to calculte value in TextBox control. Sometimes yes, sometimes no. In that kind of situation I would prefer to do it via query as SQL performs faster than control calculation.

[klarae99]

Fish,

Thanks for your quick reply, I really appreciate any help you can give me. I have tried your suggestion of changing the Form.Recordsour ce to [qryListPrice] then selecting list price in the control source. When I do this and switch back to form view, the entire form is blank, no fields, no lables, nothing.

Currently all of the other fields are linked to tblProducts. I had tried to make a queary with the information from tblProducts, tblCatigories, tblSubcategorie s, tbl Events and tbl Vendors that included all the fields that I wanted on my form but ran into the error that the queary was too complex.

I then tried to 'nest' a couple of queries to get the same fields and could make that work (sort of) but I had issues creating my comboboxes and I could not get a new record to appear in the form, even when I changed the form to data entery. Which is why I went back to creating the form from the tblProducts, and using the other tables as combobox control sources.

I know that I have read in various threads that it is better to create forms from queries, which is part of the reason I tried in the first place, but I've been working on this database for two weeks now and my boss seems to think it is taking me to long.

Any advice would be greatly appreciated. Thanks so much for your help!

[FishVal]

Hi, klarae99.

I doubt whether you can join all the tables to one query and put it to one form.
Start with some less ambitious task. You had form which was populating [tblProducts], you wanted to add one calculated field. So create a query from only necessary tables joined retrieving all the fields from [tblProducts] and one calculated field you need. Set the form RecordSource to the query and set form controls ControlSource to correspondent fields of the query.

[klarae99]

Thanks so much for your help, I had a complete mind block as to how to create the queary to include all the fields I needed (because the information comes from so many different tables); until I read your reply. I didn't think to create the query on just the information in the products table (with all the ID Foreign Keys to the other tables) and then change the rowsource to view the names instead of the ID#, just like I did on the form based on the table. That makes my life so much easier. Thank You!

I do still have an issue however, when I create the form based on the simplified queary I cannot add a new data, I can only edit the items already in the database. Is there a way I can set it up to do both? I really appreciate your continueing help.

[FishVal]

Well.

You should definitely think how to represent data from many tables. Joining all of them to one query is not a suitable way. It will complicate data managing, open door for errors and you can end upon table joining with non-updatable query just as you have so far.
I strongly suggest you to divide your form into main form and one or many subforms. Post your tables metadata and I will be glad to make you suggestions of how form/subform interface may be organized.

Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[klarae99]

Fish,

I was unsure how to get the MetaData from Access directly so I typed it up based on the fields in my tables, I am not sure I got all the Types 100% correct but here are (all of, I wasn't sure what you would need) my tables in the format you requested.

Table Name = tblProd
Field; Type; IndexInfo
Item; Number; PK
VendorID; Number; FK
Description; Text
Price; Text
CatID; Number; FK
SubCatID; Number; FK
Discontinued; Y/N
Reorder; Number

Table Name = tblVen
Field; Type; IndexInfo
VendorID; AutoNumber; PK
VendorName; Text
VendorCode; Text
Address; Text
Address2; Text
City; Text
State/Country; Text
PostalCode; Text
ContactName; Text
E-mail; Text
Website; Text
Phone; Text

Table Name = tblCat
Field; Type; IndexInfo
CatID; AutoNumber PK
Category; Text
Tax Rate; Number

Table Name = tblSub
Field; Type; IndexInfo
SubID; AutoNumber; PK
CatID; Number; FK
SubCategories; Text

Table Name = tblTrans
Field; Type; IndexInfo
SubProdID; AutoNumber; PK
Date; Date
EventID; Number; FK
Received; Number
Sold; Number
Our Cost; Currency
Discount; Currency
Item; Number FK

Table Name = tblEveCons
Field; Type; IndexInfo
ContactsID; AutoNumber; PK
CompanyName; Text
FirstName; Text
LastName; Text
Address; Text
City; Text
State; Text
Zip; Text
Title; Text
WorkPhone; Text
FaxNumber; Text
Email; Text

Table Name = tblEveInfo
Field; Type; IndexInfo
Event ID; AutoNumber; PK
Name; Text
EventContactID; Number; FK
StartDate; Date
EndDate; Date
Location; Text
StartTime; Time
EndTime; Time

The form that I am working on for data entry and editing currently consists of a form with a subform, they are listed below:

Form Name = frmProd
Table; Field
tblProd; Item
tblProd; VendorID (Rowsource tblven; 2 col, 0;1 width)
tblProd; Description
tblProd; Price
tblProd; CatID (Rowsource tblCat; 3 col. 0;1;0 width)
tblProd; SubCatID (Rowsource qrySub, Requeary on Enter)
tblProd; Discontinued
tblProd; Reorder
unbound; List Price

SubFormName = frmSubProd
Table; Field
tblTrans; SubProdID (not visable)
tblTrans; Date
tblTrans; EventID (Rowsource tblEveInfo 2 col, 0;1 width)
tblTrans; Received
tblTrans; Sold
tblTrans; Our Cost
tblTrans; Discount
tblTrans; Item (not visable)

With your previous post inspireing me to simplify my query I created one that consists of the fields below to base my form on.

Query Name = qryQuick
Table; Field
tblProd; *
qryListPrice; List Price

This queary returns all the products entered into the table along with the list price that I calculated with the List Price Query that we have already discussed briefly. The form that I tried to create using this queary would be identical to the frmProd that I already have but would be based of qryQuick not tblProd. The fields that I have placed so far work fine but there is not the option to add new data. I hope that this is not an overload of information and that it will help you 'see' my database (which I know is not 100% Normalized but right now it will have to do so that I can get it functioning by next week, also I should not ever have to combine the Event and Vendor individuals)

Thank you again for taking the time to look at this, I really appreciate all the help!

[FishVal]

Excellent.

Very nice and comprehensive post. I'll take a close look on it in a hour, when will get home.

[klarae99]

Fish,

I'm glad it is what you wanted. I'll look for your reply tomorrow. Thanks!

[FishVal]

Hi, klarae.

My suggestions will be the following.


Section 1: Tables.

Generally your table structure is good, but not perfect.
Here is the list of changes you should make to the table structure.

• 1. Very impotant (breaking basic db design rules)
  • [tblProd] stores duplicate reference to Category: [CatID] and [SubCatID]. These are already related one to other with [tblSub], so [tblProd].[CatID] is not
    needed as long as it stores duplicate relation of [CatID] and [SubCatID]. Storing duplicate information requires synchronizing mechanism. For example: you need to relate subcategory to another category, for this purpose you change correspondent record in [tblSub], but all [CatID]/[SubCatID] relations in [tblProd] will remain intact, to correct this you'll need to run update query on [tblProd]. The rule "not to store duplicate information" is one of the basic db design rules
• 2. Less important / recomendation (not very critical, but will improve overall db design and will help in maintaining data integrity)
  • [tblVen] contain fields [Address] and [Address2]. Why not to move them to separate table (let us say [tblVenAddr])? [tblVen] 1->many [tblVenAddr]
  • [tblVen] and [tblEveCons] contain fields [City] and [State]. I see here 1->many hierarchy [State]->[City]->([tblEveCons], [tblVen])
  • [tblEveInfo] stores Data and Time in separate fields. This actually an overkill as Date/Time field stores both. Though I would like to store Date/Time in same field it is not critical at all, just an issue of programming style.

Section 2: Interface.

NB: statements like "form based on [tblProd]" mean form which controls are bound to [tblProd] fields only, form recordsource may be [tblProd] or any table join query returning [tblProd] fields with additional calculated fields

At the bottom of your tables structure hierarchy you have [tblTrans] which is in many-1 relationship with [tblProd] and [tblEveInfo] implementing many-many relationship between them. Here we have the most tricky part of interface: subform based on [tblTrans] should have two main forms - one based on [tblProd] and one on [tblEveInfo]. Though Access forms themselfs don't support such kind of linking this may be easily done for subforms set with some code.

So, I propose the following:

Option 1.

The main form is just a background where the following subforms are placed:

• form based on [tblTrans] (let us say [frmTrans])
• form based on [tblProd] (let us say [frmProd]). [tblProd] fields [Vendor] and [SubCat] are bound to comboboxes. [frmProd] has an additional unbound combobox with RowSource=tblCa t, it is used to filter RowSource of [SubCat] combobox on category modification and is being populated with relevant category based on existing [tblProd].[SubCat] value.
  NB: comboboxes cascade will not work in datasheet or continious form view unless table structure will not be adjusted correspondently .
  There are several special methods to input via combobox data not existing in its RowSource table simaltaneously appending it to RowSource table, so don't worry so far about entering new Vendor or/and Category/Subcategory.
• you have two options to implement interface on [tblEveCons]-[tblEveInfo]-[tblTrans] relation (let us say this will be [frmEvent])
  • form based on [tblEveCons]-[tblEveInfo] join query
  • form based on [tblEveCons] with subform based on [tblEveInfo]

These 3 forms are placed on the main background form (you may use tab control to save space). A simple code will populate [frmTrans] with records related to current [frmProd] and [frmEvent] record.

Option 2.

Unlike the above symmetrical interface you may place [frmTrans] as subform on [frmProd]. [frmTrans] contains combobox bound to [tblTrans].[Item]. [frmEvent] will be opened when you'll need to enter to combobox new info not existing in [tblEveInfo] and/or [tblEveCons].

Option 3.

Just the same as option 2 where [frmTrans] and [frmEvent] have changed places.

Let me know whether it all makes you sense and whether you have additional questions/wishes/suggestions on the design before will start design itself.

Regards,
Fish