display a value dependant on a selection

I have a form to enter data into a table for materials orders. when i
place the orders i'm often asked for a contact number for the person
receiving the goods. I have a table with all of these names and
numbers in, and a combo-box in the orders form to select who's
receiving the materials. what i want is a text box on the same form
that will show the contact number for the person selected in the
combobox. how would i go about linking the two?

the more complicated question is this.... the form for the orders has
space for 6 different items, in the form of item1, rate1, quantity1
through to item 6 etc. as i have a large number of materials to enter
i'm looking for a way whereby i select the material from a combobox
(already set up and working fine) and the price is automatically
looked up in previous orders, more as a reference than automatically
filling my price field as i may be ordering the same material as
before but from a different supplier hence the price may be slightly
different. as the material could be in item1,2,3,4,5, or 6 i've got
no idea how to set a query up. in addition some materials have fixed
prices wherever they are delivered to, whilst others have different
prices depending on location. To be honest i've no preference between
an output similar to the above, or a query result showing the price
from different suppliers for the current site, is this relatively
simple to set up or will it be more hassle than its worth?

Re: display a value dependant on a selection

On Oct 15, 4:09 am, themastertay...@hotmail.com wrote:
> I have a form to enter data into a table for materials orders. when i
> place the orders i'm often asked for a contact number for the person
> receiving the goods. I have a table with all of these names and
> numbers in, and a combo-box in the orders form to select who's
> receiving the materials. what i want is a text box on the same form
> that will show the contact number for the person selected in the
> combobox. how would i go about linking the two?

In the combo boxes' onclick event, put code similar to the following:

txtPhoneNumber=dlookup("tblAddressBook","LastName = ' " & cmbLastName
& " ' ")

(use your own table names, field names and control names.)

> the more complicated question is this.... the form for the orders has
> space for 6 different items, in the form of item1, rate1, quantity1
> through to item 6 etc. as i have a large number of materials to enter
> i'm looking for a way whereby i select the material from a combobox
> (already set up and working fine) and the price is automatically
> looked up in previous orders, more as a reference than automatically
> filling my price field as i may be ordering the same material as
> before but from a different supplier hence the price may be slightly
> different. as the material could be in item1,2,3,4,5, or 6 i've got
> no idea how to set a query up. in addition some materials have fixed
> prices wherever they are delivered to, whilst others have different
> prices depending on location. To be honest i've no preference between
> an output similar to the above, or a query result showing the price
> from different suppliers for the current site, is this relatively
> simple to set up or will it be more hassle than its worth?

What you want is a list that pops up showing the sales history for
that item. Put a button next to each item. Put code in the on_click
event of each command button to load the rowsource of the listbox and
then make the listbox visible.

listbox.rowsource = "SELECT SaleDate, Description, ItemPrice FROM
tblSales WHERE tblSales.ItemNumber = ' " & txtItemNumber1 & " ' "
listbox.visible=true

Re: display a value dependant on a selection

On 15 Oct, 14:56, OldPro wrote:
> On Oct 15, 4:09 am, themastertay...@hotmail.com wrote:
>
> > I have a form to enter data into a table for materials orders. when i
> > place the orders i'm often asked for a contact number for the person
> > receiving the goods. I have a table with all of these names and
> > numbers in, and a combo-box in the orders form to select who's
> > receiving the materials. what i want is a text box on the same form
> > that will show the contact number for the person selected in the
> > combobox. how would i go about linking the two?
>
> In the combo boxes' onclick event, put code similar to the following:
>
> txtPhoneNumber=dlookup("tblAddressBook","LastName = ' " & cmbLastName
> & " ' ")
>
> (use your own table names, field names and control names.)
>
> > the more complicated question is this.... the form for the orders has
> > space for 6 different items, in the form of item1, rate1, quantity1
> > through to item 6 etc. as i have a large number of materials to enter
> > i'm looking for a way whereby i select the material from a combobox
> > (already set up and working fine) and the price is automatically
> > looked up in previous orders, more as a reference than automatically
> > filling my price field as i may be ordering the same material as
> > before but from a different supplier hence the price may be slightly
> > different. as the material could be in item1,2,3,4,5, or 6 i've got
> > no idea how to set a query up. in addition some materials have fixed
> > prices wherever they are delivered to, whilst others have different
> > prices depending on location. To be honest i've no preference between
> > an output similar to the above, or a query result showing the price
> > from different suppliers for the current site, is this relatively
> > simple to set up or will it be more hassle than its worth?
>
> What you want is a list that pops up showing the sales history for
> that item. Put a button next to each item. Put code in the on_click
> event of each command button to load the rowsource of the listbox and
> then make the listbox visible.
>
> listbox.rowsource = "SELECT SaleDate, Description, ItemPrice FROM
> tblSales WHERE tblSales.ItemNumber = ' " & txtItemNumber1 & " ' "
> listbox.visible=true

