I have a client I'm currently working for that has dumped Access on me. I'm not originally here for Access and am sort of learning as I go along (I'm here to setup, run, and maintain servers, etc.)
The problem is they have a large contact database in Access, and want the forms to pop up a dialog asking if data changes are okay before actually updating. I know that Access normally just does the change, but there is an issue with someone that doesn't understand computers at all making a mess of it (adding in one client 15 times, each with one different piece of information, erasing things in an attempt to search, etc, etc.)
I've been going all over the place trying to research the issue for hours and have had no luck finding a solution. Any help would be beyond appreciated. Thanks in advance for any help!
So I have this relatively simple problem: I need to create a button that once clicked will open the Save As dialog box and allow the user to save a copy of the current database where he wishes. The filename should contain todays date in DDMM format along with some pre-set text e.g. DDMM PresetText.
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 !
I have a problem. This problem is that when I generate the report, I want to show a save file dialog box. How can I do this and and retrict the files show to only "*.pdf" files. I tried it as follow:
Private Sub SaveFile_Click()
Set FDialog = Application.FileDialog(msoFileDialogSaveAs)
With FDialog .Filters.Add "Acrobat Files", "*.pdf" .Show End With
End Sub
error is araised when executing .Filters.Add "Acrobat Files", "*.pdf": Object doesnt support property or method.
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 *****************
The code below sets my printer to "Cute PDF writer" and then opens a report in print view, which creates a PDF version of my report. So far so good. But just before the last step, it brings up the Save As dialog box, asking for a filename and location.
Three points are important:I always want to call the PDF document "Specialty Report".I always want to save it to C:Documents and Settings80435 Desktop. I always want to overwrite the previous version.
Is there some code I can insert that would achieve this automatically with no further user interaction i.e. user clicks a button and the PDF file is created/overwritten?
Code:
'Produce the report Dim defPrinter As String, NewPrinter As Printer defPrinter = Application.Printer.DeviceName 'Get the default printer name Set NewPrinter = Application.Printers("CutePDF Writer") 'Create a new printer object Set Application.Printer = NewPrinter 'Set the default printer to the new printer DoCmd.OpenReport "Specialty Report", acViewPrint 'Open the report in print view (which will PDF it) Set NewPrinter = Application.Printers(defPrinter) 'Reset the printer back to the default printer
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"?
I'm just starting out with VBA. I want to create a form that has a button on it which when clicked, opens the "Save As" dialog box. Whatever filepath the user selects from this box, I want to store in a variable.
Is there a way to get information in the print dialog box (File -> Print)? Like when i click ok or cancel? Or any other way that i will be able to know that the report is sent to printer or being printed.
I am asking my users to interact with the Get External Data dialog, and I'm trying to restrict what they can do.
Code: DoCmd.RunCommand acCmdImportAttachText
I really like the mapping feature Access uses so I want to use that part of the Wizard, but I don't want my users to import data to the wrong table.Can I skip the first page of the 'Get External Data' Dialog series? Invoke the 'Import Text Wizard' without the previous dialog?see: AWF2. I can get the file path from a File Picker dialog.
I have a form that no longer saves data in some fields, but does save data in others. Nothing has changed, except that it no longer saves the new data which it did save at one time. I looked at the code behind the form, and the requery statement is still there.
Hi Guys, What i am trying to do is, i have two tables called Table1 and Table2.
I have created a form called Form1. This Form1 has all the fields from Table1.
What i want to do is, as soon as a user fills in the details in Form1, obviuosly it saves those details in Table1, BUT i want it to save a couple of field values into Table2 as well.
How do i go about doing this??
In Table1 i can access the fields by "Me.[Fieldname]" (from the VB script), but how do i access Table2 OR how do i save data to Table2 from Form1.
Hi.. I cannot save the data in the forms I have made. once I close the form, all the saved infomation is lost when I reopen the form. But somehow they reman stored in the table. Any suggesstion on how to retain the records in the form too. Regards Rahul
On my form I have calculated fields based on information from my sub forms. I have the calculations all working fine however I can seam to figure out how to save them in the table that my form is linked to as my control source is the calculation. Should I be using my calculations in an area other than the control source?
Hi everybody, i am an U Student from Singapore.and i am a new MS ACCESS learner. So please kindly advice me a question about MS ACCESS. I created one form called MonthlyTransaction. Inside this Form, i have another sub-form called Forecast. So my question is how do i save the MonthlyTransaction data into monthlyTransaction table and Forecast data into Forecast table? But the field name in Forecast table called MonthlyTransID is the value of MonthlyTransID in the MonthlyTransaction table.
I'm wondering if its possible to have a "would you like to save changes, yes/no" thing pop up after data has been added or changed to a form, or have the option to save when closing a form and if no is chosen then any data changed is not saved.
My NewOccupancy for will not save data. I enter in the data in the form and click it away. When I go back to the form it is not there and I have to enter data in all over again. This cycle repeats many time.
I need to make a calculated field show up on my table. My database is for technicians at work to log their hourly health checks. I have a field on form "Health Check" called "HC_ID" that I automatically bring over to the next form called "Channel_Scan_Form" with the control source property. I would like the data from this field to copy to the accompanying table called "ChannelScan_Table" from this "Channel_Scan_Form". I have tried to place the code in several different events on the property sheet but haven't had any luck bringing the data over to the table. I'm just starting to play with databases, plan on being here for a while and hopefully contributing in the future.
Table 1: Vender Name, Number, contract, amount, quantity,and order number. Table 2: Doc #, Date. Multiple Doc #'s and dates will be saved under one vendor name (hence the two tables).
What I need is a MACRO where once I save the Doc #and Date to a record, I need to be able to go back to that record and enter a new Doc # without saving over the one I originally did.
I have created several "pass through" queries in the last several years. However, Is it possible to run an SQL Procedure as a Pass Through Query. The procedure they want to run is set up to run a query, save the results to a server, and then run another query based on the saved data. I can get both queries to run as pass through queries, but I can't figure out how to save the data on the server. Is there a way to accomplish this? Can SQL procedures be run via Access?
I have a form where the user can add as many rows as he wants - it's usually only 1 or 2 but that's not the point.When he has finished editing he clicks a Save and Exit button.In here I want to save data from the last row he has entered. I have this working but only if the cursor remains on the last row or on the 'new' record row. If he manages to, say, put his cursor on the penultimate row then I save the data for that.
eg.
Col1.........Col2..........Col3..............Col4 AA...........AAA...........AAAA.............AAAAA BB...........BBB...........BBBB.............BBBBB CC...........CCC...........CCCC.............CCCCC DD...........DDD.........DDDD.............DDDDD EE...........EEE............EEEE.............EEEEE NEW LINE
So I for instance want to save Col3 on the last line filled in. Lets say the control is called Col3 then
If the cursor is on NEW LINE I have got the code so that Me.Col3 "EEEE" If the cursor is on the last line (the 5th) I have got the code so that Me.Col3 "EEEE"
If the Cursor is on the first line i get "AAAA", "BBBB" for the 2nd etc. How do I always pick up the last line?
When i preview, i see all data just fine but when i save a PDF of my report, i am missing some data in the report. That data is rather important because its the quantities and size of my items in my Purchase Order Report.
There is a public master database with a bunch of tables and data in it being maintained by another group. My boss wants to skim some information from this, add some of his own information to it, and save it in a completely separate .mdb file on our server.
I've used Access to link to the public database, built a custom table just for us, and built a form. The form uses bound controls on the left side to pull in data from the public database, and unbound controls on the right side for user entry of data. I coded a VBA save button that should save all controls (bound/imported as well as unbound/data entry) to our local table.
The unbound controls save just fine, but the bound controls are missing from the table. A new row is created with no problems, I get no error messages, but half the fields in the table are just blank.
Code: DoCmd.GoToRecord , , acNewRec Dim Rs As Recordset 'Dim SDB As Recordset 'Dim strSQL As String Set Rs = CurrentDb.OpenRecordset("Supervisor Table", dbOpenDynaset)
I am about to start working on a new project where I have a front-end in Access2000 that is linked to a ODBC Data Source (ORACLE).
There could be some performance issues in the future, when the users have to retrieve (query) the data from the ODBC over the network and it slows down. Nothing is sure yet, but when this is the problem, I will need to look at an alternative plan. I had the following in mind:
I would like to create a scheduler program, that will retrieve all the relevant data from the ODBC and update the tables that are local to each user’s Access .mdb file. Can I do something like this in VBA, or do I need to do something with the Windows Scheduler?
I have a form where user can chose his own criteria for the report, like time period, ppl involved and other parameters. i open report filtered with these conditions. I also create a nice text (as public variable) bout what where the search criteria and put it in the header/footer. Then I export the report as xls file. it's nicely saved as column names and data, just what i need.how to add a field that would store the criteria, ie that string public variable that has it all neatly explained?
Text0 = Textfield for ID Text2 = Textfield for LName Text3 = Textfield for FName
btnRet = Button for retrieve when ID is entered in Text0 btnUpdate = Button for saving the changes made in the textfields.
How can I search the ID from the Table I made? And when it was matched, get the details of that ID unto the designated text fields. It was like retrieving the data from the table with the ID typed in the text field from the form I made.
Then when it was retrieved, I can edit the fields and when I hit the Update button, the edited fields will replaced the original data.
Also after it was updated, a new record will be added in the history table that the ID was edited. Is is possible?
For now, i want to know how to retrieve the data with the ID and edit and save it after and update the table. Does it applies with the codes with the retrieve button and update button?