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 Replies


ADVERTISEMENT

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

Date Advance

Aug 4, 2005

I have a very generic form I use to track all my To Do things on a daily basis. The form has the following fields: fldComplete, fldDueDate, and fldSubject.

The fldComplete is a check box and the fldDueDate is of course a date data type.

What I would like to create is a query that once you click the fldComplete signifying that the task is complete:

1. The record is removed from the form.

2. The fldDueDate advances either annually, monthly, quarterly etc. which automatically goes to the end of the form in sequential order.

Any help is much appreciated.

ChiefRetired

View 8 Replies View Related

Advance Query

Apr 12, 2006

I have been struggling with this one a while now and need to throw this open as im Stuck!

i have a database of domain names that I currently own however i need to be able to report on the TLD (eg .co.uk .com. me.uk) part of the domain. in the database the field domain is held in the format "imstuck.co.uk" i need a query that will strip the domain part and just return the TLD in a seprate field.

I have used numvers of functions but cant seem to get the right format for it to work

Help!

Kevin

View 4 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

Booking Too Far In Advance Or Too Close

Mar 24, 2006

Hello everyone, if anybody could help me I'd deeply appreciate it.


Booking ID = the time when it is booked over the phone
Hiring ID = the time when the event is to be held

Basically I have to make some booking dates, and use January and February 2006 as test data. My only dilemma is it says that the booking dates must not be booked more than 8 weeks before the hiring, and at least 1 week before it. So if someone wanted to the hiring for 01/01/06, then they wouldn't be able to book before 01/11/05, but before 25/12/05. Can anybody help me? Thanks. I need it so that whatever the hiring date is, the booking date has to be no more than 8 weeks in advance, but 1 week before the hiring.

View 4 Replies View Related

Setting Up A Paid In Advance

May 5, 2006

I have a problem with figuring out how to implement what I call “Paid In Advance”. If a customer pays 4, 5, 6, or 12 months in advance, I for some reason can’t think of how to set this up with tables, queries, forms, and reports. The example that closely matches my db is “Order Entry” from access sample db’s.

Tables are as follows:
tblCustomerDetail
tblCustomerHistory
tblPayments

For every customer (tblCustomerDetail) there are multiple service records (tblCustomerHistory) and for every service record there are or can be multiple payments (tblPayments) for that one service record.

Just like in “Order Entry” where Customers have multiple Orders and then one or many Payments for that one Order. (Pretend that Order Details is in the Orders table.)

The problem that I’m having is trying to come up with a way to assign the “Paid In Advance” money to the customers account without assigning it to a service record or payment. I can’t assign it to a service record because it hasn’t been serviced yet. And I can’t assign it to a payment because the service record generates a new record in the tblPayments when a new service record is entered, and besides that wouldn’t work because the “Paid In Advance” money would only be applied to that one service record.

I hope everyone is following what I’m saying.
If anyone has any clue as to what I am trying to do, please feel free to help me.
As always any help would be greatly appreciated.

Thanks

P.S. If anyone knows where there is a sample db of what I am trying to do, I would be grateful if you could post the link in your reply.

View 4 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







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