Data Entry Fields that are dependent of Combo Box selection (1 Viewer)

[jaymardo]

I am putting together what appears to be a simple database for our "Production" department to use. There are 4 different types of employees in this department, or 4 different job types. I would like to create form that would show their job responsibilities only when they select their job title. Here is the example:

Job Types: Collecting, Posting, Billing, Coding

Responsibilities by type:

Collecting: Rejections
Posting: Adjustments
Coding: Excisions
Billing: Transactions

These of course are not the only responsibilities within the job types. I wanted to see if there is a way to select "Collecting" as a job type, and then only have those job responsibilities associated with Collecting show up so the associated can then enter values for their productivity.

Any Ideas?

 

[pbaldy]

Sounds like one of these is what you're looking for:

http://www.baldyweb.com/CascadingCombo.htm

http://www.baldyweb.com/ConditionalVisibility.htm

 

[jaymardo]

Thanks for the help...I need to tell you that I am a novice at Access...are you going to tell me to get professional help, or is this something that can be done by a novice...

 

[jaymardo]

I am in the design view of this form...I would go to the Property Sheet of the Combo Box, and then input the Conditional Visibility language in the coding?

 

[pbaldy]

Not knowing the overall scope of the task, I would say that if you're willing to dive in and figure out how things work, you can probably create a working solution (we're here to keep you on track). Your head may be sore by the end, but you'll get it done.

If you're not the type that likes learning new things, you should get help. There's a lot to learn, starting with proper normalized table design. Forums like this can only point you in the right direction.

 

[pbaldy]

Looks like we were typing at the same time. Here's how to get VBA code into the appropriate place:

http://www.baldyweb.com/FirstVBA.htm

 

[jaymardo]

Thanks! Ok, so here are the fields:

Employee Name
Date of Production
Appeals Input
Refunds Input

Depending on the Department Name, which is linked to Employee Name in Relationships, I would want one to show up. So, if it is "Collections", I would want "Appeals Input" to show up and not "Refunds Input"

....how would the language be written in this? Would it go into the property sheet for Appeals/Refunds Input, or would it be put in the code for Employee Name?

 

[pbaldy]

It would go in the after update event of the employee name (since it's dependent on that). If it's a combo whose row source can include the department, you can get it there. Otherwise you'll need to look it up with a recordset or DLookup. The code would look like my second link above.

 

[jaymardo]

Ok, thanks...it is working, but when I scroll through the records in form view, that field doesn't show up in any of the fields. I saw in another post that the same language would need to be written into the current field? Can you confirm?, or, how would I make it so where I click on a new record, that field reappears.

This is what my language looks like so far:

Private Sub Combo13_AfterUpdate()

If Me.Combo13 = "Collections" Then
Me.Refunds_Input.Visible = True
Else
Me.Appeals_Input.Visible = False
End If

End Sub

1) What word would I used to include other departments, or a new criteria, like the Excel "Or" function...
2) When you look at the above language, would any record that does not say "Collections" make the field appear?

 

[pbaldy]

Yes, I addressed that in my link: "You might also want the code in the Current event, which would fire when you change records". That refers to the form's On Current event. The OR would look like:

If Me.Combo13 = "Collections" OR Me.Combo13 = "Whatever" Then

Depending on the overall situation, you might consider Select/Case instead of If/Then.

 

[jaymardo]

Thanks...what if you wanted the visibility, or lack of, to depend on the selection, so if it was Collections, you would have:

If Me.Combo13 = "Collections" Then
Me.Refunds_Input.Visible = True
Else
Me.Appeals_Input.Visible = False

But if it was Posting, you would have:

If Me.Combo13 = "Posting" Then
Me.Refunds_Input.Visible = False
Else
Me.Appeals_Input.Visible = True

How would you put both into one string?

 

[pbaldy]

Is this what you mean?

If Me.Combo13 = "Collections" Then
Me.Refunds_Input.Visible = True
Me.Refunds_Input.Visible = False
Else
...

 

[jaymardo]

That's it, thanks Paul. One more thing and I should be set, for now...if my Combo Box has two values, the first which they are selecting, and the second which shows in the drop-down, but not when it is selected, how do I look up the hidden value from the combo box to use with the conditional statement. Here is the example:

When the combo-box is clicked on, a drop down appears which has the Employee Name, and then which Department they are in. Only the Employee Name shows up after they click on their name. The Department is what the appearing fields depend on. How do I get the string to reflect the Department as the conditional...

 

[pbaldy]

Me.Combo13.Column(x)

where x is the column number containing the desired info. Note the column property is zero based, so 1 is the second column.

 

[jaymardo]

That works, thanks. I tried the Select/Case method, and here is the language:
Private Sub Form_Current()
Select Case Me.Combo13.Column(1)
Case "Collections":
Me.Refunds_Input.Visible = True
Me.Appeals_Input.Visible = False
Case "Posting:"
Me.Refunds_Input.Visible = False
Me.Appeals_Input.Visible = True
Case Else:
Me.Refunds_Input.Visible = False
Me.Appeals_Input.Visible = False
End Select
End Sub

The problem is, when "Posting" is selected, both fields are invisible, as if it were saying they belong in the Else category...

 

[jaymardo]

Disregard that, I put the quota marks on the other side of the colon...let me try it now

 

[pbaldy]

Is there really a colon in the name? If it's dropping into the Else, it doesn't see that as the contents of the control.

 

[pbaldy]

Oops, didn't see there was another page. You don't need the colon after the test. Simply this should work:

Case "Collections"

 

[jaymardo]

