See below
Ken Snell said:
If you want to write a value to a textbox, then the textbox's Control Source
must be empty or must be the name of a field (not a calculated field) in the
form's Record Source table/query.
If you're going to write the value to the textbox, then remove the
expression from the control source for that textbox.
--
Ken Snell
<MS ACCESS MVP>
Jeffrey Ross said:
See below:
Ken Snell said:
=IIf(Dir("C:\MyFolder\CV" & [ID] & ".doc")<>"", [ID] & ".doc", "")
Ken Snell
<MS ACCESS MVP>
I want to add a document field to a form. Let's call the form Customer
(with existing fields Name and Id) and the new field Resume. The
Resume
field should:
1. Be derived from the Id field, eg if Id=1, Resume=CV1.doc
2. Be blank if there is no corresponding document file, ie if CV1.doc
does not exist, otherwise it should contain the name of the file, ie
CV1.doc
I've tried a text box, but can't make its pre-filled value
conditional
on a
file's existence.
I've tried a combo box but can't get it to pre-fill on opening the
form
or
moving from one Customer to another.
I've managed to test for file existence and create a new file from a
template successfully. Now all that I need is to tidy up my user
interface.
Help would be much appreciated.
Jeffrey.
Thanks, Ken.
I've used a text field with that sort of code in the control source.
What I'd like it to do is pre-fill to the filename when the field is
entered, so I tried setting the on enter property to call the following VB
code:
Private Sub Resume_Enter()
Resume = "CV" & Right$("000" & [ID], 4) & ".doc"
End Sub
However this code halts with a run time error claiming that I can't assign
a
value to that object. I assume that's because I put the code in the
control
source field. Wher should I have put it?
Regards,
Jeffrey.
Thanks Ken.
I found a solution. In simplified terms...
The Control Source is set to =iif(dir$("CV1.doc"),"CV1.doc","")
It displays the filename if it exists, otherwise it is blank. Its On Click
property displays a message box telling the user to click the Word icon
beside the field.
The Word icon's On Click property creates a CV1.doc file from a template if
CV1.doc doesn't exist, otherwise it simply opens CV1.doc. It also requeries
the field so that it is no longer blank.
Jeffrey.
The actual code (for anyone else trying to do the same thing) is:
Rem Use Chr$(34) to put quotes around the filename to handle a filename
containing spaces.
template = "CV Template.doc"
quotedTemplate = Chr$(34) & template & Chr$(34)
Rem If the document field is empty the file does not exist so set up the
name correctly.
If (Len(Document) = 0) Then
doc = "CV" & Right$("000" & [Id], 4) & ".doc"
Else
doc = Document
End If
quotedDoc = Chr$(34) & doc & Chr$(34)
On Error Resume Next
Rem GetObject opens another Word instance
Set wdApp = GetObject(, "Word.Application")
If wdApp Is Nothing Then
Rem CreateObject opens Word if no instance currently exists
Set wdApp = CreateObject("Word.Application")
End If
If (Dir$(doc) = "") Then
If (Dir$(template) = "") Then
MsgBox "The template file (" & template & ") does NOT exist"
Else
If (MsgBox("Create '" & doc & "' from the template?", vbYesNo) =
vbYes) Then
Rem If the directory doesn't exist, create it.
If (Dir$(Mid(doc, 1, InStrRev(doc, "\"))) = "") Then
MkDir (Mid(doc, 1, InStrRev(doc, "\")))
End If
Rem Copy the template to the document location.
On Error GoTo Err_Button_Click
FileCopy template, doc
Else
GoTo Exit_Button_Click
End If
End If
End If
On Error GoTo Err_Button_Click
Rem Open file specified by user
With wdApp
.Documents.Open (quotedDoc)
.Visible = True
Rem Raise and give focus to Word window
AppActivate (wdApp)
End With
Rem Update the text box with a newly created document name (which would
otherwise be blank).
Document.Requery
