How to fill a for automaticly(just some boxes)

M

marko

Hello. I hope tht someone is going to be able tohelp me, so here is my
problem.
I have a main table with ID(primary Key),Brend,Model,Price.
Then I have a sales table which is filled with a sales form. In the
sales form i have ID,Size and Date. So the sales table is filled with
ID,Size and Date.
I would like to put one more box for the price in the form but like
this: When i write the ID and press enter i would go to the second box
to fill the size BUT THE PRICE BOX WOULD FILL AUTOMATICLY(it would take
the price of that ID from the main table)! And I would also like to
write a new price over that one if that price isn't good for me(let's
say if I give a discount or something like that). How can I do that?

Marko
 
R

Rick Brandt

marko said:
Hello. I hope tht someone is going to be able tohelp me, so here is my
problem.
I have a main table with ID(primary Key),Brend,Model,Price.
Then I have a sales table which is filled with a sales form. In the
sales form i have ID,Size and Date. So the sales table is filled with
ID,Size and Date.
I would like to put one more box for the price in the form but like
this: When i write the ID and press enter i would go to the second box
to fill the size BUT THE PRICE BOX WOULD FILL AUTOMATICLY(it would take
the price of that ID from the main table)! And I would also like to
write a new price over that one if that price isn't good for me(let's
say if I give a discount or something like that). How can I do that?

Marko
Click to expand...

Make ID a ComboBox and in the RowSource include two columns. One for ID and one
for Price. Make the width of the Price column zero so it does not show. Then
in the AfterUpdate event of the ComboBox have code...

Me.Price = Me.ID.Column(1)

That will "push" the value of the hidden column into the Price control on your
form, but you will still be able to change it manually.
 
M

marko

If it is not complicated for you, can you explain to me what do i
exactly have to do because i'm a beginner in access.
How can i include 2 columns in the row source? Do i make a query?
What does Column(1) stand for?
Please help me if you can.


Marko
 
R

Rick Brandt

marko said:
If it is not complicated for you, can you explain to me what do i
exactly have to do because i'm a beginner in access.
How can i include 2 columns in the row source? Do i make a query?
What does Column(1) stand for?
Please help me if you can.
Click to expand...

The relevant properties of the ComboBox are...

RowSourceType
RowSource
ColumnCount
BoundColumn
ColumnWidths
ControlSource

Find the RowSourceType property on the data tab of the property sheet and make
sure it is set to Table/Query. Then move to the RowSource property and click
the builder [...] button to the right side of it. That will take you to a query
designer window where you can build your two column query. Add your table and
then put ID into the first column and Price into the second one. Close and
save. This will return you to the form designer and you will now have a valid
SQL string in the RowSource property. This is the same as a saved query except
you won't see it in the db query window.

Now set the BoundColumn property to 1. This is what causes the ComboBox to
store the ID as part of your record. The ControlSource property should be set
to ID because that is the field where the value will be saved.

Now move to the Format tab of the property sheet. Set ColumnCount to 2 (because
your RowSource has two columns) and set the ColumnWidths to n";0" (replacing n
with how wide you want the ID column to be in the drop-down list). Having zero
in the second entry will cause the Price column to be hidden.

Finally move to the Event tab of the property sheet. Enter [Event Procedure]
into the AfterUpdate box and then press the builder [...] button. This will
take you to the VBA code editor. Your cursor will automatically be positioned
between two pre-made lines that define the code procedure that will be executed
whenever the value in the ComboBox is changed. Between those lines is where you
enter...

Me!Price = Me!ID.Column(1)

In this context column numbering begins with zero so Column(1) is actually the
second (hidden) column containing the price. You should now find that whenever
you enter an ID into the ComboBox that the correct price is automatically copied
into the Price control on your form.
 
Top