Okay, so I thought it was going to work...it still isnt updating for each new record...here is what I have, there is still work to do, but this is the start:


Select Case Me.Combo174.Column(1)
Case "Collections":
Me.Additional_Charge_Input.Visible = False
Me.Additional_Charge_Output.Visible = False
Me.Adjustment_Transactions_Input.Visible = False
Me.Adjustment_Transactions_Output.Visible = False
Me.All_Requests__AJM__TRF__RFD__SFA__BCR__SCF__Input.Visible = True
Me.All_Requests__AJM__TRF__RFD__SFA__BCR__SCF__Output.Visible = True
Me.Anesthesia_Blocks_Input.Visible = False
Me.Anesthesia_Blocks_Output.Visible = False
Me.Appeals_Input.Visible = True
Me.Appeals_Output.Visible = True
Me.APS_Billing_Registration_Input.Visible = False
Me.APS_Billing_Registration_Output.Visible = False
Me.Emdeon_Input.Visible = False
Me.Emdeon_Output.Visible = False
Me.Other_Procedures_Input.Visible = False
Me.Other_Procedures_Output.Visible = False
Me.Self_Pay_STMT_s_Input.Visible = True
Me.Self_Pay_STMT_s_Output.Visible = True
Me.Attorney_Ledgers_Input.Visible = False
Me.Attorney_Ledgers_Output.Visible = False
Me.Attorney_Negotiations_Input.Visible = False
Me.Attorney_Negotiations_Output.Visible = False
Me.Attorney_Settlements_Input.Visible = False
Me.Attorney_Settlements_Output.Visible = False
Me.Billing_Transactions_Input.Visible = False
Me.Cataract_YAG_Eyes_Input.Visible = False
Me.Cataract_YAG_Eyes_Output.Visible = False
Me.Collection_Module__Intergy__Input.Visible = True
Me.Collection_Module__Intergy__Output.Visible = True
Me.Colonoscopy_EGD_Input.Visible = False
Me.Colonoscopy_EGD_Output.Visible = False
Me.Correspondence__Letters__no_EOB_s__Input.Visible = True
Me.Correspondence__Letters__no_EOB_s__Output.Visible = True
Me.Excisions_Cysts_Tumors_Input.Visible = False
Me.Excisions_Cysts_Tumors_Output.Visible = False
Me.GHN_Input.Visible = False
Me.GHN_Output.Visible = False
Me.GHN_Emdeon_Denials_Rejections_Input.Visible = True
Me.GHN_Emdeon_Denials_Rejections_Output.Visible = True
Me.Incoming_Calls_Input.Visible = True
Me.Incoming_Calls_Output.Visible = True
Me.New_A_R_Claims_Input.Visible = True
Me.New_A_R_Claims_Output.Visible = True
Me.New_Correspondence_Input.Visible = False
Me.New_Correspondence_Output.Visible = False
Me.Ortho___Podiatry_Input.Visible = False
Me.Ortho___Podiatry_Output.Visible = False
Me.Pain_Management_Input.Visible = False
Me.Pain_Management_Output.Visible = False
Me.Paper_Claims_Input.Visible = False
Me.Paper_Claims_Output.Visible = False
Me.Payment_Transactions_Input.Visible = False
Me.Payment_Transactions_Output.Visible = False
Me.Physician_Surgery_Input.Visible = False
Me.Physician_Surgery_Output.Visible = False
Me.Plastic_Surgery_Input.Visible = False
Me.Plastic_Surgery_Output.Visible = False
Me.Recoups_Input.Visible = False
Me.Recoups_Output.Visible = False
Me.Refunds_Input.Visible = False
Me.Refunds_Output.Visible = False
Me.Secondary_Billing_Transactions_Input.Visible = False
Me.Secondary_Billing_Transactions_Output.Visible = False
Me.Short_Pay_Denials_Input.Visible = True
Me.Short_Pay_Denials_Output.Visible = True
Me.Subpoenas_Input.Visible = False
Me.Subpoenas_Output.Visible = False
Me.System_Change_Forms_Input.Visible = False
Me.System_Change_Forms_Output.Visible = False
Me.Target_Action_Items_Input.Visible = True
Me.Target_Action_Items_Output.Visible = True
Me.TPRC_Office_Input.Visible = False
Me.TPRC_Office_Output.Visible = False
Me.Transfers_Reversals_Input.Visible = False
Me.Transfers_Reversals_Output.Visible = False
Me.Zero_Pay_Denials_Input.Visible = True
Me.Zero_Pay_Denials_Output.Visible = True
Case "Posting":
Me.Additional_Charge_Input.Visible = False
Me.Additional_Charge_Output.Visible = False
Me.Zero_Pay_Denials_Input.Visible = True
Me.Zero_Pay_Denials_Output.Visible = True
Case "Coding":
Me.Additional_Charge_Input.Visible = False
Me.Additional_Charge_Output.Visible = False
Me.Zero_Pay_Denials_Input.Visible = True
Me.Zero_Pay_Denials_Output.Visible = True
Case "Billing":
Me.Additional_Charge_Input.Visible = False
Me.Additional_Charge_Output.Visible = False
Me.Zero_Pay_Denials_Input.Visible = True
Me.Zero_Pay_Denials_Output.Visible = True
Case Else:
Me.Additional_Charge_Input.Visible = False
Me.Additional_Charge_Output.Visible = False
Me.Zero_Pay_Denials_Input.Visible = True
Me.Zero_Pay_Denials_Output.Visible = True
End Select
End Sub


Any ideas on why it wont update with each new record?

 

[pbaldy]

Where is the code being executed from?