loop through a table vba Results


Page 1 of 14.
Results 1...20 of 264

Sponsored Links:



how do i loop through a table and get the data from each row so that i can use it for something else?

there's 2 columns from each row that i need and it's less than 100 rows

thank you very much!!




Hi all,

Can anyone please tell me how to loop through a table.
I have a table with an undetermined number of records.
I want my vba to msgbox a field of the first row, then the second row etc etc.
Im just stuck on looping through the table.

Thanks for any help.




I have a table (ExportTables) with the following data

ExportTables
--------------------
MPI_CODE
MPI_IDS
MPI_REFF
REFFERALS
NOK_AD
--------------------

I want some VBA code that can loop through the table above one by one returning the value. The table data will change so i would like the code to handle change as well.

I want the returning value to be returned has a string i.e. TblValue =

Because then i want to use the value to be put in this sql query- DoCmd.RunSQL "INSERT INTO TEST_DOC SELECT * FROM " & TblValue

then loop onto the next value.

Hope you understand what im after.




My database creates work estimates

On my estimate form I have a subform where the user can enter notes. Each note is a string and each note is stored as new recorded in a related table.

There are several notes that are almost always used so I have an unrelated table with a series of default notes in it. The table contains and ID column (AutoNumber) and the DefaultNote (String) column.

What I'd like to do is program a button to loop through all the default notes and add them to the related notes table but I just don't know the syntax.

If I could read the records into an array in vba I can do the rest.

Thanks

PS the tables are all on an SQL server backend




I need to rename a table so that I can archive it and create a new copy.

The table is currently called UPDATED_ROUTES and I want to rename it to "UPDATED_ROUTES" & Now (i.e., append the current day and time).

I just need to do it for this specific table, but the examples I've found are for looping through multiple tables, and so they are more complicated than I need.

Can anyone suggest some code for a complete beginner at VBA?

Thanks in anticipation




Hi, I am fairly new to access VBA although been doing Excel for a long time so understand the coding structure etc.

I have a table of 130 invoicees and a list of activity which includes the invoicee in a field.

I would like access to loop through my activity database and generate a csv file for each batch of invoicee data where there is activity for them.

Does anyone know how to do this?

thanks for your help




Good Day,

I've got to create an access vba script/query to search through a table and loop through up to five of the alpha-numeric charcters of each record to match the first characters with an exact match from another table.

I've been searching for a way to do this, but I can't seem to wrap my head around how to make this work.

Any suggestions you could throw my way would help a lot. I'm just stumped here.

-Thanks




Hi,

This is what I am trying to do:
1. I have a form with a sub-form which is a query output in datasheet view.
2. Adjacent to this sub-form is another sub-form which is another table with information about the corresponding line items in the previous sub-form.
3. I need to use the information in both these sub-forms to do some calculations and update another table.

Since the first sub-form is a filtered list, I cannot use a counter directly to loop through the records.

Any suggestions/instructions would be very helpful as I have just started out using Access/VBA. I have attached a screenshot of the sub-forms to make the situation clear. Thank you in advance




Hi all, VBA newbie here

i want to write a vba code to loop through a column in my table and check for a condition, and where it does not equal that condition i want it to display the whole row in a message box and ask to amend the field to meet that condition. If i say Yes i want the amendment to proceed.

For example, the fields would be mostly say 31/08/2012 but one entry may be 30/07/2012. I want the code to pick this out and suggest whether i want to change this to 31/08/2012.

Cheers
A




Hi All,

Im sort of new to VBA but need some help.

What I would like to do is loop through a table and grab the results and populate them into my text string like

Available in the Following Colours:

White
Mist Grey
Ivory
Peach

In my code, I link to a colour table and grab the colour and link this with my product description table and then wrap it in html tags. Then I save this using the open file.
Then I can view this html file in a broswer window on my form the check the html code.

**** but when I do the loop it goes straight to the last one!, and misses out the first 3 colurs.

Can anyone point me in the right direct PLEASE!!!!!

