Cascasding Combos

[YNWA]

Hi, I have 2 combo boxes on my form.

First one is employee, you select an employee from my employee query. This then populates a range of boxes such as name etc...

I then have a combo box called payment, which is linked in to my payment query. When a date is selected it populates a range of text boxes with date, payment amount, pension % etc...

What I need to happen is when I select an employee from the employee combo, only the dates that match this employee show in the payment combo, thus eliminating selecting a payment date for the wrong employee.

This is the code I have so far....

Private Sub CboPaymentDate_AfterUpdate()
Me.TxtGrossPay = Me.CboPaymentDate.Column(7)
Me.TxtExpenses = Me.CboPaymentDate.Column(3)
Me.TxtPaymentID = Me.CboPaymentDate.Column(1)
LngPaymentID = Me.TxtPaymentID


End Sub
Private Sub CboEmployees_AfterUpdate()
Me.TxtNI = Me.CboEmployees.Column(2)
Me.TxtBand = Me.CboEmployees.Column(3)
Me.TxtPensionRate = Me.CboEmployees.Column(4)
Me.TxtPCT = Me.CboEmployees.Column(5)
Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
Me.TxtID = Me.CboEmployees.Column(0)
Me.CboPaymentDate.RowSource = "Select DateofSession From QryHistory Where GPID =" & Me.CboEmployees & " Order By DateofSession DESC;"
LngEmployeeID = Me.TxtID

End Sub

Any help?

Thanks
Will

 

[ezfriend]

You need the cbo.Requery to refresh the new data. See below.

Private Sub CboEmployees_AfterUpdate()

    Me.TxtNI = Me.CboEmployees.Column(2)
    Me.TxtBand = Me.CboEmployees.Column(3)
    Me.TxtPensionRate = Me.CboEmployees.Column(4)
    Me.TxtPCT = Me.CboEmployees.Column(5)
    Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
    Me.TxtID = Me.CboEmployees.Column(0)
    Me.CboPaymentDate.RowSource = "Select DateofSession From QryHistory Where GPID =" & Me.CboEmployees & " Order By DateofSession DESC;"
    LngEmployeeID = Me.TxtID

    [B][COLOR="Blue"]CboPaymentDate.Requery[/COLOR][/B]

End Sub

 

[YNWA]

You need the cbo.Requery to refresh the new data. See below.

Private Sub CboEmployees_AfterUpdate()
 
    Me.TxtNI = Me.CboEmployees.Column(2)
    Me.TxtBand = Me.CboEmployees.Column(3)
    Me.TxtPensionRate = Me.CboEmployees.Column(4)
    Me.TxtPCT = Me.CboEmployees.Column(5)
    Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
    Me.TxtID = Me.CboEmployees.Column(0)
    Me.CboPaymentDate.RowSource = "Select DateofSession From QryHistory Where GPID =" & Me.CboEmployees & " Order By DateofSession DESC;"
    LngEmployeeID = Me.TxtID
 
    [B][COLOR=blue]CboPaymentDate.Requery[/COLOR][/B]
 
End Sub

Thanks, I have put that in but still get the box to enter Parameter Values.

I think my field names in this line

Me.CboPaymentDate.RowSource = "Select DateofSession From QryHistory Where GPID =" & Me.CboEmployees & " Order By DateofSession DESC

may be wrong.

I have checked my query and the names match up, not sure what i am doing wrong here.

 

[David R]

A box asking for parameter values is usually indicative that you haven't specified the fieldname accurately enough for Access. Try putting them in [] or putting the form name in front?

I've had success in the past when it couldn't find the form by going into the form Design View, clicking on Record Source or something similar in Properties, then choosing the "Build" option and navigating to get a very precise definition of the field I'm trying to reference. Copy that definition, DON'T save your form, and paste the definition into your query where it's looking for the criterion.

 

[YNWA]

just cant get this to work.

 

[namliam]

I am assuming the DateOfSession is a proper field...

If so then your parameter beeing asked is probably the Employee name, Am I correct?
If not, what parameter is the query asking for?

 

[YNWA]

I am assuming the DateOfSession is a proper field...

If so then your parameter beeing asked is probably the Employee name, Am I correct?
If not, what parameter is the query asking for?

When I select the employee from dropdown all works fine. Then I click on dropdown of dates and I get that parameter value for DateofSession. I have tried changing this field to match what I have but not sure what it has to be.

