Lookup tables to populate a master field with multiple entries

[Guest]

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Ron2005]

If you use a listbox you can specify "Multiselect" on the "Other" tab.

There is a simple or extended option for that, refer to help to
determine which you want.

 

[Guest]

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

 

[Guest]

Got that bit - allows me to select multiple entries from the list - is there
a way of then copying the selected entries into a field such as 'keyword
list', that can then be searched on in the website, to return matching
records with the keywords conrtained in any record's 'keyowrd list' field?

Iain

 

[Guest]

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

Great, I'll try that soon.
Now, just a little question. I am using these same "tricks" on a new DB that
I started on.
Is there a way to keep the selections in the list visible for every record
separately ?
I mean :
If listbox contains
AIDS
DIABETES
MS

Can it be like this. When I open record 1
AIDS & Diabetes are selected in the listbox
When I open Record 2
MS is selected.

And that this always stays selected unless you unlock the listbox again and
edit the listbox. (I know how to lock)
--
Novice

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

:

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

I'm not exactly sure what you're asking, Ben.

The solution I proposed did not use a listbox to either to make selections
for new Specialties or to display existing Specialty records, it rather uses
a continuous subform to display them, which includes a combo box to select a
new Specialty. In this scenario, all of the Specialty records shown on the
form are specific to each main form record.

If you'd rather use a listbox to display the current selections, you could
certainly use code in the OnCurrent event procedure of the main form to do
it, but it seems like a lot of effort for no benefit.

If I've misunderstood your intention, please provide some further detail and
I'll try again.

Sprinks

Great, I'll try that soon.
Now, just a little question. I am using these same "tricks" on a new DB that
I started on.
Is there a way to keep the selections in the list visible for every record
separately ?
I mean :
If listbox contains
AIDS
DIABETES
MS

Can it be like this. When I open record 1
AIDS & Diabetes are selected in the listbox
When I open Record 2
MS is selected.

And that this always stays selected unless you unlock the listbox again and
edit the listbox. (I know how to lock)
--
Novice

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice


:

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

:

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

--
Novice

I'm not exactly sure what you're asking, Ben.

The solution I proposed did not use a listbox to either to make selections
for new Specialties or to display existing Specialty records, it rather uses
a continuous subform to display them, which includes a combo box to select a
new Specialty. In this scenario, all of the Specialty records shown on the
form are specific to each main form record.

If you'd rather use a listbox to display the current selections, you could
certainly use code in the OnCurrent event procedure of the main form to do
it, but it seems like a lot of effort for no benefit.

If I've misunderstood your intention, please provide some further detail and
I'll try again.

Sprinks

Great, I'll try that soon.
Now, just a little question. I am using these same "tricks" on a new DB that
I started on.
Is there a way to keep the selections in the list visible for every record
separately ?
I mean :
If listbox contains
AIDS
DIABETES
MS

Can it be like this. When I open record 1
AIDS & Diabetes are selected in the listbox
When I open Record 2
MS is selected.

And that this always stays selected unless you unlock the listbox again and
edit the listbox. (I know how to lock)
--
Novice

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

:

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice


:

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

:

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

Sorry, I wasn't very clear it would seem.

Forget the full speciality thing.

I am now on a totally new Database.

Contact person can have several criteria as a person. I would put them in a
multiselect listbox. (Example: several diseases)
How to select them in the listbox and have the selections always visible in
the listbox for every Contact.
I mean... when you do a multiselect for a specific record... that every time
you come back on that record, you would still see the same selection in the
listbox.
And that for every record...







--
Novice

I'm not exactly sure what you're asking, Ben.

The solution I proposed did not use a listbox to either to make selections
for new Specialties or to display existing Specialty records, it rather uses
a continuous subform to display them, which includes a combo box to select a
new Specialty. In this scenario, all of the Specialty records shown on the
form are specific to each main form record.

If you'd rather use a listbox to display the current selections, you could
certainly use code in the OnCurrent event procedure of the main form to do
it, but it seems like a lot of effort for no benefit.

If I've misunderstood your intention, please provide some further detail and
I'll try again.

Sprinks

Great, I'll try that soon.
Now, just a little question. I am using these same "tricks" on a new DB that
I started on.
Is there a way to keep the selections in the list visible for every record
separately ?
I mean :
If listbox contains
AIDS
DIABETES
MS

Can it be like this. When I open record 1
AIDS & Diabetes are selected in the listbox
When I open Record 2
MS is selected.

And that this always stays selected unless you unlock the listbox again and
edit the listbox. (I know how to lock)
--
Novice

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

:

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice


:

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

:

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain

 

[Guest]

Ben,

You are trying to model a one-to-many relationship with a control that can
only have one value. If a Listbox' MultiSelect property is set to None, the
Listbox' value is the value selected, and it will be saved to the underlying
table field specified in the ControlSource property. If set to either of the
Multi-Select modes, the Listbox' value is NULL.

In order to *store* multiple values for a person, you will need another
table for the many side that includes the primary key from the Person table
and the criteria. There will be one record for each criteria. Sample data
for a person with the PersonID = 4 would look like the following:

CriteriaID (PK) PersonID Criteria
-------------------- ------------- ------------
1 4 AIDS
2 4 DIABETES

Normally, a one-to-many relationship is represented with a continuous
subform linked to the main table by the common field, here PersonID. Then
each main form record "owns" its specific set of detail records and displays
them as you scroll from one main table record to the next. By providing a
combo box on the subform, you could allow users to add new criteria records.
Neither the detail table's primary key nor the linking field need be
displayed in a control on the subform, but they must be in the recordset
underlying the subform.

Using a List Box to represent multiple records is possible, but would take
code to do it--in the OnCurrent event of the main form you'd need to create a
temporary recordset filtered by the main form's primary key, loop through it,
writing all selected values to an array, then loop through the list box
looking for matches, and selecting the ones that match.

