MS Access Populate Subform

Question

Hi.

Thank you in advance for any assistance you may be able to provide.

My project is a Access DB that collects consumption/inventory data for hospitality bars.

My issue is creating populating a subform based on some of the data entered in a mainform.

The mainform has entry fields for basic event information, the fields I wish to use to populate the subform are; number of bars (number of bars for an event), and Bar type (the type of liquor the bar will be stocked with).

The number of bars could be any number, and a basic bar type consists of a total of approximately 20 different liquors, beers and wines.

What I would like the subform to display is a row for every bar, and every type of liquor. At the same time I would like to have entry fields in the same subform for consumption purposes, IE: the bottle weight of Bacardi rum allocated to bar number 1.

Example:

Bar Number | Product Name | Beginning Weight

        1              Bacardi Rum        

        1              Tanq Gin

        2              Bacardi Rum

        2              Tanq Gin

With the beginning weight entered by the user.

These rows will ultimately be blown into a Consumption Detail table, and subsequent information entered upon completion of the event.

I am ultimately trying to limit the amount of data that has to be entered (beginning weight only) as there may be as many as 30 bars, and entry even with the use of a lookup table/combo box can be time consuming.

Again, thanks for any help you give me.

Answer 1

I have to disagree with your disagreement, Scott.  Bars constitute an a entity type, not an attribute.  Even if the entity type has only one attribute, BarNumber, it's nevertheless an entity type, and as such should be modelled by a table.

Looking again at the original post, I think I would on reflection modify the logical model a little.  The OP refers to bar types, so the Bars table would have a BarType column referencing the key of a BarTypes table.  There would then be a BarTypeProducts table which would model the relationship type between BarTypes and Products, eliminating the redundancy which would exist in a BarProducts table if, as the OP implies, there can be a number of bars of one type all stocking the same range of products.

The representation of the model in a form/subforms and the methodology for the insertion of rows into the EventProducts table would be similar, requiring only a slightly different SQL statement for the latter.

Answer 2

I very rarely disagree with Ken, but I'm going to have to here. I really don't see the need for the Bar table. As I understand the need and event may have a number of bars setup. This number will vary from event to event. Unless there is a need to track more information about a bar than has been referred to, I don't see the need for a separate bar table. I think the only need that a record be created for each bar # is because usage will be tallied per bar. 

Of course the OP can decide whether a bar table is needed or not.

Otherwise, Ken's solution is just about the same as mine.

Answer 3

This is essentially a question of getting the logical model right and then representing the model in a form/subforms.  You start with tables for the three principle entity types:

Bars

....BarNumber (PK)

....BarName

Events

....EventID (PK)

....EventName

....EventDate

Products

....ProductID (PK)

To model the many-to-many relationship type between bars and products, i.e. the products stocked by default in each bar, you need a table:

BarProducts

....BarID (FK)

....ProductID (FK)

the primary key of this table is a composite one made up of both columns.

To model the many-to-many relationship type between bars and events you need a table:

EventBars

....EventID (FK)

....BarID (FK)

The primary key of this table is again a composite one made up of both columns.

You then need a table to model the relationship type between EventBars and BarProducts:

EventProducts

....EventID (FK)

....BarID (FK)

....ProductID (FK)

....BeginningWeight

The primary key of this table is again a composite one, this time made up of the three columns EventID, BarID and ProductID.  EventID and BarID make up a composite foreign key referencing the composite primary key of EventBars, and BarID and ProductID make up a composite foreign key referencing the composite primary key of BarProducts.

In an events form, in single form view, you'll firstly need a subform, in continuous forms view, based on EventBars, linked to the parent form on EventID and a further subform in continuous forms view, based on EventProducts.  In the first you enter one row per bar used for the event.  The EventProducts subform needs to be correlated with the EventBars subform so that as you navigate from row to row in the latter, the EventProducts subform will be requeried to show the rows for the currently selected bar.  This is done by including a hidden text box in the parent form, txtBarIDHidden say, whose ControlSource property references the BarID control in the EventBars subform, e.g.

=sfcEventBars.Form!BarID

where sfcEventBars is the name of the subform control, i.e. the control in the parent form which houses the subform.  The LinkMasterFields property of the EventProducts subform will then be:

EventID;txtBarIDHidden

and its LinkChildFields property will be:

EventID;BarID

To automatically insert rows into the EventProducts subform you can execute an SQL statement in the AfterInsertEvent of the EventBars subform:

Dim strSQL As String

strSQL = INSERT INTO EventProducts(EventID, BarID, ProductID) " & _

    "SELECT " & Me.EventID & ", BarID, ProductID " & _

    "FROM BarProducts " & _

    "WHERE BarID = " & Me.BarID

CurrentDb.Execute strSQL, dbFailOnError

' requery EventProducts subform to show new rows

Me.Parent.sfcEventProducts.Requery

This will show rows in the subform for all products stocked by default in the bar in question.  If there are any which will not be available for the current event you can delete those rows in the EventProducts subform.

In the relationship between EventBars and EventProducts be sure to enforce cascade deletes so that if a row ids deleted in the EventBars subform the rows in EventProducts for the bar in question will automatically be deleted.

Answer 4

OK, First You need at least three tables for this functionality:

tblEvent

EventID (PK Autonumber)

EventDate

EventLocation

NumberofBars

other info abouth the event.

tblLiquor

LiquorID (PK Autonumber)

LiquorName

other info about the liquor.

tblEventLiquors

EventLiquorID (PK Autonumber)

EventID (Foreign Key)

LiquorID (FK)

BarNumber

BeginWeight

EndWeight

Your main form will be bound to tblEvent. The subform to tblEventLiqour and linked on EventID

On your main form you would have a button to populate the Bars. The code behind that button would look like this:

Dim strSQL As String

Dim i As Integer

strSQL = "INSERT INTO tblEventLiquors (LiquorID, EventID, BarNumber) " & _

"SELECT LiquorID, " & Me.EventID & " AS EventID, " & i & " AS BarNumber " & _

"From tblLiquor;"

For i = 1 To Me.NumberofBars

CurrentDB.Execute strSQL, dbFailOnError

Next i

Me.subformname.Requery

The Subform should have a combobox for LiquorID so it displays the liquor name.

This should add a record for each liquor for each bar that you indicate will be there.

Answer 5

Use a form to enter data to identify your event and add a Yes/No field to your Bar/Beverage table that you click.  Then run an Append query to add records to your Event table to create the records needed for the event.