G
Guest
How do I set up the code so that multiple selections in the list box are
displayed in the sub form?
displayed in the sub form?
J
Jeff Boyce
Nanette
You've left out a lot of details .. general descriptions lead to general
suggestions...
As each item in the listbox is selected, write a record to the table that is
displayed in the subform.
Regards
Jeff Boyce
Microsoft Office/Access MVP
You've left out a lot of details .. general descriptions lead to general
suggestions...
As each item in the listbox is selected, write a record to the table that is
displayed in the subform.
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
Sorry Jeff,
I thought this process would be generic. Guess I need to think like a
programmer.
I have a main form (frmBOESelection, Record Source is tblRF) that has a
Multi Select List Box (lboChange) (set to Extended). This multi select list
box is coded so that when I make a choice in a Combo Box (cboRFNo), the
appropriate choices applicable to the combo boxes RF # show up in the list
box. This works amazingly
).
Now I would like to set the Multi Select List Box so that can the user
select several items, and based on those selections, fill in a sub form
(sfrmBOESelection, Record Source is tblLineItem).
The Multi Select List Box consists of:
Change#, LineItem#, and RF# (set to text) and the main form is bound to the
table tblRF. I have set the list box so that the RF# is not seen by setting
the column width to 0. These three items are what constitued a unique item
and are therefore the primary keys in the tblLineItem table.
Tables Involved:
Table: tblRF
Fields: RFNo (PK), RFType, etc.
Table: tblLineItem
Fields: RFNo (PK), Change (PK), LineItem (PK), NGPN, RPN, etc.
Table: tblLIDetails
Fields: RFNo (FK), Change (FK), LineItem (FK), VPD, etc.
Is there anything else that you need to be able to answer my question?
I thought this process would be generic. Guess I need to think like a
programmer.
I have a main form (frmBOESelection, Record Source is tblRF) that has a
Multi Select List Box (lboChange) (set to Extended). This multi select list
box is coded so that when I make a choice in a Combo Box (cboRFNo), the
appropriate choices applicable to the combo boxes RF # show up in the list
box. This works amazingly
).Now I would like to set the Multi Select List Box so that can the user
select several items, and based on those selections, fill in a sub form
(sfrmBOESelection, Record Source is tblLineItem).
The Multi Select List Box consists of:
Change#, LineItem#, and RF# (set to text) and the main form is bound to the
table tblRF. I have set the list box so that the RF# is not seen by setting
the column width to 0. These three items are what constitued a unique item
and are therefore the primary keys in the tblLineItem table.
Tables Involved:
Table: tblRF
Fields: RFNo (PK), RFType, etc.
Table: tblLineItem
Fields: RFNo (PK), Change (PK), LineItem (PK), NGPN, RPN, etc.
Table: tblLIDetails
Fields: RFNo (FK), Change (FK), LineItem (FK), VPD, etc.
Is there anything else that you need to be able to answer my question?
J
Jeff Boyce
Nanette
.... and the subform? What is it based on?
Regards
Jeff Boyce
Microsoft Office/Access MVP
.... and the subform? What is it based on?
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
The subform is based on the tblLineItem
Jeff Boyce said:
J
Jeff Boyce
Nanette
So the listbox AND the subform both are based on the LineItem table?
I'm still confused about the relationships among the tables.
You've describe "how" you want to do something (use multiselect list box to
fill in a subform). Why? Why do you need the same information in both the
listbox and the subform? What business need are you trying to solve?
I ask, not to be difficult, but to get a clearer picture of what you hope to
be able to do. There may be alternate ways to get the same thing done.
Regards
Jeff Boyce
Microsoft Office/Access MVP
So the listbox AND the subform both are based on the LineItem table?
I'm still confused about the relationships among the tables.
You've describe "how" you want to do something (use multiselect list box to
fill in a subform). Why? Why do you need the same information in both the
listbox and the subform? What business need are you trying to solve?
I ask, not to be difficult, but to get a clearer picture of what you hope to
be able to do. There may be alternate ways to get the same thing done.
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
Hi Jeff,
Clear communication is difficult sometimes and being new at this, perhaps I
set it up differently than the normal way. Let me try to explain better.
Yes, the listbox and the subform are based on the LineItem table.
I did this so that the user could choose one, all, or several rows of data
based on the three primary keys. In the subform, other fields will be
displayed that are in the LineItem table.
Example of potential data chosen and displayed on subform:
RF# 1a, Change# 00, LineItem# 00100, other fields
RF# 1a, Change# 01, LineItem# 00200, other fields
RF# 1a, Change# 02, LineItem# 00100, other fields
Sometimes a RF# can have up to 40 rows of data with various change#s and
lineitem#s. And, sometimes the boss only wants to create a report based on 1
or 5 or 10 rows.
The user is restricted to choosing only one RF#.
The Change# and LineItem# will vary.
I'm using the form/subform as a tweakable view for the user. After this form
is filled in and reviewed for correctness, the user will click on a button to
create a report from the data on this form. There will be another button to
create a report that will be exported to excel as well.
If there is another easier/better way to do this, I'm definitely open to new
suggestions.
Hope this helps.
Clear communication is difficult sometimes and being new at this, perhaps I
set it up differently than the normal way. Let me try to explain better.
Yes, the listbox and the subform are based on the LineItem table.
I did this so that the user could choose one, all, or several rows of data
based on the three primary keys. In the subform, other fields will be
displayed that are in the LineItem table.
Example of potential data chosen and displayed on subform:
RF# 1a, Change# 00, LineItem# 00100, other fields
RF# 1a, Change# 01, LineItem# 00200, other fields
RF# 1a, Change# 02, LineItem# 00100, other fields
Sometimes a RF# can have up to 40 rows of data with various change#s and
lineitem#s. And, sometimes the boss only wants to create a report based on 1
or 5 or 10 rows.
The user is restricted to choosing only one RF#.
The Change# and LineItem# will vary.
I'm using the form/subform as a tweakable view for the user. After this form
is filled in and reviewed for correctness, the user will click on a button to
create a report from the data on this form. There will be another button to
create a report that will be exported to excel as well.
If there is another easier/better way to do this, I'm definitely open to new
suggestions.
Hope this helps.
G
Guest
Hi Jeff,
Just wondering if you were out there with any ideas or did I stump you.
Is anyone out there that can help me.
Just wondering if you were out there with any ideas or did I stump you.
Is anyone out there that can help me.
J
Jeff Boyce
Nanette
I was away from the newsgroups yesterday. I'll take another look...
Regards
Jeff Boyce
Microsoft Office/Access MVP
I was away from the newsgroups yesterday. I'll take another look...
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
Thanks Jeff!
Jeff Boyce said:
J
Jeff Boyce
Nanette
please see comments in-line below...
It seems like you are trying to give the user a way to update data about
multiple records all displayed at the same time. This seems like it could
be confusing ... "am I updating all selected with these changes, or am I
only updating one of these, and if one, which one?"
I'll suggest that you may want to consider giving the user a way to select a
single record, update it in the subform, then select another record, update
it, etc.
Maybe you need a list, a subform, and a sub-subform (embedded within the
subform)?
please see comments in-line below...
Nanette said:
It seems like you are trying to give the user a way to update data about
multiple records all displayed at the same time. This seems like it could
be confusing ... "am I updating all selected with these changes, or am I
only updating one of these, and if one, which one?"
I'll suggest that you may want to consider giving the user a way to select a
single record, update it in the subform, then select another record, update
it, etc.
Maybe you need a list, a subform, and a sub-subform (embedded within the
subform)?
G
Guest
Hi Jeff,
Just wanted to say thanks for your help. I hadn't forgotten about the
thread. I've just been laid up with the flu. I thought I could get some work
in today, but I'm still feeling sick, so I'll work with you helpful
suggestions tomorrow.
Just wanted to say thanks for your help. I hadn't forgotten about the
thread. I've just been laid up with the flu. I thought I could get some work
in today, but I'm still feeling sick, so I'll work with you helpful
suggestions tomorrow.
G
Guest
Hi Jeff,
Per your last post of:
It seems like you are trying to give the user a way to update data about
display (in a subform/datasheet view) the users choice of records/rows of
data. The user should not have to change the data, but I wanted it tweakable
so the user wouldn't have to go back into another form for changes. Hence,
the subform in datasheet view.
From this main form that contains the multi select list box, etc. and the
subform that lists one or several records, I'll be creating a report. That is
why I want to have the user select one or many items from the list box.
The main form will have the following fields:
Prime ID
Program name
Etc.
The subform will have the following fields based on the selection from the
list box:
NSPN
RPN
Desc
Line Item
Qty
Type
Unit Price
If this does not make sence, let me know. Maybe I should create a report
with a button to a form so the user can go back to another form and make
changes, then run the report again. Either way, I still need to have a list
box so the user can select one or many items. I figured my idea would be the
fastest way and ease of use for the user.
I keep getting a Compile Error message (Method or data member not found)
with the following code at the line indicated by >>>>>>>>>>>:
Private Sub cmdConfirmChoices_Click()
Dim rs As DAO.Recordset
Dim intI As Integer
If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset( _
"SELECT id_LI FROM LineItem " & _
'Select the items currently on the list
With Me.lstChange
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!id_LI) Then
.Selected(intI) = True
Exit For
End If
Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With
End If
End Sub
Nanette
Per your last post of:
It seems like you are trying to give the user a way to update data about
What I'm trying to do is use a multi select list box (from the main form) to
display (in a subform/datasheet view) the users choice of records/rows of
data. The user should not have to change the data, but I wanted it tweakable
so the user wouldn't have to go back into another form for changes. Hence,
the subform in datasheet view.
From this main form that contains the multi select list box, etc. and the
subform that lists one or several records, I'll be creating a report. That is
why I want to have the user select one or many items from the list box.
The main form will have the following fields:
Prime ID
Program name
Etc.
The subform will have the following fields based on the selection from the
list box:
NSPN
RPN
Desc
Line Item
Qty
Type
Unit Price
If this does not make sence, let me know. Maybe I should create a report
with a button to a form so the user can go back to another form and make
changes, then run the report again. Either way, I still need to have a list
box so the user can select one or many items. I figured my idea would be the
fastest way and ease of use for the user.
I keep getting a Compile Error message (Method or data member not found)
with the following code at the line indicated by >>>>>>>>>>>:
Private Sub cmdConfirmChoices_Click()
Dim rs As DAO.Recordset
Dim intI As Integer
If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset( _
"SELECT id_LI FROM LineItem " & _
'Select the items currently on the list
With Me.lstChange
Do Until rs.EOF
For intI = 0 To (.ListCount - 1)
If .ItemData(intI) = CStr(rs!id_LI) Then
.Selected(intI) = True
Exit For
End If
Next intI
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End With
End If
End Sub
Nanette
J
Jeff Boyce
Nanette
Sorry for the additional delay...
Your code seems to be saying to use the value of the listbox... but your
description seems to be saying that there could be multiple items selected.
This would confuse Access -- you would have to interate through all the
selected values and dynamically build your WHERE clause to reflect
each/every choice.
It sounds like you want to create a report. Are you trying to give the user
flexibility to include/exclude fields in the report? That would seem to
duplicate the current report wizard.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
Sorry for the additional delay...
Your code seems to be saying to use the value of the listbox... but your
description seems to be saying that there could be multiple items selected.
This would confuse Access -- you would have to interate through all the
selected values and dynamically build your WHERE clause to reflect
each/every choice.
It sounds like you want to create a report. Are you trying to give the user
flexibility to include/exclude fields in the report? That would seem to
duplicate the current report wizard.
Good luck!
Regards
Jeff Boyce
Microsoft Office/Access MVP
G
Guest
Hi Jeff,
Thanks for coming back. I'm impressed!
I got someone to help me fix it. He created a loop statement, made sure the
parent child relationship was correct, changed the sub forms record source to
a query, and wrote some other code. I can't explain much further than this.
I've got to study to code so I understand it.
Thanks for your efforts.
Thanks for coming back. I'm impressed!
I got someone to help me fix it. He created a loop statement, made sure the
parent child relationship was correct, changed the sub forms record source to
a query, and wrote some other code. I can't explain much further than this.
I've got to study to code so I understand it.
Thanks for your efforts.