Runtime Error 3070 on cascading combo box

[Jamesy_007]

Can someone explain my problem here? I begun making cascading combo boxes, great practice. I have this combo box with names bound to a tables rowsource. That combo box when filtered and user selects a name. The DetentionDate [field] to that person filters in another or cascading combo box…. So, say john has three (3) DetentionDates…Moe has Four (4) DetentionDates good! … These dates come up in the second combo boxes (Cascading) and the users has to pick the appropriate date.


Here’s the catch

1. Each date can have many receipts numbers. Therefore, I created a table tblReceipts with a one to many relationships with the Detention table (tblDetention)

2.Created a form with both combo boxes relating to each other i.e. Name to DetentionDate

3.However, I want the user to pick any date cascading from the person and the sub form show in datasheet view the receipts of that person assigned to that particular date

4.Cascading combo boxes work but. when I try to pick a date, I get this error in the code from the AfterUpdate event “Runtime error 3070: Microsoft Access database engine does not recognize “May” as a valid field name or expression”

Note, which date is chosen the error message changes to that month. The field DetentionDate is format to medium date.

Private Sub cboDate_AfterUpdate()
    Dim strCriteria  As String
    strCriteria = "[Dent_ID] = " & Me.cboDate
     Me.RecordsetClone.FindFirst (strCriteria)
    If Me.RecordsetClone.NoMatch Then
        MsgBox "Person not found"
    Else
        Me.Bookmark = Me.RecordsetClone.Bookmark
         End If
   
End Sub

[Jamesy_007]

WoW.....WoW Omg...

After spending all this time writing out this new thread. I now see what is the problem .... The field DetetionDates is not a foreign key Lol. It is formatted as a medium date....
Hmmmm How to get around this

[Micron]

I'm confused.
"Cascading combo boxes work but when I try to pick a date, I get this error in the code."
How can they work but produce an error? Anyway, not enough info on the combos to zero in on the issue but I suspect one or more of the downstream combos has a rowsource property that is looking at the wrong column in the combo that precedes it. Put a break point on the me.recordsetclone line and when it stops there, mouse over the variable and see what it contains. I suspect you're referring to the wrong column of the "upstream" combo in the rowsource of the downstream combo(s).

EDIT - Posted before I saw your update. Will have to review and probably am done for the night. Site is acting up here, and it's getting late.

[mike60smart]

Hi
Can you upload a copy of the database?

[CJ_London]

How to get around this

use 5 for may, 6 for June etc

[Jamesy_007]

Hi MS Access family,

I have a copy of the db. Please tell me where I am wrong... or if this can be done. I have read where cascading combo and datasheet view might be an issue. However, this is more a cascading combo with a subform in datasheet view please look at the AfterUpdate event. When a date is chosen, I want the user to see the respective receipts attached to that specific date. Hope you guys understand what I am trying to solve here....

Cascading db.zip

[davegri]

Problem is that there is absolutely nothing in the other tables that relate to a key in tblFinesReceipts.
What is conviction_fk ?

[Jamesy_007]

Hey davegri,

Good eyes....
However, I think I am understanding the issue here [Den_ID] a primary key is tied to [Conviction_fk].... I actually just made that relationship but the second combo box is a drop list of dates format to medium data Type. In the AfterUpfate event I keep getting a break in the code... it is not filtering so that the subform information will come up

[davegri]

Ah, so. Den_ID related to Conviction_fk - I should have caught that
The only person in the db that meets matching criteria is Zee, Test.

Option Compare Database
Option Explicit


Private Sub cboDate_AfterUpdate()
    Dim strCriteria  As String
    strCriteria = "Inm_PK = " & Nz(Me.cboName, 0) & " AND DetentionDate = #" & cboDate & "#"
    Me.RecordsetClone.FindFirst (strCriteria)
    If Me.RecordsetClone.NoMatch Then
        MsgBox "Person not found"
    Else
        Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
End Sub


Private Sub cboName_AfterUpdate()
    cboDate.RowSource = "Select DetentionDate from tbldetention where Inm_pk = " & Me.cboName
End Sub

[Jamesy_007]

Ah, so. Den_ID related to Conviction_fk - I should have caught that
The only person in the db that meets matching criteria is Zee, Test.

Option Compare Database
Option Explicit

Private Sub cboDate_AfterUpdate()
    Dim strCriteria  As String
    strCriteria = "Inm_PK = " & Nz(Me.cboName, 0) & " AND DetentionDate = #" & cboDate & "#"
    Me.RecordsetClone.FindFirst (strCriteria)

Hey.... trust me theirs a special place in programming paradise for you guys... this is the trick!
But when the user is changing a name to filter a next date. The Onchange how to get rid of the old date that still shows, prefer it goes blank

[davegri]

Blank it out when a new name is selected...

Private Sub cboName_AfterUpdate()
    cboDate.RowSource = "Select DetentionDate from tbldetention where Inm_pk = " & Me.cboName
    cboDate = Null
End Sub