When I go into the query, the field I want to bring out is called... Session Date: DateofSession

I have used Date of Session and DateofSession.

I have tried all the names I can think of that should go there, nothing changes.

I searched google and found this could which I changed to my field names:

Private Sub CboEmployees_AfterUpdate()
With Me![CboPaymentDate]
If IsNull(Me!CboEmployees) Then
.RowSource = ""
Else
.RowSource = "SELECT [Date of Session] " & _
"FROM QryHistory " & _
"WHERE [GPID]=" & Me!CboEmployees
End If
Call .Requery
End With
End Sub

But still nothing

 

[namliam]

What is the column name your trying to retrieve? That column HAS TO BE in the query your querying... QryHistory

 

[YNWA]

I want the Date of Session field. This is called "Date of Session: DateofSession" in the query.

 

[namliam]

Can you post the SQL for the QryHistory??

If that has "Date of Session: DateofSession" then the [Date of Session] should work...

 

[YNWA]

SELECT payments.GPID, payments.ClaimID AS [Claim Id], payments.DateofSession AS [Session Date], rate_tbl.[Type of Rate] AS [Rate Type], payments.RateofPay AS [Rate £p], payments.HoursWorked AS Hours, payments.MileageClaim AS Mileage, payments.TotalClaim AS [Total £p]
FROM payments INNER JOIN rate_tbl ON payments.TypeofRate = rate_tbl.rate_ID
ORDER BY payments.ClaimID DESC;

Could you write a piece of code to fit this for the CboPayment drop down?

I have this at the moment...

Private Sub CboEmployees_AfterUpdate()
Me.TxtNI = Me.CboEmployees.Column(2)
Me.TxtBand = Me.CboEmployees.Column(3)
Me.TxtPensionRate = Me.CboEmployees.Column(4)
Me.TxtPCT = Me.CboEmployees.Column(5)
Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
Me.TxtID = Me.CboEmployees.Column(0)
LngEmployeeID = Me.TxtID
Me.CboPaymentDate.RowSource = "Select [Date of Session] From QryHistory Where [GPID] =" & Me.CboEmployees & " Order By [Date of Session] DESC;"
CboPaymentDate.Requery


End Sub

 

[YNWA]

SELECT payments.GPID, payments.ClaimID AS [Claim Id], payments.DateofSession AS [Session Date], rate_tbl.[Type of Rate] AS [Rate Type], payments.RateofPay AS [Rate £p], payments.HoursWorked AS Hours, payments.MileageClaim AS Mileage, payments.TotalClaim AS [Total £p]
FROM payments INNER JOIN rate_tbl ON payments.TypeofRate = rate_tbl.rate_ID
ORDER BY payments.ClaimID DESC;

Could you write a piece of code to fit this for the CboPayment drop down?

I have this at the moment...

Private Sub CboEmployees_AfterUpdate()
Me.TxtNI = Me.CboEmployees.Column(2)
Me.TxtBand = Me.CboEmployees.Column(3)
Me.TxtPensionRate = Me.CboEmployees.Column(4)
Me.TxtPCT = Me.CboEmployees.Column(5)
Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
Me.TxtID = Me.CboEmployees.Column(0)
LngEmployeeID = Me.TxtID
Me.CboPaymentDate.RowSource = "Select [Date of Session] From QryHistory Where [GPID] =" & Me.CboEmployees & " Order By [Date of Session] DESC;"
CboPaymentDate.Requery


End Sub

 

[namliam]

SELECT payments.GPID, payments.ClaimID AS [Claim Id], payments.DateofSession AS [[B][U]Session Date[/U][/B]], rate_tbl.[Type of Rate] AS [Rate Type], payments.RateofPay AS [Rate £p], payments.HoursWorked AS Hours, payments.MileageClaim AS Mileage, payments.TotalClaim AS [Total £p]
FROM payments INNER JOIN rate_tbl ON payments.TypeofRate = rate_tbl.rate_ID
ORDER BY payments.ClaimID DESC;

That says "Session Date", not "Date of Session"... Seems logical access would ask you for the parameter "Date of Session" as that column doesnt excist in the originating query...

Changing "Date of Session" to "Session Date" should do the trick

 

[YNWA]

