Help! Confused!

  • Thread starter Thread starter Brook
  • Start date Start date
B

Brook

I think what I am trying to do should be simple, but I
guess I am just missing something:

What I have:
tblStockData: StockID, StockNumber, StockName, etc
tblOrders: OrderDate, DueDate, StockNumber, StockName,
Quantity, Price, etc

What I want to do is set up a form to process new
orders:

So my form would contain:
OrderDate (from tblOrders, Manual Entry)
DueDate (from tblOrders, Manual Entry)
StockNumber : What I am trying to do is set up a
ComboBox (from tblStockData) so that when
the user selects the StockNumber they would like to order
it would automatically populate the StockName.

The catch is this, I would like to have
StockName set up as a ComboBox as well (from
tblStockData), that would perform a lookup on the
StockName and when the user selects the StockName they
would LIke it would automatically populate the StockNumber
from the tblStockData

The reason I am needing to set it up this way is
so that if the user doen't either the StockNumber or
StockName they will be able to choose the one they do know.

Please: can anyone help?

Thanks in advance...

Brook

Please let me know if you have any questions
 
Hi,

If the controls on your form are bound to the form's data, then simply add
two combos with the same control source (the primary key of your
tblStockData) but with one showing the stock number and the other showing
the description .. as they are both bound to the same field, they will
change value together.

you can change the sort order of the row sources so that each is different,
the data will still match.

HTH


MFK.
 
thanks for the info,

But I guess I'm confused on what you mean by the
Controls? And same control source?
Brook
 
Brook

Why do you have StockName redundantly recorded in two tables? If you know
StockNumber, you can retrieve StockName.
 
Thanks for the info,

Will this work If I have two ComboBoxes on my form? One
that drops down for StockNumber then filles in the Second
ComboBox StockName. and visa versa?

thanks,

Brook
 
You want each combo box to be linked to the other? I forsee some potential
"fun" with the form's setup if you do this (not that it's impossible, just
tricky).

If your intent is to allow the user to search either by number or by name,
can you keep the two combo boxes but have a textbox for each that would
display the nonchosen info?
 
That is exactly what I want to do! And to have the
information from the ComboBox(es)saved to the tblorders.

Brook
 
Have you worked out the "logic" for the following scenarios so that you'll
know how to tell ACCESS what to do?

(1) If user chooses an entry from one combo box, does it overwrite the value
already chosen in the other combo box? If not, should the "other" combo box
be locked once an entry has been made in the first combo box? And if it
should be locked, then how will user be able to change his/her mind and
decide to select based on the second combo box after having done the initial
selection in the first box?

(2) Can user change the stock name for a given stock number? (or vice
versa). If not, then I echo Jeff's question about why are you storing the
same data (which cannot be changed) in two separate tables -- why not just
store the stock number in the orders table and you can always look up the
stock name from it?

(3) Will this be just a data entry form? Or will this form be used to view
already entered orders? If the latter is true, then can users change the
already entered information? If yes, how will you update all the other info
that is tied to the stock number or name (price, quantity, etc.)?

All of the above questions (and likely some others) would need to be
clarified/answered before you can begin to write the VBA code that will be
needed to cause what you want to happen to actually happen.
 
Hi,

Sorry to answer a question with a question .. but .. how do you create your
combo boxes on the form?

if you create the form with a wizard, or by dragging the field name onto the
form, then you can create another combo in the same way, from the same field
and change the way the columns in the combo are displayed to achieve what
you want.

If the combos are populated by code, then it will be a little more awkward.

MFK.
 
Hi,

Here's an example of what I've suggested, using the Northwind sample
database.

Open the Northwind database.
From the database window, open the Orders Form.
In the toolbar, click the "Field List" button.
Drag the "Customer ID" field to the top right of the Orders form (there is
already a "Customer ID" combo there, so place the new one just below it).
In the Format tab of the Properties sheet for the new combo, set the Column
Widths property to 2;0 (so that the ID is shown instead of the description)

That's it.

if you now switch the form to Form View, you will see that selecting a
company in either combo will make the correct value appear in the other
combo too.

HTH


MFK.
 
Back
Top