Design Question

[Microsoft]

Access 2007

I am attempting to write an estimating program - all good so far

I have my Item Header form linked to the subform Item Details

many records can appear on the Item Details form to make up the Item

eg Item = Fit and Supply Door

Item Header would be a sum of the records appearing on Item Details

Item Details has 2 records
Supply Door £200
Carpenter £50

This all works fine however what I would like to be able to do is set up a
Bill of Materials table for standard items like the above that can be
inserted (copied) in to a new estimate rather than having to create the item
each time whilst at the same time being able to create the the bespoke
items.

The inserted bill of material items need to be able to be edited once in the
new estimate - the edits only relating to the current estimate not the table
that the info has been inserted/copied from.

What would be the best way to do this?

Thanks

A

 

[Klatuu]

This will take some additional tables.
Although you may already have some of these tables defined, I will include
all the tables and what should be in them.

Estimate Table

This table should have all the information about an estimate, but should not
carry any information about any items.

Items Table

This table should have generic information about each item, but should not
have any information about any estimate.

Estimate Items Table

This table should contain information about items specific to an estimate.
It should have fields to carry information about the items for a specific
estimate. That is, those items where you need to modify data for the
specific estimate. It should be relates as many Items for one Estimate.
That mean you need to have foreign key fields to relate the item to the
estimate and the item to the Items table.

 

[Microsoft]

Thanks for this I'm now ok with the table structure .

I'm creating my estimate - what is the best way to insert pre-defined items
eg
Item = Supply and Fit Door
Details of this item (different table) Door $200
Carpenter £50

rather than having to build each item every time

i.e I could add a new item to the estimate each time called Supply and Fit
Door and add the detail lines each time Door and Carpenter

or what I would prefer to do is be able to insert the item Supply and Fit
Door which will automatically bring through the item details Door and
Carpenter.

Thanks for your help so far

A

 

[Klatuu]

It is too involved to give you detail, but I would suggest you build your
form so that the estimate is in the main form and you have a subform for the
items. In the subform you could use a combo box to allow the user to select
an item. The row source for the combo should be based on the item table

When a user selects an item, you can get the data from the item table to
populate fields in the subform.

 

[Microsoft]

Thanks - I don't want the user to be restricted to items that appear in the
combobox.
And also the pre-defined items (from the combobox) will have child records

Supp Door and Fit consists of Door + Carpenter

what is the best way to insert these into the new estimate?

Thanks
A

 

[Klatuu]

There is a way to include NEW as an option in a combo box. Here is a link to
a site that explains how that is done:

http://www.mvps.org/access/forms/frm0015.htm

I did not see where you had children to the item. If you do, then you will
need a subform to the item detail subform to add those entities as well.

 

[Mark Andrews]

You probably need something like this:

Private Sub cmbProduct_AfterUpdate()
On Error GoTo Err_cmbProduct_AfterUpdate
Me.txtDescription.Value = Me.cmbProduct.Column(2)
Me.txtRate.Value = Me.cmbProduct.Column(3)

Err_cmbProduct_AfterUpdate:

End Sub

In this example: after a product is choosen from a dropdown the descrition
and rate and filled in based on hidden columns in the product dropdown.
This is on my InvoiceLine subform, for each line you pick a product and it
fills in the description and rate, which then you can overwrite if you want
etc....

Take a look at my CRM template for more details on look/feel.

HTH,
Mark
RPT Software
http://www.rptsoftware.com

 

[Klatuu]

In VBA it is not necessary to specify the Value property. It is the default
returned when no other property is specified. Additionally, it is faster if
you use the With construct when referring to several properties of the same
object. I would code it as:

With Me
.txtDescription = .cmbProduct.Column(2)
.txtRate = .cmbProduct.Column(3)
End With

 

[Mark Andrews]

yes shaving milliseconds couldn't hurt
I usually use the 2 lines instead of 4 (just my preference) even though it
is wrong.
I usually don't specify .Value (not sure why I did here?)
I guess my naming conventions were ok?

 

[Klatuu]

naming conventions are just fine. Was not meaning to criticize, just adding
additional info.