Modules & VBA :: Follow A Ritual Of Moving Last To First Before Looping Through Recordset
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
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
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 "";"""
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
I am trying to create some code for a button in a report that will follow a hyperlink to a specific file. The problem I'm having is that the files that are at the end of the hyperlink can have various extensions (*.doc, *.docx, *.pdf, etc.) I'd like to be able to put a wildcard in the code to allow the opening of the file regardless of the extension.
Code so far:
Private Sub Command6_Click() Application.FollowHyperlink ("C:UsersjbeggDocumentsAccessTestFolder" & [FileName] & ".*") End Sub
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
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
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
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.
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.
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.
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 .
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)
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.
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.
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
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
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?
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?
Here is the code I have to Update a table tblExternalIncomeSchedule
It works fine and updates the table for the first record.
How do I move to the next record StartYear 6 thru EndYear 10 and write that data to the same table without overwriting the data from the first record?
It should add 5 more records to the table
Yr 6 2000 Yr 7 2000 (since inflation = 0) Yr 8 2000 (since inflation = 0) Yr 9 2000 (since inflation = 0) Yr 10 2000 (since inflation = 0)
Code: Function FillExternalIncomeSchedule(nClientID As Long, nProposalId As Integer, nProposalYears As Integer, nRetireAge As Integer) Dim rst As Recordset Dim qdf As QueryDef Set qdf = CurrentDb.CreateQueryDef("", "DELETE * FROM [tblExternalIncomeSchedule] WHERE [ProposalID] = " & nProposalId) qdf.Execute
I have two listbox (SearchResults5 has two columns) and list_asset_add (one column) both have Extended multi-selection active. I have this code that automatically moves the items between the two listbox (it runs after pushing a button):
Code: Sub CopiTo_Click() Dim Msg As String Dim i As Variant If SearchResults5.ListIndex = -1 Then Msg = "Nothing"
[Code] ....
This works quite well if I manually select the items that I want to move. Most of the times SearchResults5 has lots of elements so I have decided to create a "Select All" button to speed up the process, here the code:
Private Sub Command271_Click() Dim n As Integer With Me.SearchResults5 For n = 0 To .ListCount - 1 .Selected(n) = True Next n End With End Sub
When I use the "Select All" button and I try to move the items between the two listbox, the function does NOT work.
BUT if I manually select one or more items in the first listbox, then I clear the selection and finally I use the subroutine to move the items between the two listbox, then the it works well again. How to make it work properly.
I was wondering if there's a way to set objects so that they don't get pushed down if an object above is set as Yes in the CanGrow property?can a report be split into columns?
I have been asked to replicate a form (previously written up on Word) which has two columns of information. As luck would have it all the fixed fields are on the Left hand side, so that part doesn't need (or want) to grow, However, the Right hand side has several sub-reports which can display 1-5 records each.
So if any of the sub reports show more than 1 record, they push down everything below it, but this includes the Left hand objects, creating gaps and often pushing the report onto 2 pages.
What steps do you(s) follow or recommend to creating tables? Any standard questions? I was reading Erwin, data modeling book and didn't see a stardard check list of things to ask.
I think I need to set the following up in a db, (currently in an excel spreadsheet - has a lot of Redundancy in it) type of inspection checksheet, by machine (20 machines) and shifts (3) There are 20 or so questions that need to be filled in, (yes/no and numbers). any thoughts?
