Loop Recordset

Jul 25, 2006

Hi folks,

I have created a recordset and what i want to do is check the field value "SiteRAG" to see if it matches some criteria and if so do some action where the field "SiteID" = the same as a label on a form.

Please see CAPS in middle of code:

Dim db As Object
Dim rs As Object
Dim intCount As Integer, intRecordCount As Integer, intID As Integer
Dim strSQL As String
Dim strRAG As Long
Dim fldItem As Field

strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _
& " FROM tblSite " _
& " WHERE (((tblSite.Active)=Yes));"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


intRecordCount = 0
rs.MoveFirst

CHECK THE FIRST ROW FIELD SiteRAG & IF = TO CRITERIA THEN
DO SOMETHING BASED ON A LABEL MATCHING THE SiteID
ELSE
MOVE TO NEXT ROW

Loop
rs.Close
db.Close


Thanks for any help
Mark

View Replies


ADVERTISEMENT

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

Forms :: Recordset Loop Takes Value Only From First Record

Oct 18, 2013

I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:

Code:
Private Sub btnCalculateWeight_Click()

Dim rst As Recordset
Dim weight As Long
Dim material As Long
Dim subtype As Long
Dim locus As Long

[Code] ....

Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.

View 2 Replies View Related

Modules & VBA :: Loop Through A Recordset While Adding New Records To Another?

Jun 9, 2014

Is there a way of looping through a form record set, while adding new records to a different form record set? using some data from the 1st record set in the new records?

View 8 Replies View Related

Modules & VBA :: Loop Through Recordset Not Recognizing Next Move?

Aug 27, 2014

So I have a table "zztblArticles." Some fields should go to "tblArticles" and values in Tag_ID should go to a lookup table "tblTag" and a junction table "tblArticles_Tags." I'll explain the code I've written below:

Code:
Private Sub cmdSubmit_Click()

Dim db As Database
Dim strINSERT As String
Dim strVALUES As String
Dim rszztblArticles As DAO.Recordset

[Code] ....

The first loop through works fine, I get the records uploaded to all tables. The second loop through fails at

Code:
intArticleID = DLookup("ID", "tblArticles", "Sourcing_Date = " & !Sourcing_Date)

Because it can't decide which ID value to use. This is because the value has been duplicated in tblArticles after the code acts on the same record again. It has completely failed to move to the next record in the recordset, despite the .MoveNext before the Loop!

View 12 Replies View Related

Modules & VBA :: Continuous Form - Loop Through Recordset

Jul 22, 2014

In my database, I have a continuous form with a Name, a Date and a Yes/No field.

When the form opens, I want to look at the date of every record on the form and show a message box if it is before the current day.

The code I have is this;

Private Sub Form_Load()
With Me.RecordsetClone
While Not .EOF
If Me.Date1 < Date Then
MsgBox "" & Me.Person & ""
End If
If Not .EOF Then .MoveNext
Wend
End With
End Sub

However, it loops just the first record the amount of times there is of records (i.e., it will only show the first person's name in the message box, and will show 3 times if there are 3 records).

View 7 Replies View Related

Modules & VBA :: Recordset Loop Skipping Entry?

Jun 26, 2015

I have 2 identical tables of asset information. Table 1 has 251 records while table 2 has 84 records. All 84 records are in table 1 right now. My end goal is to be able to click a button, have vba script run, and table 2 updates table 1 with any changes from different fields. I know there are merging options with query and what not but the exact structure of my tables doesnt play well with it.

Right now, my code is able to loop through the values of each computername and display it. For some reason, when it gets to the 130th record of table one, the loop skips it and returns the 131st record, it stays one ahead for the rest of the loop and then prints the 130th record finally.

Since I am trying to compare field values, this sudden shift throws everything off. why it skips?

Code:

Option Compare Database
Private Sub UpdateAssetsBTN_Click()
On Error GoTo Err_Proc
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

[code]....

View 2 Replies View Related

Forms :: Loop Through Continuous Form Recordset Crash

Jun 27, 2014

My application crashes when trying to change the value of a text box in a continuous form. Here is the code:

Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim rst As Recordset

[Code].....

If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes! (This is a desktop application with tables linked to SP lists - not a web app)

[URL]

View 14 Replies View Related

Modules & VBA :: Loop Function Repeating First Line Of Recordset

Aug 17, 2015

managed to get some code up and running but when trying to enhance it I have hit a rut.

I have a function that is looking up a query called Optimisation - Auto Optimise with operational data in it. I only need 2 key fields; consolcode and volume. There are 106 records with different consolcodes each with different volume. e.g consolcode: Chittagong to Rotterdam201452 (Chittagong to Rotterdam by year "2014" by week "52") and volume 161 (cbm)

I then run a code that allocates the volume into specific sea freight containers and returns the values into a different output table.

The allocation code works fine but when I run the loop function for the recordset rsttradelane it runs for the correct amount of records (106) but always returns the first record of Chittagong to Rotterdam201452 and 161 cbm and not the other 105 consolcodes with the different cbm. Giving me an output table with Chittagong to Rotterdam201452 and 161 cbm repeated 105 times!!

Code:
Set rsttradelane = dbsEPIC.OpenRecordset("Optimisation - Auto Optimise")
consollane = rsttradelane!consolcode
ConsolVol = rsttradelane!Volume
Do Until rsttradelane.EOF
'Optimisation code' then
Code:
rsttradelane.MoveNext
Loop
rsttradelane.Close

How do I ensure that each consolcode and its associated cbm is recognised individually and flushed through the optimisation code?

View 3 Replies View Related

Modules & VBA :: Loop To Update Recordset Only Reaches 1st Record

Mar 13, 2014

I found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.

Also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.

Private Sub Update_Click()
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)

