Option Group Headaches

[cpc101]

Option Group Headaches..Please Assist

Hello...

As a Network Administrator unwittingly thrust into the world of Access, I am running into the following dilema...

I have a form that is to be used for Data entry for our upcoming inventory. The form contains:

Count Tag Number: Entered by user, placed into new record (Primary Key)

Item Number: Selected by user in Combo box, pulled from a seperate table and entered into the record created by Count Tag Number

Quantity: Entered by user - stored in above new record

Here is the problem... My item numbers are divided into eight different classes (1 thru 8 representing Different stages of Production ), however they have the same item number. The table containing the items has a column with the appropriate class number.

My goal is: To add an Option Group or combo box that will display the 8 different classes. The user can then select the class, at which point the combo box that displays the item number for them to select, will be filtered to only display items in the selected class.

Thank you very much for any assistance you can provide...

Christian

 

[Mile-O]

You'd be better using Cascading Comboboxes rather than one option group and one combo but, it's your choice and the method is the same.

Basically, the combo's RowSource should be the data yo wish it to show with the one field chosen in the option group has its criteria set as the option group like this:

[Forms]![MyForm]![MyOptionGroup]


On the AfterUpdate event of the option group you'd have this code:

Me.MyCombo.Requery



So, the combo's RowSource would be something like:

SELECT ClassID, Class FROM tblClasses WHERE ClassID = [Forms]![MyForm]![MyOptionGroup];

 

[cpc101]

Please excuse my ignorance... I've only been working with Access for a few days now... I'm not really sure where to enter the items you have listed...

Here is what I have:

Table: Classes
Fields: Class and Class Names
Class Field contains records: Numbered 1 thru 8
Class Names Field contains records: FG, SFG, SFGS, WIP, Raw M., FGS, Handles, Pkg

Table: ItemMaster
Fields: Item, Description, STD_Cost, Class

Table: Tagentry
Fields: Item, Description, STD_Cost, Class, Quantity

Form: Tagentry
Contains: Option Group with 8 Check boxes Named: Class
Combo Box that pulls [Item] from ItemMaster

I don't even think that I have the Option Group "linked" to the proper table...

Thanks again for your assistance...

Item Combo Row Source:

SELECT [Item Master].[Item] FROM [Item Master];

 

[dsomers]

You can enter the row source from the form properties under Data/Record Source.

The AfterUpdate option you'll have to go to the form properties/All/After Update. When you click on After update select Event Procedure and click the '...' to enter the code in the VBA window.

Later,
David Somers

 

[Mile-O]

You can enter the row source from the form properties under Data/Record Source.

The AfterUpdate option you'll have to go to the form properties/All/After Update. When you click on After update select Event Procedure and click the '...' to enter the code in the VBA window.

Later,
David Somers

Not the form's properties but the properties of the controls mentioned.

 

[dsomers]

Oh yeah... sorry...

Later,
David Somers

 

[cpc101]

Getting "Me" is an invalid macro or doesn't exist error....

 

[Mile-O]

The AfterUpdate option you'll have to go to the form properties/All/After Update. When you click on After update select Event Procedure and click the '...' to enter the code in the VBA window.

Reread this piece of advice. You are currently typing a piece of code into the wrong place. At the end of the line where you have typed the Me.... statement click the button with the ... and select [Code Builder]. The code goes in the module that is opened.

 

[cpc101]

Ok so this is waht I have in my Combo Box...

SELECT [Item Master.Class], WHERE [Item Master.Class]=[Forms]![Tag Entry All Locations]![Class];

I get the following error:

Syntax Error (missing operator) in query expression 'WHERE [Item Master.Class]=[Forms]![Tag Entry All Locations]![Class];

Any ideas?

Thanks again...

 

[Mile-O]

Take the comma out.

 

[cpc101]

GREAT!!! Now it does what I want!

However, I dont think that I have the afterupdate configured correctly.

I entered the =Me.Class.Requery statement but am still getting an error about macros. I followed the instruction listed above.

 

[Mile-O]

Where have you put the code? Like in Example A or B (see attachment)

 

[cpc101]

Example A... Ooops... I moved it to B. Now I don't get the error, but if I select a different class Item in the option group, It does not refresh the displayed data in the 'Item' Combo box.

Private Sub Class_AfterUpdate()
Me.Class.Requery
End Sub

The data entry folks should use arrow keys to select the appropriate option. (DE is now limited to 10Key and Arrow Keys for speed reasons.)

Thanks for all of the help!!!!

 

[Mile-O]

Here's what you want:

 

[cpc101]

It does the same thing mine does...

The default class is selected on opening and that is the only class that will show even after you pick another class...

If I change the default value to "" It will select the apropriate class when selected however, If I should make a mistake and select the wrong class, selecting the correct class will not update the combo with the corrected information.

Maybe my copy of Access is pooched?