Modules & VBA :: Looping Through Form Recordset And Looking Up Value

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 Replies


ADVERTISEMENT

Modules & VBA :: Way Of Looping Through Continuous Form Recordset And Running Update On Each Line?

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

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 9 Replies View Related

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

[Code] ....

View 2 Replies View Related

Problem In Looping Through A Recordset

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

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

Modules & VBA :: Looping Though Columns?

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

Modules & VBA :: Looping Error On Last Row Of Table

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

Modules & VBA :: Looping Through All Items In List Box?

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

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 :: How To Get Recordset For Empty Rows In Form

Oct 14, 2014

Are new, empty record in form have recordset presentation, how to get recordset in form for empty row?

View 1 Replies View Related

Modules & VBA :: Looping Through Columns To Find A Specific Value

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

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.

View 4 Replies View Related

Modules & VBA :: Looping Through Records In Subform Not Working

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

Modules & VBA :: Looping Through SQL And Skip If No Result Set Found?

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

Modules & VBA :: Looping Through Toggle Buttons To Email Reports

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

Modules & VBA :: Form Bound To ADODB Recordset Is Read Only - Cannot Edit?

Sep 4, 2013

I can't make edits with ADODB recordset bound to my form.

Access 2010 linking to SQL Server 2008.

Simple form bound to a single table.

Connection string works fine.

Code is as below (cursor etc is set using enums btw).

Private Sub Form_Open(Cancel As Integer)
Dim rst As ADODB.Recordset
If g1OpenRecordset(rst, "tblName", rrOpenKeyset, rrLockOptimistic, False) = False Then
Cancel = True
Exit Sub

[Code] ...

View 3 Replies View Related

Modules & VBA :: ListBox Identify Selected Item Without Looping In Whole List

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

Modules & VBA :: Looping Through Files - Import Specified Cells Into A Temp Table

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

Modules & VBA :: Email Detailed Report By Looping Through Related Tables

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

Modules & VBA :: Looping Through Records To Collect Data And Write To New Table

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

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

Modules & VBA :: Split Form - Unbound Textbox To Recordset (Vice Versa)

Jul 8, 2015

How to have the function of a splitform to a "mainform and subform"?

I have a main form "frm_main", and a subform "tbl_main" in continuous form which is linked to the table "tbl_main".

I want to be able to click on the record, then the infor will appear in textbox on the main form. I also want to be able to change the value in the textbox and on after update, it will update that record on the subform as well.

So far, in the frm_main Current, I have added the code:

Private Sub Form_Current()
Form_frm_main.lblClient = Me.client_id
End Sub

So now, what code should I add to the After update of the textbox to find the current selected to update back the value of the textbox to the selected record?

View 1 Replies View Related

Modules & VBA :: Continuous Form With Data From Table - Populating Listbox From Recordset

Nov 28, 2013

I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.

I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.

The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.

One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?

View 1 Replies View Related

Forms :: Looping Through A Form To Clear Each Field For All Rows

Sep 4, 2014

How can I loop loop through a tabular form to clear each value in a field of all rows ? I tried the below code, but it did not work.

Loopcnt = DCount("*", "Budget")
For Loopcnt = 1 To Loopcnt
[Forms]![Budget Form].[Newbudgt] = Null
DoCmd.GoToRecord , , acNext
Next Loopcnt

View 8 Replies View Related

Forms :: Continuous Forms - Looping Through Records / Manipulating Fields On Form

May 29, 2015

I have a bound continuous tabular form,However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary. I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable.

I have the code in the form_load event, however, for testing purposes I have it behind a button.If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records. I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??

Form Detail
-Form does not allow additions or deletions. Edits allowed
-All fields are disabled and locked
-I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then -enable checkbox, which yields the same results

Here is my code

Code:

Dim rstMyForm As DAO.Recordset
Set rstMyForm = Forms!MyForm.Form.Recordset
rstMyForm.MoveFirst
Do While Not rstMyForm.EOF
If Not RequiredField.Value = "" Then

[code]...

View 14 Replies View Related







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