ACCESS FEED: [MS_AccessPros] Generating a new record

Senin, 05 Oktober 2015

[MS_AccessPros] Generating a new record - via button or other method?

I'm building a database for a furniture manufacturer to define the parts needed to to fulfill orders for beds.
(I've tried posting this before, but got no help addressing the issue I'm having, so I'm asking again.)
I uploaded a zipped copy of the database (I'm using Access 2007) to the "Help Needed" folder on Sept 18, file name TableBedDB_6.zip

I have two forms which have multiple drop-down menus to select values for:

    Bed Type (dozens of choices)
    Bed Size (Queen, King, Single, Twin)
    Bed Style (Headboard only, Mattress/Box Spring, Platform, with drawers, etc.)

The first form has a sub-form with one record for each of the modules (headboard, matching footboard, low footboard, sides/middle, etc.) that make up the item(s) specified by the drop-downs.
[[By defining modules, a headboard can be part of a full bed or a "headboard only" order, but its parts are defined once.]]

This first form can be used to add a module to the selected type/size/style by selecting a module type from a drop-down in the subform.
I use the "On Change" Event (of the subform drop-down) to trigger the Event Procedure

Private Sub cbxModType_Change()
    Me.BType = Me.Parent.cbxBedType
    Me.BSize = Me.Parent.cbxBedSize
    Me.BStyle = Me.Parent.cbxBedStyle
End Sub

This all works to create a new record in the sub-form, with all 4 values filled in. (The underlying query & table have just the 4 fields.)

The second form has a fourth drop-down to select values for Module, and is used to show & specify the parts (individual pieces of wood) that go into making a given module. Each record, in addition to the four fields specified in the drop-downs, has ten other fields.
I tried using code similar to the above (with additional line to set the value for Module) tied to the "On Change" Event of the one drop-down in this subform, for the type of piece that's being specified. It does NOT work.

What I need is some way to get a new record created in the table behind the query behind the subform. I've tried multiple ways, none so far have worked.
My next try is going to be having a fifth drop-down on the main form, for Piece, but not tied to any field. Then add code (probably tied to a button) to add a new record using the 5 values specified by drop-downs. I'd like to then change the Focus to be the next field in the new record in the subform. It's going to take me a while to work out the code to do all this. Maybe this isn't the best approach?

If you do download and try the database, stick to Queen for size, and Platform or Mattress/Box Spring for style. Other choices are more likely to lead to an empty subform at this point.

Any help would be gratefully appreciated.
Sigurd Andersen