Need Help with Forms Combo Box

[ergonzalez]

I have created a form with two different combo boxes. The 1st combo box is named: "Reason Category" and the second one is named: "Reason Code". There are 14 different choices to chose from for the Reason Category.

Here is where I need help. The "Reason Code" is going to be different depending on what Reason Category is chosen. For example: The first Reason Category is "Billng" and the Reason Codes for Billing are: "Misc Fee Inquiry" or "Service Fee Inquiry". I only want these two appear when I select "Billing" as the "Reason Category"

I've created two tables.

The first table lists all of the Reason Categories under Field1. The second Field is named ID and it's just numbered 1-14.


The second table lists all of the different Reason Codes. It has 15 different fileds. The first 14 fields contain all of the reason codes. Field1 contains all of the reason categories for Billing, Field2 contains all of the reason codes for the 2nd Reason Category, Field3 contains all of the reason categories for the 3rd Reason Category and so on.

What code do I enter in order for the appropriate Reason Codes to appear for each Reason Category?

 

[LittleSmudge]

What code do I enter in order for the appropriate Reason Codes to appear for each Reason Category

You don't need CODE.

You need to go back and NORMALISE you database.

You schema is currently nothing short of a disaster area.
( Sorry if that sounds brutal - but the truth sometimes hurts. )

To do what you are suggesting you need :-

ONE table with TWO fields in it

tblReason
Category
Code

Category and Code are joint primary keys.

Then populate the table :-

[b]Category    Code[/b]
Billing        Misc Fee Inquiry
Billing        Service Fee Inquiry
Cat2Name       Cat1Code1
Cat2Name       Cat1Code2
Cat2Name       Cat1Code3
etc ..

THEN, on the form you have combo 1 ( called cboCategory )
and combo 2 ( called cboCode )

Combo 1 :-
cboCategory.RowSource = "SELECT DISTINCT Category FROM tblReason "

In cboCategory.AfterUpdate event put

If IsNull(cboCategory) Then
    cboCode.Enabled = False
    ' ( This stops user selecting Code before Category is valid )
Else
    cboCode.RowSource = "SELECT Code FROM tblReason " _
        & "WHERE Category = """ & cboCategory & """;"
    cboCode.Enabled = True
    cboCode.Requery
End If

THEN when the user 'invents' 3 more Categories at some point in the future ( yes users are like that ! ) your design will cope with it perfectly well.


'ope-that-'elps.





G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ergonzalez]

Do I need to do something to make the Category and Code are joint primary keys?

I need help with the following:

In cboCategory.AfterUpdate event put

If IsNull(cboCategory) Then
cboCode.Enabled = False
' ( This stops user selecting Code before Category is valid )


Else
cboCode.RowSource = "SELECT Code FROM tblReason " _
& "WHERE Category = """ & cboCategory & """;"
cboCode.Enabled = True
cboCode.Requery
End If

Where does this need to go?

 

[LittleSmudge]

Do I need to do something to make the Category and Code are joint primary keys?

With the table open in Design view click on the little grey boxes on the left and select BOTH rows.
Then click on the PrimaryKey tool in the toolbar.




Where does this need to go?

In cboCategory.AfterUpdate event

With the form in design view click on the combo box control.
Click on the Properties tool in the toolbox
Scroll down the properties dialog box until you see the After Update event.
Double click in the white space to the right so that [Event Procedure] appears in the space
Click on the grey box with three dots in it on the extreem right.

You are not in the Code Window inside the combo box's AfterUpdate event.
Copy the code from above and paste in there.

Close, save, Run.

'ope-that'elps.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ergonzalez]

Thanks for your quick response. I still need help.

I have the following:

cboCategory.RowSource = SELECT DISTINCT Category FROM tblReason (I took out the quotations)

cboCategory.AfterUpdate = If IsNull(cboCategory) Then

cboCode.Enabled = False (I only have the options to select yes or no on this one????)

This is where I think I'm not doing it right:

cboCode.RowSource = "SELECT Code FROM tblReason _
& "WHERE Category = """ & cboCategory & """;"
(Do I add it exactly like this or do I take quotations and "&" signs off)

cboCode.Enabled = True (I can only select Yes or No on this one.)

cboCode.Requery (I couldn't locate this one.)

Thanks for your assistance!

: )

Elda

 

[LittleSmudge]

Read the bit under the BOLD text in the post above and follow it literally.

The cboCategory.RowSource = SELECT etc is the ONLY line that you type in the properties dialog box

ALL of the rest should be typed in the CODE WINDOW.

( oops I've just seen my typo in the above )
It should read:-

Click on the grey box with three dots in it on the extreem right.

You are now in the Code Window inside the combo box's AfterUpdate event.
Copy the code from above and paste in there.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ergonzalez]

Please disregard my previous message.

cboCategory.RowSource = SELECT DISTINCT Category FROM tblReason (I took out the quotations)

I entered the following under AfterUpdate. (I selected Event Procedure and clicked on the three dots:

Private Sub cboCategory_AfterUpdate()
If IsNull(CboCategory) Then
CboCode.Enabled = False
Else
CboCode.RowSource = "SELECT Code FROM tblReason " _
& "WHERE Category = """ & CboCategory & """;"
CboCode.Enabled = True
CboCode.Requery
End If
End Sub

After I save it and try to run it, I get a box that says: Enter Parameter Value.

What do I do now?

Thanks for your assistance!

 

[LittleSmudge]

The Enter Parameter Value points to a typo in one of two places

If it is BEFORE you select an entry in the combo box then the typo is probobly in the cboCategory.RowSource

Make sure the field name exactly matches the field name as it appears in the table and the table name is correct too

I.e.
Category
and
tblReason


If it appears after you try to select an entry in the combo box the the following might be more significant.

There is a worrying aspect in the code in the last post

The First Line shows cboCategory with a LOWER case c in the first character ( as it should be to be complient with Hungarian Notation.

However, everywhere else in the code it is UPPER case C

Access should correct a typo error and change the code to whatever the Object Name actually is.
( Which should match the procedure title line. )
So check this area carefully for errors too.



( I'm off not 'till tomorrow. )

'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[ergonzalez]

It still does not work for me.