data entry in a junction table

[Colin@Toyota]

Hello all,

I just wanted to run this by some experts before I go mucking around in my db. Basically, I am creating a data entry form to populate with information about car parts, and the vehicles they apply to. This is what I am working with:

[B]tblParts[/B]        [B]tblModelsParts[/B]        [B]tblModels[/B]
PartID            ModelPartID          ModelID
PartNumber          PartID            ModelNameID
PartDesc            ModelID           ModelYearID

I am trying to build a form that will allow me to enter new part numbers and descriptions into the table, and then have the user select (through a listbox?) all the vehicles the parts apply to. Is there a way to populate the junction table with each combination of partID and ModelID automatically? NOTE: tblModels is already populated with data.

I am assuming there would be a way to do this... A query of the junction table (and tblModels?) as the recordsource for the form, and then a subform with the tblParts as its recordsource?

I would have posted this in the massive thread I have going already... but I thought it might be nice to give Craig and Adam a break from helping me

 

[pbaldy]

Not that it matters, but I just went live with a maintenance/parts inventory app that includes something like this (mine includes year as well as model). In any case, if you search for "multiselect" you should turn up the code to loop through the value(s) selected in the listbox. You can either open a recordset and use .AddNew or execute an append query within that loop.

 

[CraigDolphin]

I wouldn't use a listbox. I would use a single form view form bound to tblParts, and a subform in continuous forms view bound to tblModelsParts. Use PartID as the master/child key between the form and subform.

On your subform, make the PartID control invisible, and use a combo to lookup the relevant modelID from tblModels (the combo sql should also reference tblModelNames and tblModelYears to make it human readable).

No code required.

 

[pbaldy]

That would work Craig, and my users have that type of option too, but it quickly becomes a PITA when something fits several models and several years each. Much easier to click on the listbox items and hit a button. I guess only the OP can say if that fits their situation.

 

[CraigDolphin]

Fair enough. I take your point that sometimes it could be easier for data entry to do it that way.

I suggested the subform solution since it also allows you to see any existing model associations while viewing the part. Of course, you could code it so that the on_current event of the form selects the relevant items in the listbox to show those associations. But then you'd also need to code to deal with changes if the user unselects a previously selected item. Maybe you'd want to first delete all records in tblModelsParts where PartID = <thispart> then run an append query to add a record for each currently selected items.

I know that Colin is working to a deadline (this friday) so I just figured that quick and easy for him might be the order of the day.

 

[pbaldy]

I don't bother trying to keep the listboxes updated (I have 2, models and years). They can use them to add new combinations, but they view/edit the existing choices in a subform exactly as you described.

I certainly understand about deadlines, and in retrospect if Colin only has models and not years, the listbox might be overkill.

 

[CraigDolphin]

To clarify, in Colin's db, a ModelID represents a unique combination of ModelName (eg Corolla) and ModelYear (eg 2003). So a 2003 Corolla (modelID 5) is a different model than a 2004 Corolla (model ID 6).

I hadn't realized you were also using a subform along with your listbox. That approach makes perfect sense to me. Best of both worlds!

Even if he doesn't have time to do the listbox before the friday deadline, it could easily be added to the form later to help speed up data entry.

Incidentally, I hadn't realized you were an MVP. Congratulations on your achievement

 

[Colin@Toyota]

Paul,

I tried searching for multiselect, and something like 14 pages of threads came up. Would I find this code in a thread you started? Or in a response to someone else's thread?

Thanks

 

[Colin@Toyota]

I wouldn't use a listbox. I would use a single form view form bound to tblParts, and a subform in continuous forms view bound to tblModelsParts. Use PartID as the master/child key between the form and subform.

On your subform, make the PartID control invisible, and use a combo to lookup the relevant modelID from tblModels (the combo sql should also reference tblModelNames and tblModelYears to make it human readable).

No code required.

I like the no code part...

I was thinking there might be a way to use a form for parts that is in a datasheet view (for ease of data entry - especially in the case for an SETR where there might be several parts that are related to several models). Then, with a command button, another form would be opened with the two list boxes where the user could select multiple models and years. If this is going to be super complicated... I guess I won't do it, but it would really improve the data entry part of this db. Because even if I could get that part to work, they still have to go into the SETR form and apply all those parts to an SETR...

 

[CraigDolphin]

Colin,


I think there's four scenarios to consider.

1. The user wants to add a new part or parts to the database (new record in tblParts).

2. The user wants to add a new model or models to the database (new record in tblModels).

3. The user want to associate one or many existing parts, with one or many existing models (new records in tblModelsParts)

4. The user wants to view/edit model associations for existing parts and models.

For scenarios 1 and 2 I would create forms in either continuous form or datasheet view that are bound to tblParts or tblModels respectively, and that open in dataentry mode.

For scenario 3 I would have an unbound form that contains two or three multiselect listboxes. One lists all parts, the other(s) list all models (or model names and model years). Once the user has picked the combinations to add, then run some code to append records into tblModelsParts based on the user selections. [EDIT]This part can easily be added at a later time.

For scenario 4, I would have a main for bound to tblParts, and a subform bound to tblModelsParts, as described previously. (And/or you could do it with a form bound to tblModels and a subform bound to tblModelsParts so you can see all the parts for each model instead). This will let the user see all the associations, and add or delete records from tblModelsParts as necessary.

Now, using the form subform arrangement will let you add all the records you need without the listboxes etc, but it will be more time-consuming to enter the information that way.

 

[Colin@Toyota]

1. The user wants to add a new part or parts to the database (new record in tblParts).