thanks for the help, got stage 1 to work, as for the second part i
think i understand how the idea works but think it may have a problem
or two. lets say i input itemA in the item1 section of the form, as i
see it the code you suggest will look for a previous instance of itemA
being ordered in the item1 section of the underlying table. I may be
mistaken but it looks to me as if this system won't retrieve a price
if i'd made a larger order previously and ItemB was ordered in the
item1 section and itemA was entered in the item2 section.

secondly due to the large number of items i have in the system i have
a 2 stage selection system whereby the category the item is selected
and this creates the row source of the actual item selection combo-box
so that only items in this category are visible. will this cause
problems with the code or should it still work fine?

as i'm using the system daily i need to ensure i understand what i'm
doing completely, basically so i don't do any damage to the system.
any alterations will need to be made at the end of the day, so
apologies for the theoretical questions, obviously it would be easier
to explain things after i attempt it.

Re: display a value dependant on a selection

On Oct 16, 2:41 am, themastertay...@hotmail.com wrote:
> On 15 Oct, 14:56, OldPro wrote:
>
>
>
>
>
> > On Oct 15, 4:09 am, themastertay...@hotmail.com wrote:
>
> > > I have a form to enter data into a table for materials orders. when i
> > > place the orders i'm often asked for a contact number for the person
> > > receiving the goods. I have a table with all of these names and
> > > numbers in, and a combo-box in the orders form to select who's
> > > receiving the materials. what i want is a text box on the same form
> > > that will show the contact number for the person selected in the
> > > combobox. how would i go about linking the two?
>
> > In the combo boxes' onclick event, put code similar to the following:
>
> > txtPhoneNumber=dlookup("tblAddressBook","LastName = ' " & cmbLastName
> > & " ' ")
>
> > (use your own table names, field names and control names.)
>
> > > the more complicated question is this.... the form for the orders has
> > > space for 6 different items, in the form of item1, rate1, quantity1
> > > through to item 6 etc. as i have a large number of materials to enter
> > > i'm looking for a way whereby i select the material from a combobox
> > > (already set up and working fine) and the price is automatically
> > > looked up in previous orders, more as a reference than automatically
> > > filling my price field as i may be ordering the same material as
> > > before but from a different supplier hence the price may be slightly
> > > different. as the material could be in item1,2,3,4,5, or 6 i've got
> > > no idea how to set a query up. in addition some materials have fixed
> > > prices wherever they are delivered to, whilst others have different
> > > prices depending on location. To be honest i've no preference between
> > > an output similar to the above, or a query result showing the price
> > > from different suppliers for the current site, is this relatively
> > > simple to set up or will it be more hassle than its worth?
>
> > What you want is a list that pops up showing the sales history for
> > that item. Put a button next to each item. Put code in the on_click
> > event of each command button to load the rowsource of the listbox and
> > then make the listbox visible.
>
> > listbox.rowsource = "SELECT SaleDate, Description, ItemPrice FROM
> > tblSales WHERE tblSales.ItemNumber = ' " & txtItemNumber1 & " ' "
> > listbox.visible=true
>
> thanks for the help, got stage 1 to work,

Which was stage one? Putting an invisible listbox on the screen?

> as for the second part i
> think i understand how the idea works but think it may have a problem
> or two. lets say i input itemA in the item1 section of the form, as i
> see it the code you suggest will look for a previous instance of itemA
> being ordered in the item1 section of the underlying table. I may be
> mistaken but it looks to me as if this system won't retrieve a price
> if i'd made a larger order previously and ItemB was ordered in the
> item1 section and itemA was entered in the item2 section.
>

If you have Item1 and Item2 fields, then perhaps you don't keep a
separate detail record, but some sort of semblance of an actual order
form. If this is true, then it will make things more difficult. You
will have to search each field for the search criteria, and it will
take much longer.

> secondly due to the large number of items i have in the system i have
> a 2 stage selection system whereby the category the item is selected
> and this creates the row source of the actual item selection combo-box
> so that only items in this category are visible. will this cause
> problems with the code or should it still work fine?

This shouldn't pose a problem, as long as the items are all found in
one table.

> as i'm using the system daily i need to ensure i understand what i'm
> doing completely, basically so i don't do any damage to the system.
> any alterations will need to be made at the end of the day, so
> apologies for the theoretical questions, obviously it would be easier
> to explain things after i attempt it.- Hide quoted text -
>
> - Show quoted text -

Work on a copy of the database; if you screw up, it won't affect
anyone. Once you get it working, then simply rename the old form in
the current database and export the new form from the copy in its
stead.

For more specific help, you will have to share your data structure and
table and field names.