-----------------------------------
Dim strColour As string
Dim mydb2 As ADODB.Recordset
Dim intFile As Integer

Set mydb2 = New ADODB.Recordset
intFile = FreeFile

mydb2.ActiveConnection = CurrentProject.Connection
mydb2.Open "select Colour_Text from Colours where proddesc_id =" & Me.PRODDESCID

While Not mydb2.EOF
strColour = mydb2.Fields("colour_text")

Me.html_text = "" & Me.SHORTTEXT & "" _
& "" & "" _
& "Features" _
& ":: " & strFeatures & "" _
& "" & Me.PRODDESCR _
& "Available in the Following Colours:" _
& ":: " & strColour _ '*** See here I need this to show all colours!!!
& ""

Open "E:SBTSupplier_InformationWebsite Connectionhtmltest.html" For Output As intFile

Print #intFile, _
Me.html_text
Close intFile

mydb2.MoveNext
Wend
mydb2.Close

Set mydb2 = Nothing

Me.WebBrowser1.Navigate "E:SBTSupplier_InformationWebsite Connectionhtmltest.html"
-------

thanks
brendan




Hi,

I'm having difficulties deciding the best way of using VBA for the following problem;

I'm looking to use VBA to loop through a table to select any fields that contain the value -1 (which stands for a missing value in this table). I need to be able to run the code so that it runs through about 20 fields that could possibly contain this value and then report back on which records have -1 and in which particular fields!

I've been trying little snippets of code but I can only seem to get it working one field at a time. The added complication is that I don't want this code attached to a form as there's already quite a few records currently in the database, I was thinking along the lines of a simple printed report instead.

Any ideas or should I stop trying to do it using loops? I have checked other fields using If...else statements to produce different messages depending on whether there is data in the field and then run this through a query which I can then use as a report.

Sorry if this seems a really daft problem!
Thanks!




I have some code which basically opens up a query called "qry_tickback_template2" and by looping through a table called "tble_List_Tickback_Queries_Criteria_GTM" it changes the parameters each time and creates excel files based on each record criteria. The criteria for the query is held in the table above as field called "MYWHERE" which contains an sql where string and also the Excel file name is held in this table as "FileName".

Up until a week ago the code was working fine and now it has stopped and I am struggling to understand what is wrong. When I step through the code I can see that although the "qry_tickback_template2" is having its sql criteria changed, when the code gets to the dim called "RRun" its value is nothing. The query run directly brings back records so I know it is the code that has stopped working?!

PLEASE HELP I am going round in circles with this, Thanks

Code:

Function tickback_Export_GTM()
On Error Resume Next
Dim q As QueryDef, db As dao.Database, param1 As String, param2 As String, myrecordsin As dao.Recordset, strXLFile As String, xlApp As Excel.Application, xlBook As Excel.workbook, xlSheet As Excel.Worksheet
Dim t As Long, w As Long, x As Long, y As Long, z As Long, RRun As dao.Recordset, NumIterations As Integer, inti As Integer, dblPct As Double
Dim pgbar As progressbar
Set pgbar = Forms![TickBack_List].ProgressBar9.Object


NumIterations = DCount("Query_Name", "tble_List_Tickback_Queries_Criteria_GTM")


' Modify the Query.
Set db = CurrentDb()
Set q = db.QueryDefs("qry_tickback_template2")
Set myrecordsin = db.OpenRecordset("tble_List_Tickback_Queries_Crite ria_GTM")

myrecordsin.MoveFirst
inti = 1

Do While Not myrecordsin.EOF


pgbar.Max = NumIterations
pgbar.Scrolling = ccScrollingSmooth
pgbar.Appearance = cc3D
pgbar.Min = 0
pgbar = 0

pgbar.Value = inti
dblPct = inti / NumIterations
Forms![TickBack_List].txtPctComplete = dblPct
Forms![TickBack_List].txtPctComplete.Requery
'Forms![TickBack_List].boxPct.Width = Forms![TickBack_List].boxWhole.Width * dblPct
'Forms![TickBack_List].txtI = inti

'************************************************* ************************************************** *******************

