AddItem code sorta works?!?!

[Guest]

I have the following code to move items from the listbox to another listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can see the
many entries. What I don't get is why all the entries are not stored in the
field for that table. I only get the first entry. Also, after I add the
data on the form and go to the next record, the data doesn't change or clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Graham R Seach]

Ben,

Listboxes are simply data presentation devices, not data storage devices. If
you want the data persisted to a table, then you must push the data into the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

So how would you adjust my code to meet that recommendation?

Ben

Ben,

Listboxes are simply data presentation devices, not data storage devices. If
you want the data persisted to a table, then you must push the data into the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I have the following code to move items from the listbox to another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can see the
many entries. What I don't get is why all the entries are not stored in
the
field for that table. I only get the first entry. Also, after I add the
data on the form and go to the next record, the data doesn't change or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Graham R Seach]

Ben,

It's not that easy! In order to to what you're asking, we need to know the
following:
- The listbox's RowSource (if it's a table or query, then we need to
know the name and structure of the table that contains the data you want to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that you're
trying to accomplish? I have the feeling you're attempting to duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So how would you adjust my code to meet that recommendation?

Ben

Ben,

Listboxes are simply data presentation devices, not data storage devices.
If
you want the data persisted to a table, then you must push the data into
the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

I have the following code to move items from the listbox to another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can see
the
many entries. What I don't get is why all the entries are not stored
in
the
field for that table. I only get the first entry. Also, after I add
the
data on the form and go to the next record, the data doesn't change or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Guest]

Graham,
Here is what I want to do. I have a table called tblECR and a fieldname in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that you
just type the number of the ECR such as ECR123. Next, I have a table called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I have a
listbox assigned to ECRsSelected field. I want to be able to select multiple
values from the tblECR - ECRNumber field and store it in the ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the tblECR
and pulls in all the ECRNumbers. Then I have a command button that pulls the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple values in
that field AND when I go to the next record on the form, the ECRsSelected
listbox is cleared out? You see my code below. It works to a certain
point.

Thanks

Ben

Ben,

It's not that easy! In order to to what you're asking, we need to know the
following:
- The listbox's RowSource (if it's a table or query, then we need to
know the name and structure of the table that contains the data you want to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that you're
trying to accomplish? I have the feeling you're attempting to duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So how would you adjust my code to meet that recommendation?

Ben

Ben,

Listboxes are simply data presentation devices, not data storage devices.
If
you want the data persisted to a table, then you must push the data into
the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can see
the
many entries. What I don't get is why all the entries are not stored
in
the
field for that table. I only get the first entry. Also, after I add
the
data on the form and go to the next record, the data doesn't change or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Graham R Seach]

Ben,

Sorry for the delay. I've been really busy (as usual).

Let's see if I understand you correctly. The form, frmECR, is based on
tblECR, and this table contains a field called "ECRNumber" which is the
field of interest. I presume this table also contains a primary key,
probably an AutoNumber field called (something like) ECR_ID (an AutoNumber
datatype). On frmECN, you have two listboxes, the first of which gets its
data from tblECR and displays all the ECRNumbers from tblECR. The second
lisbox is unbound and is populated using code that gets the multi-selected
values from the first listbox. I'm guessing you want to store the
multi-selected values into tblECN.

What you haven't explained is what the data in tblECN relates to. We can of
course, just dump the data into tblECN, but this list of ECRs must "belong"
to or "relate" to something. What I mean by this is, if you were to display
a record in a form, you would probably want to see all the ECRs that
"relate" or "belong" to that record. And you might have many such "parent"
records, each potentially "owning" a number of multi-selected ECRs. The
usual way to accommodate this functionality is to create a table like this:
tblECN
ECR_ID (Long Integer)
(a foreign key to another 'related' table) (Long Integer)
ECRNumber (I assume a Text datatype)

