calculate total from multi-value field

hello everyone,

I have a multi-value field in a form. what I would like to do is calculate the total of the selected records and display the value in a textbox in the same form. i.e. the user will select whatever values desired from that field and the summation of the values should be placed in the textbox.

please note that I have 5 columns in the multi field, and want to add up the second one.

I've been trying this code but all it does is adding up the row numbers not the values of the rows!!



	Code:
	Private Sub lookup1_AfterUpdate()
Dim i As Long, msg As String, Check As String
 
     'Generate a list of the selected items
   'On Error GoTo cmdGo_Click_Error
    msg = ""
    Me!txtSum = 0
    With lookup1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                msg = msg & .Column(1, i)
                
                Me!txtSum = Nz(Me!txtSum) + .Column(1, i)
            '    msg = msg & .Column(0, i)  & " " & .Column(1, i) & vbNewLine
            End If
        Next i
    End With
 
    If msg = vbNullString Then
         'If nothing was selected, tell user and let them try again
        MsgBox "Nothing was selected!  Please make a selection!"
        Exit Sub
    Else
         'Ask the user if they are happy with their selection(s)
        MsgBox "You selected:" & vbNewLine & msg & vbNewLine
    End If



On Error GoTo 0
   Exit Sub
 
'cmdGo_Click_Error:
    'cswLogError Application.CurrentObjectName, "Error_SortCodeHeader2_Format", Now, Err.Number, Err.Description
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdGo_Click of VBA Document Form_Form2"
   Resume Next
 


End Sub

how can I use the after update function properly to calculate the total of the selected records??


regards,


Post your answer or comment

comments powered by Disqus
Hi,


I am using MS Access 2007.


I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.


Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.


How to change item source for the multi-valued field from a linked table to a list that I can type in values?


Is there a feature provided by MS Access 2007 can enable such a conversion?



Thank you in advance.

Hi,
I need to import data from an Excel spreadsheet that has a column with cells that have multiple values. The Excel spreadsheet was generated by Google Forms. The column with multiple values was populated by a question asking "What requirements does this class fulfill?" and the response is a series of check boxes. The user can check more than one box.

The entries in the Excel spreadsheet look like this:
Answer1, Answer2, Answer3 - If the user checked boxes for Answer1, Answer2, and Answer3
Answer2, Answer3 - If the user checked boxes for Answer2 and Answer3
etc

The corresponding fields in Access are currently setup as:
Data Type - Text
Display Control - Combo Box
Row Source Type - Value List
Row Source Type - "Answer1"; "Answer2"; "Answer3"
Allow Multiple Values - Yes
Limit to List - Yes

If I try to Import External Data (append), I get the error message:
"The contents of 1 records were deleted, and 0 records were lost due to key violations.
*If data was deleted, the data you pasted or imported doesn't match the field data types or the FieldSize property in the destination table.
*If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violate referential integrity rules for a relationship defined between tables

If I import the Excel spreadsheet into a new table, and then change the Lookup for the column in question, I end up with combo box fields like this:
[] Answer1
[] Answer2
[] Answer3
[] Answer1, Answer2, Answer3
[] Answer2, Answer3

So it looks like the multi-value fields from Excel are just creating new possible values in the combo box instead of checking multiple boxes like it should, even if I have "Limit to List" set to "Yes" in the Lookup for that field.

I hope this all makes sense. Any help would be greatly appreciated. I am obviously a total newb at this, but I have not found the Access help files very helpful. Thank you!

Hi,

I am using MS Access 2007.

I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.

Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.

How to change item source for the multi-valued field from a linked table to a list that I can type in values?
Is there a feature provided by MS Access 2007 can enable such a conversion?


Thank you in advance.

I have a table field (Group) that is linked to a Multi Select List Box.

I want to open a form, filtered to just ONE of the Groups stored in that Group field.

I have a simple pop up form that allows selection of the desired filter from a combo box of individual Group names and then would like the following code to run when the form opens.


Me.Filter = "[Group] = Like ""*"" & '" & Group.Text & "' & ""*""" ' List Box version

However, I keep getting an error message saying that the multi-valued field 'Group' cannot be used in a Where or Having clause.

In summary, I want to store multiple groups in the main table and then be able to select only one of the Groups from a combo and get the form to open based on the single Group selected from that combo.

Hope this is clear and that someone can guide me through. Thanks.

Given the amount of research and then trial and error, I thought I would post my soloution to what has been a tricky problem. No website gave me the simple answer or at least an answer that would actually run. Anyway here is the background.

One table has (say) 6 separate records all with a same Project Reference field. There is also a multi-value field too.

What I wanted to do was copy the records from one Project Reference to another in the same table. Sounds simple but needed to not only cope with the multi-value field but also change the Project Reference value to the new one.

