Store Added Value List Items

[Eka1618]

Hello,

I have created a text field (CUSTOMERS) which has its Display Control
Property set to a Combo Box and The Row Source Type set to Value List.

When users in my database want to add a new customer they select the button
to add more values to the list. (I have the Limit To List Propery set to no).

It seems that if the users try to add values to the list, those added values
are only staying on their PC or something. The database is split, so I guess
I do not understand the purpose of having the Add to List button for a combo
box if it will not add values across the board.

Can anyone explain to me why the values will not stay in the list for every
user? This list is not generating from another table or anything, its just a
random set of choices for the user to store for this particular field in the
table.

Thank You!

 

[vbasean]

your combo box is set to 'Value List' and that's not going to change from
station to station.

you need to store these values in a table and then set the source type to
'Table/Query' and set it to that table.

THEN on the 'Not In list' event you need to add that value to that
underlying table.

NOW with all that said, depending on the data and business rules and
everything else. What I do when I want a combo box to show viable values to
select for a certain field I create a totals query with that field and it
grouped. SO I get a list of all the items ever entered in that field without
duplicates (You'll also be able to see who is entering in bad values i.e.
names similar but not exact.) This list always stays current because it's
based off the data entered in that field.

 

[Eka1618]

Chris,

I have thought about doing this many times and I guess I am not sure if it
is praticle to create a seperate one field table to store values for a lookup
field in another table. If it is, then I will do this.

The users want to see a list of all the customers they work with each time
they use this field, not just what people have entered into the database for
this field.

If you still think I should create a seperate table, please let me know.

Thank You!!

 

[vbasean]

OK, this helps a lot.

Yes, you need a table IN THE FRONT END OF EACH USER.
Why? Because this is USER related data not BACK END related.

Also, if these records are related TO the user then you could store them in
the back end based off the user's info. This solution is not straight
forward.

The simple answer is to have a table (NOT LINKED) in each user's front end
that stores the values THEY entered. This would be the simple solution.

 

[vbasean]

As far as 'one field' lookup tables, this is common and especially fits this
case scenario.

 

[vbasean]

The users want to see a list of all the customers they work with each time

they use this field, not just what people have entered into the database for
this field.

This presents a different situation, having users see a list of customers
THEY worked with. This would require something along the lines of an
Employee Table, and some log that records who worked with what record. This
is also not straight forward.

A table that records a list of names that one user inputted is one thing. A
table that records records that were viewed or altered by a user is a much
bigger situation.

I assume you were just looking for those that a user inputted.

The simple solution for building a list based off of one users input into a
combo box would be the "local lookup table" that is updated through the form
combo box not in list function.

 

[Eka1618]

Hi Chris,

Thank you for your suggestions!

Each user in this database needs to have access to all the same customers.
Even if one user enters a new customer, EVERY user must have accessibility to
the entire list.

It would be quick to give them access to their own unlinked table, but I do
not think it would be ideal for them.

If it is common to have a one field table of data then I could do that.
However, this field is not the only field in the database that uses this
method. I have at leat 10 different fields located in different tables that
all need the ability to have users add items to the list without the need to
go to the back end so that it can be stored for every user.

Last, When you say "Local Lookup Table" are you refering to the unlinked
table you suggested I create? If I do this, will I need to reinstall the
split database onto every persons computer? I created a pacgaked solution for
them to install and their downloaded program file is linked to the master
location on the network.

Thank you so much for your help, please let me know what you think when you
get a chance!

 

[vbasean]

Have you concidered a log table, it can reside on the back end
Something like:
User: (If using Windows network, the User's ID)
Item: (The Primary Key that the user updated added)
TableName: (The table they altered, text format)
DateStamp: (Now, timestamp, date/time format)

on the after update of each form have a record inserted into the log

Dim rsLog as Recordset
Set rsLog = CurrentDb.Openrecordset("[Name of your log table]")
With rsLog
..AddNew
rsLog!User = environ("username")
rsLog!Item = Me.The Primary Key
rsLog!TableName = "Name of the Table"
rsLog!DateStamp = Now
..Update
End with


in the combo box have a query that is filtered user name and the table that
you are looking for per form.

on the form current you can add
me.combobox.rowsource = "SELECT [Item] WHERE User = '" & environ("Username")
& "' And TableName = 'the table the form is bound to'"

this is a combo with only the customers that the user added/updated.

 

[vbasean]

You could even union that query with the actual list from the form

This way you have
1) the items your user worked with first
and
2) the whole list afterwards

add
me.combobox.rowsource = "SELECT [Item], [Item name from source table],
"Recently Edited" as [Type], 1 as [My Order] FROM [your log table name] WHERE
User = '" & environ("Username") & "' And TableName = 'the table the form is
bound to'" & " INNER JOIN [Your source table] ON [Item] = [your source
primary key name] " & _
"UNION SELECT [item key field], [Field User is filtering by], "Not Edited By
You" as [Type], 2 as [My Order] FROM [your source table] ORDER BY [My Order],
[Item name from source]"

This may not be clear but let me explain
First you're unioning two sets of records
1) A list of the items the user has edited
this list is JOINED on the actual form source table because you want the
User to see the Item name and not the Item key number
2) an actual list of all items followed

