Evil Clones!

[JustJim]

I thought that this was a fairly standard method of finding a record in a form.

Private Sub cboGender_Exit(Cancel As Integer)
    ' Find the record that matches the form header.
Dim dbNA As Database
Dim strSearch As String
Dim rsSchoolName As Recordset
Dim rsData As Object
Dim strSQL As String
 
strSearch = "(Surname = '" & Me.cboSurname & "') AND " _
        & "([First_Names]= '" & Me.cboFirstNames & "') AND " _
        & "(DOB = #" & Format(Me.cboDoB, "MM/DD/YYYY") & "#) AND " _
        & "(Gender = '" & Me.cboGender & "')"
 
    Set rsData = Me.Recordset.Clone
    rsData.FindFirst strSearch
    If Not rsData.EOF Then Me.Bookmark = rsData.Bookmark
 
' Some other stuff
End Sub

The search string is made up of the contents of four cascading combo boxes in the form's header and the form does in fact show the correct student's details.

However.... another student's record is changed so that the other student gets given the Surname of the student that I search for!

I suspect that this is because I'm working with two recordsets, but I don't understand it at all. Do I need to apply an index to the cloned recordset?

I also can't see the relationship between the record searched for and the one that gets changed. If I close the form and re-open it and search for the same student, another, different record gets changed.

No wonder the wierdo right-wingers are against cloning! :)

Jim (the mad scientist's Igor)

[ADezii]

I thought that this was a fairly standard method of finding a record in a form.

Private Sub cboGender_Exit(Cancel As Integer)
    ' Find the record that matches the form header.
Dim dbNA As Database
Dim strSearch As String
Dim rsSchoolName As Recordset
Dim rsData As Object
Dim strSQL As String
 
strSearch = "(Surname = '" & Me.cboSurname & "') AND " _
        & "([First_Names]= '" & Me.cboFirstNames & "') AND " _
        & "(DOB = #" & Format(Me.cboDoB, "MM/DD/YYYY") & "#) AND " _
        & "(Gender = '" & Me.cboGender & "')"
 
    Set rsData = Me.Recordset.Clone
    rsData.FindFirst strSearch
    If Not rsData.EOF Then Me.Bookmark = rsData.Bookmark
 
' Some other stuff
End Sub

The search string is made up of the contents of four cascading combo boxes in the form's header and the form does in fact show the correct student's details.

However.... another student's record is changed so that the other student gets given the Surname of the student that I search for!

I suspect that this is because I'm working with two recordsets, but I don't understand it at all. Do I need to apply an index to the cloned recordset?

I also can't see the relationship between the record searched for and the one that gets changed. If I close the form and re-open it and search for the same student, another, different record gets changed.

No wonder the wierdo right-wingers are against cloning! :)