[Code] .....

View 14 Replies View Related

Modules & VBA :: How To Loop Through Recordset And Only Attach Records That Are True

Jul 8, 2015

I have some code that loops the clone recordset of my subform and generates a email with attachments. I have mainform and continuous subform within the subform I have field called address this holds paths to files and another field called send and this is a yes/no field

Now what I'm trying to do is loop through the subform if send field is true then attach file from the address path but if send field is false then do not attach file

Code:
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim olAttach As Outlook.Attachment
Dim rstAttach As DAO.Recordset

Set olApp = New Outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

[Code] ....

View 14 Replies View Related

Modules & VBA :: Loop On Recordset - Sending Email Out With All Needed Info

Dec 9, 2014

I have this code here that sends an email out with all the needed info. The problem i ran into was that for my field "item" it is in a table called "test" and there can be more than one record in there. I just can't get it to loop and show me all of the records. Right now it shows me only the first record. Also it is showing the name of the table before the result so i am not sure what is wrong there...

Code:
Private Sub Command4_Click()
On Error GoTo Err_SendInfo_Click
Dim varTo As Variant
Dim varCC As Variant
Dim stSubject As String

[Code] ....

View 4 Replies View Related

Modules & VBA :: How To Extract Recordset From Subform Into Recordset Object

Aug 14, 2015

Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.

The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.

The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.

The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.

Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?

Note: Immediate Window - One single field can be returned quickly

There are 48 fields that need validation - is there a way to reference the entire recordset?

Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource

Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value

Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229

View 4 Replies View Related

Does A Filtered Mdb Recordset Still Bring Down The Whole Recordset?

Apr 27, 2007

Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?

Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?

For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.

Premy

View 14 Replies View Related

Modules & VBA :: Can Use Result Of One Recordset For Other Recordset

Jul 7, 2013

I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?

View 1 Replies View Related

Create A Recordset From A Recordset

Aug 17, 2007

from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!

Table with records:
userID / Date
u1 / d1
u1 / d2
u2 / d1
u2 / d2
u3 / d2
...
1st Select Query (where Date = d1):
u1 / d1
u2 / d1

Desired 2nd Query based off 1st Query (where ?):
u1 / d1
u1 / d2
u2 / d1
u2 / d2

View 3 Replies View Related

Updating One Recordset Using Another Recordset?

Jan 21, 2015

I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.

sql example below. Which is the Access Sql in the Query

sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC" & _
"SET tbl2.Name = tbl1!Name" & _
"WHERE (((tbl2.Name) Is Null));"

Example.

Table1
Field1.Names = John

Table2
Field1.Names = "Need to UPDATE the name 'John' here"

I tried the Recordset .EDIT but I couldn't get it to work using two tables.

View 4 Replies View Related

Loop

Mar 14, 2007

Hello all, hope that you are having a Great Day!

I hopeing that someone could help me with a Loop problem. I am trying to create a directory on each name change. My code works for the first name, however, when I am trying to Loop through the table, it isn't working.

Here is my code

'MkDir "H:NewPrivateFolder"

On Error GoTo Err_cmbMkDir_Click

Dim DirName As String
Dim response As String
Dim folder As String


folder = "H:Projects"


'Forms("form1").Controls("list1").MultiSelect = 2
'DLookup("Officers_Name", "tblOfficers")
DirName = folder & DLookup("officers_name", "tblofficers")


Do
If Dir(DirName, vbDirectory) = "" Then


If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then

MkDir DirName

Else
MsgBox "Create folder cancelled. Folder not created."
Exit Sub
End If
Else
MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly
Exit Sub
End If
'response = MsgBox(DirName, vbOKOnly)

