Table with variables

[e51lrrp]

I am fairly new to Access and I have a complicated query that I am using for order processing. Basically I have a field [BaseStyle] and each basestyle has a prices associated with it. What I want to do is when a person selects from the drop down table the next field [BasePrice] and the [AssemblyParatsPice] will be also filled in on the form. The three fields associated with this one table are [BaseStyle], [BasePrice] and [AssemblyPrice]. The BasePrice table:

BaseStyle	BasePrice	AssemblyPartsPrice
Single hole-wide	$1.00	$1.35
Single hole-round	$1.00	0.8
2 hole base-wide	$1.50	$1.50
2 hole base-round	$1.50	0.85
3 hole base-wide	$2.00	$1.50
3 hole base-round	$2.00	$1.00
2 column	$4.00	$1.70
3 post	$6.50	$3.25
4 post	$6.75	$4.50

Not even sure where to start. I am trying to automate my son's business and he builds trophy's upon requests. After all the fields are filled in I will be calculating all the prices to come up with total cost of the trophy.

If anyone can help me I sure would appreciate it. I can attached the whole spread sheet if if would be helpful.
Thanks so much
s

[ssanfu]

This is called "Auto Fill".

Here is a link to a youtube video
http://www.youtube.com/watch?v=rSPS24EfijI

and an article that will walk you through the process
http://www.techrepublic.com/blog/mic...box-selection/



Or you can google "msaccess auto fill" for more links.

[e51lrrp]

WOW..THANKS so much steve. I watch and I understand.. Thanks again.
This forum is awesome.
S

[e51lrrp]

WOW..THANKS so much steve. I watch and I understand.. Thanks again.
This forum is awesome.
S

I have tried the autofill and part of it works but not the second and third field. I have attached my database. One thing to note is that my form frmTrophyDetails data source is a query. Maybe that is why it doesn't work. The example is from the form to the table only. If you could take a look at it and offer any suggestions I sure would appreciate it.
Ssylvester11-13.zip

[ssanfu]

OK, to answer your question:

For the combo box cboTrophyBaseStyle
Limit To List = Yes
Column Count = 4
Column Widths = 0,1,0,0

Use the after update event, not the change event
Code for the after update event:

Private Sub cboTrophyBaseStyle_AfterUpdate()
    Me.txtBasePrice = Me.cboTrophyBaseStyle.Column(2)
    Me.txtAssemblyPartsPrice = Me.cboTrophyBaseStyle.Column(3)
End Sub

Note: ".Value" is not needed because the value property is the default property.


-------------------------
Other things:
Using "ID" as the name of the PK in every table gets confusing. Better is "BaseID" , "ColorID", "OrderID", etc.

Displaying the autonumber is (CustID) is not a good thing to do. Autonumbers are not meant to have any real world meaning. The autonumber can go negative and is not guaranteed to be sequential.
Autonumbers are meant to link tables (PK -> FK).
Autonumber's SOLE puprose is to uniquely identify a record. It should not be considered a sequential numbering system.
I do not link (relate) tables on text fields. While you can link on text fields, it is much slower than using Long Integers.
See http://www.utteraccess.com/wiki/index.php/Autonumbers


The first two lines of every code page should be:
Option Compare Database
Option Explicit

In the IDE, click on Tools/Options. Ensure there is a check mark on "Require Variable Declaration"

In th access main window, click on File/Options/Current Database. Look for "Name Auto Correct Options"
Also, you should uncheck (all 3 options) the Auto-Corrupt.... er I mean Auto-Correct. This is a know cause of dB corruption.


You have used "Lookup fields" in the tables.
See
http://access.mvps.org/access/tencommandments.htm
http://access.mvps.org/access/lookupfields.htm

Your tables are not normalized. It is a balancing act between ease of data entry vs normalization.
The table "tblApparelOrderDetails" is set up more like a spreadsheet.


Not trying to burn you... just things I noticed.

[e51lrrp]

OK, to answer your question:

For the combo box cboTrophyBaseStyle
Limit To List = Yes
Column Count = 4
Column Widths = 0,1,0,0

Use the after update event, not the change event
Code for the after update event:

Private Sub cboTrophyBaseStyle_AfterUpdate()
    Me.txtBasePrice = Me.cboTrophyBaseStyle.Column(2)
    Me.txtAssemblyPartsPrice = Me.cboTrophyBaseStyle.Column(3)
End Sub

Note: ".Value" is not needed because the value property is the default property.


-------------------------
Other things:
Using "ID" as the name of the PK in every table gets confusing. Better is "BaseID" , "ColorID", "OrderID", etc.

Displaying the autonumber is (CustID) is not a good thing to do. Autonumbers are not meant to have any real world meaning. The autonumber can go negative and is not guaranteed to be sequential.
Autonumbers are meant to link tables (PK -> FK).
Autonumber's SOLE puprose is to uniquely identify a record. It should not be considered a sequential numbering system.
I do not link (relate) tables on text fields. While you can link on text fields, it is much slower than using Long Integers.
See http://www.utteraccess.com/wiki/index.php/Autonumbers


The first two lines of every code page should be:
Option Compare Database
Option Explicit

