Displaying data in check box or Combo box form

[dmuroff]

Hi..Here is my problem.

I have 3 tables:

1) Retailers which contains Retailer ID, Retailer Name, Address etc.

2) Distributors which contains Distributor ID, Distributor Name.

3) A table which links the 2. I have created a many to many relationship meaning that in this table I solely enter the Retailer ID and the related Distributor ID. There can be more than 1 Distributor for every retailer.

Here is my problem: I am trying to make a user friendly Form for Retailers which will allow the user to pick and choose which distributors are necessary along with entering the retailer information. Is there a way to do this with check boxes or a combo box with the way I have set up my database?

Thanks in advance!

Dan

 

[dmuroff]

Here is a little extra information.

I have created a form with a list box which shows the distributors linked to the retailers. The problem is that I have 465 retailers but it is showing 625 records because if a retailer has more than 1 distributor, Access is treating the 2 as completely seperate records. I would like Access to show all the selected distributors on 1 retailer record. Is this possible? Thanks!

 

[dhookom]

Normally, you would add a continuous subform of your junction table on your Retailer table. The subform would contain a single column/field/combo box to select a Distributor.

Is this adequate for your needs? If not, you will need code that would make the proper selections in a multiselect list box. I would think this would look horrible if you have more than about 50 distributors.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dmuroff]

I have included a subform for distributors on my retailers form but it can be somewhat confusing for users using the form (i.e. deleting). Is there a way to make it easier for users to use? Thanks

 

[dhookom]

Do you think selecting Distributors from a combo box is confusing? How may distributors are there?

It is possible to retrieve and update multi-select list box with some code. You would need a function that would loop through the junction table and make the proper selections in the list box. Other functions would be used for selecting or un-selecting a distributor.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dmuroff]

There are only 7 distributors..So it's probably not very confusing.

I am rather new to database design so I am unfamiliar with junction tables etc. Any help you can give me would be greatly appreciated. Thanks!

 

[lespaul]

Read 'The Fundamentals of Relational Database Design'. That will help!

les

 

[willir]

Dan

To confirm...

tblRetailer
RetailerID - primary key
RetailerName

tblDistributor
DistributorID - primary key
DistributorName

tblDistribution
RetailerID - foreign key to tblRetailer
DistributorID - foreign key to tblDistribution

Primary key = RetailerID + DistributorID
(To avoid duplicating entries)

Okay, the table I called tblDistribution as you know, is the many-to-many relationships. Although it may be a pain to setup all the permutations, I find it actually is a pretty powerful table.

Using the contineous / tabular form, you can create a subform based on the table. Embed the subform in the Retailer form, hide the RetailerID, change the DistributorID to a combo box - SELECT DistributorID, DistributorName and voilà, you have a list of all distributors for each retailer.

Reverse the process. For the Distribution form, for the similr subform based on tblDistribution, hide the DistributionID, change the RetailerID text field to a combo box - SELECT RetailerID, RetailerName - and now you have a list of Retailers for each distributor.

In both cases, the combo box (or list box) can be used to assign the appropriate retailer to distributor, or distributor to retailer.

A more advanced approach would be to have two forms open. In one the above discribed form / subform is displayed. In the other a contineous form displays the list of all available selections - for the Retailer main form, the second main form would display a list of the ditributors. Then the end user would double-click on the appropriate distributiors. Code would add the record to the tblDistribution table, and the subform would requery the table to display the new addition. I find this approach is very user friendly - but requires more work.

Richard

 

[dmuroff]

Any idea where I would be able to see a sample code?

Thanks for all the advice!

 

[willir]

Dan

The following is a point of sale system -- wanted something that was very easy to use.

Setup:
Order table - main form
OrderDetail table - subform embedded within main Order form

Item table - main form opens to the right of the Order/OrderDetail form.

The end user can complete an order by using just the Order/OrderDetail form by using combo boxes to select the item and quantity.

However, the user friendly part is where the end user dobule clicks on an item on the Item form. If no order entered for this item, a record is added to the OrderDetail. If an item exists, then the code adds one to the quantity ordered.

The following code is in the Item form for the DoubleClick event...

[COLOR=blue]
Private Sub Form_DblClick(cancel As Integer)[/color]

Dim dbs As DAO.Database, rst As DAO.Recordset, frm As Form
Dim lngCount As Long, strWhere As String, strSQL As String
Dim curItemPrice As Currency, curItemTotal As Currency

'CheckSister form makes sure Order/OrderDetail form is open
If CheckSisterFrm Then

    Set frm = Forms!OrderFrm

    strWhere = "[OrderID] = " & Me.OrderID & " and [ItemID] = " & Me.ItemID

    Set dbs = CurrentDb()

    lngCount = DCount("[ItemQty]", "OrderDetailTbl", strWhere)

    If lngCount = 0 Then
    
        curItemPrice = DLookup("[ItemDefPrice]", "ItemTbl", "[ItemID] = " & Me.ItemID)
        curItemTotal = curItemPrice
        Set rst = dbs.OpenRecordset("OrderDetailTbl")
        With rst
            .AddNew
            !OrderID = Me.OrderID
            !ItemID = Me.ItemID
            !ItemQty = 1
            !ItemPrice = curItemPrice
            !ItemTotal = curItemTotal
            .Update
        End With
    Else
        strSQL = "Select * from OrderDetailTbl Where " & strWhere
        Set rst = dbs.OpenRecordset(strSQL)
        With rst
            .MoveFirst
            .Edit
            !ItemQty = !ItemQty + 1
            !ItemTotal = !ItemQty * !ItemPrice
            .Update
        End With
    End If
    
    frm!ItemDetailSbFrm.Requery
    frm!SalesTotal.Requery
    
End If

End Sub


[COLOR=blue]
Function CheckSisterFrm() As Boolean[/color]

'Before allowing function to work,
' - Sales order form must be open
' - Must have an assigned member
' - Must be in ADD or EDIT mode

Dim frm As Form
Dim booPass As Boolean
Dim strMsg As String, strForm As String

booPass = False
strForm = "OrderFrm"

For Each frm In Forms

    If frm.Name = strForm Then
        If Nz(frm!memberID, 0) > 0 Then
            If Len(Nz(frm!PurchaseType, "")) > 0 Then
                If Nz(frm!OrderID, 0) > 0 Then
                    If frm!CommitOrder = False Then
                        booPass = True
                        Me.stOrderID = frm!OrderID
                    Else
                        strMsg = strMsg & "Order already committed" & vbCrLf
                    End If
                Else
                    strMsg = strMsg & "No sales order initialized" & vbCrLf
                End If
            Else
                strMsg = strMsg & "No purchase type selected" & vbCrLf
            End If
        Else
            strMsg = strMsg & "No member assigned for order" & vbCrLf
        End If
    End If

Next frm
    
If Not booPass Then
    If Len(strMsg) = 0 Then strMsg = "Sales Order From not open"
    MsgBox "Can not assign item to order !!" & vbCrLf & strMsg, vbOKOnly
End If
    
CheckSisterFrm = booPass

Me.SalesFormOpen = CheckSisterFrm

End Function

This code of course is for the specific issues I had to deal with. You will have to review and adjsut accordingly.

Richard

 

[dmuroff]

Thanks Willir..I'll take a look

I appreciate it!