docmd.find record not finding the record

J

John Sh

Member
Local time
Today, 18:38
Joined
Feb 8, 2021
Messages
513
I am copying an existing record to a new record using the "copyRecord" code below.
My problem is I can't get to the new record.
The "copyRecord" code creates the new record ok and the "requery" in the "btnCopy" code returns the table to the first record as expected but the subsequent "docmd.findrecord" fails.
Running the same function "goToNewRecord" from a button on the form also fails until I shift focus on the form to another control.
Now I can access the new record. Why is it so?
The code is in calling order.
newAccno as double and Finder as string are public variables

Code: 
Private Sub btnCopy_Click()  
    If UserLevel = 3 Then
        MsgBox "Students are not authorised to copy / paste records!"
        Exit Sub
    End If
    newAccno = Me.txtAccNo
    Call copyRecord("Main", newAccno, "New")
    Me.Requery
    Call gotoNewRecord([Forms]![main collection], finder)
    Call setLocked("Z")
    Me.txtStatus = "R"
    isNewRecord = Me.txtAccNo
    Me.txtAccNo.Locked = True
    isCopy = True
    Me.txtSpec.SetFocus
End Sub


Public Sub copyRecord(strTbl As String, strAcc As Double, strNew As String)
    Dim db As Database
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim sNum As Integer
    Dim lNum As Integer
    Dim strname As String
    finder = Trim(str(strAcc))
    isCopy = False
    sNum = 0
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(strTbl, dbOpensnapshot)
    Set rs2 = db.OpenRecordset("Main", dbOpenDynaset)
    lNum = rs1.Fields.Count - 1
    rs1.MoveLast
    If rs1!AccessionNumber <> strAcc Then
        rs1.MoveFirst
        rs1.FindFirst "Accessionnumber = " & finder
    End If
    rs2.Edit
    rs2.AddNew
    rs2!Label = True
    sNum = 1
    rs2![AccessionNumber] = getAccno()
    finder = rs2!AccessionNumber
    sNum = 2
    Do Until sNum = lNum
        strname = rs1.Fields(sNum).Name
        rs2.Fields(strname) = rs1.Fields(sNum)
        If strNew = "New" Then
            If strname = "Create Date" Then
                rs2.Fields(strname) = Date
            End If
            If strname = "createdby" Then
                rs2.Fields(strname) = strFullName
            End If
            If strname = "Genus" And Nz(rs1.Fields(sNum), "") = "" Then
                rs2.Fields(strname) = "X"
            End If
        End If
        sNum = sNum + 1
    Loop
    rs2.Update
    rs2.Close
    rs1.Close
    DoCmd.SetOrderBy "AccessionNumber"     'If I don't do this and the next line, the form is blank.
    DoCmd.GoToRecord , , acLast
End Sub

Public Function gotoNewRecord(ByVal frm As Form, strFindWhat As String)
    Dim str As String
    Dim nTries As Integer
    frm.Dirty = False
    strFindWhat = Trim(strFindWhat)
    DoCmd.FindRecord strFindWhat, acStart, False, acAll
End Function
 
what is "finder", you set value to it in CopyRecord sub but was not defined first.
i define it i btnCopy_Click event and pass it to CopyRecord.
Code: 
Private Sub btnCopy_Click()
    Dim finder As String
    If UserLevel = 3 Then
        MsgBox "Students are not authorised to copy / paste records!"
        Exit Sub
    End If
    newAccno = Me.txtAccNo
    Call copyRecord("Main", newAccno, "New", finder)
    Me.Requery
    Call gotoNewRecord([Forms]![main collection], finder)
    Call setLocked("Z")
    Me.txtStatus = "R"
    isNewRecord = Me.txtAccNo
    Me.txtAccNo.Locked = True
    isCopy = True
    Me.txtSpec.SetFocus
End Sub


