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
create a datasheet from 2 odbc linked tables which I will be exporting to Excel. The resulting datasheet will have four columns, 'Purchase Order Number' 'Stock Code' 'Delivery Date' 'Unit Price'. The information I require in the datasheet is a unique stock code (one occurrence of each stock code) with the most recent unit price based on the last delivery date for each product. The linked tables contain many Purchase Orders for the same stock codes over a time period of a number of years where the unit price has varied.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I am using access 2010. I have "classlevel" table with 2 columns-Class and Value1 .Value1 column has numeric values that i ll input from webpage (webpage to ms access connectivity).
I want to sum the values of column "Value1" and i have another table-"Volume" which has 2 columns "VolumeLevel" and "Value2". So i want to match the sum that i calculated from first table-"ClassLevel" with the "value2" column in "Volume" table and get the corresponding "volumelevel" column value from that table and there is a third table that will get this volumelevel value.
There is no common column to join these tables.
Classlevel-
Class Value1 Class 0 3000 Class 1 2000 Class 2 300 Class 3 400 Class 4 500
Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.
What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.
Hi guys, Im working on a DatABase at the mo that is heavily featured around forms - the problem is I dont have a clue about how the code side of it is structured in access. I know a lot about visual basic, and I've been having pot shots at how to do this, but with no success...
How do I read the value of an object from a subform with macros? Lets say on my main form I have a textbox called 'ExampleTXT' - to read a value from the text box would be simple...
Code:msgbox Me.ExampleTXT
but on this form I also have a subform, which contains an array of textboxes (they link to a seperate table, so there can be any number of these textboxes) and they have the name 'ExampleARR'. How do I read the values from these? I have tried the following code (like I siad, these are random stabs in the dark) but I have had no success...
I'm a noobie with Access, and on my form which displays Account information, I also want to display the multiple Addresses this account can have. I can't find any type of scrollable text box (something to look like a spreadsheet), and I know it's not a list box I need to use.
How do I create this and what is it called? I tried using a data grid but I dont think that is it either :confused:
Also, my scrolling text icon on the toolbox is greyed out, not sure why.
I have a form where a user can select from a listbox (pulled from a database) and "Add product" - it then goes into a Textbox, each taking a new line when "Add product" button pressed. Then when they press "Add Customer" those details along with other entered details gets stored in my Customer Database..
The textbox store multi-line entries in the "ProductName" field heading in database.
Is there anyway i can use a List instead of the textbox? Adding individual entries to one certain customer? I have an EDIT facility aswell so would be easier to have a "Remove Product" button instead of manually backspacing a whole product entry from another textbox (containing all products, on new lines).
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.
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?
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
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)
I'm trying to have users enter multiple IDs from a table into a text box separated by newline chars, and then (once they press a button) have some sort of macro or code read each line and pull a report I've created based on the ID. Is that possible?
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?
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?
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()
I see a lot of requests for this operation, but apparently .DB files are so general that sometimes it's impossible? Any all-encompassing program that can read files like this? I believe they come in different formats and are produced by a variety of different programs. The only thing I ever see though are files with [.db] as the extension.
If I open the one I have with notepad on a windows machine I see some of the info but some of it is not there and is garbled.
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?
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?