In the IDE, click on Tools/Options. Ensure there is a check mark on "Require Variable Declaration"

In th access main window, click on File/Options/Current Database. Look for "Name Auto Correct Options"
Also, you should uncheck (all 3 options) the Auto-Corrupt.... er I mean Auto-Correct. This is a know cause of dB corruption.


You have used "Lookup fields" in the tables.
See
http://access.mvps.org/access/tencommandments.htm
http://access.mvps.org/access/lookupfields.htm

Your tables are not normalized. It is a balancing act between ease of data entry vs normalization.
The table "tblApparelOrderDetails" is set up more like a spreadsheet.


Not trying to burn you... just things I noticed.

Steve: I am not offended by your assistance. I have only been at this for a few months and I am teaching myself from DVD's, Books and online tutorial so all the help I can get appreciated. I am also 65 years old so not nearly as sharp as I would like to be.

Again I appreciate your help. I will study your suggestions, right now I can tell you I am not sure I understand all that you have suggested but will take it one step at a time. For instance your reference to data entry vs normaliation...Why isn't my tblApparelOrderDetails normal. This table is critical to all that I am doing just as the table I am working on with Trophys. Would appreciate your helping me understand this.
Thanks again so much...you have no idea how helpful you have been. S

[e51lrrp]

Steve:
I looked at my table and I do have two lookup's on color and

[e51lrrp]

Steve:
I looked at my table and see that I have two fields with lookup's both of them are linked tables. It was the only way that I could make my tables appear on my form. How else do you make them appear in a dropdown menuon the form? Just by assigning them a relationship?
S

OK, to answer your question:

For the combo box cboTrophyBaseStyle
Limit To List = Yes
Column Count = 4
Column Widths = 0,1,0,0

Use the after update event, not the change event
Code for the after update event:

Private Sub cboTrophyBaseStyle_AfterUpdate()
    Me.txtBasePrice = Me.cboTrophyBaseStyle.Column(2)
    Me.txtAssemblyPartsPrice = Me.cboTrophyBaseStyle.Column(3)
End Sub

Note: ".Value" is not needed because the value property is the default property.


-------------------------
Other things:
Using "ID" as the name of the PK in every table gets confusing. Better is "BaseID" , "ColorID", "OrderID", etc.

Displaying the autonumber is (CustID) is not a good thing to do. Autonumbers are not meant to have any real world meaning. The autonumber can go negative and is not guaranteed to be sequential.
Autonumbers are meant to link tables (PK -> FK).
Autonumber's SOLE puprose is to uniquely identify a record. It should not be considered a sequential numbering system.
I do not link (relate) tables on text fields. While you can link on text fields, it is much slower than using Long Integers.
See http://www.utteraccess.com/wiki/index.php/Autonumbers


The first two lines of every code page should be:
Option Compare Database
Option Explicit

In the IDE, click on Tools/Options. Ensure there is a check mark on "Require Variable Declaration"

In th access main window, click on File/Options/Current Database. Look for "Name Auto Correct Options"
Also, you should uncheck (all 3 options) the Auto-Corrupt.... er I mean Auto-Correct. This is a know cause of dB corruption.


You have used "Lookup fields" in the tables.
See
http://access.mvps.org/access/tencommandments.htm
http://access.mvps.org/access/lookupfields.htm

Your tables are not normalized. It is a balancing act between ease of data entry vs normalization.
The table "tblApparelOrderDetails" is set up more like a spreadsheet.


Not trying to burn you... just things I noticed.

[ssanfu]

I looked at my table and see that I have two fields with lookup's both of them are linked tables. It was the only way that I could make my tables appear on my form. How else do you make them appear in a dropdown menuon the form? Just by assigning them a relationship?

For forms I always use queries, never tables. And I never use a form set to Datasheet view. If I need/want to have a grid, I use a form set to Continuous forms and adjust the controls to look like a datasheet. This is just my preference. It takes a little more time, but I have control over the look and feel. And there are a couple of things a datasheet cannot do. Its been so long ago, I don't remember what I was trying to do, but it couldn't be done in datasheet view. Again, no datasheets is my choice.

I use forms in Continuous forms view and use combo boxes.

Here is another link on lookup fields. Scroll down to page 3-3
http://allenbrowne.com/binary/Access...Chapter_03.pdf

Why isn't my tblApparelOrderDetails normal.

I can't open your dB right now (at work), but IIRC, you have sizes (data) as field names S, M, X, 2X, 3X....
The table is laid out like a spreadsheet. This is known as "Committing spreadsheet". Spreadsheets are normally short and fat, while database tables are tall and thin.

In your current design, what happens if you decide to add another size? You have to modify your table. Then you have to modify the queries... and forms.... and reports.... and any code modules. Pretty much a whole dB redesign.

If you had a field for "Size", all you would have to do is add the size to a table. No changes. I realize this is simplistic, because there are prices, etc involved, but the point is no structure changes are necessary.


--------------
Crystal has a lot of good info at
http://www.accessmvp.com/Strive4Peace/

And there are good tutorials at Rogers Access Library
http://www.rogersaccesslibrary.com/f...s_forum46.html