param1 = Left$(myrecordsin("MyWHERE"), 255)
param2 = Mid$(myrecordsin("MyWHERE"), 256)
'param = myrecordsin("MyWHERE")



'************************************************* ************************************************** *******************

strXLFile = myrecordsin("FileName")

Kill "c:temp" & strXLFile
Kill "K:COMMONDISReport Logging DatabaseOutputsTickback_Output" & strXLFile
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Open("c:temp" & strXLFile)

If Err.Number = 0 Then
Else
Set xlBook = xlApp.Workbooks.Add
End If


Set xlSheet = xlBook.Worksheets.Add
xlSheet.Name = "Tickback"

q.SQL = "SELECT qry_tickback_template.* FROM qry_tickback_template " & param1 & param2
'Q.Close

' Run the query.
DoCmd.SetWarnings False
'DoCmd.OpenQuery "qry_tickback_template2"
varretval = SysCmd(acSysCmdSetStatus, "Creating " & strXLFile)
'************************************************* *******************
Set RRun = db.OpenRecordset("qry_tickback_template2")

y = 1
x = 0

'x in the following line being the column of data from RRun
For x = 0 To RRun.fields.Count

xlSheet.cells(1, x + 1).Value = RRun.fields(x).Name
'xlSheet.cells(1, x).Value = RRun.fields(x).Name
'Exit For

Next x


'MAKE SURE you start at row 2 column 1
y = 2
x = 0
'INSERT DATA ON SPREADSHEET x = Column y = row
RRun.MoveFirst
Do While Not RRun.EOF

For x = 0 To RRun.fields.Count
'xlSheet.cells(Y, X - 1).Value = RRun.Fields(X).Value
xlSheet.cells(y, x + 1).Value = RRun.fields(x).Value
'Exit For
Next x

y = y + 1

RRun.MoveNext
Loop


xlSheet.Range("a1:Ab1").Font.Bold = True
xlSheet.Range("q1:w1").Font.Color = RGB(255, 0, 0)
xlSheet.Range("a1:Ab1").Rows.Interior.ColorIndex = 15
Dim highlrow As String
Dim BORDERrow As String
highlrow = "w" & CStr(y - 1)
xlSheet.Range("q2", highlrow).Rows.Interior.ColorIndex = 36
BORDERrow = "ab" & CStr(y - 1)
xlSheet.Range("A2", BORDERrow).VerticalAlignment = xlTop

xlSheet.Range("A2", BORDERrow).Borders.Color = 0
xlSheet.Range("A2", BORDERrow).Borders.LineStyle = xlContinuous
xlSheet.Range("A2", BORDERrow).Borders.Weight = xlThin


xlSheet.SaveAs "c:temp" & strXLFile
xlSheet.Columns.AutoFit
'Delete unwanted sheets
For x = 1 To xlBook.Worksheets.Count
If xlBook.Worksheets(x).Name Like "Sheet*" Then
xlBook.Worksheets(x).Delete
End If
Next x


xlSheet.SaveAs "c:temp" & strXLFile
xlBook.Close
FileCopy "c:temp" & strXLFile, "K:COMMONDISReport Logging DatabaseOutputsTickback_Output" & strXLFile


myrecordsin.MoveNext
inti = inti + 1
Loop


DoCmd.SetWarnings True
db.Close
Set db = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Call statusreset
' Release the object variable
Set RRun = Nothing
End Function




I tried looking this up and maybe I am using the wrong words, but I can't seem to find anything. That will help me.

What I need to do is loop through all the tables in the database which start with the word "Agreement" and take out the word "Agreement" from the title.

It seems simple... but I don't know how to make it work... can someone help me here?

Here's the code I have which loops through the tables... but I don't know how to reference the table name in order to cange it:

Quote: Dim dbCurr As DAO.Database
Dim intLoop As Integer
Dim tblName As String
Dim tblNewName As String

Set dbCurr = CurrentDb()

For intLoop = (dbCurr.TableDefs.Count - 1) To 0 Step -1

