Inconsistent Add Record Performance

[Rob M]

I have a main form with a subform. The subform is used to store the
location of various files for viewing. There's an Add Record command
on the Main form which *sometimes* adds a new record to the subform,
and sometimes overwrites the currently selected record.

I can't find a pattern to whether a record is added or whether an
existing one is overwritten. I've tried switching the focus to
different subdocument records, to different fields within the same
subdocument record, to different main form records, etc., before
pressing the Add Record button. It seems like a random problem from
my perspective...

Can anyone help based on the code below?

Many thanks,
Rob


--
Private Sub cmdAddDocument_Click()
Dim fDialog As Office.FileDialog
Dim DocFileName As String

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog
.AllowMultiSelect = False

.Title = "Please Select a File..."

'Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Word Documents", "*.doc; *.docx"
.Filters.Add "Text Files", "*.txt"
.Filters.Add "Rich Text Files", "*.rtf"
.Filters.Add "All Files", "*.*"

If .Show = True Then
DocFileName = Mid(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\") + 1)

Me.DocumentsSubform.SetFocus
Me.DocumentsSubform.Form.txtDocumentPath.SetFocus

DoCmd.GoToRecord , , acNewRec

' Save full path and filename to view the document
Me.DocumentsSubform.Form.txtDocumentPath.Text
= .SelectedItems(1)
'Me.DocumentsSubform![DocumentPath] = .SelectedItems(1)

' Display filename only for user friendliness
Me.DocumentsSubform.Form.txtDocumentFileName.SetFocus
Me.DocumentsSubform.Form.txtDocumentFileName = DocFileName

' Add the date added
Me.DocumentsSubform.Form.txtDateAdded.SetFocus
Me.DocumentsSubform.Form.txtDateAdded = Now()

' Return focus to Main form
Me.cmdAddDocument.SetFocus
Else
Exit Sub
End If
End With

End Sub
--

 

[Mike Painter]

Why not just add the new record to the table rather than to the form?
With YourTableNameGoes here
.addnew
.txtDateAdded = Now() ' Date() might be better
.txtDocumentFileName = DocFileName
etc
.update
End With
and refresh.

 

[Rob M]

Thank you, I'll try that and post back!

Cheers,
Rob