Listing Values of a table line by line 1

[JaredSangco]

Fellow experts,

Along with many things, this is what I'm trying to do.

I have a form [frmPartsInfo] that contains Parts information that the user will be inputing. Once the Parts are inputed, I have a Save button [cmdSave] as well as another button [cmdEnterPartsClassification] to open a new form [frmPartsClassification] that is linked by [PartsId]. (The parts table [tblParts] and classification table [tblPartsClass] have 1 to Many relationships)

For frmPartsClassification, i want to list all the values from the table [tblPartsClass] line by line instead of using list boxes. Reason for this is for each Part Classification, the user needs to assign 3 Categories - Use of an Option group or radio button is needed here. Is this at all possible?


I'm stuck. I've searched the forums with no luck on this particular specification? Your guidance, input, advice, assistance is appreciated. I have minimal VBA access as stated before.

 

[fneily]

You have stated that A Part has many Classifications. And you're also saying that those Classifications ONLY belong to that Part. Hard to believe. Are you sure?? No other Part belongs to any of those Classifications? Are you sure you don't have a many-to-many relationship - a part has many classifications and a Classification can belong to many parts?

Also, each Classification can have 3 categories. Are those the ONLY categories? Or are you saying 3 out of 10?

Please supply more precise explanation so we know what's going on.

 

[JaredSangco]

Fneily,

Thanks for your response. Maybe this helps (taken from my previous post). I may have confused myself. I apologize.

[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

As you can see, tblClass and tblCategory are basically look up tables only, therefore, the only table that needs to get populated is [tblPartCat].

I would like to list each [tblCategory.CatName] on the [frmPartsClassification] form and next to each item have a Radio button or Option Group from [tblClass] - "small, mid, large"

Does this make more sense? I hope so, but please feel free to ask me any questions. I am basically STUCK and need all the help i can get.

Thanks in advance for patience and assistance.

 

[ProgramError]

Hi JaredSangco

Using tblParts as your record source for the form and the form set to continuous form, open the field list and drag the CatID field onto your form. use the wizard to create an option group with tblClass as the source, Small, Mid and large as the labels, then save the result in the ClassID field.


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 your assistance. A few questions..

1) What does setting the form to 'continous' do?

2) Where in the properties sheet could I set 'Continuous' on the form? I can't seem to find it.

 

[JaredSangco]

Ian,

Excuse my previous response. I found the 'Continuous' property on my form. I'm going to work with it a bit and see what I can come up with.

 

[JaredSangco]

Ian et all,

Ok..this is where I am at. Ian, thanks for your assistance.

I'm almost there with this, so please bare with me.

As mentioned above... I now have functional Parts Information form [frmPartsInformation] that a user will input and save the record.

Now, that I have a form [frmCategory] that list all the Category Names [tblCategory.CatName] and a Option Group Button [optgroupClass] next to each Category Name, I am ready to assign each NEW part to a Category and Class.

Trick here would now involve VBA, i believe, which I have minimal knowledge so your expertise and patience is very much needed.

Question 1.

The two tables that I will be storing to my tables are [tblparts] and [tblPartCat]. [tblParts] is DONE!! Issue now is with [tblPartCat].

I've created a button in the [frmPartsInfo] that opens the [frmCategory]. I need to pass the 'Newly' created (AutoNumber PartID) to frmCategory since I will need the PartID to populate [frmPartCat.PartID]. How can I assign this PartID to frmCategory?

Question 2.

For each Category listing on [frmCategory] - once the user assigns a Class to each listing, a record will be inserted to the [tblPartCat]. NOTE: on frmCategory, i have the tblCategory.CategoryID and tblClass.ClassID just for reference but will most not be visible to the user.

A new record for the [tblPartCat] would look like...

[tblPartCat]

(Autonumber) PartID            CatID           ClassID
1                                1                2
1                                2                3
1                                3                4

I would need to make sure each 'OptionGroup' ClassListing is linked to each Categorylisting.

Can anyone advise what the best approach will be? Should I insert each row one at a time after a user assigns a class to each category OR should I have a 'bulk' insert the user assigns all Categories a Classification.

 

[JaredSangco]

Here's an update fellow gurus'

I have answered my first question...I was able to pass the PartID autonumber field from the form [frmPartsInformation] to the Category form [frmCategory] by adding a text box field on frmCategory and setting the control source of the text box to =[Forms]![frmPartsInformation]![PartID]


What I really need assistance with is my second question.. Would you kindly assist.

I believe I would need an insert statement to load each partID, CatId, and Class Id to [tblPartCat]

Trick here is that my option group [optgroupClass]has to be linked to each of the 'CatIDs'. How do I accomplish this.

Right now my [frmCategory] looks like this

PartId tblCategory.CatID tblCategory.CatName optgroupClas

 

[JaredSangco]

just some more information regarding the previous post...

Each row should be treated separately.

PartId tblCategory.CatID tblCategory.CatName optgroupClas

Right now, when I select a 'radio'button from Option Group, [optGroupClass], it applies to ALL the rows.


Does this make more sense?

 

[ProgramError]

continued in thread thread702-1457741

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