If Left$(dbCurr.TableDefs(intLoop).Name, 4) = "Agre" Then
tblName = dbCurr.TableDefs(intLoop).Name
tblNewName = Mid(tblName, 10, Len(tblName) - 9)

'OK so here is where I am missing the correct code
'what I want to say is something like:

'dbCurr.TableDefs(intLoop).Name tblNewName (but I know this won't work...)

End If

Next intLoop

Set dbCurr = Nothing

End Function Thanks for your help!
-Gary




Hi Everyone,

I'm rather new at Access and DB programming and I have a question I would like to ask. I'm currently building a DB that has a set of paramerised queries that evatually write a new record in a table. I used a macro to run all the set but I still need to manually enter the parameters. I want to run this macro for over a 100 different parameters (which are organised in another table).

Is there a way to write the macro so it will go through all the list? Would I need to use VBA?

Thanks in advanced,

Okarmi




Hello-

I've created this code below that loops through a file and perform some caclulations. However, I am having trouble with the output. The output table will have the same number of columns and rows however I cannot seem to be able to get past the first column in my array before I get a run-time error 9 telling me my subscripts are out of the range. Basically, I am looking to have the first column to contain text (the names of the columns) and first row to be the same. The corresponding data will fill in appropriately.

Example of Output
Index 1 Index 2
Index 1 0.52 0.64
Index 2 0.64 0.33

Iíve played around with the code and I still canít seem to get it to work. Any pointers are much appreciated.

Thanks!

EK

Here is the code


	Code:
	 
Sub CovarianceMatrixLoader()
Dim rs As ADODB.Recordset
Dim IndexNames() As String
Dim arrOutputTable() As Double
Dim RetOne, AvgOne, RetTwo, AvgTwo, CoVar As Double
Dim TotalRows As Double
Dim icount, kcount As Long
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
'Find the total count of number of indices for the matrix
rs.Open "SELECT tblIndices.Index FROM tblIndices;", , adOpenForwardOnly, adLockReadOnly
icount = 0
ReDim IndexNames(0)
Do While Not rs.EOF
    ReDim Preserve IndexNames(icount)
    IndexNames(icount) = rs.Fields("Index")
    icount = icount + 1
    rs.MoveNext
Loop
rs.Close
'Find the Average for the first index in the group as well as the total count of observations
For icount = 0 To UBound(IndexNames)
    ReDim arrOutputTable(0 To UBound(IndexNames), 0 To UBound(IndexNames))
 
    rs.Open "SELECT Avg(" & IndexNames(icount) & ") AS AvgFirst, Count(*) As TotalRows FROM tblMonthlyTotalReturns;", ,
adOpenForwardOnly, adLockReadOnly
    AvgOne = rs.Fields("AvgFirst")
    TotalRows = rs.Fields("TotalRows")
    rs.Close
'Find the Average for the second index in the group
        For kcount = 0 To UBound(IndexNames)
 
            rs.Open "SELECT Avg(" & IndexNames(kcount) & ") AS AvgSecond FROM tblMonthlyTotalReturns;", , adOpenForwardOnly,
adLockReadOnly
            AvgTwo = rs.Fields("AvgSecond")
            rs.Close
 
'Pull the monthly individual returns for each index
                rs.Open "SELECT " & IndexNames(icount) & ", " & IndexNames(kcount) & " FROM tblMonthlyTotalReturns;", ,
adOpenForwardOnly, adLockReadOnly
 
                CoVar = 0
'Loop through the monthly returns and calculate the variance and covariance of returns
                Do While Not rs.EOF
                RetOne = rs.Fields(IndexNames(icount))
                RetTwo = rs.Fields(IndexNames(kcount))
                CoVar = CoVar + (RetOne - AvgOne) * (RetTwo - AvgTwo)
                rs.MoveNext
                Loop
                    CoVar = CoVar / TotalRows
                    arrOutputTable(kcount, icount + 1) = CoVar
                    rs.Close
        Next kcount
