Looping Thru An Array Cheking For Values In A Table

Feb 18, 2008

arrLocations = Me!customerBranchLocations.Value
'MsgBox (arrLocations)

arrParams = Split(arrLocations, ";")
For i = 0 To UBound(arrParams)
MsgBox (arrParams(i))

strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & ""
'MsgBox (strSql1)
DoCmd.RunSQL strSql1
Set rs = db.OpenRecordset(strSql1)
Dim count
count = rs.RecordCount


Next

Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?

Thx.

View Replies


ADVERTISEMENT

Get Combo Box Values Into String Array

Jan 9, 2006

I'm populating a combo box from a query. I'm running a DCount on 2 criteria. The user selects the criteria from 2 combo boxes. If the user types * into the combo box, I want to be able to loop through each combination from the combo box values. I need to get the list of values from the combo box and put it into a string array, however it throws a type mismatch if I did something like
BRANCHES(x)=CStr(cboBranches(x)) 'cboBranches(x)=cboBranches.Value(x)
So I tried to run the sql from vb but found out that I can't return a string value from that (vb sucks). So I'm back to trying to get the values from the combo box. Any help would be appreciated.

View 3 Replies View Related

Storing Array Values In Access

Aug 2, 2007

Hello,

I wanted to catch a little advice if I may be so bold. I have a HTML form with a series of checkbox values.

Code:<form method=post action=form-test.asp><input type=checkbox name=drinks value='cocal-cola'>cocal-cola<input type=checkbox name=drinks value='sprite'>sprite'<input type=checkbox name=drinks value='lucozade'>lucozade<input type=checkbox name=drinks value='oasis'>oasis<input type=submit value='Submit'></form>

Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.

Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client

However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.

Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?

Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?

Cheers,


trufla

View 2 Replies View Related

Modules & VBA :: Deleting Values In Array?

Jul 1, 2013

How do I delete values in an array? I want to reuse that array but first I need to delete its values so I would start with Arr(0) rather than where I stopped off with the last loop at Arr(10) [assuming the last calculation inserted until Arr(9)]

Here is my code:

Set rst = CurrentDb.OpenRecordset( _
"Select * from dbo_ProductStructure where ChildProductNbr Like '*" & txtPartNumber & "*'") 'search associated fields with user input
While rst.EOF = False
ReDim Preserve Arr(i)
Arr(i) = rst.Fields("ParentProductNbr")
i = i + 1
rst.MoveNext

Wend 'end of while loop

x = Arr

View 9 Replies View Related

Modules & VBA :: Empty Values In Array

Aug 16, 2013

I'm trying to store all the OrderNumber + Item combinations in 2 arrays and then because the OrderNumber column really contains 2 values I'm interested in, I split it up and store that column's values in 2 arrays. So in total, I have 3 arrays. An array for Item, an array for Order, and an array for RepId (which is the one that I split up from the OrderNumber column).

Anyways, when I print the RepId array with the ' MsgBox PostValCol1(x) ' It prints 4 values like it's supposed to. But when I tested it again by looping through the values and just doing a MsgBox, It goes for a long time and that's because it has a lot of empty values in that array. How to get rid of those empty values/not store them in the first place?

Code:

Set rop = CurrentDb.OpenRecordset("Select OrderNumber, ItemNumber From dbo_EntryStructure Where (ProductNumber = '" & txtPartNumber & "') AND (ActionCode = 'I')")

While rop.EOF = False
ReDim Preserve ArrRepOrder(j)
ReDim Preserve ArrItem(j)

[Code] .....

View 3 Replies View Related

Modules & VBA :: Find Duplicates Of Values In Array

Oct 4, 2014

I have to deal with string arrays that store text. I need info on copying, comparing, appending arrays. Also on passing arrays as parameters to subs or functions. Where I can get to this info quickly without having to browse through many screens.

In addition to this I have some questions:

I have to find the duplicates of values in an array. Here is the code that I use.

Code:
Sub FindDuplicates()
Dim I As Integer, J As Integer, IEND As Integer, text() As String
ReDim text(IEND)
For I = 1 To IEND - 1
For J = I + 1 To IEND
If text(I) = text(J) Then text(J) = ""
Next J
Next I
End Sub

It works but is not performing well. Are there more efficient ways of doing this?

View 14 Replies View Related

Modules & VBA :: Populate Array And Use Values In Another Function

Jul 16, 2013

I've created an array that I created and declared as a Public array in my module. I created a function that populates the array so that I can use the values in another function. I've gotten the array to populate but when I go to use the values in the array in another function, the array appears at Empty. I seem to be stuck on declaring it properly or something so that it can be used by other functions.