it is ordered by
1) the users items
2) the rest
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Have you concidered a log table, it can reside on the back end
Something like:
User: (If using Windows network, the User's ID)
Item: (The Primary Key that the user updated added)
TableName: (The table they altered, text format)
DateStamp: (Now, timestamp, date/time format)

on the after update of each form have a record inserted into the log

Dim rsLog as Recordset
Set rsLog = CurrentDb.Openrecordset("[Name of your log table]")
With rsLog
.AddNew
rsLog!User = environ("username")
rsLog!Item = Me.The Primary Key
rsLog!TableName = "Name of the Table"
rsLog!DateStamp = Now
.Update
End with


in the combo box have a query that is filtered user name and the table that
you are looking for per form.

on the form current you can add
me.combobox.rowsource = "SELECT [Item] WHERE User = '" & environ("Username")
& "' And TableName = 'the table the form is bound to'"

this is a combo with only the customers that the user added/updated.


--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hi Chris,

Thank you for your suggestions!

Each user in this database needs to have access to all the same customers.
Even if one user enters a new customer, EVERY user must have accessibility to
the entire list.

It would be quick to give them access to their own unlinked table, but I do
not think it would be ideal for them.

If it is common to have a one field table of data then I could do that.
However, this field is not the only field in the database that uses this
method. I have at leat 10 different fields located in different tables that
all need the ability to have users add items to the list without the need to
go to the back end so that it can be stored for every user.

Last, When you say "Local Lookup Table" are you refering to the unlinked
table you suggested I create? If I do this, will I need to reinstall the
split database onto every persons computer? I created a pacgaked solution for
them to install and their downloaded program file is linked to the master
location on the network.

Thank you so much for your help, please let me know what you think when you
get a chance!

 

[vbasean]

did this work?
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

You could even union that query with the actual list from the form

This way you have
1) the items your user worked with first
and
2) the whole list afterwards

add
me.combobox.rowsource = "SELECT [Item], [Item name from source table],
"Recently Edited" as [Type], 1 as [My Order] FROM [your log table name] WHERE
User = '" & environ("Username") & "' And TableName = 'the table the form is
bound to'" & " INNER JOIN [Your source table] ON [Item] = [your source
primary key name] " & _
"UNION SELECT [item key field], [Field User is filtering by], "Not Edited By
You" as [Type], 2 as [My Order] FROM [your source table] ORDER BY [My Order],
[Item name from source]"

This may not be clear but let me explain
First you're unioning two sets of records
1) A list of the items the user has edited
this list is JOINED on the actual form source table because you want the
User to see the Item name and not the Item key number
2) an actual list of all items followed

it is ordered by
1) the users items
2) the rest
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Have you concidered a log table, it can reside on the back end
Something like:
User: (If using Windows network, the User's ID)
Item: (The Primary Key that the user updated added)
TableName: (The table they altered, text format)
DateStamp: (Now, timestamp, date/time format)

on the after update of each form have a record inserted into the log

Dim rsLog as Recordset
Set rsLog = CurrentDb.Openrecordset("[Name of your log table]")
With rsLog
.AddNew
rsLog!User = environ("username")
rsLog!Item = Me.The Primary Key
rsLog!TableName = "Name of the Table"
rsLog!DateStamp = Now
.Update
End with


in the combo box have a query that is filtered user name and the table that
you are looking for per form.

on the form current you can add
me.combobox.rowsource = "SELECT [Item] WHERE User = '" & environ("Username")
& "' And TableName = 'the table the form is bound to'"

this is a combo with only the customers that the user added/updated.


--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Hi Chris,

Thank you for your suggestions!

Each user in this database needs to have access to all the same customers.
Even if one user enters a new customer, EVERY user must have accessibility to
the entire list.

It would be quick to give them access to their own unlinked table, but I do
not think it would be ideal for them.

If it is common to have a one field table of data then I could do that.
However, this field is not the only field in the database that uses this
method. I have at leat 10 different fields located in different tables that
all need the ability to have users add items to the list without the need to
go to the back end so that it can be stored for every user.

Last, When you say "Local Lookup Table" are you refering to the unlinked
table you suggested I create? If I do this, will I need to reinstall the
split database onto every persons computer? I created a pacgaked solution for
them to install and their downloaded program file is linked to the master
location on the network.

Thank you so much for your help, please let me know what you think when you
get a chance!

--
~Erica~


:

OK, this helps a lot.

Yes, you need a table IN THE FRONT END OF EACH USER.
Why? Because this is USER related data not BACK END related.

Also, if these records are related TO the user then you could store them in
the back end based off the user's info. This solution is not straight
forward.

The simple answer is to have a table (NOT LINKED) in each user's front end
that stores the values THEY entered. This would be the simple solution.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud


:

Chris,

I have thought about doing this many times and I guess I am not sure if it
is praticle to create a seperate one field table to store values for a lookup
field in another table. If it is, then I will do this.

The users want to see a list of all the customers they work with each time
they use this field, not just what people have entered into the database for
this field.

If you still think I should create a seperate table, please let me know.

Thank You!!


--
~Erica~


:

your combo box is set to 'Value List' and that's not going to change from
station to station.

you need to store these values in a table and then set the source type to
'Table/Query' and set it to that table.

THEN on the 'Not In list' event you need to add that value to that
underlying table.

NOW with all that said, depending on the data and business rules and
everything else. What I do when I want a combo box to show viable values to
select for a certain field I create a totals query with that field and it
grouped. SO I get a list of all the items ever entered in that field without
duplicates (You'll also be able to see who is entering in bad values i.e.
names similar but not exact.) This list always stays current because it's
based off the data entered in that field.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud


:

Hello,

I have created a text field (CUSTOMERS) which has its Display Control
Property set to a Combo Box and The Row Source Type set to Value List.

When users in my database want to add a new customer they select the button
to add more values to the list. (I have the Limit To List Propery set to no).

It seems that if the users try to add values to the list, those added values
are only staying on their PC or something. The database is split, so I guess
I do not understand the purpose of having the Add to List button for a combo
box if it will not add values across the board.

Can anyone explain to me why the values will not stay in the list for every
user? This list is not generating from another table or anything, its just a
random set of choices for the user to store for this particular field in the
table.

Thank You!