Here is is:

Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
"WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
"ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)

Set rstTo = dbs.OpenRecordset("Risk Assessment and Area Classification", dbOpenDynaset)

rstFrom.MoveFirst
Do Until rstFrom.EOF

rstTo.AddNew
For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber

If rstFrom.Fields(iFor).Type 104 And rstFrom.Fields(iFor).Type 109 Then ' types 104 and 109 are Multi-Value field

If rstTo.Fields(iFor).Name = "ProjectRef" Then
rstTo.Fields(iFor).Value = myProjectRef
Else
rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value
End If

Else
Set rstMVFrom = rstFrom.Fields(iFor).Value

If rstMVFrom.RecordCount > 0 Then
Set rstMVTo = rstTo.Fields(iFor).Value

Do While rstMVFrom.EOF = False
rstMVTo.AddNew
rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
rstMVTo.Update
rstMVFrom.MoveNext
Loop
rstMVFrom.Close
rstMVTo.Close
End If ' rstFrom

End If ' iFor
Next iFor

rstTo.Update

rstFrom.MoveNext
Loop ' rstFrom

rst2.Close
Set rst2 = Nothing

Hope this helps someone.

Hi

I have a table (Property_Match) which has some Multi-Value fields in there. For the purposes of this, lets use the Furnishing_Level field. This has "Unfurnished" , "Part Furnished" and "Fully Furnished" as the 3 options.

I have created a form (frm_Property_Match) in which this control is updated. I have also created a query, into which I have entered in the criteria...

[Forms]![frm_Property_Match]![Furnishing_Level]


I would expect this query to return records but it doesnt, unless I delete the multi-value fields from the query. What am I doing wrong ?

Thanks in advance

Al

Hello,

I would like to extract a value from a particular row for a field that contains more than one value (i.e. multiple rows).

I will first describe the setup. The main form is called “Extract” and the subform is called “ExtractCategory”. The ExtractCategory subform contains two fields called “CategoryId” and “Description”. Both of these fields can contain multiple values. In this case, the ExtractCategory Table has a many-to-many relationship with the Extract table (i.e. many categories can be applied to many extracts).

For example, the screen may look something like the following:

Extract Id: 001

Extract Text: Blah blah blah ….

Category Id Description
001 Finance
002 Taxes
003 Exemption

I would like to be able to extract a value at any row from the Description field within a VB program. If I just refer to the description field in VB it only returns the value which currently has focus. So, if the user is on the second row, referring to the Description will return the value “Taxes”. However, I may want to obtain the value in the third row, the first row, etc.

Likewise, when I am in the Extract form and attempt to interrogate the Description field (using the command “Forms![Extract]![ExtractCategory].Form![Description]” it always returns the first row (which in the example above would be “Finance”).

I have looked for an answer to this questions but as of yet have come up empty. I have also tried referring to description as an array (such as Description(3) or Description[3]) but nothing appears to allow me to do this.

My questions are as follows:

1. How do I extract a value from a particular row for a field with multiple values / rows within the active form.

2. How would the statement “Forms![Extract]![ExtractCategory ].Form![Description]” have to be modified in order to effect the same purpose as in the first question. In this case the “ExtractCategory” subform would not have focus and I would want to extract the value while the operator was in the “Extract” form.

I apologize if I am using the term multi-valued incorrectly. However, in the Unidata environment where I used to work it was standard terminology to refer to a field that contained multiple values as a multi-valued field. In the case of Unidata, each value would be separated by a delimiter and one could refer to each value as follows:

Description for the first value “Finance”.
Description for the second value “Taxes”, etc.

Thanks in advance for any help with this problem.

i have table on sharepoint which i can not link to my access database so i have to import it. table on sharepoint is same as in access and i need table from sharepoint updated with my access. unfortunately this table has multi valued fields. when i import table from sharepoint it, the table gets renamed so if the original table is tablea the imported table becomes tablea1. im trying to update my table in access but then i get error "An INSERT INTO query cannot contain multi-valued field." if i remove multi valued fields from query, the query is working fine. does anyone have any ideas how i can get the values into my table in access? any help is greatly appreciated

I have the following in a query which seperates a multy value field.

SELECT D-Report.*, Left([Customer_Action_Req_d_Reason],InStr(1,[Customer_Action_Req_d_Reason],",")-1) AS Code1, Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+1,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) AS Code2, Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")+5,InStr(Mid([Customer_Action_Req_d_Reason],InStr([Customer_Action_Req_d_Reason],",")),",")+2) AS Code3
FROM [D-Report];


This works when there is 2 or 3 sets of code example

111,111,111
or 111,111

But does not work for anything not trailing a comma
example 111 shows as null