Public arrWebIDs As String
Public Function FillArray()

View 6 Replies View Related

General :: Reading Multi-line Column Values Into Array?

Aug 19, 2013

I am working on setting up a Document Control System and have a table, called List_of_SOP where I have, amongst others, the following fields:

- Document Title
- Document Number (unique identifier)
- Responsible
- DL (where this is a multiline lookup column where one ticks the names of the people to whom this document needs to be distributed)

I want to be able to fill an array with the checked values of the DL field, given a particular Document Number. So far, the code is somewhat on these lines:

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL1 As String
Dim strSQL2 As String

[code]....

View 9 Replies View Related

Queries :: Pull Data Values In Array To Become Grouping Headers In A Report

Apr 23, 2014

I have a table with application records. One of the fields captures schools the applicant will work at. This field stores data in comma delimited format. There could be 1 school name; there could be 5 school names.

My ultimate goal is to build a report which shows me records of all applications, grouped by school choice. I want to see: School A was selected by 5 people, School B was selected by 7, etc.

Is it possible to write a query from this table that will enable this?

View 4 Replies View Related

Modules & VBA :: Store Entire Query (single Field) In Array And Check Its Values

Mar 12, 2014

Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?

View 1 Replies View Related

Looping Through Table Fields

Oct 14, 2004

I am trying to edit data in a table that has 30 fields.The fields names are W1,W2,- up to W30, and their value would correspond to a control on a form I have declared the controls in an array, so i would really like to use some sort of loop

Is There any way to use a loop to refer to fields to update them rather than
having to use
rst.edit
![W1]=Ctl(1)
![W2]=Ctl(2)
![W3]=Ctl(3)
etc
rst.update

What would the correct syntax be using a for next loop
for i = 1 to 30

rst.edit
!["W" & i]=Ctl(i) - this doesnt work
rst.update

next

thanks in advance
Bjackson :)

View 1 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 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 :: 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

Deleting Query Table With An Array

Apr 11, 2008

Hi, I have a problem deleting a specific row and field that my query have.

What I want to do is mainly delete a specific rows in the my Query table that has : Field 1, Field 2, Field 3, Field4

what i tried to do was doing is create and array that loop through the Query table and check in field 1 for a certain value and if that certain value appears I want to delete that row: for example

Dim IntRow As Integer
Dim IntCol As Integer
Dim varData As Variant


For IntCol = 0 To numFields Step 1
For IntRow = 0 To numRows Step 1
If (rst.EOF = False) Then
'Check Field 1 for value 47-72 to delete
Else
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72) Then
varData(IntCol, IntRow).Delete
End If
End If
Next IntRow
Next IntCol



In the example Above my syntax is not correct for :
If (varData(0,IntRow) > 47 And varData(0,IntRow) < 72)
and for varData(IntCol, IntRow).Delete.

can anyone help me to find the exact syntax i need to use to execute the program above?

THANK YOU!

View 2 Replies View Related

Modules & VBA :: Upload CSV To Array And Then Add Records To Table

Jun 9, 2013

I'm trying to upload a csv file into an Array then add records to a table. I have the following code which gets the information from a csv file which works fine.

Open filePath For Input As #1
Do While Not EOF(1)
Line Input #1, MyData

Problems:
1. When i try to load this into an array , it does not return all the information. It will if a smaller amount of data is sought. For example if only 1 months month of data is requested. Is there a maximum data limit that can be parsed into an array with this method?

2. I'm not quite sure how to parse individual lines (records) to update a table in access. I have provided the code that I'm trying to use to accomplish this below. From code below am I doing this correctly?

3. For each record that is created in the database I would like to add a ticker string to the record for later querying. Can this be done and am I on the right track from the supplied code?

Private Sub ImportData(filePath As String, ticker As String)
'On Error GoTo Errorhandler
Dim arrData() As String
Dim MyData As String
Dim i As Integer
Dim Db As DAO.Database

[Code] ....

View 6 Replies View Related

Tables :: Fill Exiting Table With Data From Array

Jan 24, 2013

I need to fill a pre existing table with data based in an array.

View 2 Replies View Related

Table With Multiple Like Fields That Need To Display Into Array-like Format

Jan 8, 2013

I have a table that contains the following;Company name, PositionID, Firstname1, Lastname1, title1, gender1, Firstname2, Lastname2, title2, gender2, Firstname3, Lastname3, title3, gender3 [...up to 50 names]

The data is uploaded to my table from a 3rd party database source via .csv file.I can capture up to 50 names, titles and gender per record. I would like to display this data in a table/array like format. I'm not too good at VBA but I can use expressions for unbound fields.I have attached a file that shows the data in a format I would like to see in Access displayed in a form.