If you are then going to permit additions and deletions using the listbox,
your AfterUpdate event procedure will have to do Insert and Delete queries to
reflect the currently selected values.

To my mind, a continuous subform seems much easier, but perhaps you have
your own reasons for doing it this way.

Hope that helps.
Sprinks

Sorry, I wasn't very clear it would seem.

Forget the full speciality thing.

I am now on a totally new Database.

Contact person can have several criteria as a person. I would put them in a
multiselect listbox. (Example: several diseases)
How to select them in the listbox and have the selections always visible in
the listbox for every Contact.
I mean... when you do a multiselect for a specific record... that every time
you come back on that record, you would still see the same selection in the
listbox.
And that for every record...







--
Novice

I'm not exactly sure what you're asking, Ben.

The solution I proposed did not use a listbox to either to make selections
for new Specialties or to display existing Specialty records, it rather uses
a continuous subform to display them, which includes a combo box to select a
new Specialty. In this scenario, all of the Specialty records shown on the
form are specific to each main form record.

If you'd rather use a listbox to display the current selections, you could
certainly use code in the OnCurrent event procedure of the main form to do
it, but it seems like a lot of effort for no benefit.

If I've misunderstood your intention, please provide some further detail and
I'll try again.

Sprinks

Great, I'll try that soon.
Now, just a little question. I am using these same "tricks" on a new DB that
I started on.
Is there a way to keep the selections in the list visible for every record
separately ?
I mean :
If listbox contains
AIDS
DIABETES
MS

Can it be like this. When I open record 1
AIDS & Diabetes are selected in the listbox
When I open Record 2
MS is selected.

And that this always stays selected unless you unlock the listbox again and
edit the listbox. (I know how to lock)
--
Novice


:

Ben,

To allow multiple specialties for a given main table record, you need to
create a new table in a one-to-many relationship with your main table, then
delete the Specialties field from the main table. For purpose of example,
I'll call the new table MainSpecialties:

MainSpecialties
--------------------
MainSpecialtyID AutoNumber (Primary Key)
MainID Integer (Foreign key to your main table)
SpecialtyID Integer (Foreign key to Specialties)

First:

- Make a backup of your current database
- Define the new table. I have assumed that your two tables have numeric
primary keys. If this isn't true, define them such that the corresponding
data types match.
- Perform an Insert query that inserts all existing MainID/SpecialtyID
combinations into the new table. Access will assign a unique primary key to
each record.
- Create a continuous subform based on the new table, and insert it onto
your main form.
- Check the subform control's LinkMasterFields and LinkChildFields
properties. If you named the fields with the same names as in the main
table, Access will assign these properties, otherwise, enter the name of the
matching field (MainID) from the main and new tables, respectively.
- Make necessary cosmetic adjustments and save the form.
- Review the main table records and inspect whether the specialties
correctly display in the subform.
- When you're sure that it is operating correctly, you may delete the
Specialty field from the main table. If you've established Relationships on
that field, you will have to delete the relationships first.
- Define relationships between the new table and the main table, and the new
table and the Specialties table.

Hope that helps.
Sprinks

:

Great !
I need similar thing and I tested it on my DB, this really works.
Now, I have one worry... I would like to update this possibility on my
existing operational Database.
I have Field speciality that refers to a table speciality and have it as a
combo box as I didn't count on double specialities... now how can I update ny
DB keeping the 15000 records' specialities unchanged ?


--
Novice


:

A combo box can only select one entry at a time, so the code is not
applicable to them.

To clear the entries, fire the following code in the form's OnCurrent event:

Private Sub Form_Current()
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]

' Loop through and deselect selected items
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
ctlSource.Selected(intCurrentRow) = False
End If
Next intCurrentRow

Set ctlSource = Nothing

End Sub

Sprinks

:

Thank you very much for that - its exactly what I was looking for.

Although its not quite perfect. If you select any entries from the list box,
the populate the designated field as they should. However, when you move off
the current record, the entires remain selected. You kind of have to manually
deselect them each time.

Also - will a similar thing work with combo boxes? I tried the same code,
but it didn't seem to like it.

Cheers,
Iain

:

Iain,

There are several ways to do this. Litwin/Getz/Gunderloy describe 3 in the
Access 2002 Desktop Developer's Handbook (an indispensible tool, IMO),
however, one way is to loop through the selected items in the list box, and
write a concatenated string to another textbox control:

Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer

Set ctlSource = Me![YourListBox]
Set ctlDest = Me![YourTextBox]

' Loop through selected items; add to string
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(0, _
intCurrentRow) & ";"
End If
Next intCurrentRow

' Set destination control to string
ctlDest = strItems

' Clear memory
Set ctlSource = Nothing
Set ctlDest = Nothing

You can put the code in the AfterUpdate event of the List Box. Note that it
will overwrite a previous entry; you could modify it to allow users to simply
add additional entries.

Hope that helps.
Sprinks

:

Don't know if anyone will know this, but here goes..

You know how in Outlook you can add multiple names to the to field of an
email, by selecting multiple names from the address book and clicking to ->?

Is there a way to replicate this in an access form?

Basically, i'm doing a website that uses keywords for photographs, so want
the most effective way of entering them into the database, ideally to remove
the risk of typos, and work from a set list of keywords.

So the ideal would be to replicate the thing from Outlook - so you can
select however many keywords from a single list, to populate a master
keywords field.

The closest I can see is to add a combo box into a form, and tell it to look
up values from a keywords table. It does have an option at the end to store
that value in another field, but unfortunately overwrites each time you
select a different word.

If it did this, but added each word that you select it would be perfect.

Hope this makes sense.

Cheers,
Iain