Loop

Exit_cmbMkDir_Click:
Exit Sub

Err_cmbMkDir_Click:
MsgBox Err.Description
Resume Exit_cmbMkDir_Click


End Sub

View 13 Replies View Related

While Loop Help

Jul 11, 2007

hello,

Let's say I have 10 variables (ID1, ID2, .........,ID10)
In java, I can use a while loop as follows to go through each variable

For example:
i=1
j=1
while (j<=10)
{
ID+i = 0 //this is some type of initialization of each var to 0
i = i + 1
j = J + 1
}
What would be the equivalent code in VBA, especially for "ID+i" ?? I use the '&' symbol but it concatenates everything meaning in the first loop goes to ID1 (which is correct), the second loop goes to ID12 (which is NOT correct), the third loop goes to ID13 (which is NOT correct either), and so on.

Thank you very much

View 2 Replies View Related

Do . . . Loop

Aug 4, 2005

I have a form that has a command button that requeries to the next days events. I want to skip days where there are no events. I created the Do Loop below but need it to stop after 10 days and return a MsgBox that says there are no further events.

I tried Do Until EOF but since the query is empty is fails. I have searched and read some other sources and did not find anything on limiting a loop the way that I need it. Or at least the way I understand it.

Any Ideas?

Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1
If DCount("*", "qryflypages_old") <= 0 Then
Do
Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1
Requery
Repaint
Loop
Else
Requery
Repaint
End If



Thanks RichB

View 1 Replies View Related

For Loop

Nov 15, 2005

Hi,

I have a question

email1 = "......@hotmail.com"
email2 = ".......@yahoo.com"
email3..
...

For i = 1 to num
msgbox email & i
next i

I run like this code, but it only show the numbers, it cannot be shown the email address.

For i = 1 to num
msgbox email & cstr(i)
next i

I tried it, but it's still NOT work

For i = 1 to num
msgbox cstr(email & cstr(i))
next i

Not work, too.

What's wrong with it?

Please let me know, thanks.

View 2 Replies View Related

Need Help With A Do Loop Containing EOF

Jun 19, 2006

I'm having a problem with my form where everytime I try importing a file into my database, I received an "overflow" message. It appears the problem is somewhere in my Do loop. The following shows what my code looks like right now:

Open txtCSVFileToUse For Input As #1
lvRandNum = Int((1000000 * Rnd) + 1)
lvINTERNAL_ID = lvRandNum
lpCntr = 1
Do While Not (EOF(1))

If lpCntr = 1 Then lpCntr = lpCntr + 1


Input #1, csvPro, csvJobName, csvJobNumber, csvShipper, csvConsignee, csvBillTo, csvServiceCode, csvCopies, csvWeight, csvUniqueContainerID, csvManifest, csvDueDate, csvTareWt, csvClass, csvVersion
With objRS
.AddNew
.Fields("K95_ID") = lvINTERNAL_ID
.Fields("K95_INTERNAL_ID") = lvINTERNAL_ID




.Fields("K95_PRONUM") = csvPro
.Fields("K95_JOBNAME_VERSION") = csvJobName
.Fields("K95_JOBID_MOTHER_PALLET") = csvJobNumber
.Fields("K95_SHIPPER") = csvShipper
.Fields("K95_CONSIGNEE") = csvConsignee
.Fields("K95_BILL_TO") = csvBillTo
.Fields("K95_TARIFF") = csvServiceCode
.Fields("K95_NUMBER_OF_COPIES") = csvCopies
.Fields("K95_WEIGHT") = csvWeight
.Fields("K95_UNIQUECONTAINERID_MPAL") = csvUniqueContainerID
.Fields("K95_MANIFESTNUM") = csvManifest
.Fields("K95_DUE_DATE") = csvDueDate
.Fields("K95_TARE_WT") = csvTareWt
.Fields("K95_CLASS") = csvClass
.Fields("K95_VERSION") = csvVersion
If EOF(1) = True Then Exit Do
lpCntr = lpCntr + 1
End With
SkipLine:
lpCntr = lpCntr + 1
Loop

I'm thinking it has something to do with with my counter and/or my EOF stuff. Any help would be much appreciated.

View 3 Replies View Related

Run SQL In VBA With A Loop?

Jun 8, 2006

Hi,

I am runnig a query via VBA where I have a variable defined for the criteria value.
However I do want to run this query several times for several values for the the variable.
These values are stored in a table.

Is it now possible to make sort of loop function in VBA that picks the first value in the table as criteria value, runs the query and then picks the second value in the table as criteria value, runs the query, etc, until all values have been picked?

Thanks,

Gurkentopf

View 6 Replies View Related







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