Public Sub copyRecord(strTbl As String, strAcc As Double, strNew As String, ByRef finder As String)
    Dim db As Database
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim sNum As Integer
    Dim lNum As Integer
    Dim strname As String
    finder = Trim(str(strAcc))
    isCopy = False
    sNum = 0
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(strTbl, dbOpenSnapshot)
    Set rs2 = db.OpenRecordset("Main", dbOpenDynaset)
    lNum = rs1.Fields.Count - 1
    rs1.MoveLast
    If rs1!AccessionNumber <> strAcc Then
        rs1.MoveFirst
        rs1.FindFirst "Accessionnumber = " & finder
    End If
    rs2.Edit
    rs2.AddNew
    rs2!Label = True
    sNum = 1
    rs2![AccessionNumber] = getAccno()
    finder = rs2!AccessionNumber
    sNum = 2
    Do Until sNum = lNum
        strname = rs1.Fields(sNum).Name
        rs2.Fields(strname) = rs1.Fields(sNum)
        If strNew = "New" Then
            If strname = "Create Date" Then
                rs2.Fields(strname) = Date
            End If
            If strname = "createdby" Then
                rs2.Fields(strname) = strFullName
            End If
            If strname = "Genus" And Nz(rs1.Fields(sNum), "") = "" Then
                rs2.Fields(strname) = "X"
            End If
        End If
        sNum = sNum + 1
    Loop
    rs2.Update
    rs2.Close
    rs1.Close
    DoCmd.SetOrderBy "AccessionNumber"     'If I don't do this and the next line, the form is blank.
    DoCmd.GoToRecord , , acLast
End Sub

Public Function gotoNewRecord(ByRef frm As Form, Byval strFindWhat As String)
    Dim str As String
    Dim nTries As Integer
    frm.Dirty = False
    strFindWhat = Trim(strFindWhat)
    DoCmd.FindRecord strFindWhat, acStart, False, acAll
End Function
 
arnelgp said:
what is "finder", you set value to it in CopyRecord sub but was not defined first.
i define it i btnCopy_Click event and pass it to CopyRecord.
Click to expand...
As stated, "NewAccno" and "Finder" are public variables defined in a module.
The whole copy function is working fine. My problem is being able to see the new record in a form.
I cannot use the standard copy functions as I have a field, not autonumber, that must be unique.
The value of this field is created by the call to "GetAccno()" which is working correctly.
 
try:
Code: 
Public Function gotoNewRecord(ByRef frm As Form, strFindWhat As String)
    Dim str As String
    Dim nTries As Integer
    'frm.Dirty = False
    strFindWhat = Trim(strFindWhat)
    With frm.RecordsetClone
        .FindFirst "AccessionNumber = " & strFindWhat
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With
    'DoCmd.FindRecord strFindWhat, acStart, False, acAll
End Function
 
arnelgp said:
try:

With frm.RecordsetClone
.FindFirst "AccessionNumber = " & strFindWhat
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Click to expand...
I use the "goToNewRecord" sub elsewhere so I removed it and replaced it with your code above and it works great.
Once again I am ever so thankful for your help.
John
 
We're not there yet. arnelgp's solution worked the first time but not since.
The unique field, Accessionnumber, in my table is a double, mostly in the form 12345.
If the original record has no decimal point the copy code works as expected and I am shown the copied record. Because of the unique value this new record appears at the end of the table with the next available accession number, eg 12366 assuming 12365 is in use.
If, however, there is a decimal involved the copied record is not immediately visible to the system via either docmd.findrecord
or recordset.findfirst.
Where the original record has a decimal, 12345.1, the new record will be numbered 12345.2.
If I move to the original record,12345.1, then DoCmd.GoToRecord , , acNext, the copied record, 12345.2, appears and is now visible to both findrecord and findfirst.
This only works programmaticaly if the original record is the last record on the table.
The new records are numbered in the sub "getAccno", code below.
I hope this explanation makes sense, it's a bit difficult to put into words.
I will work on a sample table and form if needed.


Code: 
Public Function getAccno()
    Dim db As Database
    Dim rs As Recordset
    Dim rs1 As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("lastNum", dbOpenDynaset)
    Set rs1 = db.OpenRecordset("Main", dbOpenSnapshot)
    If newAccno > 0 And Int((newAccno - Int(newAccno)) * 10) > 0 Then
        newAccno = newAccno + 0.1
        getAccno = newAccno
        rs.Close
        Call setAccno
        Exit Function
    End If
    rs.MoveFirst
    rs.Edit
checkNum:
    rs!last_number = rs!last_number + 1
    rs1.FindFirst "int(AccessionNumber) ='" & rs!last_number & "'"
    If rs1.NoMatch Then
        getAccno = rs!last_number
        newAccno = rs!last_number
        GoTo ender
    Else
        GoTo checkNum
    End If
ender:
    rs.Update
    rs.Close