View 2 Replies View Related

Add Records To Table In Bulk (not One Record At A Time) Via Multidimensional Array

Aug 13, 2015

I'd like to add 600+ records at a time to a table (or at least a lot) and do this 1200 or so times. Yes, this is a very big table, its okay and the size is not an issue.

The key here is that speed is very important. I DO NOT want to AddNew and Update a recordset 700k times (one for each new record). I want to add 600 records or so 1200 or so times.

I am open to any way of doing this in VBA and/or SQL in MS Access; however, I am thinking of building an array that has 600 rows and 16 columns, then somehow add that to the table all at once. I will size the array down if it causes problems. That isn't the issue. The issue is how to add a 2d array to a table in bulk (all at once).

Code:
Sub test()
Dim dbLocal As DAO.Database
Set dbLocal = CurrentDb()
Dim sql As String
'build my array
Dim x(0 To 1, 0 To 1) As Integer

[Code] ....

View 14 Replies View Related

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

View 5 Replies View Related

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

Sep 18, 2013

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

View 2 Replies View Related

Queries :: Rename Field Values With Values From Another Table

Mar 2, 2015

I have two tables.descriptions I'd like to relate and use to find/replace in bulk.

[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)

I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".

Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico

[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico

View 4 Replies View Related

Checking Values Of Textbox With Table Values

Sep 15, 2005

There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.

How can I do that ? :confused:

Thanks

View 1 Replies View Related

Looping

Jan 16, 2007

need help with looping to get a total for a specified time block. It needs to look at a time block and count the number of entries for a hour before to an hour after 7 am, 11am and 3pm on given 12 shifts. The list of shifts come from another sheet with the date and the time of the end of the shift.

I have the following which gives me a total for the 12 shift but now need to loop for the times given:

For rowz = 13 To 20
endtime = Sheets(sheetname).Cells(rowz, 1).Value
begintime = endtime - 12 / 24
If IsDate(endtime) = False Or endtime > Now() Or endtime = Empty Then
GoTo Exit
End If

SQLstatement = "SELECT count(Field1), count(Field2),count(Field3), count(Field4), count(Field5), count(Field6)" & _
" FROM dbo.table WHERE [Date] Between '" & begintime & "' And '" & endtime & "' "
rs.Open SQLstatement, , adOpenDynamic

If rs.EOF = False And IsNull(rs(0)) = False Then
fieldcount= rs(0) + rs(1) + rs(2) + rs(3) + rs(4) + rs(5)
Else
fieldcount = "N/A"
GoTo Exit
End If

Thanks for all the help,
SLH

View 1 Replies View Related

Looping Cont

Mar 16, 2007

Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.

Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.

Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.

Do I need another loop here? Example Do Until EOF RST1?

Here is the code:



Dim rst As DAO.RecordSet
Dim DIRName As String
Dim folder As String
Dim Dir As String
Dim strnewname As String
Dim stroldname As String
Dim Pause As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim blnExcelOpen As Boolean
Dim strMacro As String
Dim mysheetpath As String
Dim rst1 As DAO.RecordSet
Dim objdb As Database

Dir = Me.Combo19
folder = "H:" & Dir & ""

'DoCmd.OpenForm "frmWait"
'DoCmd.RepaintObject acForm, "frmWait"

Set rst = CurrentDb.OpenRecordset("tblofficers")
Set rst1 = CurrentDb.OpenRecordset("tbl_Q1")

Do Until rst.EOF
DIRName = folder & rst!OFFICERS_NAME
stroldname = "H:Asset Verification Template.xls"
strnewname = folder & rst!OFFICERS_NAME & "" & rst!OFFICERS_NAME & ".xls"


' Processing each officer and creating a directory
MkDir DIRName


Sleep 10
FileCopy stroldname, strnewname
'Name stroldname As strnewname
Sleep 10
mysheetpath = strnewname
blnExcelOpen = IsExcelRunning()
If (blnExcelOpen) Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If

Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False)
Set xlSheet = xlBook.Worksheets("Sheet1")

'Transfer the data to Excel
xlSheet.Range("A2:r10000").ClearContents

xlSheet.Range("A2:r10000").CopyFromRecordset rst1
'rst1.Close
xlBook.Save

' back to the top...

xlApp.CutCopyMode = False





rst.MoveNext


Loop
' if we started Excel, then close it now…

If (Not blnExcelOpen) Then
xlApp.Quit

End If

' clean up...
Set rst1 = Nothing
Set objdb = Nothing

Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

View 3 Replies View Related

Looping Thru DB Tables

Aug 10, 2007

Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!

View 7 Replies View Related







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