Using CommonFileOpenSave in a relational table

[Guest]

I’m using Access 2003

The following code is called from a button on a userform.
The routine calls the CommonFileOpenSave box, prompting the user to search
and select a file on the network/local drive.

Once selected, the file and its path is captured in a string
(strInputFileName).

The String is then saved to a table (Con_DocPath) as a hyperlink in the
field (Subject)... this works fine!

The user can repeat the task, as the table is linked as a child of the
primary table.

My problem is that this routine will only place the string in the field
(Subject) programmatically and does not trigger the auto-number field in the
same record, as it would if one was to enter a new record manually. This is
causing broken links between my tables as the missing autonumber is not
establishing the record key and therefore the relationship.

Any ideas how this can be resolved?

Thank you, Paul


Dim strFilter As String
Dim strInputFileName As String
Dim strSQL As String
Dim str1SQL As String
strFilter = ahtAddFilterItem(strFilter, "Word Files (*.doc)", "*.doc")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select the documents associated with
this contract...", _
Flags:=ahtOFN_HIDEREADONLY)

strSQL = "INSERT INTO Con_DocPath (Subject) "

strSQL = strSQL & "VALUES ('" & strInputFileName & "');"

CurrentDb.Execute strSQL

 

[Allen Browne]

Access should automatically supply an AutoNumber when you add a new record.

But if you have the form already open, with (say) the DocName text box in a
subform named Sub1, it might be easier to use something like this:

Me.Sub1.SetFocus
If Not Me.Sub1.Form.NewRecord Then
RunCommand acCmdRecordsGotoNew
End If
Me.Sub1.Form.DocName.SetFocus
RunCommand acCmdInsertHyperlink

 

[Guest]

Thank you for your response.

I records added this way do not appear to to trigger the autonumber in the
table because the data is not bein placed via the form.

I figure there has to be a programmatical approach to this. Unfortunately
i'm not experienced enough to know where to place your suggestion in my code
- would it be in the existing routine, if so, where would you suggest?

I've been loking at 'sendkey' commands to in the vain hope this solution
could provoke access into providing a new key when my string is placed in the
table, but there is a lack of meanigful examples around.

Thanks,

Paul.

 

[Allen Browne]

The code I suggested could go in the Click event of a command button on your
main form, to add a new document to the subform.

The AutoNumber will be assigned when you execute an Append query statement
as per your example. (I am assuming the data is in an Access (JET) table
here.)