Access 2013 - creating a multiple select listbox - please help!


Hi,

I’m trying to create a bibliographic database with MS Access 2013. Please bear with me as I'm a newbie to this...


I have created a search query form that uses three list boxes with multiple (extended) select. Basically, users should be able to search for articles by year (1991-2013), country, and keyword (i.e. politics, economy). Without multiple select, the search query works fine, i.e. just clicking one option from each listbox. But now if I click for example 1991 + 1992 and two countries, it returns no records.


Is this an issue of entering the correct criteria in the query design ( for example, [Forms]![Search Form 0902]![Year].[MultiSelect]) or am I getting into the realms of complex
programming?


Would really appreciate any help!

Alec


Sponsored Links:



Hi
First of all i'm new to access. I'm making an exam registration system database. In it's candidate table, there's a subjects field which is actually a multiple selection listbox that get's the values from another table. The subjects table has their names and codes.
The problem is that when I want to make a report using the wizard, I see all selected and unselected values in the listbox in the report. I wanna see just the selected subjects with their code.
I think I have to change the query for the listbox in the report. But i'm out of ideas to what to do. If u need I can attach my database file.
and sorry, if it's on the wrong place, I dont exactly know the best place for it.
and I have windows 7 64bit and office 2010 64bit




Hello,

Does anybody know if there's a way to use the NOT selected items in a multiple select listbox?

The selected items are put into a txtbox (A) but I want the unselected items to be put in txtbox (B). Is that possible?

The multiple select listbox is updated by an cbobox.

Thanks allready for your time.

Regards, Bas




If anyone knows how to populate a field in a table from a multi-select listbox in the table's form, please let me know how. I am trying to capture everything selected in a string, which appears to be working, but it just refuses to populate the field in my table (the field is not even getting populated with the first or last selection made in the listbox). Any assistance anyone can offer would be greatly appreciated...




Hello,

I have a multiple selection listbox (datasource on a tble). I try to delete the selected row from the list box with that VBA:

Private Sub Command61_Click()
On Error GoTo Err_Exit_Click
Dim i
For Each i In ListInputBudget.ItemsSelected
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("W1_Budget Input")
rs.Delete
Next i
rs.Close
Set rs = Nothing
Set db = Nothing
Me.ListInputBudget.Requery
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub

Problem is that it does not delete the selected line but the first line and follow...

Many thanks for whom is helping me.




I created a multi-select listbox which I populate with “distinct” furnace names that have been entered into an inspection table. The end user has the option to delete records from the inspection table. The listbox gives the end user the option to filter results on a sub-form, nothing more. When the last record relating to a specific furnace is deleted; the multi-select listbox removes that furnace name.

The problem is the row where the old furnace name existed is still highlighted, after the value is removed. So when a user selects another furnace name(s) they get an SQL error because a null value is highlighted in the listbox. Clicking on the empty selection will not deselect it. I have to close and reopen the form for this to go away. I tried requery, recalc, and refreash.

The only fix I have right now is to do an isnull() check for each selected item. If the selected item is null then it’s not placed in my query string.

I made a conditional expression which checked for null selections. If true, I deselected all the values and requeryed the listbox to see if it would remove the empty selections...nope!




Dear Friends of Access,

Since this forums helped me out before, I have another request for you. I did all the analysis. My final query looks as follows

Jan_N-1, Feb_N-1, Mar_N-1, Jan_N-2, Feb_N-2, Mar_N-2
0.98......... 0.90..... 0.88....... 0.95....... 0.88...... 0.70


but I need Access to create a excel table like output. which looks as like

.......Jan. Feb. Mar
N-1 0.98 0.90 0.88
N-2 0.95 0.88 0.70

Anybody any suggestion? That would be great!
Greetings from Hongkong




Hi All,
How can multiple instances of one continuous subform (10 copies; no tab control) be made to behave like one multi-select listbox, populating a join table? I'd like to use something like Albert Kallal’s MultiSelect.mdb from http://www.kallal.ca/msaccess/msaccess.html, but am unsure how to use it as an unbound, list-box-style subform.

