A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

help needed with a combo box



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 10:14 AM posted to microsoft.public.access.forms
alanb[_2_]
external usenet poster
 
Posts: 2
Default help needed with a combo box

Hi, I am trying to create a database for ordering materials. I have a form
with two tabs. The first tab is for order details and the second tab is for
order items (with a subfrm to list products for that order).
On tab one I select the supplier for the order. When I go to tab two, to fill
out the order items I have a combo box for ‘Product code’ and I want the
combo box to only show products for the supplier I selected on tab one.
The products are all in the same table and the column for ‘supplier name’ is
linked to ‘supplier name’ in another table for suppliers details.
In the combo box I have tried to create a query to only show products for
that supplier but it does not work for each record, It only works for one
record and does not seem to change when I change the supplier on tab one. I
also tried to set the criteria as ‘please enter supplier name’ (this is not
ideal put I did not know what else to try), but this works only once. It
works for the first product I enter and that’s it, it does not change until I
close and re open.

I hope this makes sense, and hope somebody can help.
Many Thanks, Alan.

  #2  
Old May 19th, 2010, 11:58 AM posted to microsoft.public.access.forms
alanb[_2_]
external usenet poster
 
Posts: 2
Default help needed with a combo box

My main tables/fields are;
TblOrders
AB ref. Number , Order date, contract number, Supplier Name, job details, etc.

TblSuppliers
Supplier Name, address, tel, fax, email, contact name, etc.
TblPriceList
Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
TblWorkItems
Work item ID, AB ref. Number, product code, item description, quantity, unit,
price, extended price.

My forms are as follows;
Frmorders
Tab one, Order Details, included button to print order (report)
Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
required.

Frm Suppliers
Tab one, Supplier Details
Tab two, subfrmPriceList (linked to supplierName)
Tab three, subfrmOrderHistory

  #3  
Old May 19th, 2010, 01:38 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default help needed with a combo box

I would use an unchanging number for SupplierID. Names change.

Anyhow, in the After Update event of the supplier combo box, something like:

Dim strSQL as String

strSQL = "SELECT * FROM tblPriceList " & _
"WHERE [Supplier Name] = " " " & Me.SupplierName & " " " "

Me.sfrmItems.Form.cboProduct.RowSource = strSQL

This assumes a subform control (the "box" containing the subform) named
sfrmItems, and that the subform control's source object (form) has a combo
box named cboProduct.

I have used generic SQL, with an asterisk, but you probably want to use the
fields in your current Row Source query.

You probably want similar code in the form's Current event, after testing
whether a supplier has been selected, so that the combo box list shows a
filtered list of products when you revisit an existing record.


alanb wrote:
My main tables/fields are;
TblOrders
AB ref. Number , Order date, contract number, Supplier Name, job details, etc.

TblSuppliers
Supplier Name, address, tel, fax, email, contact name, etc.
TblPriceList
Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
TblWorkItems
Work item ID, AB ref. Number, product code, item description, quantity, unit,
price, extended price.

My forms are as follows;
Frmorders
Tab one, Order Details, included button to print order (report)
Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
required.

Frm Suppliers
Tab one, Supplier Details
Tab two, subfrmPriceList (linked to supplierName)
Tab three, subfrmOrderHistory


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #4  
Old May 19th, 2010, 02:19 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default help needed with a combo box

Alanb -

You need to set the row source for the combo box on tab 2 to be a query like
this (use your subform name that is on the first tab, or if not in a subform,
just reference the field):

SELECT [Product Code], [Item description]
FROM TblPriceList
WHERE [Supplier Name] = Forms![Frm
Suppliers].[SubformNameOnTab1].Form.[Supplier Name]

You may need to requery this combobox in the AfterUpdate event of the
Supplier Name on tab 1.

If you have problems, post your SQL or the code that is not working.

--
Daryl S


"alanb" wrote:

My main tables/fields are;
TblOrders
AB ref. Number , Order date, contract number, Supplier Name, job details, etc.

TblSuppliers
Supplier Name, address, tel, fax, email, contact name, etc.
TblPriceList
Supplier Name, Product Code, Item description, unit, Price, cost code, etc.
TblWorkItems
Work item ID, AB ref. Number, product code, item description, quantity, unit,
price, extended price.

My forms are as follows;
Frmorders
Tab one, Order Details, included button to print order (report)
Tab two, subfrmWorkItems (Linked with AB ref number), user to enter products
required.

Frm Suppliers
Tab one, Supplier Details
Tab two, subfrmPriceList (linked to supplierName)
Tab three, subfrmOrderHistory

.

  #5  
Old May 19th, 2010, 03:57 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default help needed with a combo box

That is another way to do it, but "need to" implies it is the only way, which
is not the case. With your approach, requerying the combo box is the only
code, but when I can I prefer to avoid form and control references in queries
if for no other reason than that the query is specific to the form, and can't
be worked on unless the criteria are temporarily changed.

Also, for reasons I mentioned it would probably be best to use the same code
(whichever approach is used) in the form's Current event.

Daryl S wrote:
Alanb -

You need to set the row source for the combo box on tab 2 to be a query like
this (use your subform name that is on the first tab, or if not in a subform,
just reference the field):

SELECT [Product Code], [Item description]
FROM TblPriceList
WHERE [Supplier Name] = Forms![Frm
Suppliers].[SubformNameOnTab1].Form.[Supplier Name]

You may need to requery this combobox in the AfterUpdate event of the
Supplier Name on tab 1.

If you have problems, post your SQL or the code that is not working.

My main tables/fields are;
TblOrders

[quoted text clipped - 20 lines]

.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #6  
Old May 19th, 2010, 05:14 PM posted to microsoft.public.access.forms
alanb via AccessMonster.com
external usenet poster
 
Posts: 1
Default help needed with a combo box

sorry quys, still not working, I am just getting more confused.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

  #7  
Old May 19th, 2010, 05:42 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default help needed with a combo box

What have you tried that isn't working?

alanb wrote:
sorry quys, still not working, I am just getting more confused.


--
Message posted via http://www.accessmonster.com

  #8  
Old May 19th, 2010, 06:19 PM posted to microsoft.public.access.forms
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default help needed with a combo box

Alanb -

Post your code so we can help...

--
Daryl S


"alanb via AccessMonster.com" wrote:

sorry quys, still not working, I am just getting more confused.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:53 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.