Main form sub form combo boxes

[Mar1810]

I have a main form frmShip, with underlying table tblShip, with a combo box that selects a Client. A sub form, frmShipDetail, based on tblShipDetail. The sub form has a combo box to select inventory ID from tblInventory. The row source runs a query to select from tblInventory where the ClientID = forms!frmShip!cboClientID. I can't get it to filter on ClientID, it give me all the records in tblInventory. Help.

[AccessPower]

' Apply filters button


Dim strWhere As String


    'Make filter string
    If Nz(Me.cboClientID, "") <> "" Then
        strWhere = strWhere & "[tblInventory.ClientID]='" & Me.cboClientID & "' AND "
    End If


    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.frmShipDetail.Form.Filter = strWhere
        Me.frmShipDetail.Form.FilterOn = True
    Else
        Me.frmShipDetail.Form.Filter = ""
        Me.frmShipDetail.Form.FilterOn = False
    End If

This can be tied to a button click or afterupdate event.

[ssanfu]

The sub form has a combo box to select inventory ID from tblInventory. The row source runs a query to select from tblInventory where the ClientID = forms!frmShip!cboClientID. I can't get it to filter on ClientID, it give me all the records in tblInventory. Help.

tblShip
ShipID_PK Autonumber
other fields

tblInventory
InventoryID_PK Autonumber
other fields


tblShipDetail
ShipDetailID_PK Autonumber
ShipID_FK Long - link to tblShip
InventoryID_FK Long - link to tblInventory
other fields


The combo box to select inventory ID from tblInventory should have a query that looks something like

SELECT InventoryID_PK, InvDesc FROM tblInventory

Why would ClientID ever be in the query for the Inventory combo box???

[Mar1810]

The format for tblInventory is ID(autonumber), CientID(number),ProductCode(short text), Item1(short text), Item2(short text), Unit(long), Pallet(long)

Inventory is stocked for multiple clients. To prepare a packing slip the Client is frmShip contains cboClientID. Based on the selection, the frmShipDetail, cboInventoryID I want it to filter the ClientID selected in frmShip, cboClientID.

[AccessPower]

The format for tblInventory is ID(autonumber), CientID(number),ProductCode(short text), Item1(short text), Item2(short text), Unit(long), Pallet(long)

Inventory is stocked for multiple clients. To prepare a packing slip the Client is frmShip contains cboClientID. Based on the selection, the frmShipDetail, cboInventoryID I want it to filter the ClientID selected in frmShip, cboClientID.

' Apply filters button


Dim strWhere As String


    'Make filter string
    If Nz(Me.cboClientID, "") <> "" Then
        strWhere = strWhere & "[tblInventory.ClientID]='" & Me.cboClientID & "' AND "
    End If


    'Apply filter
    If strWhere <> "" Then
        strWhere = Left(strWhere, Len(strWhere) - 5) 'Remove the extra AND
        Me.frmShipDetail.Form.Filter = strWhere
        Me.frmShipDetail.Form.FilterOn = True
    Else
        Me.frmShipDetail.Form.Filter = ""
        Me.frmShipDetail.Form.FilterOn = False
    End If

This should work for that.

[orange]

While the solution proposed by AccessPower may work for your specific issue, it isn't clear to me (and perhaps Steve) what your database is about. I think this is a case where a 5 line description about your business in plain English(no jargon) would help readers and you.
Good luck.

[Mar1810]

Inventory is stocked for multiple clients. To prepare a packing slip select ClientID from frmShip!cboClientID. Use cboClientID to select inventory to ship, use frmShipDetail!InventoryID filtered by cboClientID, to select ProductCode.

[orange]

I realize you have 3 posts here in this forum. But do you think there is sufficient info such that you could hire a developer and give him/her the info in post #7 and expect them to build meaningful database?

It seems like you have multiple Clients who Order 1 or more Products from you/your Company. You store some Products and Order/reOrder others that you call Inventory. You make Shipments of Ordered Products to appropriate Clients. It would also seem that you include a Packing Slip with a Shipment to identify specifically which Products are included in this Shipment. You have not mentioned Invoices nor Payments, but my guess is that you haven't gotten to those yet (guess on my part). You may also be dealing with Accounts and Payment Types, but that again is just a guess.

I'm not trying to be difficult or picky. I am trying to identify to you the level of detail of the business required to build a meaningful database and the need of a blueprint for its design.

Here is a link to a free, generic model that deals with Logistics and Shipments. It is generic and shows a typical set up which may or may not deal with your situation. You can add and/or remove pieces to meet your needs.

Here is a free tutorial that you might want to work through (30-45 minutes). It identifies a business by means of a description, then proceeds through a process to identify the things and their attributes and how they relate to each other. If you work through the process, you will learn about database, table design and relationships and you will create a blueprint for your database. And, what you learn can be used with any database.

The key point here is to separate the What you're trying to automate from the How it could be implemented. It's much like a plan followed by action(s)/task(s).

RE: Packing Slip Details
Packing slips vary depending on your business and products. Every shipment to a customer should contain a packing slip listing the order date, the products included in the order and the quantity of each product. Some businesses may want to include the weight of the product next to the item. Many customers use the packing slip as a guide when unpacking their order. If something is missing from the box, they cross-check it with the packing slip and then alert the seller.


Good luck.