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