Multiple selection combo box

[Guest]

I have read the other discussions and am not sure that I understand whether
or not what I am trying to do is allowable or not.

Tables
tblAgreement
tblMaterial
MaterialID
Material
tblMaterialDetails
MaterialDetailID
Material1
Material2
Material3

I have a form for Material (frmMaterial) which contains the different
materials that could be addressed in an Agreement. The problem is that there
could be more than one material in each Agreement.

On the frmAgreement, I have a combo box right listing the Materials. I have
tried to somehow utilize a continuous subform that would list more than one
material. Does anyone have any ideas?

 

[Guest]

A combo box can have only one selection. If you need multiple selections,
use an list box which does allow multiple selections. Once the selections
have been made, you can use the ItemsSelected collection to determine which
items in the list have been selected.

 

[Guest]

Can you give me more information about the ItemSelected collection? I have
tried to follow Allen Browne's http://allenbrowne.com/ser-50.html "Use a
multi-select List box to Filter a Report," but it doesn't seem to be working.


He doesn't really say what to name the form you create in #3 and I am not
sure where the command button is to be placed, i.e. on the form he created in
#3 or in the main form where the list box is located.

 

[Guest]

The command button would go on your current main form. You do not need to
create an additional form.

 

[Guest]

I am sorry, I know that this is probably very simple and I am making it
complicated, but I have tried to make a command button to preview the listbox
for each record on the main form as I indicated previously, but it is not
working. Do I use the code from Allen Browne's website?

 

[Guest]

Can you tell me what is not working? What error are you getting?
Also, what version of Access are you using?
I notice in his code he uses the OpenArgs argument for opening a report. I
don't think that was available prior to 2002 or 2003.

 

[Guest]

Actually, I don't get an error. I don't get anything. I have Access 2003.

This is the code:

'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Agreements by Material"

'Loop through the ItemsSelected in the list box.
With Me.lstMaterial
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[MaterialID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Materials: " & Left$(strDescrip, lngLen)
End If
End If


End Sub

 

[Casey via AccessMonster.com]

Sharon:

I am not trying to jump in the middle of this discussion but I know of a good
website that might help you out if Klatuu's explanation does not help. Try
doing this tutorial
use http://www.fontstuff.com/access/acctut18.htm
along with http://www.fontstuff.com/access/acctut19.htm

that might help you out, I know it did me...

Actually, I don't get an error. I don't get anything. I have Access 2003.

This is the code:

'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Agreements by Material"

'Loop through the ItemsSelected in the list box.
With Me.lstMaterial
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[MaterialID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Materials: " & Left$(strDescrip, lngLen)
End If
End If


End Sub

Can you tell me what is not working? What error are you getting?
Also, what version of Access are you using?

[quoted text clipped - 45 lines]

 

[Guest]

Thank you so much. I always appreciate it when someone steers me in the
right direction where I can learn!
--
S

Sharon:

I am not trying to jump in the middle of this discussion but I know of a good
website that might help you out if Klatuu's explanation does not help. Try
doing this tutorial
use http://www.fontstuff.com/access/acctut18.htm
along with http://www.fontstuff.com/access/acctut19.htm

that might help you out, I know it did me...

Actually, I don't get an error. I don't get anything. I have Access 2003.

This is the code:

'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.
strDoc = "Agreements by Material"

'Loop through the ItemsSelected in the list box.
With Me.lstMaterial
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[MaterialID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Materials: " & Left$(strDescrip, lngLen)
End If
End If


End Sub

Can you tell me what is not working? What error are you getting?
Also, what version of Access are you using?

[quoted text clipped - 45 lines]

tried to somehow utilize a continuous subform that would list more than one
material. Does anyone have any ideas?

 

[jahoobob via AccessMonster.com]

I think you want to use a subform to enter all the materials that go with one
agreement
You would create a form based on tblAgreement. There should be am auto
number AgreementID for each agreement.
You would have a seperate table (e.g. tblAgreementMaterials)that would list
Materials by agreement that would have at least:
AgreementID
MaterialID
Material
You would create a subform base on this table that would be a continuous form
so you could enter multiple materials for eachagreement.
Link the subform to the Agreement form via AgreementID.
when the form is on an agreement, any matreial entered into the subform will
have the AgreementID attached to it.
so if AgreementID is 1 then inthe tblAgreementMaterials you might have
AgreementID=1
MaterialID=1
Material=bricks
and
AgreementID=1
MaterialID=2
Material=sand
etc.

You can make the Materials boxes in thesubform combo boxes that use your
tblMaterial info.

Hope this helps
Bob

 

[Guest]

Bob,

I have tried to do this, but I keep getting a message "The changes you
requested to the table were not successful because they would create
duplicate values in the index, primary key, or relationship. Change the data
in the file or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again."

What am I doing wrong?

 

[jahoobob via AccessMonster.com]

For the table of Materials for each Agreement, change the Index of your ID to
Yes (Duplicates OK.) This takes care of the last part of your error message.

Bob,

I have tried to do this, but I keep getting a message "The changes you
requested to the table were not successful because they would create
duplicate values in the index, primary key, or relationship. Change the data
in the file or fields that contain duplicate data, remove the index, or
redefine the index to permit duplicate entries and try again."

What am I doing wrong?

I think you want to use a subform to enter all the materials that go with one
agreement

[quoted text clipped - 47 lines]