Alternative approach for unbound Option Group - 2007 1

[JaredSangco]

All,

after reading and searching the forums, most of my findings indicate that you can't utilize unbound Option Groups for continous forms. Is there an alternate solution?

Please advice.

 

[ProgramError]

reply to your subform reply...
yes everything seems ok.
have you looked into majp's answer using to/from listboxes

Ian Mayor (UK)
Program Error
Always make your words short and sweet. Because you never know when you may have to eat them.

 

[MajP]

You have lots of choices, I just like that interface where I have a large list to choose from and I will choose a lot of options from that list. I can think of some other slick interfaces but most require code.

Again the very simplest is a continous form with a Category combo and a Class option group. The difference is that you select a category and a new record is created. All of the categories exist in the combo but you do not see them all at one time in a continous form view. Instead as you pick from the list you add a new record. Again this is the most common approach and only slightly different from what you are doing. When the list is long this approach is a little clumsy unless as you select from the list you remove that as a possible choice to add.

I disagree with ProgramError. This will open a form where you can then add records as I described, but not all listed.

 

[JaredSangco]

Ian,

Private Sub Commandbutton_Click()
DoCmd.OpenForm "nameofcatclassform", , , "partid = '" & [Forms]![mainformname]![PartID] & "'"
End Sub
[code]

How would set up my form - will it basically be the same as my subform?


[code]
reply to your subform reply...
yes everything seems ok.
have you looked into majp's answer using to/from listboxes

NO I have not. I'm trying to exhaust all my options

 

[JaredSangco]

MajP

 When the list is long this approach is a little clumsy unless as you select from the list you remove that as a possible choice to add.

How can I accomplish this. This will be very helpful so the categories can't be assigned a class twice.

 

[MajP]

Here is another approach with slightly less code.
1) Add a part
2) On the after update run an append query to add a partID and category ID to table tblPartsCat for all categories.
3) Open the form bound to tblPartsCat to all records for a specific part.
4) You will now have a have every category and you can have a bound option group to classID. Just like you wanted
5) Close the form and run a delete query to remove every record not assigned a class.

The code would be very simple.

Now if you want to add categories you run this form again and the append query will only assign a new record for a category that does not yet exist.

This is simpler to develop and code than the to from list, but I think less flexible.

 

[JaredSangco]

MajP and Ian,

First off..Thanks for your assistance!! In all honesty, I appreciate your assistance GREATLY.

Majp,

First...Let me see if this would work out for me

When the list is long this approach is a little clumsy unless as you select from the list you remove that as a possible choice to add.

How would I accomplish this


Secondly...Would you mind helping me with detail instructions on how I should do this

1) Add a part
2) On the after update run an append query to add a partID and category ID to table tblPartsCat for all categories.
3) Open the form bound to tblPartsCat to all records for a specific part.
4) You will now have a have every category and you can have a bound option group to classID. Just like you wanted
5) Close the form and run a delete query to remove every record not assigned a class.

 

[MajP]

This is how I set it up.
1) the user inputs a part on the part form.
2) the user click a command button to assign categories and classes. I am going to assign all the categories and then later delete any not given a class
3) Run the update query and open the form

Private Sub Command4_Click()
  ' Add all categories
  DoCmd.SetWarnings (False)
  DoCmd.OpenQuery "qryAddCategories"
  DoCmd.SetWarnings (True)
  'open form to specified part
  DoCmd.OpenForm "frmPartCatClass", , , "PartID = " & Me.PartID

End Sub

in the above code "qryAddCategories" is an append qry that assigns all the categories to tblPartCat for the given part on the form. If the part cat combination is already in the table it is not duplicated. This is the complicated part and may give you trouble if all our names are not Identical. (Note access for some reason does not like this query. I can run it and get the correct results, but after saving it I can not open it up in design view)

qryAddCategories
INSERT INTO tblPartCat ( catID, PartID )
SELECT tblCategory.catID, [Forms]![frmPartsInformation]![PartID] AS PartID
FROM tblCategory LEFT JOIN tblPartCat ON  (tblCategory.catID = tblPartCat.catID) AND ( tblPartCat.PartID =  [Forms]![frmPartsInformation]![PartID])
WHERE (((tblPartCat.PartID) Is Null));

So basically if you started with Part 1 on your main form you would create in tblPartCat something like
Part ID catID classID
1 1 null
1 2 null
1 3 null
1 4 null
to
1 60 null

Now my continous form frmPartCatClass opens to all records for Part ID one. Here is the forms query:

SELECT tblParts.PartName, tblPartCat.PartID, tblPartCat.catID, tblCategory.catName, tblPartCat.classID
FROM ((tblParts RIGHT JOIN tblPartCat ON tblParts.PartID = tblPartCat.PartID) LEFT JOIN tblClass ON tblPartCat.classID = tblClass.classID) LEFT JOIN tblCategory ON tblPartCat.catID = tblCategory.catID
ORDER BY tblCategory.catName;

