can you solve my problem please?

[grad2009]

Hi,


i'm working in a stock database that contains (Items,Stocks,Procurements,Sales,and Transactions) tables, the attached image explains the relationships between the previous tables and the properties of each table, in my database the item can be stored in more than one stock, and the stock can store more than one item, i created two forms, one for procurements and the other for sales.The procurements form contains stockNo field (list of numbers from Stocks table),and itemNo fielld (list of item numbers from the Items table).The Sales form contains form contains stockNo field (list of numbers from Stocks table),and itemNo fielld (list of item numbers from the Items table). If I supposed that i bought to items (1 and 2) and stored them in stocks(1 and 2) respectivly.Now in the Sales form when i choose the stock number 1 from the list then goes to the item list in that form I found all the items No even thoses are not in that stock(No 1) ,now i want to do something to minimize the list of items to include only items that are found in the choosen stock.

i hope that you understand what i mean
pleeease i want your help
thanks with regards,

[grad2009]

Am i clear or there is a problem?

[carlmdobbs]

Hi,
i'm working in a stock database that contains (Items,Stocks,Procurements,Sales,and Transactions) tables, the attached image explains the relationships between the previous tables and the properties of each table, in my database the item can be stored in more than one stock, and the stock can store more than one item, i created two forms, one for procurements and the other for sales.The procurements form contains stockNo field (list of numbers from Stocks table),and itemNo fielld (list of item numbers from the Items table).The Sales form contains form contains stockNo field (list of numbers from Stocks table),and itemNo fielld (list of item numbers from the Items table). If I supposed that i bought to items (1 and 2) and stored them in stocks(1 and 2) respectivly.Now in the Sales form when i choose the stock number 1 from the list then goes to the item list in that form I found all the items No even thoses are not in that stock(No 1) ,now i want to do something to minimize the list of items to include only items that are found in the choosen stock.

i hope that you understand what i mean
pleeease i want your help
thanks with regards,

All you have to do is to enter a where clause in the query you created to specify the stock number. This can be complex or easy depending on circumstances.
First you have to write a procedure, a function, in the code module of the form or in a general module. Let us say you named your query qryStocks. Let us assume your first combo box is called cboFirst and the second combo box you refer to is cboSecond. Let us assume that the Stock ID is the bound Colum for cboFirst.

All you need to to do something like this:

Function fnRowsource(lStockNumber as Long)
Dim strSQL as string
Dim lFirst as long

lFirst = me!cboFirst
strSQL = "Select * from qryStocks where Stocknumber = " & lStockNumber " _
& " where StockNumber = " & lStockNumber

me.cboSecond.rowsource = strsql

End Function

Put this in the after update event of cboFirst so that as it changes the second combo box changes with it.

Be sure to make another sub routine an place it in the Open even of the form to restore the cboSecond rowsource to its original SQL of "qryStocks"

Yes. It can be more complicated that this. I can help you more if you write carlmdobbs@gmail.com. I'll be happy to call you and talk to you about anything having to do with MS Access. Write to me directly and see if it works.

If you send me an email from my website at dobbscomputerinstitute.web.officelive.com I'll send you regular weekly or monthly tips on how to do things with MS Access. It's a service I do for people.