DoCmd.GoToRecord issue

[dellehurley]

Hi
I can't find what I'm doing wrong
the issues is with this line
The DLookup finds ID_FlNm=2 (the primary key) but when I try use DoCmd.GoToRecord acDataForm, "frmFileName", acGoTo, ID_FlNm the error you cannot go to the specified record.

Private Sub cmdGetFile_Click()
Const msoFileDialogFilePicker As Long = 3
Dim objDialog As Object, db As DAO.Database, rst As DAO.Recordset
Dim OrgName_Path As String, OrgName As String, ReturnVal As Integer, IDno As Integer
Dim NewFileName, NewFile_Path As String, FileNo As String, ID_FlNm As Long, Ans As VbMsgBoxResult

Set objDialog = Application.FileDialog(msoFileDialogFilePicker)
StartAgain:
    With objDialog
        ReturnVal = .Show
        If ReturnVal = -1 Then
            OrgName_Path = .SelectedItems(1)
            Me.txtPathtoEdit.Value = OrgName_Path
            OrgName = Extract(OrgName_Path)
            ID_FlNm = DLookup("[ID_FlNm]", "tblFileName", "[FileName]='" & OrgName & "'")
                If ID_FlNm > 0 Then
                    Ans = MsgBox("This File Name already exists" & vbNewLine & "      YES - Edit File? or" & vbNewLine _
                    & "      NO - ReName File? or" & vbNewLine & "      CANCEL - Select New File?", vbYesNoCancel, "File Name Already Exists")
                        If Ans = vbYes Then
                            DoCmd.GoToRecord acDataForm, "frmFileName", acGoTo, ID_FlNm
                        ElseIf Ans = vbNo Then GoTo NewFileName

Many Thanks
Dannielle

 

[Micron]

Since you don't seem to be adding a record there (which is a common reason for the problem) then perhaps the ID_F1Nm isn't unique in the table, or it isn't an indexed field (or both). Looks to me like the variable is a string that contains a file path.
Or your form has an unsaved record on it, or a required field has not been filled in.

 

[dellehurley]

Since you don't seem to be adding a record there (which is a common reason for the problem) then perhaps the ID_F1Nm isn't unique in the table, or it isn't an indexed field (or both). Looks to me like the variable is a string that contains a file path.
Or your form has an unsaved record on it, or a required field has not been filled in.

This is the Get command button Get File procedure. ID_FlNm is the primary index.
The line OrgName = Extract(OrgName_Path) is a function to remove the filename from the path.
Then ID_FlNm = Not IsNull(DLookup("[ID_FlNm]", "tblFileName", "[FileName]='" & OrgName & "'")) is to ascertain if the file name has been previously processed or not.
Then if ID_FlNn is found then I want to edit that record rather than create another and that is where the DoCmd.GoToRecord acDataForm, "frmFileName", acGoTo, ID_FlNm comes into effect.
I have attached some screenshot which might make the issue clearer.

 

[Micron]

If any of that covers the possibilities I raised, I don't see it.

Or your form has an unsaved record on it, or a required field has not been filled in.

It does show that ID_F1Nm is an indexed field though. You could consider posting file somewhere if you remain stuck and I'll look at it when I can.

 

[dellehurley]

If any of that covers the possibilities I raised, I don't see it.

It does show that ID_F1Nm is an indexed field though. You could consider posting file somewhere if you remain stuck and I'll look at it when I can.

Sorry I thought my reply might add clarity BUT Nothing like fresh eyes and a good night sleep. I found the culprit -your last suggestion ...your form has an unsaved record on it, ... so the work around is to clear the form then go to the record. Thank you for your time and patience.
Dannielle

 

[Micron]

you probably do that with
If Me.Dirty Then Me.Dirty = False
either in that procedure (if it is in the form module) or in the code that calls that procedure.

 

[dellehurley]

If any of that covers the possibilities I raised, I don't see it.

It does show that ID_F1Nm is an indexed field though. You could consider posting file somewhere if you remain stuck and I'll look at it when I can.

you probably do that with
If Me.Dirty Then Me.Dirty = False
either in that procedure (if it is in the form module) or in the code that calls that procedure.

Thanks again, I appreciate you taking the time to help

 

[Micron]

You're welcome, and good luck with your project.