suggestions requesting for a form layout 2

[irethedo]

I would like to make a form that contains four different lists of types of
items that are on a sales order and allow the user to match one of each of
the items up per type into one configuration.

So if an order contains a variety of PCs, software licenses, special software,
and other options, a list of each would appear on the form along with the quantity ordered
and the order line number.

The user would then be able to configure a quantity of systems of any combination of one
PC type, one license type, and any of the special software and options by selecting the quantity of each
and pressing a configure button which would create record(s) in a table containing the information chosen and
adjust remaining quantities on the form.

This is what I envision:

Suggestions Please

Thanks

 

[MajP]

I think I would simply due four listboxes. The PC listbox is single select, license is single select, the others are multiselect. The way you describe it items are either selected or not selected. But I see all the PCs with a qty of 1, and the same for licenses. But you say you can only select 1 pc and 1 license. Are any of those quantities ever more than 1? Where is the information for the lists of PCs, Licenses, Software, Options being pulled from? Is is a single table?

 

[irethedo]

I think I would simply due four listboxes. The PC listbox is single select, license is single select, the others are multiselect. The way you describe it items are either selected or not selected. But I see all the PCs with a qty of 1, and the same for licenses. But you say you can only select 1 pc and 1 license. Are any of those quantities ever more than 1? Where is the information for the lists of PCs, Licenses, Software, Options being pulled from? Is is a single table?

Thank you for the response.

The quantities (and items of all types shown) will change from order to order as will the quantities
per order.

Also, each of these items may appear more than once in different quantities on different
sales order lines and I would like to keep each item type separate and tied to the quantity and line number.

I may want to add a checkbox by each item type to select the item and then have the quantity ordered in
drop down boxes so that the user can select one type of each and the quantity to be configured. In this scenario, one record
will be created with all items selected for each of the quantity selected.

Right now, each of the types are in individual tables which can be changed if it makes sense to do this...

Thanks again

 

[dhookom]

I would anticipate every type and every item may change over time so I would hard-code as little as possible. Each of the four sections would be continuous subforms that allow any number of items (with scrolling if too many). The subform records would be filled based on a table of items and types. When the Configure button is clicked, there would be code to append the values from the subforms into your main table.

Without specifics regarding your table structures, I can't provide more details.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Thank you for the response.

I started down that path this morning by creating continuous subforms for each of these
but I don't remember how to set up the qty combo boxes to populate with the qty from the table
and also display 0 through the remaining qty...

 

[dhookom]

Are you planning on providing your table and field names? Almost everything I see on your prospective form is data that should be stored in your tables other than the words "Qty" and "Line"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Here is the table information:

SP_tbl
This table contains the data control sources for the Options subform:

ID	Auto Number 		(PRIMARY KEY)
SPNote	Text			Option part #
LineNO	Number			Sales order Line#
Desc	Test			Option Description
Qty	Number			Order qty
Checked	Yes/No

PC_frm_tbl
This table contains the data control sources for the PC subform:

ID	Auto Number 		(PRIMARY KEY)
PartNO	Text			PC part #
LineNO	Number			Sales order Line#
Desc	Text			Option Description
Qty	Number			Order qty
Checked	Yes/No

Lic_frm_tbl
This table contains the data control sources for the License subform:

ID	Auto Number 		(PRIMARY KEY)
License	Text			License part #
LineNO	Number			Sales order Line#
Desc	Text			Option Description
Qty	Number			Order qty
Checked	Yes/No

Per_frm_tbl
This table contains the data control sources for the software subform:

ID	Auto Number 		(PRIMARY KEY)
PartNo	Text			Persyst part #
LineNO	Number			Sales order Line#
Desc	Text			Option Description
Qty	Number			Order qty
Checked	Yes/No

NewData_tbl
This table contains the data control sources for the Main form:

ID	Auto Number 		(PRIMARY KEY)
SO	Text			Sales Order #
Cust	Text			Customer Name	
PartNo	Text			Ordered part #
LineNO	Number			Sales order Line#
Desc	Text			Option Description
Qty	Number			Order qty

I am not sure how to set up the qty combo boxe to display 0 through the qty in each respective table

Thanks again

 

[dhookom]

To get a combo box to display numbers, create a small table of numbers to use as the Row Source.

I would not use multiple tables when a single table and a column for "LI", "PC", ... is all that is required.

What happens when the Configure button is clicked?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

The combo box holds the quantity and has the forms control source (from the qty field) for each record of each
of the four subforms.

When the configure button is pressed (code not written yet) the selected items will be
placed into a table with a record for each item (per the selected qty). When this happens, the value of
what was originally the ordered quantity will have the qty selected subtracted from this initial value.

In the combo box, I would like for the user to select between 1 and however many of that type are available
so I am not sure how to set the Rowsource with a simple table column as you suggest...

How would I set this up?

Thanks again

 

[dhookom]

I couldn't find anywhere in your posts "however many of that type are available".

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

For each of the sub-forms, the combo box is currently set to the record source field qty
of the table that provides the record source for each record...

but I wasn't sure if there was an easy way to allow the user to select a value between 1
and whatever the field qty is in order to specify how many of the selected type will be configured.

In this case I was planning on code to reduce the combo box choices between 1 and the remaining quantity.


Update:
I tried setting the row source up with the qty field in my table but what I ended up with is a combo-box for each line
that can be selected for any of the values for that field that resides in thate table.

 

[MajP]

In this case I was planning on code to reduce the combo box choices between 1 and the remaining quantity.

You will need to look at this thread702-1779888

If you have a continuous form and filter the combo on different records, then what ever you filter out of the rowsource will appear to disappear from the other records.
Example. In the first record you choose 7 and the next record you filter the list to 1-3. Since 7 is no longer in the rowsource of the combo it will seem to disappear from record 1. In that thread there is a way to fake this.
The other approach is when you enter the quanity field you could pop open a small form that allows you to select the quantity. Then you can control the filter for the possible amounts.

 

[irethedo]

Thanks for the info. This looks a little tricky and I am wondering if it might be best to keep it simple and use numeric fields
instead of combo boxes.

Also, on my continuous subforms I have check boxes that are tied to the table that controls the record source. Is there a way to
limit only one checkbox to be selected on each continuous subform?

Thank you

 

[dhookom]

I would go through the trouble of displaying meaningful values rather than numbers.

How are "check boxes...tied to the table"? Are they bound to a field as the control source? If so, there isn't a built-in solution. I think you would need to run an update query or step through the form's recordset clone to unselect previously selected check boxes.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

I may have an example of what Duane is suggesting. But that is the idea to make a continuous form act like a frame control.

 

[dhookom]

Here is some sample code

Private Sub chkYesNo_AfterUpdate()
    Dim rsClone As DAO.Recordset
    Dim lngPrimary As Long
[COLOR=#A40000][highlight #D3D7CF]    'assuming your primary key field name is EventID[/highlight][/color]
    lngPrimary = Me.EventID
[COLOR=#A40000][highlight #D3D7CF]    'assuming your yes/no field name is WSIBYesNo[/highlight][/color]
    If Me.WSIBYesNo = True Then
        Set rsClone = Me.RecordsetClone
        With rsClone
            .MoveFirst
            Do While Not .EOF
                If !EventID <> lngPrimary Then
                    .Edit
                        !WSIBYesNo = False
                    .Update
                End If
                .MoveNext
            Loop
        End With
    End If
End Sub

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[irethedo]

Thanks Duane

That works great!