Ok here's the code:
1) Go to Modules Tab and create a new module and paste the code below. Save
and name the module as you like
2) Create a new button on your form and named it cmdBrowseFile and put this
under Click event:
Me.txtMyFileName = ahtCommonFileOpenSaveAllFiles()
hth,
George
'to be pasted in the new module as per item#1:
'========== CODE START ===============
Option Compare Database
Option Explicit
Public strPubImageFileName As String
Type tagOPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000
Function ahtCommonFileOpenSaveAllFiles( _
Optional ByRef flags As Variant, _
Optional ByVal InitialDir As Variant, _
Optional ByVal Filter As Variant, _
Optional ByVal FilterIndex As Variant, _
Optional ByVal DefaultExt As Variant, _
Optional ByVal FileName As Variant, _
Optional ByVal DialogTitle As Variant, _
Optional ByVal hWnd As Variant, _
Optional ByVal OpenFile As Variant) As Variant
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
' Give the dialog a caption title.
If IsMissing(InitialDir) Then InitialDir = CurDir
Dim strFilter As String
strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*;")
If IsMissing(Filter) Then Filter = strFilter
If IsMissing(FilterIndex) Then FilterIndex = 1
If IsMissing(flags) Then flags = 0&
If IsMissing(DefaultExt) Then DefaultExt = ""
If IsMissing(FileName) Then FileName = ""
If IsMissing(DialogTitle) Then DialogTitle = "Browse"
If IsMissing(hWnd) Then hWnd = Application.hWndAccessApp
If IsMissing(OpenFile) Then OpenFile = True
' Allocate string space for the returned strings.
strFileName = Left(FileName & String(256, 0), 256)
strFileTitle = String(256, 0)
' Set up the data structure before you call the function
With OFN
.lStructSize = Len(OFN)
.hwndOwner = hWnd
.strFilter = Filter
.nFilterIndex = FilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = DialogTitle
.flags = flags
.strDefExt = DefaultExt
.strInitialDir = InitialDir
' Didn't think most people would want to deal with
' these options.
.hInstance = 0
'.strCustomFilter = ""
'.nMaxCustFilter = 0
.lpfnHook = 0
'New for NT 4.0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
End With
' This will pass the desired data structure to the
' Windows API, which will in turn it uses to display
' the Open/Save As Dialog.
If OpenFile Then
fResult = aht_apiGetOpenFileName(OFN)
Else
fResult = aht_apiGetSaveFileName(OFN)
End If
' The function call filled in the strFileTitle member
' of the structure. You'll have to write special code
' to retrieve that if you're interested.
If fResult Then
' You might care to check the Flags member of the
' structure to get information about the chosen file.
' In this example, if you bothered to pass in a
' value for Flags, we'll fill it in with the outgoing
' Flags value.
If Not IsMissing(flags) Then flags = OFN.flags
ahtCommonFileOpenSaveAllFiles = TrimNull(OFN.strFile)
Else
ahtCommonFileOpenSaveAllFiles = vbNullString
End If
End Function
Function ahtAddFilterItem(strFilter As String, _
strDescription As String, Optional varItem As Variant) As String
If IsMissing(varItem) Then varItem = "*.*"
ahtAddFilterItem = strFilter & _
strDescription & vbNullChar & _
varItem & vbNullChar
End Function
Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
intPos = InStr(strItem, vbNullChar)
If intPos > 0 Then
TrimNull = Left(strItem, intPos - 1)
Else
TrimNull = strItem
End If
End Function
'************** Code End *****************
-----Original Message-----
From: H.W. Vadney [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 01, 2006 5:13 PM
To: [EMAIL PROTECTED]
Subject: Re: [ms_access] Expression Results to Table
Hello, George:
"> so this is exactly what you're looking for.. if you want I can send you a
> piece of code that will allow you to browse any file from your pc or
> network
> then save the full path automatically to your field."
That would be extremely useful! Please do.
> Do you remember how you put the code behind the after update events on
> your
> date fields? Do the same too but under Click event.
Yes. I used code building, I think. Should I do the same in this case?
Thanks very much, George!
Harold
----- Original Message -----
From: "George Oro" <[EMAIL PROTECTED]>
To: "H.W. Vadney" <[EMAIL PROTECTED]>
Sent: Wednesday, November 01, 2006 1:01 AM
Subject: RE: [ms_access] Expression Results to Table
> Don't put like that...
> Do you remember how you put the code behind the after update events on
> your
> date fields? Do the same too but under Click event.
>
> ---------
> so this is exactly what you're looking for.. if you want I can send you a
> piece of code that will allow you to browse any file from your pc or
> network
> then save the full path automatically to your field.
>
> the reason i used the hyperlink address so you can open any files.
>
>
> hth,
> george
>
>
>
> -----Original Message-----
> From: H.W. Vadney [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 01, 2006 12:33 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [ms_access] Expression Results to Table
>
>
> Hi, George:
>
> Thanks for the suggestion. I am attaching an error message that I receive
> when following the instructions you provided.
>
> Any recommendation will be appreciated.
>
> Thanks,
>
> Harold
>
> ----- Original Message -----
> From: "George Oro" <[EMAIL PROTECTED]>
> To: "H.W. Vadney" <[EMAIL PROTECTED]>
> Sent: Tuesday, October 31, 2006 3:45 AM
> Subject: RE: [ms_access] Expression Results to Table
>
>
>> Good you made it...
>>
>> Regarding the linking of external documents, try to play with this.
>> 1) Put a text box and named it txtMyFileName and type there the full path
>> of
>> the documents eg.
>> C:\Database\Networking\MyDocuments.doc
>>
>> 2) Add a command button and named it cmdOpenFile
>> 3) Then put this under Click Event
>> Me.cmdOpenFile.HyperlinkAddress = Me.txtMyFileName
>> 4) Click the button and see...
>>
>> hope this what you're looking for.
>>
>> george
>>
>>
>>
>>
>> -----Original Message-----
>> From: H.W. Vadney [mailto:[EMAIL PROTECTED]
>> Sent: Monday, October 30, 2006 4:59 PM
>> To: [EMAIL PROTECTED]
>> Subject: Re: [ms_access] Expression Results to Table
>>
>>
>> OK. I'm an idiot! I found the problem. I did not bind the control source
>> for
>> Difference to the Difference column in the table.
>>
>> I do apologize for this bit of cognitive lapse ;0 !
>>
>> Now all I need to do is insert a control for each record that links it to
>> a
>> specific document or part of a document and displays the document on
>> click!
>>
>> Thanks for all of your help but I really need to ask a question: Are you
>> a
>> developer or just a very savvy user?
>>
>> Thanks very much!
>>
>> Harold
>>
>> ----- Original Message -----
>> From: "George Oro" <[EMAIL PROTECTED]>
>> To: "H.W. Vadney" <[EMAIL PROTECTED]>
>> Sent: Monday, October 30, 2006 12:33 AM
>> Subject: RE: [ms_access] Expression Results to Table
>>
>>
>>> Yes, because you're calculating this two date fields, so any of these
>>> fields
>>> changed then it always get the difference.
>>>
>>> hth,
>>> george
>>>
>>>
>>> -----Original Message-----
>>> From: H.W. Vadney [mailto:[EMAIL PROTECTED]
>>> Sent: Monday, October 30, 2006 12:35 AM
>>> To: [EMAIL PROTECTED]
>>> Subject: Re: [ms_access] Expression Results to Table
>>>
>>>
>>> Hi, George:
>>>
>>> Do you mean I should add this expression to BOTH "DateDue" AND
>>> "DateReceived" AfterEvents?
>>>
>>> Thanks,
>>>
>>> Harold
>>>
>>> ----- Original Message -----
>>> From: "George Oro" <[EMAIL PROTECTED]>
>>> To: "H.W. Vadney" <[EMAIL PROTECTED]>
>>> Sent: Sunday, October 29, 2006 2:30 AM
>>> Subject: RE: [ms_access] Expression Results to Table
>>>
>>>
>>>> Hi Harold,
>>>>
>>>> Sorry the late reply as I was on sick leave.
>>>>
>>>> First remove the Control Source of Difference field then
>>>> put or add this under AfterEvents properties of DateDue and
>>>> DateReceived
>>>>
>>>> if not isnull([DateDue]) or not isnull([DateReceived]) then
>>>> Difference=DateDiff("d",[DateDue],[DateReceived])
>>>> else
>>>> Difference=0
>>>> end if
>>>>
>>>> i didn't actually tried to your file but it should work.
>>>>
>>>> hth,
>>>> george
>>>>
>>>> PS.
>>>> Regarding your offer, thank you very much but don't mentioned it.
>>>> It's our pleasure here if the solutions we give works.
>>>>
>>>> Favourite Artist? Bon Jovi ;D
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: H.W. Vadney [mailto:[EMAIL PROTECTED]
>>>> Sent: Thursday, October 26, 2006 4:31 PM
>>>> To: [EMAIL PROTECTED]
>>>> Subject: Re: [ms_access] Expression Results to Table
>>>>
>>>>
>>>> Good morning, George:
>>>>
>>>> Thank you ever so much! It works!
>>>>
>>>> Just one more favor, if you don't mind: I need to get the "Difference"
>>>> field
>>>> value into the table. Can you do that in the file I sent?
>>>>
>>>> But I can accept the favor only if you let me have your mailing adress
>>>> so
>>>> I
>>>> can send you a little Thank you! for all of your kind and generous
>>>> help!
>>>>
>>>> I really need to be able to accept your help so please let me send you
>>>> a
>>>> gift. Something small like a CD or something. Do you have a favorite
>>>> artist?
>>>>
>>>> Very gratefully,
>>>>
>>>> Harold
>>>>
>>>>
>>>> ----- Original Message -----
>>>> From: "George Oro" <[EMAIL PROTECTED]>
>>>> To: <[EMAIL PROTECTED]>
>>>> Sent: Thursday, October 26, 2006 3:31 AM
>>>> Subject: FW: [ms_access] Expression Results to Table
>>>>
>>>>
>>>>>
>>>>> in case the group blocks the attachment.
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: George Oro [mailto:[EMAIL PROTECTED]
>>>>> Sent: Thursday, October 26, 2006 10:34 AM
>>>>> To: H.W. Vadney
>>>>> Subject: RE: [ms_access] Expression Results to Table
>>>>>
>>>>>
>>>>> Hi Harold,
>>>>>
>>>>> See the attached and check the changes I made:
>>>>>
>>>>> 1) Table structure = see the Format and Input Mask of date fields
>>>>> 2) Forms = see also the format and input mask of date fields
>>>>> 3) Forms = As I advised before to put the expression under AfterUpdate
>>>>> events, see the AfterUpdate events of DateServe to see the example and
>>>>> do
>>>>> apply to the other date fields you want to calculate.
>>>>>
>>>>> hth,
>>>>> George
>>>>>
>>>>>
>>>>>
>>>>> -----Original Message-----
>>>>> From: H.W. Vadney [mailto:[EMAIL PROTECTED]
>>>>> Sent: Wednesday, October 25, 2006 10:54 PM
>>>>> To: [EMAIL PROTECTED]
>>>>> Subject: Re: [ms_access] Expression Results to Table
>>>>>
>>>>>
>>>>> Hi, George!
>>>>>
>>>>> Thanks very much for your message and help.
>>>>>
>>>>> I tried inserting the expression but kept getting an error message.
>>>>>
>>>>> I am attaching the dbase. Can you see if it works for you?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Harold
>>>>>
>>>>> P.s. The screenshot is of an error message I get when I try Dawn
>>>>> Crosier's
>>>>> suggestion.
>>>>>
>>>>>
>>>>> ----- Original Message -----
>>>>> From: "George Oro" <[EMAIL PROTECTED]>
>>>>> To: <[EMAIL PROTECTED]>
>>>>> Sent: Wednesday, October 25, 2006 8:33 AM
>>>>> Subject: FW: [ms_access] Expression Results to Table
>>>>>
>>>>>
>>>>>>
>>>>>> Im sending this directly in case you didn't see my reply...
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: [email protected] [mailto:[EMAIL PROTECTED]
>>>>>> Behalf Of George Oro
>>>>>> Sent: Wednesday, October 25, 2006 4:30 PM
>>>>>> To: [email protected]
>>>>>> Subject: RE: [ms_access] Expression Results to Table
>>>>>>
>>>>>>
>>>>>> No reply? obviously nobody reply yet but I can see all your post.
>>>>>>
>>>>>> Regarding your question, putting an expression on the control source
>>>>>> is
>>>>>> just
>>>>>> for viewing purpose and it will not save to your table not unless you
>>>>>> will
>>>>>> reference the value to the actual field when saving.
>>>>>>
>>>>>> What you can is, put the expression on the after update event of
>>>>>> DateServed.
>>>>>>
>>>>>> if not isnull([DateServed]) then
>>>>>> me.DateDue=DateAdd("d",+21,[DateServed])
>>>>>> else
>>>>>> me.datedue=null
>>>>>> end if
>>>>>>
>>>>>> hth,
>>>>>> george
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> -----Original Message-----
>>>>>> From: [email protected] [mailto:[EMAIL PROTECTED]
>>>>>> Behalf Of hvadney
>>>>>> Sent: Wednesday, October 25, 2006 3:58 PM
>>>>>> To: [email protected]
>>>>>> Subject: [ms_access] Expression Results to Table
>>>>>>
>>>>>>
>>>>>> Hi, everyone:
>>>>>>
>>>>>> I was wondering why I had received NO responses to my question and I
>>>>>> now see why: my messages are not appearing on the list. Any clues
>>>>>> why that may be?
>>>>>>
>>>>>> Anyway, I am still having trouble getting my Expression field
>>>>>> results to enter in my table from the form, where the results appear
>>>>>> fine.
>>>>>>
>>>>>> Here's my original question again. Thanks for any help you can
>>>>>> provide!
>>>>>>
>>>>>> *********
>>>>>> Hello, everyone:
>>>>>>
>>>>>> Small problem in my DATEADD expression in Access.
>>>>>>
>>>>>> I have set up the expression:
>>>>>>
>>>>>> =DateAdd("d",+21,[DateServed])
>>>>>>
>>>>>> Where DateServed is the date to which 21 days is to be added to
>>>>>> obtain the value for DateDue.
>>>>>>
>>>>>> When typing the dates into the form the expression works perfecty.
>>>>>>
>>>>>> I have another expression DateDiff:
>>>>>>
>>>>>> =DateDiff("d",[DateDue],[DateReceived])
>>>>>>
>>>>>> which works in the form and returns the difference between the date
>>>>>> due (DateDue) and the date received (DateReceived). It works fine in
>>>>>> the form.
>>>>>>
>>>>>> My problem is that it does not appear in the table in the respective
>>>>>> DateDue and DateReceived columns.
>>>>>>
>>>>>> Note:
>>>>>>
>>>>>> In Properties Data--Control source, the expression appears. When I
>>>>>> click the down arrow to select the field into which the data is to
>>>>>> be stored it shows the expression.
>>>>>>
>>>>>> If I select the field, the field appears in the expression builder
>>>>>> window.
>>>>>>
>>>>>> In other words, if in Properties--Data--Control Source -- I select
>>>>>> in Control Source (down arrow) the field into which the data is to
>>>>>> be inserted, I lose the expression (the fieldname then appears in
>>>>>> the expression builder as well); if I don't select the field but
>>>>>> insert the expression into the box or use the expression builder, I
>>>>>> lose the field name into which the data is to be inserted and the
>>>>>> data is not inserted into the table but it works in the FORM!
>>>>>>
>>>>>> Any ideas why the data is not getting to the Table?
>>>>>>
>>>>>> Thanks,
>>>>>>
>>>>>> Harold
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Yahoo! Groups Links
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> Yahoo! Groups Links
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>
>
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ms_access/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/ms_access/join
(Yahoo! ID required)
<*> To change settings via email:
mailto:[EMAIL PROTECTED]
mailto:[EMAIL PROTECTED]
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