SELECT payments.GPID, payments.ClaimID AS [Claim Id], payments.DateofSession AS [[B][U]Session Date[/U][/B]], rate_tbl.[Type of Rate] AS [Rate Type], payments.RateofPay AS [Rate £p], payments.HoursWorked AS Hours, payments.MileageClaim AS Mileage, payments.TotalClaim AS [Total £p]
FROM payments INNER JOIN rate_tbl ON payments.TypeofRate = rate_tbl.rate_ID
ORDER BY payments.ClaimID DESC;

That says "Session Date", not "Date of Session"... Seems logical access would ask you for the parameter "Date of Session" as that column doesnt excist in the originating query...

Changing "Date of Session" to "Session Date" should do the trick

Thanks for pointing that out. I had changed that to Session Date yesterday but never had it in the [ ].

Fresh pair of eyes does the trick.

Thanks bud.

 

[YNWA]

If I have that line in the code about the RowSource on CboPaymentDate can I have the Row Source property of that field filled in or does it have to be blank?

The code works, but when I change the Employees name from CboEmployees, the date in CboPaymentDate stays there until new data is selected.

How do I get this to reset to blank field when new employee is selected?

 

[namliam]

You have to requery the target combobox...

Me.CboPaymentDate.requery

Perhaps even "null" it upon update of the first combobox

 

[YNWA]

You have to requery the target combobox...

Me.CboPaymentDate.requery

Perhaps even "null" it upon update of the first combobox

i do have that code in there, under the CboEmployees After Update code

Private Sub CboEmployees_AfterUpdate()
    Me.TxtNI = Me.CboEmployees.Column(2)
    Me.TxtBand = Me.CboEmployees.Column(3)
    Me.TxtPensionRate = Me.CboEmployees.Column(4)
    Me.TxtPCT = Me.CboEmployees.Column(5)
    Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
    Me.TxtID = Me.CboEmployees.Column(0)
    LngEmployeeID = Me.TxtID
    Me.CboPaymentDate.RowSource = "Select [Session Date] From QryHistory Where [GPID] =" & Me.CboEmployees & " Order By [Session Date] DESC;"
    Me.CboPaymentDate.Requery
  
    
End Sub

I think it might have something to do with these....

Private Sub CboPaymentDate_AfterUpdate()
Me.TxtGrossPay = Me.CboPaymentDate.Column(7)
Me.TxtExpenses = Me.CboPaymentDate.Column(6)
Me.TxtPaymentID = Me.CboPaymentDate.Column(1)
LngPaymentID = Me.TxtPaymentID

End Sub

I am trying to self populate text boxes based on my date selections.

I have the CboEmployees self populating text boxes whenan employee is selected.

I had tje CboPaymentDate populating before I entered the correct code for the cascading combo effect.

 

[namliam]

Like I said you need to "null" them upon update of the first combo
Me.TxtGrossPay = ""
or
Me.TxtGrossPay = Null
or Something like that and onwards for the others

 

[YNWA]

I have this in the code also not sure if its needed:

Private Sub Form_Load()
LngEmployeeID = 0
LngPaymentID = 0
Me.CboEmployees.SetFocus
Me.CboPaymentDate.SetFocus

End Sub

Also keep the properties field of CboPaymentDate empty, is that correct?

As with null value, doesnt seem to do anytings to the form.

Its finding the date great now, but just not populating the relvant text fields when selecting a date.

New code looks like

Private Sub CboEmployees_AfterUpdate()
Me.TxtNI = Me.CboEmployees.Column(2)
Me.TxtBand = Me.CboEmployees.Column(3)
Me.TxtPensionRate = Me.CboEmployees.Column(4)
Me.TxtPCT = Me.CboEmployees.Column(5)
Me.TxtNonCont = Trim(Me.CboEmployees.Column(6) & " ")
Me.TxtID = Me.CboEmployees.Column(0)
LngEmployeeID = Me.TxtID
Me.CboPaymentDate.RowSource = "Select [Session Date] From QryHistory Where [GPID] =" & Me.CboEmployees & " Order By [Session Date] DESC;"
Me.CboPaymentDate.Requery
Me.TxtGrossPay = Null
Me.TxtExpenses = Null
Me.TxtPaymentID = Null

End Sub

 

[namliam]

Wouldnt that be in the after update of the DATE COMBO??