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.

 

[MajP]

If you had to you could pseudo bind it.

1) add an extra field to your table. Lets say your field is "color" with choices "red", "white", "blue". You could add another field call "colorValue" and save values 1,2,3. Now your option group is bound to colorValue and the labels of the option group are "red", "white", and "blue". When you close out the form run update queries to populate "color" with the corresponding text associated with the colorValue field.

2) Or you could just have a field in your main table that is "fk_colorValue" and then another color table with primary keys 1,2,3. You then can just link to the main table by color ID.

 

[TheAceMan1]

How are ya JaredSangco . . .

[blue] . . . my findings indicate that you can't utilize unbound Option Groups for continous forms. [purple]Is there an alternate solution?[/purple][/blue]

[blue]Anything unbound in the detail section of a bound form is a no no![/blue]

[blue]So whats wrong with bound . . . which would work![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[JaredSangco]

HI all,

If you haven't noticed, this is my 3rd if not 4th thread regarding my goal. I have accomplished a lot in the past few days, but now, I'm stuck.

The AceMan1 and MajP and others, I hope you can assist. I have 1 strand of hair left, and I really don't want to pull my last strand out - that would make be BALD.

Now...just in case you have not read my previous threads, here is my table structure...

[tblparts]
PartID (autonumber)           PartsName     LocationID     
AutoNum  1                   Brakes        1               
AutoNum  2                   Shocks        2               
AutoNum  3                   Tower         1

[tblPartCat]
PartId "from [tblparts]"       CatID           ClassID
1                              1              1
1                              2              2
2                              1              3
2                              4              3
3                              3              3

[tblCategory]
CatId                CatName   
1                    Outer
2                    Frame
3                    Cross
4                    Inner


[tblClass]
ClassId              ClassName
1                    Small
2                    Mid
3                    Large

Now, i have 2 forms....1) frmPartsInformation] where the user inputs a part. In this form, the user inputs a part which is a 'free form' text, assigns a location and I have a cmdButton that saves the record in the [tblParts] table. This works fine. My issue is with the second form [frmCategory].

2) Once the cmdbutton is clicked, it takes me directly to another form [frmCategory]. I have set the form to 'continuous'. In this form, i added the
a 'txt box' that list all the 'CatName' from [tblCategory] and a 'option group' that list the values from [tblClass] which is [unbound].

I also include a 'text box' that passes the 'PartID' from the form [frmPartsInformation] since I will need this autonumber to insert the record to the [tblPartCat].

Here's my issue......Since this is strictly an 'Input' form, i need to assign each CatName to a Class ID. (picture the frmCategory form as a Survey form where each CatName needs a user to select a Class. Since I now have the 'PartId' and the CatId on this form, all i need is to make sure that each unbound classID that the user select corresponds to each row. Currently, the unbound option group when selected is the same for all the CatName.

I need each row to be treated as a separate row and once each part is assigned a class, it will be inserted to table [tblPartCat].

Can you please assist.....

 

[MajP]

I might be missing something here, but either I am missing something or you are making this to hard. You seem to have a logical setup to easily bind a option group.

The really easy way to do this is to have frmPartsInformation be a main form subform setup.

The main form is bound to tblParts. The subform is bound to tblPartCat. If you want you can make this form only open to a specified part, if you do not want the user to work on other parts. In the subform you can choose/add categories and class which are bound to classID and CatID

On the CatID you use an option group and bind it to CatID. On the classID you use a combo box with the row source coming from tblClass. Now the trick on the combo is to return 2 fields in your query classID and className. Your combo is bound to classID but you set your column widths to 0";"1". You will see the name but you will put in your table the number.

 

[JaredSangco]

Majp...

I have a few questions with the above.

1) For CatID, I have approximately 60 categories that a user will have to assign a classification to those parts. Therefore, using a Option Group may not be a good idea. This is the reason why i chose to pull the catId and Catname directly from [tblCategory] and setting the form to continuous so all names will be visible to the user. Basically, the user will ONLY need to assign a Class to each Category.

2) Not being to familiar with Subforms, is it possible to remove all the borders when including them to a MainForm making it seem that it is ONE form?

 

[JaredSangco]

Can anyone help?

 

[ProgramError]

It sounds as though you don't fully understand the purpose of and option group. you select one option from a number of options but I would minimise the number of selectable options to say 8 or 10. If you have more than this then you need to start looking at a combo box to select the option. you can use the combobox in a continuous form but it will need to be 'bound' to a field in the recordsource table/query where it will store the data. The subform solution is a better option.

If my understanding is correct you have records in a main table which have parts required to be assigned a category chosen from a list of 60 categories. Each of thes categories have a predefined class. Am I correct? or do you need to change the class, for each category, for each part?

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

 

[ProgramError]