'Paste CoVar and the corresponding index into Correlation Matrix Table
'The corresponding index only needs to be added on the first run
            If icount < 1 Then
                rs.Open "CorrelationMatrix", , adOpenDynamic, adLockOptimistic
                    rs.AddNew
                    rs.Fields("Index") = IndexNames(kcount)
                    rs.Fields(IndexNames(icount)) = arrOutputTable(icount, kcount)
                    rs.Update
                    rs.Close
            Else
                    rs.Open "CorrelationMatrix", , adOpenDynamic, adLockOptimistic
                    rs.Fields(IndexNames(icount)) = arrOutputTable(icount, kcount)
                    rs.Update
                    rs.Close
            End If
 
 
 
Next icount
'rs.Close
End Sub





I have a database that's probably poorly designed, but it exists and I need to use it!

It's a database that collects responses to a questionnaire, in effect. Each row represents a response, and each row contains personal data (name, email etc) and then 69 fields that are the responses to the questions (Q1-Q69). There are then 69 separate fields which are basically an "agree/disagree" for each of the 69 questions (Q1ad - Q69ad)

I've done the easy bit - a report that looks up Q1 and Q1ad and displays the report for all 700 or so responses. The report also groups the responses by respondent type (e.g. individual, organisation, company - of which there's a table in the database containing the 15 types). But what I need is for the report to do this for a selection of the questions - e.g. 1-40, 41-50 and 51-69. Clearly I don't want to have to drag and drop each of the fieldsets as that'll take forever! I don't mind if the VBA code creates 69 individual reports and then a separate bit of VBA that combines the reports into one (or three, as above) big report(s) at the end or if it just whirs away and creates one report - but the end result needs to be:
Q1
--Respondent group 1
----A1 (answer and agree/disagree)
----A1 (answer and agree/disagree)
--Respondent group 2
----A1 (answer and agree/disagree)
--Respondent group 3
----A1 (answer and agree/disagree) [... until all 700 responses done]
Q2
--Respondent group 1
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
--Respondent group 2
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
----A2 (answer and agree/disagree)
--Respondent group 3
----A2 (answer and agree/disagree) [... until all 700 responses done]
[... until up to whatever upper question limit - e.g. 40 as in example above]

Any suggestions as to how I can get around this? I don't want to have to create 69 reports, all with different queries but the same layout. And pulling them together into these monster reports of multiple questions is really key.

Very grateful for any assistance you can provide, and my thanks in advance.




Hi All,

I'm trying to loop through some pivots in excel from access vba.

the top refresh of my code works (if i hardcode in the pivot name)
the second refresh gives an error (passing the pivot name into a variable)


	Code:
	Dim pvt As PivotTable
For Each pvt In xlSht2.PivotTables
xlSht2.PivotTables("PivotTable1").PivotCache.Refresh
xlSht2.PivotTables(pvt).PivotCache.Refresh
Next

the error:
unable to get the pivot tables property of the worksheet class

Anyone??

Thanks,
Spin




I have written some code that generates a Batch File from Excel (2003) to copy a Backend File and another db file. Then I call a macro in the db file that relinks it to the Backend file on the users desktop. This was to speed up the process of pulling data. If I step through it it's fine but when I run it the tables aren't linked. What am I missing other than an easier way to do this? Or, how can I make this work? Any help would be great!

Here is my code in the XL workbook.
Option Explicit
Sub GetBE()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim strBatFile As String
Dim strWorkHorsemdb As String
Dim strCopyTo As String
Dim struser As String
Dim strBEmdb As String
Dim strKillFile As String
Dim strpath As String
Dim db As Object
struser = Environ("USERNAME")
strCopyTo = ActiveWorkbook.Path & ""
strKillFile = ActiveWorkbook.Path & "Work Horse.mdb"
strWorkHorsemdb = ActiveWorkbook.Path & "Work Horse.mdb"
strBEmdb = "C:Documents and Settings" & struser & "DesktopKCB_BE.mdb"
' sets the file name of the batch file to create
strBatFile = ActiveWorkbook.Path & "CopyBE.cmd"
' creates the batch file
Open strBatFile For Output As #1
Print #1, "Echo Off"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "COPY ""xxxxxxcommonBDWKCB_BE.mdb"" ""C:Documents and Settings" & struser & "Desktop"""
Print #1, "COPY ""xxxxxxcommonBDWUtilitiesWork Horse.mdb"" ""C:Documents and Settings" & struser & "Desktop"""
Print #1, ""
Close #1