on the form I show Part name (locked), catName (locked) and my option group bound to (tblPartCat.classID).

I think this looks like what you are describing.

4) The user then assigns classes to every part's category.
5) When he is done he closes the form and anything not assigned a class is deleted

Private Sub Form_Close()
  DoCmd.SetWarnings (False)
  DoCmd.OpenQuery "qryDeleteNoClass"
  DoCmd.SetWarnings (True)
End Sub

6) the delete query looks like this

DELETE tblPartCat.classID
FROM tblPartCat
WHERE (((tblPartCat.classID) Is Null));

I think this is a common dilemma. Bound controls are easy to show what records are present, but it often takes a little code to show what records are not selected or remaining.

 

[JaredSangco]

MajP..

I'll give it a shot first thing 2morrow morning. Due to 5 kids including a wife, I won't be able to experiment until then.

In any case, how would I accomplish the following..

When the list is long this approach is a little clumsy unless as you select from the list you remove that as a possible choice to add.

AGAIN..AGAIN..and AGAIN...THANKS so MUCH for your assistance.. You just don't know how much I appreciate it.

 

[MajP]

That is probably the easiest approach. You would choose from a combobox a category to add. As you select a category and add it to a part number in tblPartCat the next time you go to the pulldown that part is not in the options. This is down using a query to populate the combobox that in pseudo code is something like

Select catID, CatName from tblCategories where (catID and the current partID are not in the tblPartCat already)

so if you are working on Part 1 and the table looks like this

1 1 1
1 2 3
1 5 1

you pull down choice will not list cat 1,2 and 5 (the things you already selected)

If you can build the form as described with the combo for cat and option group for class it would be easy to talk you throuhgh the combo row source.

 

[JaredSangco]

Majp,

Good Morning..

If you can build the form as described with the combo for cat and option group for class it would be easy to talk you throuhgh the combo row source.]

Regarding the above. My form seem is set up, but I am not using a 'option group' for class. Using a option group for class is my prefered choice, however, after reading some threads, you can only use a Option Group if your PK is numeric. My PK is 'text'. Would there be any way to make an option group work?

Also, I'm ready when you are for the combo row source...

 

[MajP]

I confused
[quote
ClassId ClassName
1 Small
2 Mid
3 Large
[/quote]

What is your PK? I have to assume it is classID which appears to be numeric.

 

[JaredSangco]

MajP..

You are not confused. A couple of days ago, our Program Manager updated the table to reflect 'sm', 'md', 'lg' as the key due to customers request. I failed to mention it to you since it wasn't a biggie. I apologize.

 

[MajP]

Not sure if you are still interested, but this form demos several ideas

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html

open the form frmPartsInformation
on the main form you can add and navigate through the parts. On the subform you can add categories and classes.

1) Listbox on the mainform to add a category on the subform. List box only shows categories which have not already been added.
2) A command button to add all categories
3) A command button that removes all categories if you do not assign a class
4) a combo box that adds a category and only displays those categories not selected
5) Pseudo bound option group for assigning a class
6) or a listbox for assigning a class

This form is a demo, I do not think you would use all of these controls at one time.

 

[JaredSangco]

MajP,

WOW. I'm impressed. Thanks a bunch for takin the time to create this small demo. I'll for sure be utilizing this.

I have a few things things to do before working with your sample, so I'll let you know once I get started.

AGAIN...Thanks a bunch

 

[JaredSangco]

MajP,

2 things.

1)What property setting do i set to remove the bottom 'search' border and new record border?

2) Currently I have two cmdbutton on a Main Interface. One opens the "PartInformation' form but will strictly be for Data Entry. I've set this form with a 'Data Entry' property set to 'Yes'. For my second button, I want to utilize the same form, but will be for 'updating', therefore, I would like to have a 'search list box' for all existing parts on the top your form. Help on this, please?

 

[MajP]

Take a look at the "allow navigation button" property of the form. Set to "no". Set the form and subform "allow additions" to false

 

[MajP]

Something like:

Private Sub lstFind_AfterUpdate()
  On Error GoTo errLabel
  Dim rs As DAO.Recordset
  Set rs = Me.RecordsetClone
  rs.FindFirst "PartID = " & Me.lstFind
  bk = rs.Bookmark
  Me.Bookmark = rs.Bookmark
  Exit Sub
errLabel:
  MsgBox Err.Number & " " & Err.Description
End Sub

This could go on top of the form I provided, and you can then get rid of the navigation buttons on the bottom of the form.

 

[JaredSangco]

Majp,

I'll give it a shot in the morning. Thanks

 

[MajP]

Some more features.
1) Remove categories
2) navigation by listbox only
3) No blank records

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.

 

[MajP]

try this instead

http://www.4shared.com/dir/6086223/c26decf2/PartCatClass.html