Desired functionality:
I’d like for the frmMAIN to simply show the client’s name. The 10 subforms would group all classes by their category, and show the different classes available for the client to select. The user would click various checkboxes next to the classes' names in the subforms, and then click a submit button. This would then create all the records in the join table.

Any help in developing this would be very much appreciated.
Thanks!

Other info:
Tables:
tblClients, tblClasses, tblClassCategories, tblClassSubcategories, jtblClientsAndClasses

Form Setup:
Currently, the parent form is named frmMAIN and the 10 instances of the same subform are: sfrmClasses1, sfrmClasses2, …, sfrmClasses10. To filter, I’ve put 10 hidden textboxes on the parent form that correspond with each subform: txtClasses1, txtClasses2, …, txtClasses10. The control source of each textbox equals its number: =1, =2, …, =10. For example, the first subform’s link master fields = ClassID,txtClasses1, and link child fields = ClassID,ClassCategoryID, where the ClassCategoryID corresponds with the txtClasses1 control source. However, this is currently only showing what classes have been checked--not the entire lists of classes.

The record source for the subform is: SELECT Classes.ClassID, Classes.Class, Classes.ClassCategoryID, ClassCategories.ClassCategory, Classes.ClassesSubcategoryID, ClassesSubcategories.ClassesSubcategory FROM ClassesSubcategories INNER JOIN (ClassCategories INNER JOIN Classes ON ClassCategories.ClassCategoryID = Classes.ClassCategoryID) ON ClassesSubcategories.ClassesSubcategoryID = Classes.ClassesSubcategoryID;




This is probably in the wrong forum, but I'm not sure what area the answer would cover, either queries, macros, VBA etc.

I have a form, on which is a listbox with multiple selection enabled getting it's data from a query. What I want to do is for the user to be able to select multiple products from the listbox and have some VBA code or query concatenate each id and insert them into a table, separated by commas so I can separate them again for reports etc.

Eg.

Listbox:
ID
1
2
3

User selects 1 and 2 and clicks submit. Selections are concatenated to 1,2 and inserted into the table.

Please bear in mind I'm not the best at this kind of advanced databasing, so a simple or at least easy to follow answer would be very appreciated.

Thank you all in advance.

Steve




Hi,

i'm developing an equine massage database for my partner. she's bought a databse program off the internet, but it was developed in 2002 and is VERY out of date (e.g., doesn't have fields to store owner's email address), plus is not as functional as we'd like, nor is it very comprehensive.

i have quite a lot done, but have also hit walls with a lot! i'm going to try and nut it all out in due course, and try one thing at a time (i find i can often learn how to do one thing while figuring out another thing).

there's one section, where i have a listbox of muscles. this listbox is populated from a table of muscles with several columns, although i have only one column showing in the listbox.

what i have so far is when i double-click on any muscle from the listbox, another column from that table populates a memo field. however, if i double-click another listbox item, it replaces the item that was there before instead of adding itself to the end of the memo field (after the item that was there before).

i prefer the double-clicking to multiple-select-then-press-command-button because i feel it would be easier to dblClick additional muscles in case not all items are selected the first time (missed one accidentally, for example). If it's possible to do BOTH, then i suppose that's the ultimate in end-user friendliness, HOWEVER, at the moment i'm jsut interested in getting the thing to work at all!

The idea is to allow my partner to select muscles from the listbox and then customise the text for each massage session, which will then be printed to a report to give to the horse owner.

Something else related to this (i expect i can figure this out if i figure out my problem as explained above) is i have another listbox of massage techniques ( setup similarly to my muscles listbox) which i also want to be able to append to the same memo field.

So, in the end, the memo field will be populated with: So-And-So muscle, This muscle, That muscle were tense so i used such-and-so technique to relax the muscles and improve the circulation.

i know it MUST be possible to do this, i just can't get the VB code right. i've been trying so many different things, but seeing as i'm a newbie, i'm not surprise i haven't cracked it yet.

There is no urgency for this to be completed in the following days, so please don't anyone break their backs trying to rush me an answer!!

As usual, the VB Help was completely useless!!

This is the code i have for one of these listboxes, i did lift the code from another post on this forum, so i'm not sure what all the variables etc mean (eg, why "trim"? - i suspect this has something to do with my problem? as you can see, i've tried to comment that line out, but it doesn't actually change the result, so i'm not sure what it's there to do! i also added a new line (vbCrLf) because i wented to be better able to see the code work, which it didn't! ho hum):


	Code:
	Private Sub ListMuscle_DblClick(Cancel As Integer)

Dim varItem As Variant
Dim strMassageMuscle As String

For Each varItem In Me.ListMuscle.ItemsSelected
    strMassageMuscle = strMassageMuscle & Me.ListMuscle.ItemData(varItem) & vbCrLf

Next varItem
    'strMassageMuscle = Trim(strMassageMuscle)
    Me.MassageMuscle.Value = strMassageMuscle
    
End Sub

Thank you so much in advance for looking at my problem. Sorry about the long winded nature of my description, but i figure if you know what i want, and what i have, and who it's for, perhaps the solution can be more elegant.

Regards,
Wiklendt.




How can I create a code or macro that will allow me to select several options for a listbox and then filter a report based on these selections.

I can do it for only one selection but it can't find a match with what I'm looking for when I select multiple.

I've visited the baldy help website but it's not working for me.




I am working with a database that records "Journal Entries" that supervisors create for their direct report employees. I have just created a new Entry form that allows the supervisor to select multiple employees through the use of a multi-select listbox. I have code that will enter the "Journal Entry" for all of the employees that the supervisor selects, but I am also getting a 'Blank' entry as well. What I mean by a blank entry is that there is always an entry made that has a blank field for the employee’s name. Any help in preventing this 'Blank' entry would be greatly appreciated. Here is the code that I am currently using:


	Code:
	 
Dim varValue As Variant
 
With Me.AgentName
    For Each varValue In .ItemsSelected
         CurrentDb.Execute "INSERT INTO [Journal Data] ([Agent], [Coach], [Action_Date], [Action_Reason], [Journal_Entry],
[Entry_Date], [Entered_By]) Values " & _
                    "('" & Me.AgentName.Column(0, varValue) & "','" & Me.CoachName & "',#" & Me.ActionDate & "#,'" &
Me.JournalReasonDropDown & "','" & Me.JournalMemo & "',#" & Me.CurrentDate & "#,'" & Me.Text34 & "')"
   Next
End With





Hi there, 6 months ago i never touched access, now im using it and getting in deeper its becoming a challenge and access is winning at the moment.
I have read through alot of these threads that are very interesting and incorparated many of my findings in my database. however i'm stuck

What i want to do is from a multiple select list box is be able to select people from a table " Members" and display as well as have them added via double click or a command button to a subform on my main form. These people are the reciepients of documents that are relevant to the each record. It is a historical log of people that this information has been given to.

I had some code happening but since wrestling with it have lost what functionality i had. Can this be done with macros or is code the most viable option,

If this means anything to anyone, your advise and suggestions would be invaluable. I am going to learn this VBA stuff, any recommended easy reading books would also be much appreciated.
thanks in advance

Private Sub testmultiselect_Click()
Dim oItem As Variant
Dim sTemp As String
Dim iCount As Integer

iCount = 0

If Me!NamesList.ItemsSelected.Count 0 Then
For Each oItem In Me!NamesList.ItemsSelected
If iCount = 0 Then
sTemp = sTemp & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
Else
sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
iCount = iCount + 1
End If
Next oItem
Else
MsgBox "Nothing was selected from the list", vbInformation
Exit Sub 'Nothing was selected
End If

Me!MySelections.Value = sTemp
End Sub

Private Sub clrList_Click()
Call clearListBox
Me!MySelections.Value = Null
End Sub

Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
Dim sTemp As String
Dim sValue As String
Dim sChar As String
Dim iCount As Integer
Dim iListItemsCount As Integer

sTemp = Nz(Me!MySelections.Value, " ")
iListItemsCount = 0
bFound = False
iCount = 0

Call clearListBox

For iCount = 1 To Len(sTemp) + 1
sChar = Mid(sTemp, iCount, 1)
If StrComp(sChar, ",") = 0 Or iCount = Len(sTemp) + 1 Then
bFound = False
Do
If StrComp(Trim(Me!NamesList.ItemData(iListItemsCount )), Trim(sValue)) = 0 Then
Me!NamesList.Selected(iListItemsCount) = True
bFound = True
End If
iListItemsCount = iListItemsCount + 1
Loop Until bFound = True Or iListItemsCount = Me!NamesList.ListCount
sValue = ""
Else
sValue = sValue & sChar
End If
Next iCount
End Sub

Private Sub Send_to_RX_table_Click()
On Error GoTo Err_Send_to_RX_table_Click


DoCmd.GoToRecord , , acNewRec

Exit_Send_to_RX_table_Click:
Exit Sub

Err_Send_to_RX_table_Click:
MsgBox Err.Description
Resume Exit_Send_to_RX_table_Click

End Sub




Hi, I have a problem that is causing me extreme frustration so I hope someone can help me.

I have created a maintenance database program that selects a number of maintenance tasks, groups them and then creates a word document with them.

It works great.... until I start adding tables to put the maintenance tasks in.

It creates the tables no problem.... the first time.

The next time it crashes with the following error code:"The remote Server or Machine does not exist or is unavailble".

I then close word down, go back to Access program and try again and it works????. It seems that it works the first time and then every second time after that. I am totally baffled.

It always crashes at the same spot and that is at the following code:

"objWord.ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=1, NumColumns:=3, DefaultTableBehavior:=wdWord9TableBehavior"

I have no idea why.

I hope there is somone who can help.

Thank you




As you can directly go live after developing the software using the MS Access 2013, Is it an alternative for the ASP.net as everything now is online directly.

Please clarify,

Thanks a lot




I need to know who has access to doors. A person is given a clearance that allows entry into certain doors. Each clearance will have either a list of single door(s) and/or a door group(s). A door group consists of multiple doors. I have 2 issues.

1) I have entered all the doors and doors groups inside a parameter drop down box. If I select a single door name from the parameter, it will only compare to single doors in each clearance and print out a list of all approved person with there clearance. It will not parse any data inside any door group inside the clearance.

2) There are times that I need to know who has approved access to multiple doors. Instead of running multiple reports, I would like to make a “door group” that includes all doors that I need to run the report on. I would like the report to look at each door inside the “door group” inside the parameter and compare to all the clearances and print out a report for each door and who has approved access.




I have to define some more indexes, and in some of the tables, I already had an index on the Primary Key, but also a separate index on the same field! This doesn't fail the system as such, but does not improve performance on insert/delete either.

Here is an explanation: http://www.applecore99.com/tbl/tbl004.asp

Just in case that page should go away, here is the full story:
Quote: By default, Access indexes certain fields for you automatically, based on the name that you call the field. By default, this happens if the field has 'ID', 'code' or 'num' in the field name, then Access creates an index. This can cause a duplication of indexes if the field is also the Primary Key for the table, having one called PrimaryKey, and the other called the name of the field. Although it is a very good idea to have indexes on fields in the table, as this speeds the retrieval of data, having too many indexes will slow the deletion and creation of records.

To prevent Access from creating these indexes automatically, from the database window, select Tools|Options, and then the Tables/Queries tab. Remove the text from the field labelled 'AutoIndex on Import/Create'. Note that this is an application wide setting, so once you have set this once in an Access database, you won't need to change it again.

To remove any existing duplicate indexes, you will need to open each table in design mode, and then select Indexes (View|Indexes). If there is a duplicate index on a field, then remove it by right-clicking on the duplicate row, and selecting 'Delete Row'. Conclusion? Check you indexes.

For A2007: Access Options -> Object Designers->Query Design->AutoIndex on Import/Create




--------------------------------------------------------------------------------

I have a dilema on my hands as I can't seem to figure out the best way to accomplish this. I created a database to handle all of our letters for my department in the company. The problem I am having is that we have 26 people who could possible access the database at the same time. The letters are created using a form. (not a problem with multi-users) When someone wants to print the letter the do a search either by "Letter Date", "Provider Name", "Provider Number", or "Group Name". I set it up so either one of these search queries append data into one table called "Letter Report Information" all 38 letters get there data from this 1 table. The problem I am having is that if John and Mary try to pull different/same letters at the same time or while one is viewing their letters the data is getting pushed onto the others Letter.

Example:

Mary goes to print her Welcome letter
John also goes to print his Denial letter at the same time

When John's letters show up.. the Denial letter shows up with his data, but also with Mary's Welcome letter data.

What can I do or what kind of query can i use to avoid this problem as it is becoming a very prominent issue? ::Please Help::

Thanks.




Hi,

I'm trying to run a pass thru query into a sybase server from Access. The query contains multiple selects which Access doesn't appear to like very much. The query works fine when run in Sybase.

Is there any way of circumventing this problem?

I could split the three selects into three seperate queries to view the individual results but the third set is dependant on the previous two sets and so therefore is difficult (impossible?) to seperate out as a third individual query.

Thanks for any suggestions?


	Code:
	Select 
s.id_prtf    Portfolio_ID,
s.id_imnt   Instrument_ID, 
s.id_imnt_swap   Instrument_Swap_ID,
s.id_ccy_std   Currency, 
s.am_posn  Position, 
s.am_pnl_eqty   PNL_Eqty

into #temptable1
from 
SET_VALUATION s, INSTRUMENT i
where
s.id_imnt = i.id_imnt
and s.id_posn_ref is NULL
and s.id_imnt_swap is NULL
and s.am_pnl_eqty > 0
and i.id_typ_imnt != 'SFX'

Select 
s.id_prtf  Portfolio_ID, 
s.id_imnt   Instrument_ID, 
s.id_imnt_swap   Instrument_Swap_ID,
s.id_ccy_std   Currency, 
s.am_posn  Position, 
s.pr_imnt_mtm_prev,
sy.pr_imnt_mtm,
s.am_mv_prev,
sy.am_mv

into #temptable1a
from SET_VALUATION s, SET_VALUATION_HIST sy, REFERENCE_DATA r
where
s.id_prtf = sy.id_prtf
and s.id_imnt = sy.id_imnt
and s.pr_imnt_mtm_prev != sy.pr_imnt_mtm
and s.am_mv_prev != sy.am_mv
and r.dt_bus_lst = sy.dt_bus

select 
t.*,
tt.pr_imnt_mtm_prev,
tt.pr_imnt_mtm,
tt.am_mv_prev,
tt.am_mv

into #newtemp
from #temptable1 t, #temptable1a tt
where
t.Portfolio_ID = tt.Portfolio_ID
 
delete from #temptable1
where #temptable1.Portfolio_ID in (select Portfolio_ID from #newtemp)
delete from #temptable1a
where #temptable1a.Portfolio_ID in (select Portfolio_ID from #newtemp)

select "Where id_posn_ref is NULL and id_imnt_swap is NULL is the only criteria"
select * from #temptable1
select "Where SET VAL's pr_imnt_mtm_prev does not equal  SET VAL HIST's pr_imnt_mtm, is the only criteria"
select * from #temptable1a
select "Where the output satisfies both Where id_posn_ref is NULL and id_imnt_swap is NULL  AND Where SET VAL's
pr_imnt_mtm_prev does not equal  SET VAL HIST's pr_imnt_mtm"
select * from #newtemp

drop table #temptable1
drop table #temptable1a
drop table #newtemp





Dear All,

I am just starting out in Access Development. I have a database that runs a logistics department. This was working fine until the users wanted a little more tweaking.

The report prints out the vehicles with all corresponding drops. This comes out all on sheet.

Is there a way that I can create a form to filter the report via a multi select listbox and print each drop selected on a seperate sheet of paper.

I am using Access 2003

Many thanks for any help or source code given