Modules & VBA :: Looping Through Recordsets With Multiple Outcomes
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.
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY") Dim strSQL As String Dim db As Database Dim rs As Recordset Dim strRptName As String
I'm changing a record source on a form when the user clicks a "save" button.
I'm doing this to store a value from the current form in a table that is not part of the default form query.
This works fine.
I have a separate button on my form called "Home", when the user clicks this button it requerys the current form which triggers the before update event to run, this in turn brings up a message box which asks the user if they want to save or not. If they click yes then it runs the SaveButton click() code.
For some reason when the user presses the save button and then exits, everything works but if a user presses the “home” button which triggers the save button then it brings up the “2107 The Value you entered doesn’t meet the validation rule defined for the field or control.” Runtime error and stops on the change recordsource command.
Why triggering the same code directly from a button or indirectly from a before update event has two different outcomes.
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code: For x = 0 To 20 If Nz(rec1(x), "") <> Nz(rec2(x), "") Then DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
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
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
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 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.
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 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 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?
:eek: Trying to access a field in a table using a select case, can anyone tell me what the problem is. I get a "No Current Record Error" when I run the code
Select Case intTMO_RATE Case 5 strNew_PromoID = rst.Fields("5YR").Value Case 7 strNew_PromoID = rst.Fields("7YR").Value Case 10 strNew_PromoID = rst.Fields("10YR").Value Case 15 strNew_PromoID = rst.Fields("15YR").Value Case 20 strNew_PromoID = rst.Fields("20YR").Value End Select
Hello, I'm going to try to explain this as well as I can. I am also attaching a DB for reference. I am using a version of the Code:Private Sub lstAllCenterNames_AfterUpdate() ' Find the record that matches the control.Dim rs As ObjectSet rs = Me.Recordset.Cloners.FindFirst "[Center] = '" & Me![lstAllCenterNames] & "'"If Not rs.EOF Then Me.Bookmark = rs.BookmarkEnd Sub When you look at the DB, the form1 works fine, but is not pulling all of the data that i want. The frmMain is pulling from a query which i have setup to combine 2 columns from 2 different tables and list them into 1 single column and lists in the form all the data that i want to show. When using the query2 as the record source for the form, I can't get the record selector to update when i select new items. Just a note, the tables in the real DB are actually linked to an oracle DB so I am not able to make any changes to table structure. I have just copied only the information needed for this sample DB. Let me know if more explanation is needed.
Can someone point me in a direction to get some good information on how and when to use recordsets? Ive gotten by without having to use them for the most part up until now.. but the inevitable has happened and i figure i might as well learn to use them
for the most part i know When to use them, but its getting into the how and where to use it for the most part.
simple examples or even perhaps a tutorial would be great! thanks guys
Hi all, Anyone know how to open 2 recordsets eg recordsets of 2 tables at same time, and also how to update 2 different fields at once,eg in one line, at the moment I can only open a single recordset and I have 2 docmd.runsql "update etc" commands
Dim TableName As String Dim dbname As String dbname = "c:studentswithdisabilities" TableName = "InputStudent"
Dim CustDB As Database, CustTable As Recordset Set CustDB = DBEngine.Workspaces(0).OpenDatabase(dbname) Set CustTable = CustDB.OpenRecordset(TableName, dbOpenTable)
DoCmd.RunSQL "UPDATE InputStudent SET InputStudent.photoBalance = InputStudent!photoBalance-[Forms]![photocopy]![txttop] WHERE [InputStudent]![TNumber]=[Forms]![photocopy]![ComboTnum];"
DoCmd.RunSQL "UPDATE PhotocopyTable SET PhotocopyTable.RemainingBalance = [PhotocopyTable]![StartingBalance]-[PhotocopyTable]![TopupAmount];"
When I run this code using the (Add Record) click event on a button, it performs the first update but it doesnt perform the second until I add the next data record, Any help much appreciated
Here is the code adding a new recordset to the table from a form : - ----------------------------------------------------------------------------------- Private Sub Command21_Click()
Dim Db As DAO.Database Dim Rs As DAO.Recordset
On Error GoTo Err_Command21_Click
Set Db = CurrentDb Set Rs = Db.OpenRecordset("tbl_ProjResou", dbOpenDynaset) ' Create a new record. Rs.AddNew Rs![ProjectNr] = Forms!frm_Projects![ProjectNr] Rs![ResourceNr] = Me.ResourceNr Rs![Hours] = Me.Hours Rs![reserve] = Me.reserve Rs![assign] = Me.assign ' Save the record. Rs.Update Rs.Close MsgBox Err.Description DoCmd.Close
Exit_Command21_Click: Set Rs = Nothing Set Db = Nothing Exit Sub Err_Command21_Click: ' An unexpected error occurred, display the normal error message. MsgBox "Die Ausgewhlte Ressource existiert Schon, bitte neue Ressource auswhlen" ' Set the Response argument to suppress an error message and undo ' changes. End Sub ---------------------------------------------------------------------------------------- With the code above, I can add only one new record at a time.
In the form I have two unbound fields which are date fields and are entered by the user. I want the date difference between these dates in number of Months (obviously rounded up) and use the above code to add so many number of new recordsets to the table. For example : if date difference is 12 months then the code should add 12 new records to the table.
As I am new to VBA, I dont know how to customize the above code for the above condition. Could anyone pls help me...Please
