Modules & VBA :: Too Many Forms To Modify One At A Time
Jun 27, 2014
My problem is simple, and i imagine it is easy to fix. I have more forms in my db than I would like to count, and I have a couple lines of code to add to the onload event of each form.
Is there an way, without actually having to edit each form myself, to add the code i need to add to all forms. Is there some code I can write that will let me modify the on load property of a form, and save it?
I know what i need to do, minus the part where i actually change the onload event.
Code: Function FormatTime(Amount As Double, Rate As Double) As String Dim x As String x = CStr(Round(Amount / Rate * 60)) FormatTime = Format(x / 1440, "hh:mm:ss") End Function
How can i modify this code to accommodate time that exceed 1440 minutes?
automatically adding modified date/time to a record as it changes..I have a work log database for a team of people to keep track of workflow (documents in/out, stage in the workflow, owner of the record, etc.). I have a number of records that I want to automatically update a corresponding date/time field when it is changed. For example, one part of the workflow tracks a document through the approval of 5 different people. So I have a five records that are yes/no flags for approval and five records that are date/time for when it was approved. Right now my team is manually entering the five date/time stamps, but I'd like to have it automatically update when the check the "yes" box and update that field in the record.
I want to modify a table called FC_TEMP via adding some columns from another table called AVERAGE_TRP. Do I have to create a new query table and then insert the output into a new table?The following code shows me : Cannot execute a selected query.Is there a way to update this FC_TEMP table without creating another temporary table?
Code:
Public Sub Update() Dim strSQL As String strSQL = "SELECT FC_TEMP.*,AVERAGE_TRP.[Average_new_TRP_EUR],AVERAGE_TRP.[Average_old_TRP_EUR],AVERAGE_TRP.[Average_Margin] " & _ " FROM FC_TEMP " & _ " INNER JOIN AVERAGE_TRP ON FC_TEMP.[PRODUCT_ID] = AVERAGE_TRP.[PRODUCT_ID]"
I've question reqarding modyfying tags (metadata ) in jpg files.
I've found some code which allow to read tags from jpg files through vba but only read - editing is not possible.
So my question is: vba code which allow modify tags in jpg files? {Below code for reading tags, requires to turn on following reference in vba editor: Microsoft Shell controls and Automation )
Public Function getFileMetadata(fileFolder As String, fileNm As String, _ metadataType As String) As String Dim objShell As Shell32.Shell Dim objFolder As Shell32.Folder Dim objFolderItem As Shell32.FolderItem
I am trying to create a customizable report that would allow the users to choose fields. I have a pretty common code that I found online and adapted it, but it fails on the first SetReportControl function.
The error reads: Run-time error 2465: Application-defined or object-defined error
Could it be something as simple as an incorrect reference? I have checked multiple times, but I am stumped.
Code: Option Compare Database Option Explicit Sub MakeReport()
[Code].....
This is a trial run, in the end I need to be able to open a report, then adjust the Report controls within 1 or more subreports inside the main report. That is a battle for another day.
What I want to do instead is open an existing .XLSM wokrbook delete or update the 7 sheets it creates and replace them with the new query results from access.
I love this code below because it works really well but now I have a new requirement. I have a workbook that has a "dashboard" sheet that looks at the sheets from acccess and summerizes the data. So, I'd like Access to open that "template" excel workbook and delete the old sheets and put in the new ones..The required sheets to keep are called "Metrics", "Validation" and "Mara"
What I was trying to do for the past few hours was another work around which was to have Access run this code, then excel run some code to import the "dashboard" formulas but I can't get it to copy to another workbook because it links to the OLD workbook..Here is the working code that needs modding:
Code:
Option Compare Database Public Function ExportAdvanced() Dim strWorksheet As String Dim strWorkSheetPath As String Dim appExcel As Excel.Application Dim sht As Excel.Worksheet Dim wkb As Excel.Workbook Dim Rng As Excel.Range Dim strTable As String Dim strRange As String Dim strSaveName As String Dim strPrompt As String Dim strTitle As String Dim strDefault As String
I'm in the process of creating a database in Access 2010 that has two front ends, basically. What I want to be able to do is lock out one of those front ends for a specific period of time every day.
I have database that i am working on , i want to give a user a time limited accsess ( one week ) to the reports section for example !!
after a certain date he will require a password or the report button will be inactive ...
-at program start up , it will check for today's date if it is greater or equal to (lets say ) 24-9-2013 then it will change a field in an X table from "yes" to "no" ,
-after that the code will check table X for the yes no field , if yes then open Form A if no then open form B / or make report button inactive
how to do that in VB ... and is there is a better way to do it ? can it be done in the macro builder ?
I have written the following code. I want to change the status of my order when reminaing products shipped is equal to zero. When I run this code I get error " Cannot update. Database or object is read only". I think that this error is because that I am using the front end of the database.
Code:
Private Sub Form_AfterUpdate() Dim dbs As DAO.Database Dim rstTest As DAO.Recordse Dim strQuery As Strin strQuery = "SELECT OrderingT.Order_ID, OrderingT.UnitsRequested,OrderingT.OrderStatus, ([UnitsRequested])-Count([Product_ID]) AS [The Remaining
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday EXIT FUNCTION ELSE IF [PunchOut] ISNOT Between Midnight and 0559 hours EXIT FUNCTION ELSE DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
Any sample database where its about Time In and Time Out Attendance. It will just calculate the difference of Time In Time Out with employees Card Number.
The Time In and Time Out attendance is provided (no data entry), it is generated from other tool. I will just load it from an excel sheet to the access database and it will calculate and generate report. Default should be 9 hours per day, if they wont render 9 hours it will show in Difference column. Form will be like this:
ID Num: | Name: | Time In: | Time Out: | Total |Difference (missing hours)
12345 | Al Smith | 8.00am | 5:00pm | 9 | 0 54321 | Joy Pitt | 8.00am | 4.30pm | 8.30 | -0.30
I have a form (linked to a table). I have 'txtBoxA' and 'txtBoxB'. Now, each record will have different values in 'txtBoxA' and based on that value 'txtBoxB' will change for that specific record. In the 'OnLoad' event for the form I type the code, but when I re-open the form it only works for the 1st record, and it doesn't change all the rest (records). Do I need a loop to go through all the records?? I thought that this was done automatically by ACCESS. If I have to use a loop, can anyone please give me the exact syntax?
Please, this is really urgent so I would really appreciate an answer asap Thank you very much.
I am trying to change the value of a field (well a few fields once I get it to work for one!) in a table.
I am trying to update the last record in a table, but cannot get any code to work in VBA for this and cannot make an update query select just the last record in the table to do it that way either!
The VBA I am using is:
Dim db As Database Dim rst As DAO.Recordset Set db = CurrentDb Set rst = db.OpenRecordset("Inventory Transactions", dbOpenDynaset)
With rst .MoveLast .Edit ![UnitsOrdered] = (Me.UnitsOrdered - Me.Qty_Passed_Insp) .Update End With rst.Close db.Close
Units ordered doesn't change though!
For the update query I am trying the following criteria for the transactionID field:
=max( [Inventory Transactions]![TransactionID] )
But this returns the error message:
"Cannot have aggregate finction in WHERE clause ([Inventory Transactions].TransactionID = Max([Inventory Transactions]![TransactionID]))"
I create one database and the query and the forms for my clients in the sharing drive. Every time, the user open the form and select the field names and then execute the query depend on which fields the user select on the form, and then output to the screen. Here is the question, if there are multi user access the same form/query, one user pickup 3 fields to run the query, and another user pickup 5 fields to run the query. Becuase one user select 3 field names on the form, and then the code will modify the query contents, another user select the 6 field names on the form, and then the code will modify the query contents.
strSQL = "SELECT [Company Name],[Vendor Name],[Account],[Statement Number],[Amount] from [Statements] "
Dim cat As New ADOX.Catalog Dim cmd As New ADODB.Command Dim qry As ADOX.view cat.ActiveConnection = CurrentProject.Connection Set cmd = cat.Views("Statement Informations").Command cmd.CommandText = strSQL Set cat.Views("Statement Informations").Command = cmd
Set cat = Nothing DoCmd.OpenQuery "Statement Informations"
I used the above code to modify the existing query, "Statement Informations".
When I run it, it give out the error message:
Run-time error '3218': Could not update; currently locked.
And then, I went to queries section, and open the query "Statement Informations" directly, close the query, and then run the code again. Then, it works.
I don't know why. If I run it on next machine on other day, then it give out same error.
Can someone tell me how I can change the increment in an existing database.
The PK is currently Auto ID with each new record currently 1001, 1002 and so on.
What I am trying to do is say starting monday the next new entry begins at 5003 versus 1003, in essence I am trying to change my first number to a 5 versus a 1.
Say for instance I have a whole load of IP's in a table in the form of A.B.C.*, where and and B are fixed, C varies slightly and * can be anything from 1-255. Can I use a query to look at an IP and the append an IP record to a different table in the form of A.B.C.0. Basically, modifying the data so that whatever the value of *, it is changed to 0.
I have a report rptTeamPickStats which source is a query qryTeamPickStats
The query has columns such as name, id number etc which are GROUP BY and hours, cases as SUM and there is a DATE field where I originally had a WHERE statement specifying the from and to dates.
I want to be able to modify the where using VB so I don't have to have several queries.
The code below doesn't work (probably won't take you long to realise that) but just to show kind of what I'm after.
I want to query all the TLName where the date is between to dates.
It has happen to me many times that when I create specific queries I'm then not able to modify the values of em...
Do you know the possible causes of this?
Is it because I'm filtering? Ordering by? Grouping? Using fields of differents tables related 1-many? Calculated fields? Expressions? Modules?
----------
One specific case that has presented to me is a query that returns info from 1(main)-many(details) relationship. When I use the sorting of a field of the main table it doesn't allow me to edit values, tho when I delete this sorting it works fine.
Nevertheless, I also have another query which just returns values from one table and whether I use sorting or not, I can modify the values.
select IDno, LName + ', ' + FName as Name, course, Status, (select count(io.AC) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno ) as TotalBooks , (select sum(io.Penalty) from in_out_books io where io.IDno = b.IDno and io.DBorrowed >= '2008/03/01' and io.DBorrowed <= '2008/04/30' group by io.IDno) as Penalties from borrowers b
i also want to set the TotalBooks and Penalties to 0 when their values are null thanks
i'm hoping some guru can help me out here, i've got a function that opens up a browse window and lets me choose what sort of file type i want to view in a directory. what i need is to modify it so i can have the option of arranging the files by the date modified, so i can have the last file saved come up as the first file available.
anyone know if this is possible???
Code: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 = 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 Function
I want to be able to make code that will modify a data structure in another file. Can I do it in an access application or will I need to make a VB application? I want to add some more fields to a table. :confused:
