Modules & VBA :: Complex Do Loop / For Each

Mar 11, 2014

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]...

View Replies


ADVERTISEMENT

Modules & VBA :: Import Complex Text File Into Access

Jul 19, 2014

They are receiving e-mail with some data that they want to transfer to access database to track. The one think they can do is copy past, copy paste. That is a lot of data to copy and paste. The date looks like this format

Field1
Text1

Field2
Text2 (text2 can be more than 255 char)

Field3
Text3 (text3 can be more than 255 char)

Field4
Text4

Field1
Text1

Field2
Text2 (text2 can be more than 255 char)

Field3
Text3 (text3 can be more than 255 char)

Field4
Text4

and so on.. It can be 50 records

I am thinking they can copy this to the text file. Then the code form Access grabs the text file and imports to access table with format below.

Field1 Fied2 Field3 Field4
Text1 Text2 Text3 Text4

View 14 Replies View Related

Modules & VBA :: Cannot Get Loop To Advance

May 11, 2014

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.

View 3 Replies View Related

Modules & VBA :: Loop In Text Box

Feb 23, 2014

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 Related

Modules & VBA :: Resume Next And Remain In The Loop?

May 6, 2015

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

View 6 Replies View Related

Modules & VBA :: Loop Through SubForm RecordSet?

Jun 4, 2014

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?

View 3 Replies View Related

Modules & VBA :: Loop Over Than Declaring Constant

Jun 30, 2015

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??

View 1 Replies View Related

Modules & VBA :: Loop Through All Files In Folder?

Aug 26, 2014

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

View 10 Replies View Related

Modules & VBA :: FTP Multiple Files Using Loop

May 15, 2014

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].....

View 3 Replies View Related

Modules & VBA :: Loop But Continue To Next Command

Nov 18, 2013

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]....

View 1 Replies View Related

Modules & VBA :: Incremental Calculation Using Loop

Oct 3, 2014

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.

View 7 Replies View Related

Modules & VBA :: Run A Loop Through Each Record In Query?

Apr 30, 2015

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.

View 3 Replies View Related

Modules & VBA :: Do Loop Stopping Before Completion

Aug 27, 2013

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] ....

View 4 Replies View Related

Modules & VBA :: Loop For Different Form Parameters

Aug 18, 2015

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.

View 8 Replies View Related

Modules & VBA :: Error Handling With Loop

Aug 6, 2015

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

[code]....

View 4 Replies View Related

Modules & VBA :: Loop Recordset With IF Greater Than?

Jun 16, 2014

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

[Code] ....

View 11 Replies View Related

Modules & VBA :: Infinite Loop With First Record

Jan 9, 2014

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

[Code] ....

View 7 Replies View Related

Modules & VBA :: Build Loop Into A Variable

Jul 19, 2014

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

[Code] .....

View 7 Replies View Related

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.

View 5 Replies View Related

Modules & VBA :: Why For Loop Collapse When Cutting A Shape

Feb 3, 2015

Code:

For Each Shape In pptslide.Shapes
If Shape.Top > pptshape.Top Then
'MsgBox Shape.Name
Shape.Cut
newslide.Shapes.Paste
End If
Next Shape

If I keep only the message Box inside the loop, it iterates all the shapes in the slide. But when I cut and paste the shape, it comes out of the loop after cutting and pasting.

View 1 Replies View Related

Modules & VBA :: Loop Through A Recordset And Send Appointments

Aug 2, 2013

I have this code (below) that loops through a recordset and sends appointments. It executes the queries correctly and sends all appointments in the table, but sends them only to the contacts listed in the first record of the query. How do I get it to loop the contact details?

Code:
Private Sub SchedFollowUp()
Dim rsFollow As DAO.Recordset
Set rsFollow = CurrentDb.OpenRecordset("SELECT * FROM Follow_Up WHERE HR_Approved = True AND Added_to_Outlook = False AND Cancelled = False;", dbOpenDynaset)
Dim rsEmployee As DAO.Recordset

[Code] ....

View 10 Replies View Related

Modules & VBA :: Loop Returns Null Error

Jul 18, 2015

VBA creating and have spent about 4 hours trying to figure out the error with my simple loop to update a few fields. As you can see from the attached picture, the "Recalc" sub simply stops updating partway through the loop.

View 4 Replies View Related

Modules & VBA :: Using Access Of Count Worksheets Loop

Mar 10, 2015

I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set".

Here is the code:

Code:
'This block of code will cycle through each tab and name them
Dim WS_Count As Integer
Dim I As Integer

[Code]....

View 4 Replies View Related

Modules & VBA :: Loop Through All Text Boxes On Form?

Aug 23, 2013

I have about 25 text boxes that get populated based on a financial transaction; quite often about half of them are empty. i wanted to gray them out if they are emtpy (.enabled = false). I could put a series of IF statements in the vba for each one, but its bulky and time-consuming. I've done for each record in tables and recordsets. Is there a way to do a for each textbox on a form?

View 3 Replies View Related

Modules & VBA :: Recordset Loop Stuck On First Record

May 8, 2014

I am attempting to use 2 fields from a query to supply the Top and Left Properties of a Collection of Rectangle Controls on my form. The purpose of this is to display the locations on a map of "Spots" in a haunted house. The query that I am using shows the spots that have been pre-tagged with the location of where they belong on the map (currently the query has only 24 tagged spots). On the actual form I have rectangle controls (control type acRectangle) with their visible property set to False by default, named box1 through box25 (there will be more eventually, as I am just working with this test group).

I started with the following code, yet it stops after (correctly) placing the first spot on the map (please see the attached jpg):

Code:
Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset

[Code]....

I'm sure I need to have 'Loop' in there somewhere, but I am not sure exactly where to place it, or if another line is also needed.

View 10 Replies View Related

Modules & VBA :: Constant Loop With Retry Button

May 29, 2014

I'm trying to do the following with the code below:

1. If the file is not in your My Documents folder than goto errhandler and a message box pops up asking you to retry or cancel.
2. When the user clicks retry it goes back and trys again but if the file still isn't there a runtime error 1004 occurs.

I want it to continue to loop when the user presses retry or until cancel is pressed. I've been trying with the code below but no luck.

Code:

Public Function AddITARPicOffloadAnalysis()
Dim xlApp As Object
Dim wb As Object
Dim ws As Object
Dim Lastrow As Long
Dim objFolders As Object
Set xlApp = CreateObject("Excel.Application")

[Code] ....

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved