Modules & VBA :: Looping Through A Recordset
Jul 8, 2014
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)
Code:
sub loopandfix()
on error goto errorhandler:
strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
[code]....
View Replies
ADVERTISEMENT
Jun 18, 2014
Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table
Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?
Code:
mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""
View 4 Replies
View Related
Aug 6, 2015
I follow a ritual of moving last to first before looping through a recordset. I started following this because in the way back (I think on office 2003). I used to get an error if don't do it. Is the problem still exists? Is this ritual scientific still?
Code:
Sub mac()
Dim myRset As Recordset2
Dim AttachmentField As Field2
Set myRset = CurrentDb.OpenRecordset("Table1")
With myRset
.MoveLast
[Code] ....
View 2 Replies
View Related
Jun 11, 2014
i have set up an update query. is there a way of looping through a continuous form record set and running the update on each line?
View 2 Replies
View Related
Apr 27, 2006
Greetings
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================
'Public Procedures
Public Sub UpdateQuestion()
Dim UpdateAnswer As Byte
Dim db1 As DAO.Database
Dim rst, R As DAO.Recordset
Dim volval As String
Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _
"work note ready to update and complete Press" & vbCrLf & _
"YES to update and complete OR " & vbCrLf & "NO just " & _
"to update and close", vbQuestion + vbYesNo + _
vbDefaultButton1, "Update and Complete")
If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else
Set db1 = CurrentDb
Set R = Me.Form.RecordsetClone 'create a recordset of the data
R.MoveFirst 'got to start somewhere
Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels")
rst.Index = "PrimaryKey"
rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID]
rst.Edit
volval = rst![Volume]
rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume]
If rst![Volume] < 0 Then
MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle
rst![Volume] = volval
rst.Update
rst.Close
Exit Sub
End If
rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status]
rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate]
rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber]
rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID]
rst.Update
rst.Close
R.MoveNext
Loop
Set db1 = Nothing
Set rst = Nothing
End Sub
Thanks
rbinder
View 1 Replies
View Related
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
Sep 15, 2013
I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc.This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?
Code:
Dim rst As DAO.Recordset ' recordset for category data
' open the recordset for categories
Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset
' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
[code]...
View 5 Replies
View Related
Aug 1, 2014
I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.
If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.
Code:
Function RunLWSPA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
[Code] ....
View 6 Replies
View Related
Apr 5, 2015
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
View 3 Replies
View Related
Nov 20, 2013
This query returns a running asset balance for 60 months. Each month is 1 column.
1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.
2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.
View 9 Replies
View Related
Apr 24, 2014
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
View 4 Replies
View Related
May 23, 2014
Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:
Private Sub cmdComplete_Call_Click()
Dim rs As dao.Recordset
Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone
rs.MoveFirst
[code]....
It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first .
View 6 Replies
View Related
Jan 15, 2015
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results.
after item 2, do an export.
etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
[Code] .....
View 1 Replies
View Related
Apr 4, 2014
I have VBA to send multiple reports to a group of people depending on a toggle button selection. what I'm wanting to do is have it loop through the e mail process until all 5 toggle buttons have been selected and the reports have been E Mailed. Is this possible. Toggle buttons are named toggle1, toggle2 and so on.
View 4 Replies
View Related
May 9, 2014
Show me the selected item in a Listbox without looping in the whole list, because my Listbox is multiselect and I want only the item that selected recently.
View 8 Replies
View Related
Jul 22, 2015
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim nIndex As Integer
Dim strMvPath As String
Dim mvPath As String
Dim strFile As String
[Code] ....
View 6 Replies
View Related
Dec 9, 2013
I want to automate the distribution of individuals Vacation Balance and Usage via email. The data is being pulled from 2 tables related by an Employee ID#. tbl-Employees provides the Header Info for each employee and tbl-VacLog provides the detailed Usage data.
I want the output to appear as follows in the body of the email:
Name Start Date Vac. Bal TotVacToEOY Personal Bal.
John Doe 1/1/99 120 160 8
Usage Date Hours Reason Code
1/1/13 8 V
2/15/13 12 V
3/6/13 8 V
I've got a handle on creating the email and sending but where I'm having trouble is in making the link between the two tables with the Employee ID# and printing the corresponding detail data with Parent Record.
Code below...
Option Compare Database
Sub SendMails()
Dim DB As Database
Dim RecordSetA As DAO.Recordset
Dim RecordSetB As DAO.Recordset
Dim TotalRecordsA As Integer
[Code] .....
View 1 Replies
View Related
Jun 27, 2013
I am the first to admit I struggle with looping through records.
I am trying to loop through a table and collect external income data and write it to a new table.
My issue may be the way the table is laid out. ???
The attached pictures show the tables. The tblExtInc is where the data are. I am trying to create a table that looks like tblExtIncYr that shows each year and separate entries by item.
How would I loop through tblExtInc and grab ex:$2000 every year for upto 100 years but only get the $1000 for year 6,7,8 only?
View 2 Replies
View Related
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
Mar 18, 2014
I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.
I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.
Code:
Private Sub SelectTblMyMedButton_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)
[Code] .....
View 3 Replies
View Related
Jun 17, 2013
I am trying to open a child recordset but keep getting Error 424 "Object required".
Dim rstEmails As DAO.Recordset2, DBS As DAO.Database, rstFile As DAO.Recordset2
Set DBS = CurrentDb
Set rstEmails = DBS.OpenRecordset("Beldenemails")
Set rstFile = rstEmails.Fields("email").Value 'Error 424 here
End Sub
I've tried everything I can think of.
View 4 Replies
View Related
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
Nov 11, 2014
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = Nothing
View 4 Replies
View Related
Aug 6, 2013
How does one go about removing a recordset from a ListBox?
I have a list box that I want to toggle between using a query and an ADO RecordSet to populate the values.
Once I set the listbox .RecordSet property to the ADO.Recordset, I can't remove the values displayed in the listbox when I assign a query to the .RowSource property.
I suppose I can turn the .RowSource query to an ADO Query but I am being lazy and don't want to rewrite the query as a T-SQL query.
I previously thought the list box was pulling data from the .RowSource query but I realize I was wrong.
View 1 Replies
View Related
Sep 3, 2014
Is it possible to create a record set from a list box?
I have two list boxes list1 (customers) the can transfer records to list2. I want to take all records from list2 and use it to open a report, using customer id as where clause in my docmd.openreport statement.
View 4 Replies
View Related
Aug 21, 2013
As I am moving through my code, I'd like there to be a pop-up box which asks the user to choose from a list of dates. This list of dates only resides in a filtered recordset in the background.
Once the date is chosen, then my code continues onward..I know I could make some sort of pop-up form, but I'd rather not have to go in a design all that just for a list of dates...
1) Is there such a thing as a dropdown list on a Input box?
and even if there isn't...
2) Is there a way I can bind the column in a recordset to a dropdown list?
View 2 Replies
View Related
