After Update Event does not work.

[Rozeanna Jerry]

Hi Experts, am new to access. I juts created a form with sub form based on two tables having 1: many relationship.

The main form is from tblStudents and subform is from tble attendance.

First on main form I choose student name from a combo box and records for that student is displayed on the subform in form view.

1.*

Private Sub Combo14_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[StudentID] = " & str(Nz(Me![Combo14], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
    DoCmd.GoToControl "Command12"
End Sub  

The same
Ok now on the subform ( Form view) I have another combo to display records based on dates selected.

2*

Private Sub Combo12_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TimeSheetId] = " & str(Nz(Me![Combo12], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Problem is that code#2 is not working..maybe because code one is filtering so code is refusing to filter as well. I do not know what to do.. ANy help will be appreciated.

Thanks

Rozeanna

[TheSmileyCoder]

Hi
I think your problem is that you need to enclose your result from the combobox in single quotes. Try looking at this Insight Article by NeoPa:
quotes & double quotes, where and when to use them

Here is the modified line of your code:

rs.FindFirst "[TimeSheetId] = '" & str(Nz(Me![Combo12], 0)) & "'"

Also this will help you in the future:
Debugging vba by NeoPa

[Rozeanna Jerry]

Hi TheSmileyOne, Thanks for your assistance.
Replacing my code with your code gives this error message.

"Runtime Error 3464: Data mismatch in Criteria Expression"

Private Sub Combo12_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object
 
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[TimeSheetId] = '" & str(Nz(Me![Combo12], 0)) & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

[Rozeanna Jerry]

Any ideas would be appreciated. Anyway thanks for the link.

[Rozeanna Jerry]

My code#2 in post #1 works when I open the sub-form in it's own window. But does not work when I open the form with MAin+Subform. SO I was thinking that I would have the combo in main form and clone the subform recorset to find the selected record from combo. something like.

 Private Sub Combo12_AfterUpdate()
   ' Find the record that matches the control.
    Dim rs As Object
 
     Set rs = Me.Recordset.sub_form1.Clone
     rs.FindFirst "[TimeSheetId] = " & str(Nz(Me![Combo12], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
 

Any help is appreciated.

[TheSmileyCoder]

Did you look at the debuging article I linked?
Before the line that fails it would be a good idea to add:

Msgbox "[TimeSheetId] = " & str(Nz(Me![Combo12], 0))" 

so that you can verify that the criteria string is as expected.
Also I don't see a need for using your recordsetclone, but if you do, please explain. In my oppinion you could just do:

Me.RecordSet.FindFirst "[TimeSheetId] = " & str(Nz(Me![Combo12], 0)) 

then optionally do after:

If Me.Recordset.Nomatch Then msgbox "Record not found"

[Rozeanna Jerry]

 Msgbox "[TimeSheetId] = " & str(Nz(Me![Combo12], 0))" 

is ok/fine.

However,

Me.RecordSet.FindFirst "[TimeSheetId] = " & str(Nz(Me![Combo12], 0)) 
 

And

If Me.Recordset.Nomatch Then msgbox "Record not found"

Does not work in the Main/sub form but works perfectly in the sub-form in its own window. Any suggestion as to why this is happening? Obviously the sub-form doesn't want to work together with the main form; want to function individually on its own. Honestly am confused now..

[Rozeanna Jerry]

Facts & Flash back: sub-form form is link to main form by studentID (Child and Parent keys).

Sub-form displays a filtered record based on a combo from Parent form. The problem arises on the second filter combo within the sub-form.
I was just thinking that filtering a filtered sub-form would pose that problem. Because if combo filtering is working in the sub-form's own window but not working within the parent-child form then maybe there maight be some other way of handling this. You guidance is highly require here.

Thanks so much.

Anna R.

[TheSmileyCoder]

Using a combobox to select a record and then going to it like you do, is not actually filtering.

The only filtering is that a subform linked through StudentID, will of course only show records related to that Student. So the subform is filtered by the StudentID, and if you in your subform select a record which is not related to that Student, and want to go to it, then yes it will fail.

If I understand correctly:
Main form: Combobox select a student, and main form goes to that student.
Sub Form, Combobox 2 (within subform), select a timesheet and subform goes to that timesheet record? Please confirm

Do you remember to update the combobox of the subform to only show records for that student?

This is most certainly possible, I think we are just talking past each other. If you want you can upload the DB here, and I can take a look at it.

[Jerry Maiapu]

Upload the db file or answer to post# 9 and maybe we could help.

[Rozeanna Jerry]

If I understand correctly:
Main form: Combobox select a student, and main form goes to that student.
Sub Form, Combobox 2 (within subform), select a timesheet and subform goes to that timesheet record? Please confirm.

Hi, Thanks so much. What you presumed is 100% correct.
I could not upload the md file b'cause it's heavy..60MB. I'll try and delete some objects and send. AM so sorry for the late reply.
But if you tried to say something then mentioned so as what you guessed is correct.
TA.

Anna

[TheSmileyCoder]

The easiest way to proceed with uploading a new database would be to create a new one, and then import from the original database the relevant forms/tables, and then just check that its working before uploading that new database. Also please answer my previous question.

[Rozeanna Jerry]

Hi, am so sorry for the late reply. I was editing my existing db for confidentiality sake; do not what to publicise in forums like this. Anyway I came up with a similar db which is attached below. If you have spare time please have a look and reply.

**I was also thinking of just doing a search based on date entered on text box instead of combo but don know if that is possible.

A Bunch of Thanks!!

Anna

[Rozeanna Jerry]

Hi TheSmileyOne, to your Question (POST#9), the subform is filtered by the StudentID, and the combo box in the subform select a record which is related to the Student, and therefore want to go to it. But Does not.

You understood me correctly:
Main form: Combobox select a student, and main form goes to that student.
Sub Form, Combobox 2 (within subform), select a timesheet (Date) and subform goes to that timesheet record. (The sample db has been attached for help like you requested)

Thanks Alot!

Anna

[Mr Key]

Try this one also
Just browse to the link below, I have answered the similar question at the post named Record lookup with 2 Combo boxes