Is there a way around this?

So I have a bit of a conundrum. I use MS Access and SQL a bit, but I'm a fairly novice VBA programmer and am not sure how to make two forms operate together properly.

Basically, form2 is used for data entry and contains fairly standard directory-type fields (name, address, etc) along with a multi-value field called "aCoverage" which stores the geographic coverage areas of the contact record. For instance, some of my contacts operate in a number of states, though most operate in just one. Thus "aCoverage" might contain marked checkboxes for "GA", "SC", and "FL", the two letter abbreviations of applicable state coverage areas.

Form2 contains fields based on two tables, "tblAgency" and "tblPersonnel", which are joined by the primary key "agencyID". The multi-value field, "aCoverage" is stored in tblAgency, and draws from a pick list table "plStateList" which contains both the two-letter abbreviation and full name of each state (seperate fields).

What I would like to do is use Form1 as a switchboard to enter Form2, with a subset of records showing based on the geographic coverage area desired. I did some internet searches and found that MS Access has issues implementing filters based on MV fields, which looks like what I want to do. (http://support.microsoft.com/kb/926701 ). I would like to be able to press a command button to programmatically filter and open the 2nd form though, rather than manually applying an "advanced filter" after opening the form. Would be happy to make intermediate queries or tables as necessary, but I really don't want to redo the MV field since the geographic coverage area is variable.

I did already create one potential "intermediate" query called "qryAgencyByStateCoverage" which contains the fields "agencyID", "agencyName", "agencyCoveragePL" (2 - letter abbrev), and "plStateFull" (entire state name). The first four fields come from tblAgency and the last one comes from plStateList, which is joined to the former (agencyCoveragePL.value) via a field called "plStateAbbrev". I thought that this query might be useful, though I am not sure how to connect it to anything.

Anyways, what would be the best way to address this issue? Basically, I would use the filter to, for instance, display all the records for agencies that operate in Georgia (some of which also operate in adjacent states). The applied filter should operate based on some kind of drop-down list of states located on Form1 (preferably the full spelling rather than abbreviation.) Ideally, I could also generate a report based on the same [type of] filter, through an additional command button on Form1.

Well, thanks in advance for any assistance anyone can give! (Sorry this is such a long description; please let me know if there is any other info needed.)

Hi Again

Thanks for all the help previously to the guys that helped me last time if they read this.

My question is i am trying to setup an POS system on my laptop so that i can print invoices from it so i am trying to calculate totals from the sub datasheets that i have inserted into my form.

I am using a relationship between the tables so that the linking field is the invoice number.

Now as i fill in the details of what the customer has ordered in the sub datasheet i would like a total displayed in the main form where the customer details are located, currently at the moment i am using this code in the total box at the top of the page.

=Sum([InvoiceDetails subform].[Form]![Text14])

Text14 being the running totals for each of the items the customer purchases, for example if they purchase 10 of one item at £5 and 5 of another at £7, the totals for the first item and the totals for the second item are calculated in this field.

This is the field i need adding together line by line i.e Total of (£5 x 10=£50)+(£7 x 5=£35)+(etc......)+(etc......) and then displayed in the main form in the grand total box.

Any help would be appreciated.

Hello peeps , I'm new to access programming and I came up with an issue with elaborating a database for a small business unit. My database is complete and everything is functional apart from one small detail. I have a form that controls the "re-supply" of products to the stores. Therefore I need to be able to send multiple products at the same time , and I will need my "stock" table to be readjusted ( I don't necessarily need help on that part if I manage to solve my current issue , I can run an update query , easy-peasy )

So on my form you can see that I have a field to specify the products I need to resupply ( based on their product id ( PK ) ) and a field where I need to specify the quantity ( non look up/relationship based (I guess) ). Ergo , my goal would be to associate a quantity to the products being delivered. Any suggestions will be welcomed Attached Thumbnails   Reply With Quote 07-27-2011, 01:53 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,121 My advice is to not use multi-value field. I can't see a way to accomplish what you want.

I have a report where I am trying to display the items in a multi valued field sorted by name. The report is based on a "Meetings" table with a multi valued field (Participants) linking to a "People" table. The problem is that I can never get the people sorted by name, it always sorts by the associated ID in the People table. The SQL I have right now is as follows:

SELECT People.ID, People.FirstName+" "+People.LastName AS Expr1
FROM People
ORDER BY People.LastName, People.FirstName;

Any help would be greatly appreciated.

Hi All,

I am trying to join two tables on a multi-valued field and getting the error message:
"The multi-valued field '[linking jobs to people].[linking jobs to people]' is not valid in the specified join clause."