....and then create a one-to-many relationship between the "parent" table,
and tblECN (to the foreign key in tblECN). Another one-to-many relationship
would exist between tblECR and tblECN (to ECR-ID). Such a table structure
will store a separate record for every ECR that was selected (for each
"parent" record).

Once you have such a structure, the code needed to store each datum into
tblECN is as follows:
The following pseudo-code should replace the line that adds the record
to the second listbox.

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

For Each .........
'Code that gets each selected value from the first listbox

strSQL = "INSERT INTO tblECN (foreign_key, ECRNumber) " & _
"VALUES (" & lngForeignKey & ", """ &
strSelectedValue & """)"

db.Execute strSQL, dbFailOnError
Next ....

Set db = Nothing

Does this make sense?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,
Here is what I want to do. I have a table called tblECR and a fieldname
in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that you
just type the number of the ECR such as ECR123. Next, I have a table
called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I have
a
listbox assigned to ECRsSelected field. I want to be able to select
multiple
values from the tblECR - ECRNumber field and store it in the ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the
tblECR
and pulls in all the ECRNumbers. Then I have a command button that pulls
the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the
ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple values
in
that field AND when I go to the next record on the form, the ECRsSelected
listbox is cleared out? You see my code below. It works to a certain
point.

Thanks

Ben

Ben,

It's not that easy! In order to to what you're asking, we need to know
the
following:
- The listbox's RowSource (if it's a table or query, then we need to
know the name and structure of the table that contains the data you want
to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that you're
trying to accomplish? I have the feeling you're attempting to duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

So how would you adjust my code to meet that recommendation?

Ben

:

Ben,

Listboxes are simply data presentation devices, not data storage
devices.
If
you want the data persisted to a table, then you must push the data
into
the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can
see
the
many entries. What I don't get is why all the entries are not
stored
in
the
field for that table. I only get the first entry. Also, after I
add
the
data on the form and go to the next record, the data doesn't change
or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Guest]

Graham
I almost have it all.

tblECR
ECRID (PK)
ECRNumber (text)
ECNID (FK)

tblECN
ECNID (PK)
ECRsSelected (Text) listbox

The way it works is you can have MANY ECRs assigned to one (1) ECN. Not
visa versa.
There is an unbound text box on frmECN that does a query for all the ECRs to
be listed. THEN, a command button "copies" the values I selected in that
unbound text box to a new listbox linked to the field ECRsSelected in tblECN.
I have that part working. HOWEVER, in the table view of tblECN, fieldname
"ECRsSelected" only shows 1 value not 2 or 3 or whatever.

So based on your code below what do I replace the .... with? How should I
tie all this together? I wish I could just email the file to you.

Ben

Ben,

Sorry for the delay. I've been really busy (as usual).

Let's see if I understand you correctly. The form, frmECR, is based on
tblECR, and this table contains a field called "ECRNumber" which is the
field of interest. I presume this table also contains a primary key,
probably an AutoNumber field called (something like) ECR_ID (an AutoNumber
datatype). On frmECN, you have two listboxes, the first of which gets its
data from tblECR and displays all the ECRNumbers from tblECR. The second
lisbox is unbound and is populated using code that gets the multi-selected
values from the first listbox. I'm guessing you want to store the
multi-selected values into tblECN.

What you haven't explained is what the data in tblECN relates to. We can of
course, just dump the data into tblECN, but this list of ECRs must "belong"
to or "relate" to something. What I mean by this is, if you were to display
a record in a form, you would probably want to see all the ECRs that
"relate" or "belong" to that record. And you might have many such "parent"
records, each potentially "owning" a number of multi-selected ECRs. The
usual way to accommodate this functionality is to create a table like this:
tblECN
ECR_ID (Long Integer)
(a foreign key to another 'related' table) (Long Integer)
ECRNumber (I assume a Text datatype)

...and then create a one-to-many relationship between the "parent" table,
and tblECN (to the foreign key in tblECN). Another one-to-many relationship
would exist between tblECR and tblECN (to ECR-ID). Such a table structure
will store a separate record for every ECR that was selected (for each
"parent" record).

Once you have such a structure, the code needed to store each datum into
tblECN is as follows:
The following pseudo-code should replace the line that adds the record
to the second listbox.

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

For Each .........
'Code that gets each selected value from the first listbox

strSQL = "INSERT INTO tblECN (foreign_key, ECRNumber) " & _
"VALUES (" & lngForeignKey & ", """ &
strSelectedValue & """)"

db.Execute strSQL, dbFailOnError
Next ....

Set db = Nothing

Does this make sense?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,
Here is what I want to do. I have a table called tblECR and a fieldname
in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that you
just type the number of the ECR such as ECR123. Next, I have a table
called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I have
a
listbox assigned to ECRsSelected field. I want to be able to select
multiple
values from the tblECR - ECRNumber field and store it in the ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the
tblECR
and pulls in all the ECRNumbers. Then I have a command button that pulls
the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the
ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple values
in
that field AND when I go to the next record on the form, the ECRsSelected
listbox is cleared out? You see my code below. It works to a certain
point.

Thanks

Ben

Ben,

It's not that easy! In order to to what you're asking, we need to know
the
following:
- The listbox's RowSource (if it's a table or query, then we need to
know the name and structure of the table that contains the data you want
to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that you're
trying to accomplish? I have the feeling you're attempting to duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


So how would you adjust my code to meet that recommendation?

Ben

:

Ben,

Listboxes are simply data presentation devices, not data storage
devices.
If
you want the data persisted to a table, then you must push the data
into
the
table, not to the listbox. Once the data are in the table, requery the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can
see
the
many entries. What I don't get is why all the entries are not
stored
in
the
field for that table. I only get the first entry. Also, after I
add
the
data on the form and go to the next record, the data doesn't change
or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Graham R Seach]

Ben,

Ahhhh, OK, so what you're saying is that tblECN has a bunch of other stuff
in it?? In that case, you need yet another table that lnks tblECR and tblECN
together. This new table should contain the primary key from both tblECR and
tblECN. The combination of those two fields will be its primary key, thereby
ensuring that only one of each combinaton can be stored.

tblECR ----- tblECRECM ----- tblECN

You don't put anything into the second listbox! Use the philosophy behind
the SQL construct to store the data in tblECRECN instead, and bind the
second listbox to tblECRECN. You no longer need a ECRsSelected field,
because this new table structure stores each selected ECR/ECN combination as
a single record.

The RowSource for the second listbox will be something like this:
SELECT * FROM tblECRCEM WHERE ECRID = Forms!frmECR!txtECR

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham
I almost have it all.

tblECR
ECRID (PK)
ECRNumber (text)
ECNID (FK)

tblECN
ECNID (PK)
ECRsSelected (Text) listbox

The way it works is you can have MANY ECRs assigned to one (1) ECN. Not
visa versa.
There is an unbound text box on frmECN that does a query for all the ECRs
to
be listed. THEN, a command button "copies" the values I selected in that
unbound text box to a new listbox linked to the field ECRsSelected in
tblECN.
I have that part working. HOWEVER, in the table view of tblECN, fieldname
"ECRsSelected" only shows 1 value not 2 or 3 or whatever.

So based on your code below what do I replace the .... with? How should I
tie all this together? I wish I could just email the file to you.

Ben

Ben,

Sorry for the delay. I've been really busy (as usual).

Let's see if I understand you correctly. The form, frmECR, is based on
tblECR, and this table contains a field called "ECRNumber" which is the
field of interest. I presume this table also contains a primary key,
probably an AutoNumber field called (something like) ECR_ID (an
AutoNumber
datatype). On frmECN, you have two listboxes, the first of which gets its
data from tblECR and displays all the ECRNumbers from tblECR. The second
lisbox is unbound and is populated using code that gets the
multi-selected
values from the first listbox. I'm guessing you want to store the
multi-selected values into tblECN.

What you haven't explained is what the data in tblECN relates to. We can
of
course, just dump the data into tblECN, but this list of ECRs must
"belong"
to or "relate" to something. What I mean by this is, if you were to
display
a record in a form, you would probably want to see all the ECRs that
"relate" or "belong" to that record. And you might have many such
"parent"
records, each potentially "owning" a number of multi-selected ECRs. The
usual way to accommodate this functionality is to create a table like
this:
tblECN
ECR_ID (Long Integer)
(a foreign key to another 'related' table) (Long Integer)
ECRNumber (I assume a Text datatype)

...and then create a one-to-many relationship between the "parent" table,
and tblECN (to the foreign key in tblECN). Another one-to-many
relationship
would exist between tblECR and tblECN (to ECR-ID). Such a table structure
will store a separate record for every ECR that was selected (for each
"parent" record).

Once you have such a structure, the code needed to store each datum into
tblECN is as follows:
The following pseudo-code should replace the line that adds the
record
to the second listbox.

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

For Each .........
'Code that gets each selected value from the first listbox

strSQL = "INSERT INTO tblECN (foreign_key, ECRNumber) " & _
"VALUES (" & lngForeignKey & ", """ &
strSelectedValue & """)"

db.Execute strSQL, dbFailOnError
Next ....

Set db = Nothing

Does this make sense?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham,
Here is what I want to do. I have a table called tblECR and a
fieldname
in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that
you
just type the number of the ECR such as ECR123. Next, I have a table
called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I
have
a
listbox assigned to ECRsSelected field. I want to be able to select
multiple
values from the tblECR - ECRNumber field and store it in the
ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the
tblECR
and pulls in all the ECRNumbers. Then I have a command button that
pulls
the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the
ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple
values
in
that field AND when I go to the next record on the form, the
ECRsSelected
listbox is cleared out? You see my code below. It works to a
certain
point.

Thanks

Ben

:

Ben,

It's not that easy! In order to to what you're asking, we need to know
the
following:
- The listbox's RowSource (if it's a table or query, then we need
to
know the name and structure of the table that contains the data you
want
to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that
you're
trying to accomplish? I have the feeling you're attempting to
duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


So how would you adjust my code to meet that recommendation?

Ben

:

Ben,

Listboxes are simply data presentation devices, not data storage
devices.
If
you want the data persisted to a table, then you must push the data
into
the
table, not to the listbox. Once the data are in the table, requery
the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to
another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can
see
the
many entries. What I don't get is why all the entries are not
stored
in
the
field for that table. I only get the first entry. Also, after I
add
the
data on the form and go to the next record, the data doesn't
change
or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Guest]

Graham,
Lol. Now i'm all spun around.

So I should create a table tblECRECN and have 1 field in there than combines
the PK from tblECR and tblECN? That's a crazy query isn't it?

So If I do all that, do I need the commandbutton at all, if the row source
of the second listbox is the SELECT command?

I wish I could send this to you so you can show me. Any chance of that
happening?

Ben

Ben,

Ahhhh, OK, so what you're saying is that tblECN has a bunch of other stuff
in it?? In that case, you need yet another table that lnks tblECR and tblECN
together. This new table should contain the primary key from both tblECR and
tblECN. The combination of those two fields will be its primary key, thereby
ensuring that only one of each combinaton can be stored.

tblECR ----- tblECRECM ----- tblECN

You don't put anything into the second listbox! Use the philosophy behind
the SQL construct to store the data in tblECRECN instead, and bind the
second listbox to tblECRECN. You no longer need a ECRsSelected field,
because this new table structure stores each selected ECR/ECN combination as
a single record.

The RowSource for the second listbox will be something like this:
SELECT * FROM tblECRCEM WHERE ECRID = Forms!frmECR!txtECR

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham
I almost have it all.

tblECR
ECRID (PK)
ECRNumber (text)
ECNID (FK)

tblECN
ECNID (PK)
ECRsSelected (Text) listbox

The way it works is you can have MANY ECRs assigned to one (1) ECN. Not
visa versa.
There is an unbound text box on frmECN that does a query for all the ECRs
to
be listed. THEN, a command button "copies" the values I selected in that
unbound text box to a new listbox linked to the field ECRsSelected in
tblECN.
I have that part working. HOWEVER, in the table view of tblECN, fieldname
"ECRsSelected" only shows 1 value not 2 or 3 or whatever.

So based on your code below what do I replace the .... with? How should I
tie all this together? I wish I could just email the file to you.

Ben

Ben,

Sorry for the delay. I've been really busy (as usual).

Let's see if I understand you correctly. The form, frmECR, is based on
tblECR, and this table contains a field called "ECRNumber" which is the
field of interest. I presume this table also contains a primary key,
probably an AutoNumber field called (something like) ECR_ID (an
AutoNumber
datatype). On frmECN, you have two listboxes, the first of which gets its
data from tblECR and displays all the ECRNumbers from tblECR. The second
lisbox is unbound and is populated using code that gets the
multi-selected
values from the first listbox. I'm guessing you want to store the
multi-selected values into tblECN.

What you haven't explained is what the data in tblECN relates to. We can
of
course, just dump the data into tblECN, but this list of ECRs must
"belong"
to or "relate" to something. What I mean by this is, if you were to
display
a record in a form, you would probably want to see all the ECRs that
"relate" or "belong" to that record. And you might have many such
"parent"
records, each potentially "owning" a number of multi-selected ECRs. The
usual way to accommodate this functionality is to create a table like
this:
tblECN
ECR_ID (Long Integer)
(a foreign key to another 'related' table) (Long Integer)
ECRNumber (I assume a Text datatype)

...and then create a one-to-many relationship between the "parent" table,
and tblECN (to the foreign key in tblECN). Another one-to-many
relationship
would exist between tblECR and tblECN (to ECR-ID). Such a table structure
will store a separate record for every ECR that was selected (for each
"parent" record).

Once you have such a structure, the code needed to store each datum into
tblECN is as follows:
The following pseudo-code should replace the line that adds the
record
to the second listbox.

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

For Each .........
'Code that gets each selected value from the first listbox

strSQL = "INSERT INTO tblECN (foreign_key, ECRNumber) " & _
"VALUES (" & lngForeignKey & ", """ &
strSelectedValue & """)"

db.Execute strSQL, dbFailOnError
Next ....

Set db = Nothing

Does this make sense?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,
Here is what I want to do. I have a table called tblECR and a
fieldname
in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that
you
just type the number of the ECR such as ECR123. Next, I have a table
called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I
have
a
listbox assigned to ECRsSelected field. I want to be able to select
multiple
values from the tblECR - ECRNumber field and store it in the
ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the
tblECR
and pulls in all the ECRNumbers. Then I have a command button that
pulls
the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the
ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple
values
in
that field AND when I go to the next record on the form, the
ECRsSelected
listbox is cleared out? You see my code below. It works to a
certain
point.

Thanks

Ben

:

Ben,

It's not that easy! In order to to what you're asking, we need to know
the
following:
- The listbox's RowSource (if it's a table or query, then we need
to
know the name and structure of the table that contains the data you
want
to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that
you're
trying to accomplish? I have the feeling you're attempting to
duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


So how would you adjust my code to meet that recommendation?

Ben

:

Ben,

Listboxes are simply data presentation devices, not data storage
devices.
If
you want the data persisted to a table, then you must push the data
into
the
table, not to the listbox. Once the data are in the table, requery
the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to
another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can
see
the
many entries. What I don't get is why all the entries are not
stored
in
the
field for that table. I only get the first entry. Also, after I
add
the
data on the form and go to the next record, the data doesn't
change
or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Guest]

Graham,
Now I'm a little confused.

I need to create a table tblECRECN that has 1 field in it that pulls a
combination of the primary keys of tblECR and tblECN? That right?

Now I see the Select statement being assigned to the rowsource of the second
listbox. Does that mean I don't need the command button? It'd be easier to
send you the file.

Ben

Ben,

Ahhhh, OK, so what you're saying is that tblECN has a bunch of other stuff
in it?? In that case, you need yet another table that lnks tblECR and tblECN
together. This new table should contain the primary key from both tblECR and
tblECN. The combination of those two fields will be its primary key, thereby
ensuring that only one of each combinaton can be stored.

tblECR ----- tblECRECM ----- tblECN

You don't put anything into the second listbox! Use the philosophy behind
the SQL construct to store the data in tblECRECN instead, and bind the
second listbox to tblECRECN. You no longer need a ECRsSelected field,
because this new table structure stores each selected ECR/ECN combination as
a single record.

The RowSource for the second listbox will be something like this:
SELECT * FROM tblECRCEM WHERE ECRID = Forms!frmECR!txtECR

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Graham
I almost have it all.

tblECR
ECRID (PK)
ECRNumber (text)
ECNID (FK)

tblECN
ECNID (PK)
ECRsSelected (Text) listbox

The way it works is you can have MANY ECRs assigned to one (1) ECN. Not
visa versa.
There is an unbound text box on frmECN that does a query for all the ECRs
to
be listed. THEN, a command button "copies" the values I selected in that
unbound text box to a new listbox linked to the field ECRsSelected in
tblECN.
I have that part working. HOWEVER, in the table view of tblECN, fieldname
"ECRsSelected" only shows 1 value not 2 or 3 or whatever.

So based on your code below what do I replace the .... with? How should I
tie all this together? I wish I could just email the file to you.

Ben

Ben,

Sorry for the delay. I've been really busy (as usual).

Let's see if I understand you correctly. The form, frmECR, is based on
tblECR, and this table contains a field called "ECRNumber" which is the
field of interest. I presume this table also contains a primary key,
probably an AutoNumber field called (something like) ECR_ID (an
AutoNumber
datatype). On frmECN, you have two listboxes, the first of which gets its
data from tblECR and displays all the ECRNumbers from tblECR. The second
lisbox is unbound and is populated using code that gets the
multi-selected
values from the first listbox. I'm guessing you want to store the
multi-selected values into tblECN.

What you haven't explained is what the data in tblECN relates to. We can
of
course, just dump the data into tblECN, but this list of ECRs must
"belong"
to or "relate" to something. What I mean by this is, if you were to
display
a record in a form, you would probably want to see all the ECRs that
"relate" or "belong" to that record. And you might have many such
"parent"
records, each potentially "owning" a number of multi-selected ECRs. The
usual way to accommodate this functionality is to create a table like
this:
tblECN
ECR_ID (Long Integer)
(a foreign key to another 'related' table) (Long Integer)
ECRNumber (I assume a Text datatype)

...and then create a one-to-many relationship between the "parent" table,
and tblECN (to the foreign key in tblECN). Another one-to-many
relationship
would exist between tblECR and tblECN (to ECR-ID). Such a table structure
will store a separate record for every ECR that was selected (for each
"parent" record).

Once you have such a structure, the code needed to store each datum into
tblECN is as follows:
The following pseudo-code should replace the line that adds the
record
to the second listbox.

Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb

For Each .........
'Code that gets each selected value from the first listbox

strSQL = "INSERT INTO tblECN (foreign_key, ECRNumber) " & _
"VALUES (" & lngForeignKey & ", """ &
strSelectedValue & """)"

db.Execute strSQL, dbFailOnError
Next ....

Set db = Nothing

Does this make sense?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,
Here is what I want to do. I have a table called tblECR and a
fieldname
in
tblECR called 'ECRNumber'. On a form, 'frmECR' i have a text box that
you
just type the number of the ECR such as ECR123. Next, I have a table
called
tblECN with a fieldname called 'ECRsSelected'. On the form, frmECN I
have
a
listbox assigned to ECRsSelected field. I want to be able to select
multiple
values from the tblECR - ECRNumber field and store it in the
ECRsSelected
field. Essentially I want to store 1 or many entries.

What I have currently is an unbound listbox that does a query on the
tblECR
and pulls in all the ECRNumbers. Then I have a command button that
pulls
the
multi-selected items from the unbound listbox and copies them to the
ECRsSelected listbox. So far, I can see multiple values in the
ECRsSelected
listbox. But, when i open the table datasheet view of tblECN, the
ECRsSelected field only has 1 data item. How can i store multiple
values
in
that field AND when I go to the next record on the form, the
ECRsSelected
listbox is cleared out? You see my code below. It works to a
certain
point.

Thanks

Ben

:

Ben,

It's not that easy! In order to to what you're asking, we need to know
the
following:
- The listbox's RowSource (if it's a table or query, then we need
to
know the name and structure of the table that contains the data you
want
to
copy)
- The name and structure of the destination table

But before we go too far down the track, exactly what is it that
you're
trying to accomplish? I have the feeling you're attempting to
duplicate
data.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


So how would you adjust my code to meet that recommendation?

Ben

:

Ben,

Listboxes are simply data presentation devices, not data storage
devices.
If
you want the data persisted to a table, then you must push the data
into
the
table, not to the listbox. Once the data are in the table, requery
the
listbox.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I have the following code to move items from the listbox to
another
listbox.
It works, but almost.

Once I have moved multiple items to the destination listbox I can
see
the
many entries. What I don't get is why all the entries are not
stored
in
the
field for that table. I only get the first entry. Also, after I
add
the
data on the form and go to the next record, the data doesn't
change
or
clear
out.

Please help. My code below:

Dim ctrlListBox As ListBox
Dim FRM As Form, CTL As Control
Dim varitem As Variant
Dim strSQL As String
Set FRM = Forms![frmECN]
Set CTL = FRM!ECRNumber
Dim DB As Database
Set DB = CurrentDb
Set ctrlListBox = FRM!ECRsSelected

For Each varitem In CTL.ItemsSelected
ctrlListBox.AddItem Item:=CTL.ItemData(varitem)
Next

Thanks for the help

Ben

 

[Marshall Barton]

Not quite.

tblECRECN need to have TWO fields. One is the foreign key
to tblECR and the other field is the foreign key to tblECN.
The primary key for tblECRECN is a two field primary key.

 

[AccessVandal via AccessMonster.com]

Hi Ben,

Please refer to my prevoius reply from another post. Graham had explained you
need another table for this.

My sample code should work by using the SQL syntax in the listbox rowsource
with a little change.

Hi,

That's what it's doing. It does not populate the table (tblECR), which is not
the correct thing to do. By populating the same records into the same table,
you are breaking the database normalization rules.

If you want to fill records from tblECR to another table somewhere, what is
that table name?

What the code does is to take the selected rows in the listbox "ECRNumber"
and populate the listbox "ECRsSelected". It doesn't fill the selected records
into the same table.

Bound listbox only fill the data into the Form's RowSource property. It
doesn't fill selected records into the same table.

 

[Graham R Seach]

Exactly!

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Not quite.

tblECRECN need to have TWO fields. One is the foreign key
to tblECR and the other field is the foreign key to tblECN.
The primary key for tblECRECN is a two field primary key.
--
Marsh
MVP [MS Access]

So I should create a table tblECRECN and have 1 field in there than
combines
the PK from tblECR and tblECN?