Modules & VBA :: Modify Progress Bar So Instead Of Saying Running Query?
Nov 8, 2013Is there any way to modify the progress bar so instead of saying "running query" it states the name of the query?
Like: Running Query [qryFRED] <progress bar>
ADVERTISEMENT
Running A Query To Modify A Field NOT Record
Feb 21, 2006Is it possible to run a query/filter in order to hide/show a field in a record instead of it affecting the entire record.
What I'm trying to do is, in a form the user can enter information about a service call and they can enter a recommendation for the next visit. When they enter a recommendation they are able to select which month the recommendatin should pop up. This Service report get's printed on a monthly basis as a report. Now the recommendation field should only pop up if that particular month which was selected earlier is true. I've tried different methods, but it would affect the entire record as opposed to just one field.
Thanks.
Modules & VBA :: Modify Working Code - Export Query And Update Worksheets In Excel Template
Mar 12, 2014What 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
[code]...
Modules & VBA :: Progress Bar In Do While
Aug 22, 2014How can I do a progress in bar in a do while. I export contract and during the export i want to have a progress bar.
do while
msgbox "the export runs", progress bar
Loop
Modules & VBA :: Progress Bar Not Accurate
Mar 27, 2015I'm making a code to run a Progress Bar from different forms and as per their conditions. Have two forms:
1- Form1="frmProgressMeter"
2- Form2="frmDatabaseStwitcher"
3- Module1=for Functions
On frmDatabaseStwitcher, the code for Progress Bar is:
Option Compare Database
Option Explicit
Private Declare Sub sapiSleep Lib "kernel32" _
Alias "Sleep" _
(ByVal dwMilliseconds As Long)
[code]...
This code is working in two ways:
If I go with "SetProgressBar n / NOS" then progress bar shows 2% and then connecting process time.
Or
If I go with "SetProgressBar n + NOS" then progress works till 100% but then connecting process time.
Also
SetProgressBar (n / NOS) * 100 but then connecting process time.
Modules & VBA :: Splash Screen / Dashboard Progress Bar
Jul 9, 2014I want a progress bar in a form that will increase from bottom to top . The data will from Access table. The bar will start from 0 to Maxvalue. The Maxvalue will be calculated as below:
Total number of batches in the table/ No. of working days between 14072014 and 31112014 / 2
So the above calculation will define the range of a progress indicator.
And now we have to show the % percentage that means how many batches have been scanned for today. For this the following calculations will be used:
Count total batches where scandate=today and scannedby<>null
Now this will show the percentage in the bar that means hopw much work has been completed.
The progress bar should move from bottom to top when % increases.
Modules & VBA :: Get File And Calculate Progress Of Downloading Action
Jun 21, 2015Now I use wininet.dll library to get file and calculate progress of downloading action it is something like this:
Code:
Do
If InternetReadFile(hRequest, VarPtr(Buffer(0)), BUF_SIZE, dwBytesRead) Then
If WriteFile(hFile, VarPtr(Buffer(0)), dwBytesRead, dwBytesWritten, 0) Then
' TODO:// calculate progress
dwStatus = (dwStatus + dwBytesWritten)
dwPercent = (dwStatus / dwFileSize) * 100
Debug.Print dwPercent
'form1.Caption = dwPercent '<-- change this
[code]....
Modules & VBA :: How To Show A Msgbox With Text Update In Progress
Mar 20, 2014How can I show a msgbox with the text "Update in progress! Please wait a while", when I execute the following make table querye on form load?
Code:
Private Sub Form_Load()
'Turns the Access warning messages off
DoCmd.SetWarnings False
DoCmd.OpenQuery "q_tblUdtrk2"
'Turns the Access warning messages back on
DoCmd.SetWarnings True
[code]...
Modules & VBA :: Running Query With No Lock
Apr 1, 2014I am running delete and update queries using docmd.runsql. I am finding that particularly after the update query is ran the record is locked and other field in that record can not be updated via a form without a warning saying another user has made changes to the record. To be more specific -- I have a vendor table which has fields vendorname, vendorstreet, vendorcity, vendoraddress. A form is used to place a street address to the vendorname. onexit of the street textbox an update query is ran using DoCmd.RunSql updateing the vendoraddress field to an 'X'. When I put the city in and then close the form this is when the error occurs stating that another user has made changes to the record. My thought is that the update query has not unlocked the record. The customer wants the X to be placed in the field without his manually placing it in the field.
View 1 Replies View RelatedModules & VBA :: How To Modify A Table
Dec 11, 2013I 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]"
[code]...
Progress Bar Query Yes I Know!
May 11, 2005I have a form with a man that does carwheels across the form with the message please wait, i have been trying to get this to run while my other form loads and cant get it to work :( either the please wait screen freezes and doesnt do anything till the other screen has loaded... or I have to load the please wait screen and then use a button with continue on it for it to then action opening the second form which is the one that takes the time... either way I am stuck with a frozen screen....... where am I going wrong?
View 3 Replies View RelatedModules & VBA :: Too Many Forms To Modify One At A Time
Jun 27, 2014My 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.
Modules & VBA :: Modify Tags (metadata) In JPG Files
Jun 18, 2014I'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
[Code] ....
Modules & VBA :: Data Type Mismatch Error When Running Sql Query
Jan 16, 2014I have vba code that creates the following SQL:
SELECT SubscheduleID, EventID, WeekOrder, DayID, StartTime, EndTime, Priority, CanJoin, PatientTitle, PatientNickname, IncludesPatient, IncludesAftercare, Letter1
FROM [qryScheduleCombinedDetails]
WHERE (SubscheduleID = 1 AND IncludesPatient = -1 AND DuringAftercare <> "AC only" AND (WeekOrder = "All" OR WeekOrder = 3 OR (WeekOrder = 1 AND Letter1 = "XYZ")) AND DayID = 2 AND StartTime <= #8:00:00 AM# AND EndTime >= #8:30:00 AM#);
When I try to run it, I get a "data type mismatch" error. When I put the same code into a query, I get the same error. However, it will run if I delete either condition from within the (WeekOrder = 1 AND Letter1 = "XYZ") pairing. I can't figure why it can run with either of those, but not both together.
WeekOrder is defined as String. Letter1 is calculated as Cstr(Nz(IIf(Letter,"XYZ","ABC"))) within [qryScheduleCombinedDetails], because I wanted to make sure that it would be recognized as a string.
Modules & VBA :: Module To Modify Report Controls Based On Form?
Feb 12, 2015I 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.
Modules & VBA :: How To Modify Code To Accommodate Time That Exceed 1440 Minutes
Oct 8, 2014I have a function that formats minutes to "hh:mm:ss" but its doesnt work if minutes is equal to or greater than 1440 minutes/24 hrs.
Code:
Forms![frmTasksTimer]!estimate_time = FormatTime(Forms![frmTasksTimer]!amount_left, Forms![frmTasksTimer]!Rate)
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?
Modify Query
May 18, 2007Hi,
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.
So, will the query affect each other?
Modify Query
Apr 25, 2006Dim strSQL As String
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.
How can I solve it?
Please let me know, thanks.
Modify Data Using A Query
Jun 1, 2006Hi all,
Is it possible to modify data using a query.
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.
Cheets,
Matt
Modify Query For A Report
Jan 9, 2007Hi all
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.
DoCmd.OpenReport myReport, acViewPreview, "", _
"[TLName]='" & myFilter & "' And qryTeamPickStats!Date >= #" & myFromDate & "# And qryTeamPickStats!Date <# " & myToDate & "# And [OTcode] " & myOp & " 'NA'"
Hope this makes sense
Cheers
Kev.
Can't Modify Values In A Query
Feb 4, 2008Hi!
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.
What's happening??? :( :confused:
Can Anyone Modify This Query Statement
Apr 6, 2008hi im a first time user of access,
can anyone modify this query statement...
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
How To Modify Data Retrived Form A Query !?
Mar 27, 2006i am very rookie with access.. infact i am doing my first project in access for a friend.. here is my doubt...
this frnd has toy making business.. he wants to keep track of this inventory... i have made one table (ItemId(PK), Item descripstion, noOff, parentItem(FK), stock)..
noOff is the no of child items required for a particular parent item..
now i have to run a query to get all the items which have same parentItem.. for this i designed a form... have put 3 text boxes and a subform.. one to accept the parentItem.. 2nd to display its description.. 3rd one to accept no of parent items which i wud be the multiplying factor for the child item noOff.. (default is one..)
http://img.photobucket.com/albums/v355/sledge045/171205/form_prd_data.jpg
now i can retrive the parentItem id.. from the form and use it in the WHERE clause...
SELECT ItemData.ItemID, ItemData.ItemDescription, ItemData.noOff
FROM ItemData
WHERE (((ItemData.ParentItem)=[Forms]![Display Product Data]![txtProdId]));
but i want to retive the value in the No off: box and multiply it to all the child item's no off...
i have tried to multiply ItemData.noOff with [Forms]![Display Product Data]![txtNoOff] but did not got the desired results....
i need help with this... or if u know ne other way to do it... plese post a reply... thanks...
Queries :: Modify Titles Of Query Fields?
Jan 17, 2014I have a simple Query which very satisfactorily exports data to a Tab Delimited Text File to upload to a website.
The field titles are dictated by the Table Field Names but I would like to modify these for the export. Is there a simple criteria code by which this can be achieved.
Can't Modify Fields In Form With Table INNER JOIN Query
Oct 5, 2004Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief:
tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff
tableSignatories has ID for a primary key and has SignatoryTypeID as a field
tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
Any thoughts?
Queries :: Modify Access SQL To Exclude Data From Query
Apr 13, 2013I have been successfully using the following statement in Access 2010 to retrieve data from a large csv relational database:
SELECT [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
FROM 1995_1 INNER JOIN 1995_2 ON [1995_1].RPT_REC_NUM = [1995_2].RPT_REC_NUM
GROUP BY [1995_1].RPT_REC_NUM, [1995_1].PRVDR_NUM, [1995_2].WKSHT_CD, [1995_2].LINE_NUM, [1995_2].CLMN_NUM, [1995_2].ITM_VAL_NUM
HAVING ((([1995_2].WKSHT_CD)="A000000") AND (Not ([1995_2].LINE_NUM)="09500") AND (([1995_2].CLMN_NUM)="0100" Or ([1995_2].CLMN_NUM)="0200"))
ORDER BY [1995_1].RPT_REC_NUM, [1995_2].LINE_NUM;
This query returns one long column of line numbers (LINE_NUM) representing the itemized salary (CLMN 0100) and non-salary expenses (CLMN 0200) and a total for each of the organizations represented in the data base. My question is: is it possible to modify the query so that it returns only organizations whose data passes that following test that checks to see if the itemized line numbers 00100 to 10099 equal the total for each organizations line 10100: SUM(LINE_NUM 00100:10099)=SUM(LINE_NUM 10100:10100).
I have tried several times to upload a sample file but have been prevented from doing so because of a missing security token. I have communicated this to the administrator.