the mysql query (excuse table names and column names) is :
"SELECT [linking jobs to people].[full_name], [linking jobs to people].[linking jobs to people]
FROM [linking jobs to people] INNER JOIN [risks per job] on [risks per job].[jobs]=[linking jobs to people].[linking jobs to people]"

Can a join be performed against a multi-valued field?

Thanks a million,
Smudger.

I am currently working on a form to generate a Child Record from an existing record on a Sharepoint List Table in Access 2007. There are a handful of Multi Value Fields that need to be copied from the Parent to the Child record. I am using VBA to pre-load the form with the Parent information for the majority of the fields that should be the same, but I am encountering errors when the Multi Value Fields are attempted.

I am able to use a Field Recordset against the Parent record to list out the different values stored in the Parent Multi Value Field, but now I need to know if there is a way to apply these values to the Multi Value Field on the Form? NOTE: this form is not submitted or saved, so referencing the record to update it is not an option since a user may need to update fields prior to the initial save.

PS - I am now finding out how difficult the Multi Value Fields are to use in Access and VBA, but I must use this as the lists are from Sharepoint and I don't have any other options.

Let me preface to say that I'm new to Access.

I am creating a database for tracking projects/assignments - where multiple people can be assigned to one project. I created a multi-value field called "Assigned To" which is looking up the data from a table listing personnel.

The problem is unlike my other tables, where there are sub-datasheet is created once the relationship between the tables is established; the personnel table does not have a sub-datasheet.

Is there anyway around the issue I'm having? Can I:
1. Create a sub-datasheet so I can see all the projects a person is assigned to. OR
2. How do I go about creating fields that address one project to many people.

Thanks.

Regarding my previous post, I have figured out how to have it SetValue a value to my fields upon click of the button for all except my multi-valued fields. I have 2 multi valued fields, on one "Expense Type" I want to have it click all the checkboxes for that field. For the "Earn Code" I want to have just a specific value selected.

Can someone help? Thanks in advance,

hi,

i used gromits search db and converted it, but now im in a pickle, i changed one field to a multi valued field and now when i try to search something on that field, it does not work, it tells me the WHERE clause cannot be used there, how do i fix it?

hi,

i have a multi-valued field that has sunday to saturday.
what i'm trying to accomplish is being able to search my form using a multi-valued field, how do i code it in using vba? and what should me row source for the field? thanks in advance.

Scenario:
2 tables
1)product
a)sn
b)onhand

2) issued
a)id
b)product
i)product.value


on access 2010. when u create a new issue record it allows to select multiple products (lookup multi-value field) I want to be able to set [product].[onhand] to false if they were selected.

Is this possible?

I am trying to merge 2 tables into 1 and I get an error "Multi-Valued fields are not allowed in SELECT INTO statement" error when using the Make Table query. How do I find the offending field? Between the 2 tables there are close to 75 fields.

Hello,
I have a split form for a certain table. in this table i have a multi valued field (checkbox list).
i would like my form to have a dropdown combobox with certain values and when one value is chosen, the table will filter according to the multi valued field and the combobox value.
how can i do this? using a macro or query? please advice.

thank you

Hi, new to access here so may seem like a stupid question.

Im creating a data entry form for a database to log inventory.
Running Access 2010 in layout view I'm creating a combo box to have a list in which multiple items could be selected. The first one I created works fine, second and third etc will only give a single select option (single value field if I'm thinking correctly?). Is there anyway of setting this to a multi-valued field without having to start again?
Walking slowly through the process there is no option coming up for me to select to set it for multi-value use.


Any help would be very welcome.

Phil.

Hello All and thanks for any help you can provide

I have a form that has a MVF in it, originally I had the MVF set as text in the table, but had to convert it to a number field to make it web compatible.
Before I converted to a number field I've been able to only show the selected Comments within the MVF for the current record.
I still can do that (see picture link) but I can't for the life of me figure out how to not only show the CommentID but also the
actual Comment next to it. Setting Column Count to 2 does not work, it only shows the ID again in the second column.

The goal is to have the top List box only show the selected for the current record with the "comment name", and have the bottom list box for adding or deleting any comments. The reason for this is so the user has a simple view of the comments related to the current record, and not have to scroll through the long list on the bottom.

Any help you can provide with this would be great. Thank You. Attached Thumbnails   Reply With Quote 10-24-2012, 10:45 AM #2 rpeare VIP Windows XP Access 2003 Join Date Jul 2011 Posts 1,917 I don't think you're using a mutli value field unless you're using a newer version of access than your profile says. True multi value fields came into existence with Access 2010 I think. (p.s. true multi value fields are the devil and you should not use them) If you are actually doing something different than a multi value field can you provide a sample database a screen shot is not much help without the underlying code/SQL setup.


Not finding an answer? Try a Google search.