subform -- table update

[benjee]

Hey,

i have two questions: -

1. My subform uses combo boxes linked to indepedent tables to show values. These values once saved update fields in tableA. However when i lookup certian values in the tableA it displays primary/foreign keys.

As im eventually going to create a report on the values in tableA, how can i store descriptive names i.e fldarDesc rather than the primary keys in the table.
The fields in the 'lookup' combo box are (made up) :-

fldcarId
fldcarDesc
fldcarCost

2. Part of the same subform contains a subtotal text box that uses an unbound control to calculate several values in comboboxes etc i.e. -

=(Val(Nz([car1]))+Val(Nz([car2))*(Val(Nz([car3].Column(2)))+Val(Nz([lcar4].Column(2)))

Now this code occupies the Control Source field in properties. My proplem is i want to set the Control Source to SubTotal, so the SubTotal field in TableA is updated. However theres a conflict.
Is there a way of keeping the unbound control and saving the SubTotal to tableA

I hope you get an idea of what im on about

 

[directormac]

Oh, sweet clarity...

Not sure I do know for certain what you're on about, but let's see if we can find out.

Number 1 sounds like you have a combo box that gets its info (rowsource) from a table other than the table ("table A") which is the datasource for the form itself, yes? And you store the primary key of the selected item as a foreign key in a field in the main table. So, you have tables something like:

tblFoo
autoFooKey (autonumber, PK)
txtFooDescription (text)
fkFooWidgetType (long)

tblWidgets
autoWidgetKey (autonumber, PK)
txtWidgetDescription


And on frmFoo you have cboWidgetType which has tblWidgets as its rowsource, with the bound column being stored in the current tblFoo.fkWidgetType field.

The problem is, when you select something in cboWidgetType, you see the value of tblWidgets.autoWidgetKey when what you want to see is the value of txtWidgetDescription.

Is this an accurate description of the setup and problem? If so, all you need to do is adjust the properties of the combo box so that it displays 2 columns, but the size of the first (bound) column is 0" so it doesn't get seen. If you created this with the combobox wizard, it should have set this up for you.

DON'T store that description (txtWidgetDescription) in your tblFoo records. Continue storing the key, and when you get to the report you can use a query or other method to retrieve the descriptive name.

Have I got what you're on about?

--Hopeful Mac

 

[benjee]

Hello yeah i understand what your saying, but let me shed some more light on my situation -- i read my first message back and it did sound disconcertingly vague, i apologse -- ok back to it.

Using a different example to yours, i have a form -- frmTest, this form comprises of a subform -- fsubTester.

fsubTester contains cBox1, cBox2, txtQuantity & txtSubTotal. The values from the two CBox's, and the txtQuantity produce a subtotal.

Values from the combo boxes come from tables,

cBox1 from tblContractor
cBox2 from tblSurveyor

tblContractor
fldContractorID
fldContractorType
fldContractorCost

tblSurveyor
fldSurveyorID
fldSurveyorType
fldSurveyorCost

Both the combo boxes display the ID & the Cost respectively from each table.

Now my problem is saving these values after i select them from fsubTester. I have a separate table in which these values are to be saved -- tblQuote comprising the following fields: -

fldQuoteID
fldContractorType
fldSurveyorType
fldSubTotal

I must just point out that these tables are not linked directly in the relationship window. Does this matter? I expect this is the wrong way of doing it.

Anyway i can get the ContractorType & SurveyorType to save fine in tblQuote but only as a Primary Key?

HTH or not

 

[Pat Hartman]

1. Contractor and Surveyor should be 1 table instead of 2. I will refer to this combined table as Worker for lack of a better word. The Worker table should have a field (WorkerType) that indicates whether someone is a Contractor or a Surveyor (or possibly something else in the future) and this field would be used as criteria for the queries used to populate combo boxes.
2. You need to store the ID field rather than the Type field. Then subsequent forms or reports can be based on a query that joins the Quote back to the the Worker table.
3. The quote table should be split into two tables. Once you understand that contractor and surveyor are simply two instances of the same entity, you can see that quote has a 1-to-many relationship with this entity.
4. tblQuoteWorker table should include the Cost field from the Worker. Its primary key should be fldQuoteID and fldWorkerType. This will restrict the table so that it can only hold 1 contractor and 1 surveyor (unless your business rules actually say otherwise). The cost field is copied from the Worker table at the time this row is created. That "fixes" the cost at a point in time. Otherwise when you produce reports, they will include invalid data if the cost of something has changed since the quote was first created.
5. Although the lack of relationships has nothing to do with your present problem, it can cause data integrity problems. RI should ALWAYS be defined and enforced. Open the relationships window and draw the correct relationships. Check the box to enforce referential integrity. If Access won't let you, it is because you already have bad data in your tables and you will need to correct it before proceeding. The relationship between tblQuote and tblQuoteWorker should also specify Cascade delete. That way if a quote is deleted, the related worker records will also be deleted.

 

[directormac]

My current sig says it all...

Pat's answer is, as usual, concise and on-target. If you need a hand with the "how" of getting the info from/to the right places after you implement Pat's corrections, just shout out.

--Pat's-Groupie Mac

 

[Pat Hartman]

I am thrilled to have a groupie. It is a bit like having a gallery (group of watchers) when I'm playing bridge. Although usually when I'm playing bridge, the gallery is behind my partner, watching him since he is the star.

 

[directormac]

Maybe so, but I bet you kick his a$$ at normalizing tables...

"Bridges? We don' need no steenkin' bridges..."

--Number-One-Fan Mac

 

[benjee]

Pat, as directormac pointed out that was a clear and concise explaination -- thankyou.

I can relate it to my example.

If you look at my attachment the Id's of the combo boxes appear in the tblEstimate now -- great.

The attachment shows my 'real' project that i am developing. I hope you don't mind the format, its a zipped MS Word document.

However you will properly appreciate that i cannot combine the comboBox 'source' tables, because they use the cascading combo box structure, and unlike surveyor and estimator which are essentially the same thing, my combo boxes don't relate in that kind of way.

Now the part of MS Access i am unfamilar with is reports, and i need to create a report showing the results from each estimateNo (a quote if you will).

How do i go about doing this?

The other issue i have is the SubTotal is always £0.00 in the table, despite having saved all records in my subform fsubEstiamtes. Is this because the figure is created dynamically and should not be stored as my housemate seems adamant about!

At this point can i just apologse for all these examples i have been providing. If i used my 'real' attached exmple in the first place i would of saved some hassle