Modules & VBA :: Creating A Small Loop To Run Code
Oct 1, 2013
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.
I have a little problem in creating a msg box that then activate a code. How can I do? I was doing
Private Sub Chiusura_Pratica_Click() MsgBox "Bla Bla Bla " _ VbMsgBoxStyle.vbYesNo If Response = vbYes Then Me.Stato.Value = "A Scadere" Me.Assegnato_a.Value = "" Else MyString = "No" End If End Sub
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant Dim CountImplementedTotal As Variant
CountCost = 0 CountImplementedTotal = 0
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordSet("ECM Details") Do While Not rst.EOF If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then If rst![Has Measure been Selected] = "YES" Then Count = Count + rst![Total Measure Cost] End If End If rst.MoveNext Loop
This is what I have so far Def: IIf([30 day notification]<"1/1/2005","h") What I need to accomplish is, any records less than 1/1/2005 show me "H". It does show up in some but not others. This is a date field. What is it that Iam doing wrong.
Doing my best at learning code. I don't know how you people learn this stuff
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
Small piece of code that can send / recieve small text-strings over a network. I finally got it to work, but forgot to think ahead . Right now it only works in a formular but i really need to be a function with input / output.
My problem is that i am using the "Withevents" to call the Ostrosoft Winsock network module and "Withevents" does not work in functions.
How to build this into a function ?
Code: Option Compare Database Option Explicit Dim sBuffer As String Dim spage As String Dim WithEvents wsTCP As OSWINSCK.Winsock
I am having difficulty creating code that will compare a date in one table (or query) in the [Expire] field and subtracting two months from the date and automatically placing the newly calculated date into the table (or query) in the [Notify Expiration] second field. For example:
Please note that for the month of January and February, the subtraction is minus two for these two months and the subtraction is minus one for the year. All other subsequent months just subtracts two for the months leaving the year intact. I would appreciate any help that will progmatically accomplish these calculations.
I am pullling data from a query using an unbound form and a query that that uses linked tables. I can not edit the index of the tables, so is there a way i can create a new index for sorting data in a form?
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.
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
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.
i 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
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
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.
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
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried
Code: On Error Resume Next
but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.
Code: Dim mydatabase As DAO.Database Dim rst As DAO.Recordset Set mydatabase = CurrentDb Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset) Dim rsBN As String Dim rsDose As Double
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code: Private Sub txtQty_AfterUpdate() Dim rs As DAO.Recordset DoCmd.OpenQuery "qryQuantitySoFar" Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
I have succeeded with retrieving subTable data from a One to many relationship, however, I'm stuck in an infinite loop processing the first record of the subtable and not moving to the next one.
Dim db As DAO.Database Dim rstLandData As DAO.Recordset Set db = CurrentDb Set rstLandData = db.OpenRecordset("qryLandData", dbOpenDynaset) Dim strLandType As String
I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.
Set ctl = Me.listSeeAllAssets For Each varItem In ctl.ItemsSelected rs1.AddNew rs1!AssetNo = ctl.ItemData(varItem) rs1!DateOfService = Me.DateOfService