I want to make delay, for example 2 minutes when the code is within the loop.
Example
Code:
For i = 0 To 100
Debug.Print i
'Before go to the next to make the delay
Next
I 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?
creating a small loop to run code.For each record from AREA with PLANTPROGRAMID = VARIABLE Then...In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.
View 5 Replies View RelatedI am building a faux Electronic Medical Records database for the purpose of training med students. I need a command button to pull up a report , but i want to delay the opening of the report (as if waiting for "tests" to come back or be uploaded) is there a way to do this with VBA? i read about the sleep api but i dont know how to get it to work or where to put the code
so what would i add and where would i add it to delay opening the report that is called EKG W/ Subreport ...
I have a command button (well, I have a few) that I would like to open a report, first opening a form that allows the user to enter a start and end date.
The code so far is:
On Error Resume Next
If Err = 2501 Then Err.Clear
DoCmd.OpenForm "DateSelect", acNormal
DoCmd.OpenReport "All Events Report", acPreview
Now, when run, this code opens the form (DateSelect), but the report starts to run immediately - the On No Data event of the report kicks in (message box along the lines of "No data, closing report", so before the user has the opportunity to enter the dates, the report has decided that there is no data an closes.
Is there a way to pause the opening of the report until after the OK button is clicked on the pop up form?
Just for the sake of clarity, the pop-up form DateSelect is used to open various reports, so I can't add the open report command to the code for the OK button (that I know of?)
I am using Access 2007 & trying to update a database to include a check & balance.
Currently a user enters their name into a form, selects a check box (Pass or Fail) & clicks a save button. Real simple form. The actual testing is performed on a standalone piece of equipment separate or outside of the MS Access database.
Here is the challenge, if someone does select Fail, I want to ensure this person comes back into a blank form (new entry) & confirms they have eventually passed. I want to give a time limit of 10 minutes before an email is sent out to their superiors. (I have the email portion figured out already).
Other entries will take place before the failed user comes back to the entry form so the form needs to be available for other people to enter their results.
I believe this can be done using some VBA and a query but I am not sure where to start especially with the 10 minute allowance to recheck the test status.
"how to COMPACT the DB by introducing delay of 10 seconds and then close the DB".In the Database, I'm able to accomplish the "Compact" the database using the function below.
Function Compact()
SendKeys "%(FMC)", False
End Function
As my DB is quite huge, the Compact action takes around 10 seconds to complete.Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.
Function Compact()
SendKeys "%(FMC)", False
DoCmd.Quit
End Function
how to introduce this delay of 10 seconds and then close the DB.
Basically I have a table setup where Time Fields are stored. What I need to do is in a Query, have the difference of two Time Fields calculated, and then displayed on a Report. Here's what I have so far...
Table
- Start Time
- End Time
Query
Difference Time: DateDiff("n",[Start Time],[End Time])
All that works fine and shows up on the report, except for the fact that the time is showed in straight minutes, so for a difference of 1:30 minutes, it just shows 90 minutes. I need to make it so that on the report in the Difference Time Text Box, it's showed as 1:30 instead of 90.
I tried putting the following in the Control Source for the Difference Time Text Box on the report, but it returns an error.
=[Difference Time] 60 & Format([Difference Time] Mod 60, ":00")
I have a sales report that I generate each week based on a query, which relies on the input from a combo box on a form. This works great - but I have select each salesperson, one at a time and run their reports separately.
I'd like to have some code that will run through each person on the list, generate their report, create the PDF, and email (or save) the file.From my initial research, it appears I will need to utilize a List Box (vs a combo box), so I can select multiple salespersons for the report.
I've found a few examples of code to use...but a little lost on how to implement. I create a new form with a list box that queries the property table for the list. I then added a button to execute the code, and attempted to add this (with my control and reports names substituted). Getting all sorts of errors -- so not sure I'm on the correct path.
'Posted by ADezii 5.23.2009 on bytes.com
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003 with a Combo Box
Dim intCounter As Integer
Dim cboCode As ComboBox
Set cboCode = Me![ YourControl ]
'If Your Data Type is Numeric use this section
[code]...
How can I convert hours and minutes to minutes as an Integer?
For example, convert 2:15 to 135.
Any help is greatly appreciated.
I have the following code for hours worked by staff over a 24hr period. However, I want to calculate staff pay by using their hours worked. The table I am using is populated with the minutes i.e. 270 but I want this field to display 3.5 hrs. I have initially got round this by using a calculated field in the table [Hoursworked]/60*[StaffPay] but this is not an ideal fix. Can I add something to the code below to display the hours to 2 decimal places.
Code
Dim ilHoursWorked
ilHoursWorked = IIf(CDate(Me.txtStartTime) < CDate(Me.txtFinishTime), DateDiff("n", CDate(Me.txtStartTime), CDate(Me.txtFinishTime)), 1440 - DateDiff("n", CDate(Me.txtFinishTime), CDate(Me.txtStartTime)))
If the database sits dormant or unused for 10 minutes the login window needs to reappear for a possible new user.
View 1 Replies View RelatedI have to write piece of code to achieve the following:
1. When the Form gets opened then timer will start.
2. It will fire BtnClose_click event automatically after 10 minutes if there is no activity for 10 minutes.
3. Whenever BtnSave is clicked by the user then reset the timer.
I am writing the following piece of code to be called again and again after 30 min. But I need to do invisible mouse move to stop screen saver occurring so i want to sendkeys after every 5 minutes. The Sub abc is in module and everything works except stopping screensaver from occurring.
Code:
Private Sub UserForm_Initialize()
'Update the Barcodes printed today
Call CommandButton1_Click
'Update batches to be scanned / batches scanned today
Call CommandButton3_Click
[Code] ....
I have to loop thru 2 recordsets.I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.the following is the field names not the field value.
Tier 1 2 3 4 5 6 PrYr
Qtr1 Q1T1, Q1T2, Q1T3,Q1T4, Q1T5, Q1T6 .385
Qtr2 Q2T1, Q2T2, Q2T3.Q2T4, Q2T5, Q2T6 1.25
Qtr3 Q3T1, Q3T2, Q3T3,Q3T4, Q3T5, Q3T6 .774
Qtr4 Q4T1, Q4T2, Q4T3,Q4T4, Q4T5, Q4T6 .333
if Qtr1 = Q1T1 = 0.44 Q1T2 = .50, Q1T3 = 1.45, Q1T4 = 3.00, Q1T5 = .25, Q1T6 = 6.0
So I need to be able to set the value of PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.As so forth for each quarter.then I need to compare the value of the PrYr and if it is the following then
If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = 0
elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then
Sum(TotalNetUSExp * T1E)
ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then
Sum( TotalNetUSExp * T2E)
and repeat for each Tier per Each Qtr.Here is my code so far:
Code:
Public Function BkOvrCalc(ByVal gContractID As String) As Long
Dim curDB As DAO.Database
Dim strSQL As String, strSQL1 As String
[code]...
Here is the code:
Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
' do stuff
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
End Sub
I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.
I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.
View 3 Replies View Relatedi have the following code to get file list from specified directory:
Code:
Dim db As DAO.Database
Dim TB1 As DAO.Recordset
Dim p As String, x As Variant
[Code].....
the field "File_Name " is not set to accept duplicate the above code work fine , but when it find the file name exist on the TB1 it return an error i need the code if find any error continue to retrive other files on the folder not exit loop and also i don't want to make check if file exist it will lower the speed of code
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
why my access always go over the loop that I am declaring..Here is a sample of my code:
Code:
Dim rs As New ADODB.Recordset
rs.ActiveConnection = conn
rs.Open mySQL, conn, adOpenDynamic, adLockOptimistic
For lngProd = 1 To 5
[code]...
I get the error of access cannot find the tbl_data6. Where in the declaration I wrote 6??
I believe this is most recurring scenario for all. Any simple way like:
Code:
For each file in folder.files
Msgbox File.Name
Next File
For your information the above code doesn't work
Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using
Code:
Private Sub Form_Load()
Dim objFTP As FTP
Dim strfile As String
[Code].....
this is a progress bar, what i need is, while execute loop (progress bar) but also execute next command = "LedgerExe:
On Error GoTo Proc_Err
Dim inti As Integer
Dim dblPct As Double
[Code]....
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
Is it possible to run a loop through each record in query, but refresh the query in between loops?
The query I am looping needs to be updated before moving to the next record.
t can't figure out how to get the query to refresh before looping.
I am using to select and append data to an output table. Everything is working fine until the last step. The last step appends all the data to an output table from the initial table. The intermediate steps select the data to output.
"AddExpenseToOutput()" is stopping about halfway through the list of criteria. Initially I thought there might be a null in the list, but it doesn't look like there is after dropping everything into Excel to manipulate.
Code:
Option Explicit
Dim rstAccount As DAO.Recordset
Dim rstAccounts As DAO.Recordset
Dim CAccounts As String
Dim Item As String
Dim AccountKey As String
[Code] ....