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 Replies


ADVERTISEMENT

Modules & VBA :: Storing A Single Record In A Single Dimension Array?

Sep 8, 2014

Any Single line of code available?

View 2 Replies View Related

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

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

Storing Values?

Mar 10, 2006

Howdy

I know that you shouldn't store totals in Access, but I can't think of a way around what I want to achieve and wondered if anyone had any ideas.

An item on stock will move from one location to another, I will need to list the 'balances' of this stock at each location, easy enough to do, but there are hundreds of item movements per day and the users do not want a history of movements to be kept but do require an ongoing balance.

e.g.
100 of the item moves from A to B, this movement record gets deleted, how would I know that the item stock at A has been reduced by 100 and B raised by 100?

View 1 Replies View Related

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 2 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

Calculating And Storing New Values From Existing Ones

Jan 30, 2006

I am pretty new to access, I will get right to it.

I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale.
For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70
I need access to add those up and save them so I can use them in a report.
I also need access to add the frequency of certain questions that were responded to with the answer 3.
I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere.
Thanks

View 2 Replies View Related

Tables :: Storing Calculated Values?

Mar 7, 2014

I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.

attached is my table and Input Screen.

View 3 Replies View Related

Forms :: Storing Multiple Values

Apr 29, 2014

In my database (attached), I need to be able to store multiple notes for tasks performed in various cities -- each note has a small description (more on that at the bottom of this post). For the "San Antonio, TX" task, notes "Heartbleed" and "BAT Scan" apply. From what I understand, this is a many-to-many relationship which will require the use of a junction box. My structure is like this:

tbTasks
TaskID (PK - autonumber)
Task

tbNotes
NotesID (PK - autonumber)
Notes
Details

jcttbTaskNotes
TaskID (FK from tbTasks)
NotesID ( FK from tbNotes)

My mainform (fmTasks) is based off the tbTasks table; my subform (subfmTaskNotes -- continous form) is based off the jcttbTaskNotes junction table. The Master/Child link for the subform control is TaskID. In the subform I have a combo box bound to the NotesID field in the junction table that allows me to choose a note.

My problem: I also have a "Details" text box in the subform that I want to be populated with the description of the Note selected in the combobox mentioned above. For example: if I choose the "Heartbleed" note, then I want that "Details" field to be populated with "Mitigate vulnerability associated with CVE-2014-0160". I assume it has to be somehow tied to the "Details" field of the tbNotes table, but how to implement that. How do I get the Details text box to display the details for each note?

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

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

Forms :: Subform Storing Duplicate Values

Jan 20, 2015

I am fairly new to using access and am currently creating a database for a small bakery. They have new legislation whereby they have to list all their product ingredients and any allergens in the ingredients must be shown in bold.

So far I have 3 tables as follows:

Products (*ProductID, ProductName, Photo)
Ingredients (*IngredientID, Ingredient, Allergen)
Product_Ingredients (ProductID, IngredientID)

I have 2 forms set up:

one to input ingredients and a checkbox if it is an allergen

The other has a main form and subform to get ProductName, Photo and then a combo box in the subform to add ingredients.

Everything appears to be working ok but I have an issue with my subform. The combo box has no duplicates and lists the ingredients in alphabetical order as I require. However, if I view the subform separately from the main form I see that it is storing all the ingredients selected for each product. Therefore it is storing a lot of duplicate information.

View 3 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

Adding Feild Values And Storing In Another Field In Same Table

Mar 19, 2005

I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!

View 2 Replies View Related

Access 2007 Control Array

May 5, 2007

Anyone know if Access 2007 has control arrays or some thing similar?
Thanks

View 7 Replies View Related

Control Array In Access / Text Boxes As Objects

Apr 13, 2005

Hi,

Is it possible to simulate a control array in MS Access?

I’m using a restricted version of Access 2000. I can use all normal features but some Active X controls are not licensed and it will not be possible to licence them in time I have available for my project.

I have a form which has 14 combo boxes and 24 text boxes to allow user to choose shift type and enter start and end times of shift. I know in VB 6 I have used control arrays which has vastly simplified the whole code.

I have experimented with treating the text boxes as objects and trying to create a string with the first part of the name and using numbers to differentiate between the textboxes but Access does not seem to like this.

The code I have tried is below:

Dim obTextBox As TextBox
Dim str As String

str = "Forms!frm_Shift_Entry_3!txtFST2"

‘Set obTextBox = Forms!frm_Shift_Entry_3!txtFST
'Set obTextBox = str

obTextBox.Name = "txtFST2"
obTextBox.Value = Format("12:35", "Short Time")
'b = 7
'obTextBox.Name = "txtFST" & b
'obTextBox.Value = Format("17:12", "Short Time")

commented out sections are other options I have tried.

Can anyone point out any mistakes I’m making or advise whether this is possible in Access?

Thanks

Frozbie

View 8 Replies View Related

Storing Images In Access

Feb 24, 2005

Which is a more robust approach when designing an Access database in regards to storing images, such as TIFFs. Is it better to store the images in each data record as necessary or is it better to just store a path in the database that points to a location of where the image resides (on a hard drive somewhere for instance.)

View 1 Replies View Related

Storing Outlook Emails In Access

Jul 30, 2014

I'm wondering if there is anyway I could drag and drop an email from outlook into a field in access? we are currently taking the email and converting it to PDF, then adding it to an attachment field with the appropriate record it corresponds to.

I'd like to be able to simply have access and outlook open, and drag the email from my inbox right into access and store it in access, is this possible?

View 2 Replies View Related

Modules & VBA :: Using Array To Specify Email Recipients (Access 2007 - Lotus Notes)

Aug 7, 2013

I've got a form which emails out a report when a new record is added to my database but I'm stuck when it comes to making it send it to more than one recipient.

I came across this on the web... which suggests I should be using an array but I don't know anything about arrays.

For multiple email addresses you just set MailDoc.sendto to an array of variants each of which will receive the message. So

Code:

Dim recip(25) as variant
recip(0) = "emailaddress1"
recip(1) = "emailaddress2" e.t.c

maildoc.sendto = recip

My problem is I'm not sure how to implement it? My 'recipients' are being pulled from a query... how do I get that into the array of variants?

View 11 Replies View Related

Storing Links To Images In MS Access Tables

Sep 24, 2004

I have an application in MS Access that contains some forms and reports. I want to be able to show pictures on the forms. So I am saving images on file system and storing links to those images in MS Access tables so that they can be retrieved and be shown on the forms. Can someone throw light on the effects of this approach on performance of the application (especially vis-a-vis the approach of storing images in the Access tables as OLE objects)?

Would appreciate if responses can be directed to my email-id Satya_Nadiminti@infosys.com

Thanks,
Satya

View 1 Replies View Related

Help With An Array

Jun 4, 2007

I have a table that holds the names of locations and a count for each location. example below

LocationCount
INR100
INR16
MTU245
MTU123
INR300
RVO1234
TC45
TC188

The table is always changing and I need to create a query in code that sums the count, grouping for each location. These values will need to be stored in a dynamic array for further processing. How do I store this query value into a dynamic array.

array would need to store these values

(INR, 316)
(MTU, 368)
(RVO, 1234)
(TC, 233)


Thanks in advance

View 2 Replies View Related

Array

Mar 16, 2005

Hey, is there a way to get the full list of tables in the database by using VBA? I am trying to do a backup function, where I want the database to fetch all the table names and put them into an array, to export them one by one to the backup file........hope you guys understands me.....thanks in advance

View 2 Replies View Related







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