How to open the regular filterdialog (with the checkboxen for the different values) in a report with VBA. With the regular filterdialog I mean the one you get when you select a column and then push the filterbutton.
View RepliesI am trying to filter a subreport on open.
In my report I have a field called "Packages" and my subreport consists of a table, one of the columns is "Packages" so I want to use the value that I have in "Packages" to filter for only those items in the sub report.
Is this possible?
I'm trying to use a command button in a form to filter and open a report. I am able to get it to open the report, but I cannot get it to filter the report based on a combobox in the form. I've tried every combination of code I could think of and find. Here is what I currently have:
Code:
Private Sub FilterReport_Click()
DoCmd.OpenReport "Report", acViewReport, "First Name='" & Me.FName & "'"
End Sub
Code:
Option Compare Database
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Me.FName
End Sub
"FilterReport" = Form Button
"Report" = Report
"First Name" = Report Field
"FName" = Form Combo Box
I wish to export a report to excel, but I wish to prompt the user to be able to choose where to save the file at. How can I have the "Save As" dialog box pop up, with the filter set to "*.xls"?
View 2 Replies View RelatedAnother question:
I used the suggested code from this forum to import a file with the open file dialog box. It works great to that point, but how do I specify which table I want it to populate with this data?? Thanks!
Here's the code:
vb Code: Original - vb Code Type tagOPENFILENAMElStructSize As LonghwndOwner As LonghInstance As LongstrFilter As StringstrCustomFilter As StringnMaxCustFilter As LongnFilterIndex As LongstrFile As StringnMaxFile As LongstrFileTitle As StringnMaxFileTitle As LongstrInitialDir As StringstrTitle As StringFlags As LongnFileOffset As IntegernFileExtension As IntegerstrDefExt As StringlCustData As LonglpfnHook As LonglpTemplateName As StringEnd TypeDeclare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As BooleanDeclare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As BooleanDeclare Function CommDlgExtendedError Lib "comdlg32.dll" () As LongGlobal Const ahtOFN_READONLY = &H1Global Const ahtOFN_OVERWRITEPROMPT = &H2Global Const ahtOFN_HIDEREADONLY = &H4Global Const ahtOFN_NOCHANGEDIR = &H8Global Const ahtOFN_SHOWHELP = &H10' You won't use these.'Global Const ahtOFN_ENABLEHOOK = &H20'Global Const ahtOFN_ENABLETEMPLATE = &H40'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80Global Const ahtOFN_NOVALIDATE = &H100Global Const ahtOFN_ALLOWMULTISELECT = &H200Global Const ahtOFN_EXTENSIONDIFFERENT = &H400Global Const ahtOFN_PATHMUSTEXIST = &H800Global Const ahtOFN_FILEMUSTEXIST = &H1000Global Const ahtOFN_CREATEPROMPT = &H2000Global Const ahtOFN_SHAREAWARE = &H4000Global Const ahtOFN_NOREADONLYRETURN = &H8000Global Const ahtOFN_NOTESTFILECREATE = &H10000Global Const ahtOFN_NONETWORKBUTTON = &H20000Global Const ahtOFN_NOLONGNAMES = &H40000' New for Windows 95Global Const ahtOFN_EXPLORER = &H80000Global Const ahtOFN_NODEREFERENCELINKS = &H100000Global Const ahtOFN_LONGNAMES = &H200000Function TestIt()Dim strFilter As StringDim lngFlags As LongstrFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _"*.MDA;*.MDB")strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:", _Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _DialogTitle:="Hello! Open Me!")' Since you passed in a variable for lngFlags,' the function places the output flags value in the variable.Debug.Print Hex(lngFlags)End FunctionFunction GetOpenFile(Optional varDirectory As Variant, _Optional varTitleForDialog As Variant) As Variant' Here's an example that gets an Access database name.Dim strFilter As StringDim lngFlags As LongDim varFileName As Variant' Specify that the chosen file must already exist,' don't change directories when you're done' Also, don't bother displaying' the read-only box. It'll only confuse people.lngFlags = ahtOFN_FILEMUSTEXIST Or _ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIRIf IsMissing(varDirectory) ThenvarDirectory = ""End IfIf IsMissing(varTitleForDialog) ThenvarTitleForDialog = ""End If' Define the filter string and allocate space in the "c"' string Duplicate this line with changes as necessary for' more file templates.strFilter = ahtAddFilterItem(strFilter, _"Access (*.mdb)", "*.MDB;*.MDA")' Now actually call to get the file name.varFileName = ahtCommonFileOpenSave( _OpenFile:=True, _InitialDir:=varDirectory, _Filter:=strFilter, _Flags:=lngFlags, _DialogTitle:=varTitleForDialog)If Not IsNull(varFileName) ThenvarFileName = TrimNull(varFileName)End IfGetOpenFile = varFileNameEnd FunctionFunction ahtCommonFileOpenSave( _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' This is the entry point you'll use to call the common' file open/save dialog. The parameters are listed' below, and all are optional.'' In:' Flags: one or more of the ahtOFN_* constants, OR'd together.' InitialDir: the directory in which to first look' Filter: a set of file filters, set up by calling' AddFilterItem. See examples.' FilterIndex: 1-based integer indicating which filter' set to use, by default (1 if unspecified)' DefaultExt: Extension to use if the user doesn't enter one.' Only useful on file saves.' FileName: Default value for the file name text box.' DialogTitle: Title for the dialog.' hWnd: parent window handle' OpenFile: Boolean(True=Open File/False=Save As)' Out:' Return Value: Either Null or the selected filenameDim OFN As tagOPENFILENAMEDim strFileName As StringDim strFileTitle As StringDim fResult As Boolean' Give the dialog a caption title.If IsMissing(InitialDir) Then InitialDir = CurDirIf IsMissing(Filter) Then Filter = ""If IsMissing(FilterIndex) Then FilterIndex = 1If IsMissing(Flags) Then Flags = 0&If IsMissing(DefaultExt) Then DefaultExt = ""If IsMissing(FileName) Then FileName = ""If IsMissing(DialogTitle) Then DialogTitle = ""If IsMissing(hwnd) Then hwnd = Application.hWndAccessAppIf 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 functionWith 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 = 255End 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 ThenfResult = aht_apiGetOpenFileName(OFN)ElsefResult = 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.FlagsahtCommonFileOpenSave = TrimNull(OFN.strFile)ElseahtCommonFileOpenSave = vbNullStringEnd IfEnd FunctionFunction ahtAddFilterItem(strFilter As String, _strDescription As String, Optional varItem As Variant) As String' Tack a new chunk onto the file filter.' That is, take the old value, stick onto it the description,' (like "Databases"), a null character, the skeleton' (like "*.mdb;*.mda") and a final null character.If IsMissing(varItem) Then varItem = "*.*"ahtAddFilterItem = strFilter & _strDescription & vbNullChar & _varItem & vbNullCharEnd FunctionPrivate Function TrimNull(ByVal strItem As String) As StringDim intPos As IntegerintPos = InStr(strItem, vbNullChar)If intPos > 0 ThenTrimNull = Left(strItem, intPos - 1)ElseTrimNull = strItemEnd IfEnd FunctionPrivate Sub Command20_Click()Dim strFilter As StringDim strInputFileName As StringstrFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.csv")strInputFileName = ahtCommonFileOpenSave( _Filter:=strFilter, OpenFile:=True, _DialogTitle:="Please select an input file...", _Flags:=ahtOFN_HIDEREADONLY)End Sub'This code was originally written by Ken Getz.'It is not to be altered or distributed,'except as part of an application.'You are free to use it in any application,'provided the copyright notice is left unchanged.'' Code courtesy of:' Microsoft Access 95 How-To' Ken Getz and Paul Litwin' Waite Group Press, 1996 Type tagOPENFILENAMElStructSize As LonghwndOwner As LonghInstance As LongstrFilter As StringstrCustomFilter As StringnMaxCustFilter As LongnFilterIndex As LongstrFile As StringnMaxFile As LongstrFileTitle As StringnMaxFileTitle As LongstrInitialDir As StringstrTitle As StringFlags As LongnFileOffset As IntegernFileExtension As IntegerstrDefExt As StringlCustData As LonglpfnHook As LonglpTemplateName As StringEnd 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 BooleanDeclare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long Global Const ahtOFN_READONLY = &H1Global Const ahtOFN_OVERWRITEPROMPT = &H2Global Const ahtOFN_HIDEREADONLY = &H4Global Const ahtOFN_NOCHANGEDIR = &H8Global Const ahtOFN_SHOWHELP = &H10' You won't use these.'Global Const ahtOFN_ENABLEHOOK = &H20'Global Const ahtOFN_ENABLETEMPLATE = &H40'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80Global Const ahtOFN_NOVALIDATE = &H100Global Const ahtOFN_ALLOWMULTISELECT = &H200Global Const ahtOFN_EXTENSIONDIFFERENT = &H400Global Const ahtOFN_PATHMUSTEXIST = &H800Global Const ahtOFN_FILEMUSTEXIST = &H1000Global Const ahtOFN_CREATEPROMPT = &H2000Global Const ahtOFN_SHAREAWARE = &H4000Global Const ahtOFN_NOREADONLYRETURN = &H8000Global Const ahtOFN_NOTESTFILECREATE = &H10000Global Const ahtOFN_NONETWORKBUTTON = &H20000Global Const ahtOFN_NOLONGNAMES = &H40000' New for Windows 95Global Const ahtOFN_EXPLORER = &H80000Global Const ahtOFN_NODEREFERENCELINKS = &H100000Global Const ahtOFN_LONGNAMES = &H200000 Function TestIt()Dim strFilter As StringDim lngFlags As LongstrFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _"*.MDA;*.MDB")strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:", _Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _DialogTitle:="Hello! Open Me!")' Since you passed in a variable for lngFlags,' the function places the output flags value in the variable.Debug.Print Hex(lngFlags)End Function Function GetOpenFile(Optional varDirectory As Variant, _Optional varTitleForDialog As Variant) As Variant' Here's an example that gets an Access database name.Dim strFilter As StringDim lngFlags As LongDim varFileName As Variant' Specify that the chosen file must already exist,' don't change directories when you're done' Also, don't bother displaying' the read-only box. It'll only confuse people.lngFlags = ahtOFN_FILEMUSTEXIST Or _ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIRIf IsMissing(varDirectory) ThenvarDirectory = ""End IfIf IsMissing(varTitleForDialog) ThenvarTitleForDialog = ""End If ' Define the filter string and allocate space in the "c"' string Duplicate this line with changes as necessary for' more file templates.strFilter = ahtAddFilterItem(strFilter, _"Access (*.mdb)", "*.MDB;*.MDA")' Now actually call to get the file name.varFileName = ahtCommonFileOpenSave( _OpenFile:=True, _InitialDir:=varDirectory, _Filter:=strFilter, _Flags:=lngFlags, _DialogTitle:=varTitleForDialog)If Not IsNull(varFileName) ThenvarFileName = TrimNull(varFileName)End IfGetOpenFile = varFileNameEnd Function Function ahtCommonFileOpenSave( _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' This is the entry point you'll use to call the common' file open/save dialog. The parameters are listed' below, and all are optional.'' In:' Flags: one or more of the ahtOFN_* constants, OR'd together.' InitialDir: the directory in which to first look' Filter: a set of file filters, set up by calling' AddFilterItem. See examples.' FilterIndex: 1-based integer indicating which filter' set to use, by default (1 if unspecified)' DefaultExt: Extension to use if the user doesn't enter one.' Only useful on file saves.' FileName: Default value for the file name text box.' DialogTitle: Title for the dialog.' hWnd: parent window handle' OpenFile: Boolean(True=Open File/False=Save As)' Out:' Return Value: Either Null or the selected filenameDim OFN As tagOPENFILENAMEDim strFileName As StringDim strFileTitle As StringDim fResult As Boolean' Give the dialog a caption title.If IsMissing(InitialDir) Then InitialDir = CurDirIf IsMissing(Filter) Then Filter = ""If IsMissing(FilterIndex) Then FilterIndex = 1If IsMissing(Flags) Then Flags = 0&If IsMissing(DefaultExt) Then DefaultExt = ""If IsMissing(FileName) Then FileName = ""If IsMissing(DialogTitle) Then DialogTitle = ""If IsMissing(hwnd) Then hwnd = Application.hWndAccessAppIf 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 functionWith 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 = 255End 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 ThenfResult = aht_apiGetOpenFileName(OFN)ElsefResult = 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.FlagsahtCommonFileOpenSave = TrimNull(OFN.strFile)ElseahtCommonFileOpenSave = vbNullStringEnd IfEnd Function Function ahtAddFilterItem(strFilter As String, _strDescription As String, Optional varItem As Variant) As String' Tack a new chunk onto the file filter.' That is, take the old value, stick onto it the description,' (like "Databases"), a null character, the skeleton' (like "*.mdb;*.mda") and a final null character. If IsMissing(varItem) Then varItem = "*.*"ahtAddFilterItem = strFilter & _strDescription & vbNullChar & _varItem & vbNullCharEnd Function Private Function TrimNull(ByVal strItem As String) As StringDim intPos As IntegerintPos = InStr(strItem, vbNullChar)If intPos > 0 ThenTrimNull = Left(strItem, intPos - 1)ElseTrimNull = strItemEnd IfEnd Function Private Sub Command20_Click() Dim strFilter As String Dim strInputFileName As String strFilter = ahtAddFilterItem(strFilter, "CSV Files (*.csv)", "*.csv")strInputFileName = ahtCommonFileOpenSave( _Filter:=strFilter, OpenFile:=True, _DialogTitle:="Please select an input file...", _Flags:=ahtOFN_HIDEREADONLY) End Sub 'This code was originally written by Ken Getz.'It is not to be altered or distributed,'except as part of an application.'You are free to use it in any application,'provided the copyright notice is left unchanged.'' Code courtesy of:' Microsoft Access 95 How-To' Ken Getz and Paul Litwin' Waite Group Press, 1996
Thanks!!
KellyJo
Hello,
I would like to put a cmdbutton on a form to open the File Dialog box directly from Access whereby users can search through folders and open a any file selected.
I have searched the web and found a link:
http://www.mvps.org/access/api/api0001.htm
I have copied the code into a module and the code behind the cmdbutton but getting an error.
Can anyone help me is finding a solution or an example? Thanks.
Hi every body . I found this code that supposed to allow me browse for .mdb file
and select it. But unfortunely i could not set it up. could an expert help me set this up
in access 2000. Furthermore how i can refrence the loaded .mdb file when ever i want to use
it instead of current db in vba.Thanks
Code:***************** Code Start **************'This code was originally written by Ken Getz.'It is not to be altered or distributed,'except as part of an application.'You are free to use it in any application,'provided the copyright notice is left unchanged.'' Code courtesy of:' Microsoft Access 95 How-To' Ken Getz and Paul Litwin' Waite Group Press, 1996Type 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 StringEnd TypeDeclare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _ Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As BooleanDeclare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _ Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As BooleanDeclare Function CommDlgExtendedError Lib "comdlg32.dll" () As LongGlobal Const ahtOFN_READONLY = &H1Global Const ahtOFN_OVERWRITEPROMPT = &H2Global Const ahtOFN_HIDEREADONLY = &H4Global Const ahtOFN_NOCHANGEDIR = &H8Global Const ahtOFN_SHOWHELP = &H10' You won't use these.'Global Const ahtOFN_ENABLEHOOK = &H20'Global Const ahtOFN_ENABLETEMPLATE = &H40'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80Global Const ahtOFN_NOVALIDATE = &H100Global Const ahtOFN_ALLOWMULTISELECT = &H200Global Const ahtOFN_EXTENSIONDIFFERENT = &H400Global Const ahtOFN_PATHMUSTEXIST = &H800Global Const ahtOFN_FILEMUSTEXIST = &H1000Global Const ahtOFN_CREATEPROMPT = &H2000Global Const ahtOFN_SHAREAWARE = &H4000Global Const ahtOFN_NOREADONLYRETURN = &H8000Global Const ahtOFN_NOTESTFILECREATE = &H10000Global Const ahtOFN_NONETWORKBUTTON = &H20000Global Const ahtOFN_NOLONGNAMES = &H40000' New for Windows 95Global Const ahtOFN_EXPLORER = &H80000Global Const ahtOFN_NODEREFERENCELINKS = &H100000Global Const ahtOFN_LONGNAMES = &H200000Function TestIt() Dim strFilter As String Dim lngFlags As Long strFilter = ahtAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _ "*.MDA;*.MDB") strFilter = ahtAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF") strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT") strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*") MsgBox "You selected: " & ahtCommonFileOpenSave(InitialDir:="C:", _ Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _ DialogTitle:="Hello! Open Me!") ' Since you passed in a variable for lngFlags, ' the function places the output flags value in the variable. Debug.Print Hex(lngFlags)End FunctionFunction GetOpenFile(Optional varDirectory As Variant, _ Optional varTitleForDialog As Variant) As Variant' Here's an example that gets an Access database name.Dim strFilter As StringDim lngFlags As LongDim varFileName As Variant' Specify that the chosen file must already exist,' don't change directories when you're done' Also, don't bother displaying' the read-only box. It'll only confuse people. lngFlags = ahtOFN_FILEMUSTEXIST Or _ ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR If IsMissing(varDirectory) Then varDirectory = "" End If If IsMissing(varTitleForDialog) Then varTitleForDialog = "" End If ' Define the filter string and allocate space in the "c" ' string Duplicate this line with changes as necessary for ' more file templates. strFilter = ahtAddFilterItem(strFilter, _ "Access (*.mdb)", "*.MDB;*.MDA") ' Now actually call to get the file name. varFileName = ahtCommonFileOpenSave( _ OpenFile:=True, _ InitialDir:=varDirectory, _ Filter:=strFilter, _ Flags:=lngFlags, _ DialogTitle:=varTitleForDialog) If Not IsNull(varFileName) Then varFileName = TrimNull(varFileName) End If GetOpenFile = varFileNameEnd FunctionFunction ahtCommonFileOpenSave( _ 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' This is the entry point you'll use to call the common' file open/save dialog. The parameters are listed' below, and all are optional.'' In:' Flags: one or more of the ahtOFN_* constants, OR'd together.' InitialDir: the directory in which to first look' Filter: a set of file filters, set up by calling' AddFilterItem. See examples.' FilterIndex: 1-based integer indicating which filter' set to use, by default (1 if unspecified)' DefaultExt: Extension to use if the user doesn't enter one.' Only useful on file saves.' FileName: Default value for the file name text box.' DialogTitle: Title for the dialog.' hWnd: parent window handle' OpenFile: Boolean(True=Open File/False=Save As)' Out:' Return Value: Either Null or the selected filenameDim OFN As tagOPENFILENAMEDim strFileName As StringDim strFileTitle As StringDim fResult As Boolean ' Give the dialog a caption title. If IsMissing(InitialDir) Then InitialDir = CurDir If IsMissing(Filter) Then Filter = "" 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 = "" 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 ahtCommonFileOpenSave = TrimNull(OFN.strFile) Else ahtCommonFileOpenSave = vbNullString End IfEnd FunctionFunction ahtAddFilterItem(strFilter As String, _ strDescription As String, Optional varItem As Variant) As String' Tack a new chunk onto the file filter.' That is, take the old value, stick onto it the description,' (like "Databases"), a null character, the skeleton' (like "*.mdb;*.mda") and a final null character. If IsMissing(varItem) Then varItem = "*.*" ahtAddFilterItem = strFilter & _ strDescription & vbNullChar & _ varItem & vbNullCharEnd FunctionPrivate Function TrimNull(ByVal strItem As String) As StringDim intPos As Integer intPos = InStr(strItem, vbNullChar) If intPos > 0 Then TrimNull = Left(strItem, intPos - 1) Else TrimNull = strItem End IfEnd Function'************** Code End *****************
When trying to set a password I receive the following message.
You must have the database open for exclusive use to set or remove the database password.
To open the database exclusively, close the database, and then reopen it by clicking the File tab and using the Open command, In the Open dialog box, click the arrow next to the Open button and then select Open Exclusive.
When I follow the instructions, I don't get a dialog box. How do I set a password?
this cod ein access vba for opening a file dialog and selecting a file in a textbox ..
Private Sub Command26_Click()
Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogOpen)
[Code]....
this code,on the form i have a button whose click event is this code and a a text box with it which is text29.
It gives an erro
Run time error '2185':
you cant refernce a propery or method for a control unless the control has the focus .
I want to retrieve some parameters from file, So for that i want to implement File Open Dialog
in One of the form. File Open Dialong is available in Micrososft Common Dialog controls.
When i choose Microsoft Common dialog control from extra toolbars, it gives me error saying that "You don't have licence for using Common Control ActiveX.
Any help appreciated.
I am trying to display an open file dialog window so that the user can pick up a file. I wish the window to show a specific folder. How can I do this? The code I am using is below. The parameter InitialFileName has no effect on the outcome.
Code:
Function GetFileName(strPath As String, imtype As String) As String
On Error GoTo Err_GetFileName
Dim Dlg As FileDialog
Dim sfl As FileDialogSelectedItems
Dim sflitem As Variant
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
[Code] .....
I want to put a print button on my data entry form that prints the current record in a report that is laid out all nice and pretty. I found this code that works, but it goes straight to the printer - it does not bring up the printer dialog so you can select a printer.
Code:
Private Sub RecordPrint_Click()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
[Code] ....
How do I get it to bring up the printer dialog?
I have a database with a mail label printer and another printer attached to the computer. I would like the user to hit the command button for each of the printers and have the selected data printed without having to select which printer to use each time.
Note: Printer "set up" doesn't work, even when form is in design view, because after print is done, back to dialogue box when trying to print the next time.
I'd like to directly print a report from a button on a form.
I want to click the button and the report just prints to my default printer without any other user interaction.
Currently the print dialog appears but I don't want it.
I'm trying to automate a process of selecting a set of file/s and move them in a folder. When I click on a button, it should open a current folder that is setup in the code.
Lets say that I have a folder C:documents est, and very time I click on the button, it should open the dialog box with that path so I can select the files from another folder, drag them there and they will be saved in that folder.
This will form part of wider automation that will send an email stating that those file/s where placed in that folder.
I have in the same form where the button is placed, 3 check boxes that needs to be passed to the email as well that one or all the files where placed in the folder.
I recently upgraded a DB from 2003 to 2013 and ran into the following problem.
I have a button that opens a file dialog box and allows the user to upload a file to a predetermined location (and store the address as a hyperlink). I borrowed this code from someone else on here and modified it slightly.
In any case, the button still works, but now when it opens it doesn't have an option for "All files" under file types. So I can upload MS Office files, text files, etc., but not PDF files which are by far the most common types my users upload.
Here's my code and a screenshot is attached.
Private Sub Command35_Click()
Dim dd As Integer
Dim fileDump As FileDialog
Set fileDump = Application.FileDialog(msoFileDialogOpen)
dd = fileDump.Show
[Code] ....
I have a report based on a query that has data for many dates. At the moment I have put a specific date in the criteria of the query so that I could build the report format. So it now displays all the data for the date i have in the criteria section. I will need to run this report several times per week so the specified date (and corresponding data in the report) will need to be changed to a new date when I open the report i.e. when I open my report I want to show data in the report only for a specified date.
Can I create a date parameter box open up when I open the report? Can I create a form with a button that when I click will open the report displaying data for that date? What would be the best way?I also need to display the specified date on the report.
I have a continuous form that displays a list of invoices (frmInvoiceList) based on an adjustable filter contained within the form. I have the filters working the way that I want them through VBa and I have a button on each record to open a report (rptInvoice). Inside that report, I have some controls to "print", "email", and "export to PDF". Again all that works just the way I want. The Form and the Report are based on a different query and each has an InvoiceID field to link.
What I am trying to do is put the Print, Email, and Export buttons on the main form which would perform the appropriate action for all the records that are displayed on the form. I've been able to get the Print function to work to a degree. It will print all the records, but it changes the background colors based on the alternating records. When I go to Email or Export, it creates a single file with multiple pages and each page is a new record, again with the alternating background colors.
Ideally, I would like to have a separate file exported for each record that can be renamed and archived separately. I'm not so concerned with the email function but if it would be possible to generate a separate report for each invoice, then pull the appropriate email address for the record, that would be really nice.
I've tried some "for" and "do" loops that I found through some Googling but none of the samples ended up working like I wanted.
I need code for save dialog file ,and select the file from textbox and save it to the selected location.i have only this code and i dont know what else i can do with this because it just opens the save file dialog !
View 1 Replies View RelatedI have four forms named [Information Form], [Evidence Form], [Subject Form], and [Vehicle Form]. The Final Form is a Tabbed Form named [New Incident]. The tabbed form has four tabs, with each tab displaying one of the forms above. I also have a Print Command Button named [Print All] underneath the tabbed portion of the [New Incident] form.
Also, All of the four forms are set to data entry - yes, so that on open they will be clean and read for new record entry. I need the [Print All] button to do, just that, print everything that has or has not just been entered into the four forms, even if some are printed blank because they were not necessary for this particular report. One thin of note is that each form on the tab has a field for the "Incident Number" which will be the same for all four forms.
I wanted to print multiple reports using for loop but I am not sure how to start with.
This is basically my idea:
1. Create a hidden indicator, I name it txtHidden.
2. I have one table, there are one column for "report name".
3. I have one query, filtered the "report name" column according to txtHidden, criteria written in the design view.
4. Using For loop, browse through all the existing report.
5. Every loop, txtHidden will equals to every reportItem.Name. Query will run.
6. If Query is no result, dont open report, if query returns some result, open the report.
I wanted to code something as below, but I dont know what to put in the [Dont know what to type] field...
For Each [Dont know what to type] In Access.Reports
[Forms]![frmStartup]![txtHidden] =[ Dont know what to type] .Name
If DCount("*", "ReportQuery", "") > 0 Then
DoCmd.OpenReport [Forms]![frmStartup]![txtHidden], acViewPreview
End If
Next
Can ignore the query part. It is working fine.
I have a simple bit of code below but I need something added. All the code is doing is opening a form and filtering on a combo box value. It opens all cases where the status is open but if there are no open cases it takes me to a new record, how do I get my code to recognise that there are no cases and display a message box for me instead and then close the form?
Private Sub open_Click()
Dim stDocname As String
stDocname = "frmMain"
DoCmd.OpenForm stDocname
DoCmd.ApplyFilter , "Status ='Open"
End Sub
I want to write a code that upon opening the report it looks at the sumofamount field and only shows me the records that the sum is greater than $50. It currently shows me all the records and it they are less than $50, it comes up as a negative. I only want to see the one which have amounts over $50. Would I write this in code and have an even procedure upon opening of the report? I should also add that I have the amounts listed out the bottom of the page, so I can't just go into the query and put >50.
Thanks.
="" & [first name] & ", for the month of " & Format([date],"mmmm") & " your DHL bill came to " & Format([sumofamount],"$#,##0.00") & ", the company allows you $50.00 per month, this leaves you with an overage of " & Format([overage],"$#,##0.00") & ". This amount will be deducted from future commissions. Below is an itemized list of your charges. If you have any questions, please contact me at extension 4491."
I tried Link model "bank_pay" with the main_form << to add details of information >> by "pay_num" in main_form and "num" in "bank_pay" by filter >>
Code:
Private Sub pay_type_AfterUpdate()
Select Case pay_type
Case Is = "1"
DoCmd.OpenForm "bank_pay", acNormal, "", """[forms]![main_many_1]![main_many_sub]![pay_num]="" & [num]", , acNormal
End Select
End Sub
no result :
file in attachments >>
we have gotten in to a routine of copying, pasting and bastardizing old databases instead of creating new ones from scratch - and we've had a problem recently where we've started off with one master database back in 2011, which has then been the base for practically every single major DB we've created ever since.So this month we've copied and pasted (again) and started adding new features to what's already there, and the thing has corrupted. I've copied and pasted the same version three times and added all the new bits, and the same thing has happened every time.
So this time I've copied and pasted and tried my best to clean up, get rid of the dead weight and (where necessary) create completely new objects, split into FE and BE versions etc, and I've reduced the overall weight of the DB by about 50%.Due to time constraints I now need to crack on and get this thing working again and, for the most part, it does - but now I'm having trouble carrying the filter over from the OnClick Event of a form button to the next form it's opening.
We're using the DB to record attendees at an Event we're running later in the year. This is the code that I'm running from the OnClick Event of the button on Form1:
Code:
Private Sub btnBkg_Click()
On Error GoTo Err_btnBkg_Click
Dim stDocName, vFilt As String
Dim vBkgRef As Long
Dim vContactID As Long
[code]....
You can see I've tried to use vFilt instead of the actual code for frmBooking's Where Condition, but for some reason it does not carry over to the form whichever way I try - when I open the immediate window and type ?vFilt it returns a blank entry. Not sure how to show the 'Where Condition' in the Immediate Window?Before Update, Open and Activate of frmBooking, but I'm hoping that the problem lies with what I've posted up here, as I don't want to get into posting the frmBooking code...
It may be worth noting that the button resides in the Header of a continuous form, with conditional formatting that changes the button caption depending on whether there is a value in the BkgRef text box of the record that has the focus.
I have vba code
PHP Code:
DoCmd.OpenReport "MonthlyData2", acPreview, , " YEAR(CourseDate) = '" & Me.txtCourseDateMonth & "' "
this works sucessfuly how can I add the following to the code so it filters month and year?
PHP Code:
'" MONTH(CourseDate) = '" & Me.txtCourseDateYear & "' "