Solved

Run-Time Errror 424 - Object Required

Posted on 2008-10-03
8
371 Views
Last Modified: 2013-11-28
I've read through plenty of postings on here regarding this message, but did not find one that suited my needs.  I have "frmProfile" that has a tabbed control on it, "ProfileTab".  On the first page, "Profile", Index(0), I have "cmbExemptStatus", a combo box that applies to an employee.  What I need is when the user changes the selection in that combo box, it filters a combo box, "cmbBenefitsStatus" on another page, "Benefits", Index (2).  

For example, if the user selects "Exempt" from the dropdown menu, it only displays options in cmbBenefitsStatus that apply to an exempt employee.  

I have posted what I have for it as of now.
Private Sub cmbExemptStatus_Change()

    

    If cmbExemptStatus = "Exempt" Then

       Me.ProfileTab.Pages(2).SetFocus

       cmbBenefitsStatus.RowSource "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Salaried'"

   End If

    

End Sub

Open in new window

0
Comment
Question by:njmatt
  • 4
  • 3
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22635164
might be better seeing what you have in that form.

can you attache your db here.. click Attach file below
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22644529
njmatt,

Can you tell us what happens when you run the code you have please.
Thanks.


If the two comboboxes are on the same tab this is easy.
If the two combos are on separate tabs it becomes hard.

Can both combos be on the same tab?

JeffCoachman

0
 
LVL 1

Author Comment

by:njmatt
ID: 22649699
Unfortunately, the combo boxes are on separate tabs and need to be so.  On the attached file, if you open 'frmProfile'.  You will see "Exempt Status" on the first Profile Tab.  By changing that combo box, it should update the "Benefits Plan" combo box on the "Benefits" tab.

If Exempt Status = Exempt, Benefits Plan should display only options for Salaried.
If Exempt Status = Non-Exempt, Non-Employee, Benefits Plan should display options for Hourly and Cobra.

Thanks for all your help.
IMED-TEST-2.zip
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22649877
try this


Private Sub cmbExemptStatus_Change()
Dim sql
If cmbExemptStatus = "Exempt" Then
    sql = "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Salaried*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
End If
   
End Sub
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 22649930
now, that will solve your problem of runtime error object required.

to make the selection options dynamic in combo cmbBenefitsStatus,

you need to make a relation between tables person_exempt_status and  benefits_dependant_status
0
 
LVL 1

Author Comment

by:njmatt
ID: 22650364
I got that to somewhat work for me.  A few things are happening that I don't understand.  

1.  When I make a selection in the Exempt Status combo box, the form jumps right to the Benefits Tab, which I don't want to happen.
2.  When I try to select an option in the Benefits Plan combo box, I get the following message:

"You tried to assign the Null value to a variable that is not a Variant data type."

3.  After I select, for example "Exempt" in the Exempt Status combo box, if I were to go back and change the selection, "Non-Exempt" disappears and the options are Exempt, Exempt, Non-Employee.  Also occurs if I select "Non-Employee", the combo box then reads Exempt, Non-Employee, Non-Employee.  And if you are to open up the person_exempt_status table, you'll see that the values will be replaced.

I've re-attached with the updated code.




IMED-TEST-21.zip
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 22650674
1.  When I make a selection in the Exempt Status combo box, the form jumps right to the Benefits Tab, which I don't want to happen.

Private Sub cmbExemptStatus_Change()
Dim sql
If cmbExemptStatus = "Exempt" Then
    sql = "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Salaried*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
End If
Me.ProfileTab.Pages(0).SetFocus '<<< ADD this line
End Sub


as for the other issues

see line three of my previous comment ...

and better post a new question for that
0
 
LVL 1

Author Comment

by:njmatt
ID: 22651082
Thanks for you're help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now