End Function
 
I hope this explanation makes sense, it's a bit difficult to put into words
Click to expand...
then provide some screenshots - pictures are often better than words

The unique field, Accessionnumber, in my table is a double
Click to expand...
note double is not a good datatype for a unique ID - same with dates which are form of double.

in any event you are converting it to an integer here which strips off the decimal part

rs1.FindFirst "int(AccessionNumber) ='" & rs!last_number & "'"
Click to expand...
 
If you need the decimal point I would use a currency format (Which is really decimal fixed to 4 places) for your unique number.
A double will suffer from rounding errors causing matches to fail.

It doesn't appear anywhere that you are re-querying the forms recordset after the insert, meaning that a find would probably fail as it wouldn't see the new record?
 
CJ_London said:
then provide some screenshots - pictures are often better than words


note double is not a good datatype for a unique ID - same with dates which are form of double.

in any event you are converting it to an integer here which strips off the decimal part
Click to expand...
I only use the integer to check if there is a decimal. The double is never converted to integer.
 
Here is a sample database.
If the login screen opens use "me" as the user and "g" as the password.
The table has records from 10840 to 10881. You can select a record by clicking on the blue text box next to "Goto" and entering the "Accession Number". Press "enter" or click "Goto"
Copy any record with an accession number where there is no decimal point, then copy the copy..... will always result in a true copy and the new record will be automatically selected.
Now select record 10877.2 or 10849.2 and copy. After the first copy the last record will be selected, not the copied record. Now navigate to the copied record, 10877.3 or 10849.3 and click the copy button. The new record, .4, will be selected but the information is from the first record in the table, 10840 not 10877.3 or 10849.3 indicating the "findfirst " in the copy function has failed to find the copied record.
Note. do not attempt to copy any other than the highest dot number or the system will fail due to a no duplicate numbers conflict.
 

Attachments

I have modified the code in "getaccno" so that it now increments the dot point.
The strange thing is that this code does find the new records and successfully increases the dot point until a record with that accession number no longer exists.
Unfortunately the behavior of "copyrecord" is unchanged.

Code: 
Public Function getAccno()
    Dim db As Database
    Dim rs As Recordset
    Dim rs1 As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("lastNum", dbOpenDynaset)
    Set rs1 = db.OpenRecordset("Main", dbOpenSnapshot)
    If newAccno > 0 And Int((newAccno - Int(newAccno)) * 10) > 0 Then
checkDec:                                                                                'New code'
        newAccno = newAccno + 0.1
        rs1.FindFirst "Accessionnumber = " & str(newAccno)     'New code'
        If Not rs1.NoMatch Then                                                  'New code'
            GoTo checkDec                                                             'New code'
        End If                                                                                 'New code'
        getAccno = newAccno
        rs.Close
        Call setAccno
        Exit Function
    End If
    rs.MoveFirst
    rs.Edit
checkNum:
    rs!last_number = rs!last_number + 1
    rs1.FindFirst "int(AccessionNumber) ='" & rs!last_number & "'"
    If rs1.NoMatch Then
        getAccno = rs!last_number
        newAccno = rs!last_number
        GoTo ender
    Else
        GoTo checkNum
    End If
ender:
    rs.Update
    rs.Close
 
I have solved part of my problem by using recordset.seek in the "CopyRecord" function.

Code: 
Set rs1 = db.OpenRecordset(strTbl)
    lNum = rs1.Fields.Count - 1
    With rs1
        .Index = "Accessionnumber"
        .Seek "=", newAccno
    End With
 
The second part of my problem was that "docmd.findrecord" was unable to locate the copied record.
I have found a work around that involves writing the value if "finder" to an existing textbox and calling "docmd.findrecord " with the "afterupdate" event of said textbox. This locates, and displays, the correct record every time.
Thank you to those that offered suggestions.
 
You must log in or register to reply here.

Similar threads

K
Solved Opening a recordset and filtering it
Replies
14
Views
510
CJ_London
C
B
VBA problem relating to SQL linked table
Replies
6
Views
923
CJ_London
C
J
Expected =
Replies
2
Views
520
John Sh
J
A
Solved Consolidate Employee Time In/Out take time to load.
Replies
3
Views
379
atzdgreat
A
C
Sendkeys not working anymore
Replies
7
Views
1,214
NauticalGent
NauticalGent

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom