Subform Combo Box Issue

[fredp613]

Hi, I was hoping someone could guide me on the right direction. I created a database that allows users to enter research data. The users enter the data via a form I created. The form is bound to a master table and each control in the form is bound to either a field inside the master table or bound to a table that relates to the master table.

My issue seems very simple to fix. I want to allow the user the select multiple records from a combo box. I therefore created a Subform bound to this combo box and the user is now able to select multiple records based on a single ID from the main form. The problem is though, that each time the user selects an item from the combo box (in the subform), this item is then duplicated in the same table. So for the next record, when he or she selects teh drop down combo box, the original list of pre defined records are there PLUS the record she/he chose last time.

Anyone know how to solve this? Ideally I would like the functionality to remain the same, possibly create a new table where when the user selects an item from a combo box it is updated to another table instead of the table with that stores the pre-defined data.


Thanks in advance!!!

 

[wiklendt]

i noticed from previous posts of yours that you are running an earlier version of access than 2007. multi-select combo boxes were only introduced in 2007, so i'm somewhat lost as to what you mean by multi-select combo - the closest to what i think you mean is a listbox.

from your description i'm not sure either how your lisbox and subform relate to your main form. a sanitised version of your DB might be helpful.

 

[fredp613]

Hi, thank you for your reply. Actually, after some research I managed to find the proper VB code to pass my multiple selected items in my listbox to a table for each ID.

Now what I would like to do is create a new form which will have the same listboxes that will allow the user to pass their selections into a query. So far I have the following code however this code generates an IN criteria. What I would like for it to do is generate an LIKE AND criteria. See below for details:

Current code (which generates IN criteria) - which generates the following SQL

SELECT *
FROM Master
WHERE Master.ValueTypes IN('Economic - Market Value','Economic -Non-market Value');

VB code used:

Option Compare Database
Option Explicit
Private Sub cmdOK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!mslbValueTypesQry.ItemsSelected
strCriteria = strCriteria & ",'" & Me!mslbValueTypesQry.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM Master " & _
"WHERE Master.ValueTypes IN(" & strCriteria & ")"
' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenQuery "qryMultiSelect"
' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

--What I would like to do is generate a LIKE AND criteria instead (i.e: Like "*Aesthetic*" And Like "*Existence*") which generates the following SQL:

SELECT Master.FileID, Master.ValueTypes, Master.Focus, Master.Method, Master.Region, Master.YearID, Master.NumOfRespondents, Master.YearID2, Master.Title, Master.Comment, Master.QuestionAsked, Master.YearID3, Master.Author, Master.Results
FROM Master
WHERE (((Master.ValueTypes) Like "*Aesthetic*" And (Master.ValueTypes) Like "*Existence*"));

I should mention that this query has all the other fields I want to query based on this criteria (i.e Im looking for all the fileID's that relate to Aesthetic & existence)

I'm guessing I just have to change the SQL string in the code, but I've been unsuccessful at doing so.

Any Ideas?

Thank you!!!!

 

[wiklendt]

i once helped someone with something like this. please find attached the resulting database. it not only applies the filters to a query but you can also send the SQL string to a report.

check out the VBA in there as to how it was accomplished. the original thread regarding this DB can be found here: http://www.access-programmers.co.uk/forums/showthread.php?t=167925&highlight=Buildfilter

another example is from allen browne, which may or may not be more suitable to your needs (current or future)... it uses date fields.