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 Replies
ADVERTISEMENT
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
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
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
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
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
Mar 19, 2014
Im trying to delete values from a table based on the selected values of a listbox. The listbox values have a hidden column which related to the ID on the table to which I am trying to delete from (if that makes sense).
If I use the following code:
Code:
Set ctl = Me.Results_listbox
For Each varItem In ctl.ItemSelected
CurrentDb.Execute "Delete * FROM Table1 WHERE Table1.ID = " & ctl.ItemData(varItem)
Next varItem
I get an error stating Object doesn't support this property or method.
View 7 Replies
View Related
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
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
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 2 Replies
View Related
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
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
Jul 20, 2005
hello friends,
I have written an insert query but sometime it does not work properly and leave some column blank. I want to write a query which should check for the blank columns and delete these records.thanks in advance
View 1 Replies
View Related
Feb 16, 2005
I have a table which has duplicate values and i need to delete them, how do i do that?the table fields are
field1 field2 field3 Field4
1 a aa bb
1 a aa bb
1 b bc ab
2 a aa bb
2 a aa bb
I want to delete the ones which are bb in Field 4 and keep only bb value, how do i do this?
Thanks
View 5 Replies
View Related
Aug 23, 2006
I have an unbound text box. I put a value, run an append query and append the value to the table. I want to know if I can delete the value in the text box automatically when the append query excutes. I am using a macro to run the append query.
Thanks.
View 1 Replies
View Related
Apr 28, 2015
Is it possible to send several form's OpnArg as Array ? Now I'm using a long text string including || as divider so I can split them later into Array.
View 8 Replies
View Related
Nov 14, 2013
I am having a problem with parsing the array value to Forms!<arraryvaluehere>.dirty.
Please see the code below:
Public strListOfForms(0 To 2) As String
strListOfForms(0) = "frmForm1"
strListOfForms(1) = "frmForm2"
strListOfForms(2) = "frmForm3"
For i = 0 To 2
Debug.Print CurrentProject.AllForms(strListOfForms(i)).IsLoaded
Debug.Print Forms!strListOfForms(i).Dirty
Next i
Note: CurrentProject.AllForms(strListOfForms(i)).IsLoade d works and Forms!strListOfForms(i).Dirty does not.
Is this because it is taking "strListOfForms(i)" literally? Is there a way that I can get around this?
View 1 Replies
View Related
Apr 7, 2014
I have some code that successfully gathers some items and adds these items to a listbox, one item at a time using .AddItem.And it works ok.The problem is, when there are a bunch of items to add, everytime the .AddItem runs, the form redraws.there are a bunch and this leads to a lot of flicking while the form redraws however many times .AddItem adds an item.
I've had the idea of building an array and then assigning the array to the listbox.I can build the arrray no problem but i have not been able to assign the array to the listbox all at once.how to do is add items to the listbox from the array one specific item ListArray(i) at a time.
Obviously this would work, but gains me nothing in terms of the visual effect in the form becuase it is still adding to the listbox one item at a time.Is there some way to assign an array to a list box all at once, without doing it one item at a time?
View 3 Replies
View Related
Mar 11, 2014
I have an Access Database that I will be using on a desktop. I have a table in this database that mirrors the structure of a table on a remote server in a SQL database. I have successfully created a vba function within the Access database that uses a server-side php subroutine to select records (I usually won't know how many) from the SQL database and return them to the access database. The code I use in the access vba subroutine to access the php subroutine is:
Code:
With CreateObject("Shell.Application").Windows
Set ieWindow = CreateObject("InternetExplorer.Application")
ieWindow.Visible = False
apiShowWindow ieWindow.hwnd, SW_MAXIMIZE
ieWindow.Navigate "Web address for server-side php file"
End With
The last command in my php subroutine is "return $retrievedData." $retrievedData is a multidimensional array containing data from 42 fields in multiple records (again, I usually won't know how many). I've checked the data in php so I know it has been stored correctly.how do I access the returned data within my access vba subroutine?
I should add that my overall reason for doing it this way is that I want to maintain my server-side database as an untouched master. Users can only add data to it. My client-side database is used to update the input table and further process the data. The subroutines described above are intended to retrieve "new" records only (i.e., records posted since the last access database update) from the server-side database and transfer them to the access database for further processing.
View 3 Replies
View Related
Jan 3, 2006
Hi guys,
I have a question I've been trying to figure out with no luck. I have 2 fields, companyname and zip, these 2 fields may have duplicate values or they may be uniquei.e.
companyname zip
billybobs 68135
dilberts 68137
billybobs 68135
what I need to do is delete all of the duplicate records where both companyname and zip match. Is there a way I can do that with a query?
Any help is greatly appreciated
Thanks
James
View 14 Replies
View Related
Jun 26, 2014
I have a boolean array, foundState(3), whose 4 elements correspond to 4 variables describing conditions that will dictate what action is taken upon closing a form.
There are only 6 possible outcomes for the array, and they can be divided into just 4 cases:
Case {T,T,T,T}
Case {T,T,T,F} OR {T,T,F,F} OR {T,T,F,T}
Case {T,F,F,F}
Case {F,F,F,F}
What the proper syntax would be for this if I'm trying to create a "Select Case" statement for these 4 cases.
View 7 Replies
View Related
Aug 23, 2013
how I can make an Array and For Each statement work together. I normally don't use either, but it would be good so I could write less code where plausible. Here is the example I have:
Code:
Dim Named As Variant
Dim Ctl As Control
Named = Array(Me.Namee, Me.ID, Me.Title)
[code]....
I am unsure how I include the array in this statement. I have tried replacing "Me.Controls" with the array name, but I get a 424 runtime error (no object). What I am trying to accomplish is for each item in the array I want to make it not visible if it has a null value.
View 7 Replies
View Related
Jul 31, 2014
Now the getrows is working fine at my end. Now I have a different problem at hand:
Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
[Code]....
Recordcount is coming fine
Manual Array is also giving the right result
But the Index method is giving the wrong value. Why?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
View 7 Replies
View Related
Oct 23, 2014
I'm getting an error when trying to read a .txt file into an array for processing. The error and a snippet of the code are below.
Run-time error '9'
Subscript out of range
Dim iFileNum As String
Dim strLineInfo()
Dim i As Integer
Dim strExpensesFile As String
strExpensesFile = "c:FinanceExpensesIn.Txt"
[Code] .....
View 9 Replies
View Related
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
Jan 15, 2014
I have to decode a string into numbers and to avoid to find out the values for 47 options by select case I though about an array.
I want to decode
Number Letter
10 A
11 B
12 C
13 D
14 E
15 F
16 G
17 H
18 I
19 J
20 K
...
For example the string "ADEG" would give as result
10 13 14 16
So I would have to loop through the string and "decode" each letter into a number.
As I have still problems to understand array, need to define the dimension of the array, it has fix 47 entries to decode
Dim myarray (47,2) as variant
mayarray=(10,"A",11,"B",...)
Correct?
View 10 Replies
View Related