There will never be a case where parts are added to the db, and not appended to tblModelsParts. The only reason to add parts to the db is to relate them to models and recover them. I guess then 1. and 3. will be the same thing.

2. The user wants to add a new model or models to the database (new record in tblModels).

I will be creating this form to input new models (and their VDSs) as soon as I get the above figured out. (This part is pretty straight forward).

4. The user wants to view/edit model associations for existing parts and models.

Unless I am underthinking this, even if a wrong model-part combination is entered in step 1, as long as it doesnt get selected in fmSETR and its subforms, everything should work out. However, being able to relate new models (2009, 2010, etc) to a part would be useful...

For scenario 3 I would have an unbound form that contains two or three multiselect listboxes. One lists all parts, the other(s) list all models (or model names and model years). Once the user has picked the combinations to add, then run some code to append records into tblModelsParts based on the user selections.

Would there be a way to combine this with #1?

For scenario 4, I would have a main for bound to tblParts, and a subform bound to tblModelsParts, as described previously. (And/or you could do it with a form bound to tblModels and a subform bound to tblModelsParts so you can see all the parts for each model instead). This will let the user see all the associations, and add or delete records from tblModelsParts as necessary.

That should work perfectly.

Now, using the form subform arrangement will let you add all the records you need without the listboxes etc, but it will be more time-consuming to enter the information that way.

Since I am trying to cut down time with the creation of this db, I think I will attempt the list box method.

NOTE:
I would like to have this done... or at least in a demonstratable fashion to show my manager by this Friday, but it is not necessary. I have already made arrangements to take a laptop loaner with Access and my db loaded on it home over the holidays so I will have time to fiddle and populate it with relevent data.

 

[CraigDolphin]

Colin,

before you can make association between a part and a model, you must first input that part (or that model) into the db. They are two distinct steps that must occur in a specific order.

However, there's nothing to say that closing the form for scenario 1 or 2 can't automatically open up the form for scenario 3 or 4 to do the next bit.

The reason you can't have it all in one form is that you cannot place a subform on a form that is in datasheet view or continuous form view. You stated you might want to input many parts at once so that requires continuous or datasheet view in your main form...

Perhaps I should clarify: the form in scenario 4 is in single form view. You will be seeing all the models associated with ONE part at a time.

 

[Colin@Toyota]

Perhaps I should clarify: the form in scenario 4 is in single form view. You will be seeing all the models associated with ONE part at a time.

Ahh I see.

 

[Colin@Toyota]

The reason you can't have it all in one form is that you cannot place a subform on a form that is in datasheet view or continuous form view. You stated you might want to input many parts at once so that requires continuous or datasheet view in your main form...

What about a form with two list boxes (for model and model year), with a subform in datasheet view for the parts? or what about two subforms?

I'm just curious...

 

[CraigDolphin]

You can place a subform on an unbound form, but you cannot set master-child key between the two.

 

[Colin@Toyota]

For scenario 3 I would have an unbound form that contains two or three multiselect listboxes. One lists all parts, the other(s) list all models (or model names and model years). Once the user has picked the combinations to add, then run some code to append records into tblModelsParts based on the user selections. [EDIT]This part can easily be added at a later time.

Is there an easy way to filter for the records most recently added to tblParts? Save the PartIDs as something? I could see this listbox getting reaaaally long after a while...

 

[pbaldy]

Incidentally, I hadn't realized you were an MVP. Congratulations on your achievement

Thank you Craig!

I tried searching for multiselect, and something like 14 pages of threads came up. Would I find this code in a thread you started? Or in a response to someone else's thread?

Here's the relevant code from mine (2 listboxes):

  Dim ctl                As Control
  Dim varItem            As Variant
  Dim ctl2               As Control
  Dim varItem2           As Variant
  Dim strSQL             As String
  Dim db                 As DAO.Database

  On Error GoTo ErrorHandler

  If Me.lstModels.ItemsSelected.Count = 0 Or Me.lstYears.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 model and year"
    GoTo ExitHandler
  End If

  Set db = CurrentDb()
  Set ctl = Me.lstModels
  Set ctl2 = Me.lstYears

  For Each varItem In ctl.ItemsSelected
    For Each varItem2 In ctl2.ItemsSelected
      strSQL = "INSERT INTO tblPartApply(PartNumber, Model, Year) " _
               & "VALUES('" & Me.txtPartNum & "', '" & ctl.ItemData(varItem) _
               & "', " & ctl2.ItemData(varItem2) & ")"
      db.Execute strSQL
    Next varItem2
  Next varItem

  MsgBox "Combinations added"

And someone will inevitably mention the reserved word, and I'll blame the developer of the main database. I just added functionality to it.

 

[Colin@Toyota]

And someone will inevitably mention the reserved word, and I'll blame the developer of the main database. I just added functionality to it.

OMG! You have "Year" as a field name!! Haha

Thanks a million!!

 

[CraigDolphin]

Colin,

sometimes it's just easier to do something than to explain it. So, here's a new version of your db with all the forms and with the listbox form using Paul's brilliant code (tweaked slightly to avoid double-entering records in tblModelParts).

You'll see the button to launch the new form(s) on subformSETRModelParts. Have only done some cursory error checking. You will also need to give the user the ability to add new model names and new model years. I would suggest researching the notinlist event to accomplish that.

 

[pbaldy]

tweaked slightly to avoid double-entering records in tblModelParts

I guess I should have specified that I made the 3 fields a compound key, so duplicates simply fail silently, which is what they wanted.