Jim (the mad scientist's Igor)

Try changing Lines 6 and 14, if not let's see the 'other stuff':

Private Sub cboGender_Exit(Cancel As Integer)
    ' Find the record that matches the form header.
Dim dbNA As Database
Dim strSearch As String
Dim rsSchoolName As Recordset
Dim rsData As DAO.Recordset
Dim strSQL As String
 
strSearch = "(Surname = '" & Me.cboSurname & "') AND " _
        & "([First_Names]= '" & Me.cboFirstNames & "') AND " _
        & "(DOB = #" & Format(Me.cboDoB, "MM/DD/YYYY") & "#) AND " _
        & "(Gender = '" & Me.cboGender & "')"
 
Set rsData = Me.RecordsetClone
rsData.FindFirst strSearch
If Not rsData.EOF Then Me.Bookmark = rsData.Bookmark
 
' Some other stuff
End Sub

[JustJim]

Try changing Lines 6 and 14, if not let's see the 'other stuff':

Private Sub cboGender_Exit(Cancel As Integer)
    ' Find the record that matches the form header.
Dim dbNA As Database
Dim strSearch As String
Dim rsSchoolName As Recordset
Dim rsData As DAO.Recordset
Dim strSQL As String
 
strSearch = "(Surname = '" & Me.cboSurname & "') AND " _
        & "([First_Names]= '" & Me.cboFirstNames & "') AND " _
        & "(DOB = #" & Format(Me.cboDoB, "MM/DD/YYYY") & "#) AND " _
        & "(Gender = '" & Me.cboGender & "')"
 
Set rsData = Me.RecordsetClone
rsData.FindFirst strSearch
If Not rsData.EOF Then Me.Bookmark = rsData.Bookmark
 
' Some other stuff
End Sub

I'll try than now. I don't think it can be the "other stuff". The problem occurs with the other stuff Rem'd out and a breakpoint before it.

I'll be back.

Jim

[JustJim]

OK changed the on-exit event of the last combo box to:-

Private Sub cboGender_Exit(Cancel As Integer)
    ' Find the record that matches the form header.
Dim dbNA As Database
Dim strSearch As String
Dim rsSchoolName As Recordset
Dim rsData As DAO.Recordset
Dim strSQL As String
 
strSearch = "(Surname = '" & Me.cboSurname & "') AND " _
        & "([First_Names] = '" & Me.cboFirstNames & "') AND " _
        & "(DOB = #" & Format(Me.cboDoB, "MM/DD/YYYY") & "#) AND " _
        & "(Gender = '" & Me.cboGender & "')"
 
    Set rsData = Me.RecordsetClone
    rsData.FindFirst strSearch
    If Not rsData.EOF Then Me.Bookmark = rsData.Bookmark
 
'strSQL = "SELECT ALL_SCHOOLS.SCHOOL_NAME " _
'        & "FROM ALL_SCHOOLS INNER JOIN ALL_CAMPUSES ON ALL_SCHOOLS.SCHOOL_NO = ALL_CAMPUSES.SCHOOL_NO " _
'        & "WHERE (((ALL_CAMPUSES.SCHOOL_NO)= " & Me.txtSchoolNo & ") AND ((ALL_CAMPUSES.CAMPUS_NO) = " & Me.txtCampusNo & "))"
'Set dbNA = CurrentDb()
'Set rsSchoolName = dbNA.OpenRecordset(strSQL, dbOpenDynaset)
'Me!txtEnrolmentSchool.Value = rsSchoolName!SCHOOL_NAME
 
'strSQL = "SELECT ALL_SCHOOLS.SCHOOL_NAME " _
'        & "FROM Tbl_Outpost_Hosts INNER JOIN (ALL_SCHOOLS INNER JOIN ALL_CAMPUSES ON ALL_SCHOOLS.SCHOOL_NO = ALL_CAMPUSES.SCHOOL_NO) ON Tbl_Outpost_Hosts.Host_School_Number = ALL_SCHOOLS.SCHOOL_NO " _
'        & "WHERE ((Tbl_Outpost_Hosts.Host_School_Number)= " & Me.cboOutpost_Host_ID.Column(1) & ")"
'Set dbNA = CurrentDb()
'Set rsSchoolName = dbNA.OpenRecordset(strSQL, dbOpenDynaset)
'If rsSchoolName.EOF Then
'    Me!txtOutpostHost.Value = "Not Set"
'Else
'    Me!txtOutpostHost.Value = rsSchoolName!SCHOOL_NAME
'End If
 
'Me.cboProg_Type_ID.SetFocus
End Sub

(this includes the 'other stuff')
I thought I was doing OK because when it stoped at a breakpoint before the rem'd out stuff I got:

? rsdata!surname, rsdata!first_Names, me.RecordsetClone!surname, me.RecordsetClone!first_names
nang Htay Htay hlaing nang Htay Htay hlaing

in the immediate window (don't laugh, it's her name - yes really)
but when I let it finish and look at tblStudents, I find two with the surname "Nang" where there was only one before!
I can post the cascading combo boxes if you think that may be the problem.

Jim

[JustJim]

Just noticed that the parser didn't capitalise the dao in dao.RecordSet. I do have

Microsoft 3.6 DAO Object Library

selected as a reference though.

Jim