Answer to question 2. Yes subform can have no border, close ,min and max buttons look in the properties of the form.

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

 

[ProgramError]

Majp has given you the solution...

Main form
record source tblParts

subform
record source tblpartcat

combobox in subform for class
recordsource tbl class

Tip...
"the user will ONLY need to assign a Class to each Category" textbox locked property set to no

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

 

[JaredSangco]

Ian,

Thanks for responding and coming to the rescue.

1) Just for clarification. So there is no way that I can list all categories on a single form and have the user assign a class to it every time?

 

[ProgramError]

You can but you need a subform to list the categories and class for ech of those categories. Do you understand the principles of a subform?


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

 

[ProgramError]

Take a look at

http://office.microsoft.com/en-gb/access/HP051883951033.aspx?pid=CH063650401033

hope this helps

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

 

[JaredSangco]

Ian...

I am using a subform.

The Mainform works fine. Its the subform that I am having issues with. This is what I have done.

[SubFormPartsClassifiation]

1) Using the [tblPartCat] table, I added PartId, CatId, And Class Id.

2) For PartID, i set the following control source to: =[Forms]![FormPartInformation]![PartID]

3) I added a combo box [cboPartCatName] and bound it to CatId. I then suppressed the CatId field on the form.

4) I then added a combo box [cboClassName] and bound it to ClassId. I then suppressed the ClassId field on the form.


Is this correct so far?

 

[MajP]

I am a little confused, but I think I got it.

A part could have have from 1 to approximately 60 categories. So you would like to choose all of the categoires from a list. For the ones you choose you need to assign a class.

My original design does this because you choose each category that you want to add in a subform from a drop down list and you choose the class from an option group. This is the most common interface, but not necessarily the easiest to use. Actually this does not have to be a subform. It can simply be a continous form bound to tblPartCat filtered to only show those records associated with the specific part.

Howver, I think you want a design where you can see all of the categories and choose from that list. My interface of choice (if I understand you correctly) would be a "To From" listbox. After I enter a part name I would have two multi select listboxes side by side and buttons that allow me to move from the left list box to the right listbox selected values (like you see in some of the access wizards). I have posted a FAQ on this subject. This allows you to see all of the possible categories and the ones selected. Once you move everything from left to right the left list box should have remaining categories that are not associated with a part and the right would have all categories associated with a part.

Once you choose all of your categories you run an append query that will add a partID and a catID to tblPartCat for every category. Then after the query runs it closes that form and opens an assign class form which is bound to tblPartCat and filtered to show records for the selected part. This would now be a continous bound form with each category showing and a bound option group for the class.

 

[MajP]

My assumption is that a part does not have all categories, but if a part is assigned all categories than this is a little easier.

1) You add a part.
2) After part is entered it runs an append query to append a partID and catID into tblPartCat for each category
3) Now open a continous bound form of tblPartCat. Every category will be listed and you can have a bound option group.

 

[ProgramError]

subform record source = [tblPartCat] table

2) For PartID, i set the following control source to: =[Forms]![FormPartInformation]![PartID]
this should be partId of the tblpartcat table not the mainform

everything else ok

check the parent/child links are partID

So everytime the record of the mainform is changed then the corresponding category/classes are shown in the subform.



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

 

[JaredSangco]

MajP

A part could have have from 1 to approximately 60 categories. So you would like to choose all of the categoires from a list. For the ones you choose you need to assign a class.

YES!!

Howver, I think you want a design where you can see all of the categories and choose from that list.

YES!! again

After I enter a part name I would have two multi select listboxes side by side and buttons that allow me to move from the left list box to the right listbox selected values (like you see in some of the access wizards). I have posted a FAQ on this subject. This allows you to see all of the possible categories and the ones selected. Once you move everything from left to right the left list box should have remaining categories that are not associated with a part and the right would have all categories associated with a part.

If this is my only option, then I guess I have no other choice. (Beggars can't be choosers) I would have rather have the user enter a part, then go to another form where all the categories are listed and alongside each category the user selects a class using an option group.

Is this still possible? If not, would be willing to help me with your suggesstion? Everything I know about Access has been only beenresearching this week and last week.

 

[JaredSangco]

IAN...

subform record source =  [tblPartCat] table

My 'Record source' is a query? Is this correct?

2) For PartID, i set the following control source to: =[Forms]![FormPartInformation]![PartID]
 this should be partId of the tblpartcat table not the mainform

I removed the control source and just added the PartID from tblPartCat.
everything else ok

check the parent/child links are partID

Both are set to PartID

 

[ProgramError]

I would have rather have the user enter a part, then go to another form where all the categories are listed and alongside each category the user selects a class using an option group.

Is this still possible?

Yes

instead of the subform use a commad button to open the cat/class form using the buttons' vba onclick event.

if partid is a text value use

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

otherwise use

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



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