' runs the batch file
Shell strBatFile 'This executes my Batch file
Application.StatusBar = "Verifying Local Tables..."
'Making sure Files are copied to the Desktop
Do Until FileThere(strWorkHorsemdb) = True
Loop
Do Until FileThere(strBEmdb) = True
Loop
Application.StatusBar = "Local Tables verified..."

Application.StatusBar = "Linking Local Tables..."
strpath = LCase(Environ("USERPROFILE"))
strpath = strpath & "DesktopWork Horse.mdb"
' Get a reference to Access
Set db = CreateObject("Access.Application")
'Open database...
db.OpenCurrentDatabase strpath
'Hide Access app...
db.Visible = False
db.DoCmd.RunMacro "M_LinkTables"
' Close the database.
db.CloseCurrentDatabase
'Quit Access.
db.Quit
'Free up memory
Set db = Nothing
Application.StatusBar = "Local Tables Linked and refreshed..."
End Sub
Function FileThere(FileName As String) As Boolean
FileThere = (Dir(FileName) > "")
End Function

Here is the Function I have in my Access db (Work Horse.mdb) that is being called by the macro "M_LinkTables"
Option Compare Database
Option Explicit
Public Function RelinkTables()
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
Dim NewPathname As String
Dim struser As String

struser = LCase(Environ("USERNAME"))


NewPathname = "C:Documents and Settings" & struser & "DesktopKCB_BE.mdb"
'Loop through the tables collection
For Each Tdf In Tdfs
If Tdf.SourceTableName "" Then 'If the table source is other than a base table
Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
Tdf.RefreshLink 'Refresh the link
End If
Next 'Goto next table
End Function




I have some code that loops to parse some data. To get the data into a table, do I use Recordsets?

Is it easier to parse the data into a string, use split and then cycle that array to push the data into a table? Or perhaps do it on the fly when looping through my parsing code?


	Code:
	 
    Dim i As Integer

    'Cycle through lines
    For i = 0 To UBound(var) - 1
        'Check if Move line
        If (Len(var(i) > 0)) And (InStr(var(i), "[") = 0) Then
            'Cycle through Move lines only
            'Clear move line
            tmpMove = ""
            'Reset counter
           
            x = 0
            Do While (Len(var(i + x) > 0)) And (InStr(var(i + x), "[") = 0) And (UBound(var) > x + i - 1)
                'Add the current row marked by i+x
                
                tmpMove = tmpMove & var(i + x)
                x = x + 1
                If (UBound(var) < x + i) Then Exit Do
            Loop
            'Debug.Print i, x
            i = i + x
            'MsgBox tmpMove
            Debug.Print tmpMove
            'strPGNtoCSV = strPGNtoCSV & tmpMove & "," & Chr(10)
            strPGNtoCSV = strPGNtoCSV & tmpMove & ";" & Chr(10)
        Else
            Debug.Print var(i)
            'strPGNtoCSV = strPGNtoCSV & var(i) & "," & Chr(10)
            strPGNtoCSV = strPGNtoCSV & var(i) & ";" & Chr(10)
        End If
    
    Next





Hello all,

I'm a newby to this forum and have some slight understanding of VBA but I'm wondering if this is possible.

I have a table that has some fields in [band],[start date],[end date]

What I want to do is I have a query that pulls criteria from another table. In one field the brand code is inserted such as AAA and then on another field in criteria I want a date between criteria range.

What this does is pulls back selected data from the master table and inserts it into another table. The problem I have is that there are 20 brands with all different dates. Can a loop through query in VBA help me instead of having 20 serperate queries? If so how would I even write something like this?

Much appreciated.

Ric


Page 1 of 14.
Results 1...20 of 264