Populate a list box from 'unlinked' table in other database???

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Hello

Is it possible to populate the values in a list box from a table that is in
another database, but where the table is not linked?

I have an unbound multi-select list box in a form, the user will select
values from this to update a field that is held against a record. However,
the user should also be able to update the list box with new values 'on the
fly'.

I can do this with a local table - but am struggling to do this using a
back-end database that stores the data. I would be happy to post examples
if anybody responds to this post.

Many Thanks in Advance.
Kevin
 
I'm not sure I have the full picture of what you're doing here:
Is "a field that is held against a record" the same as a field in a
record?
If not, what's the difference?
If you're using the listbox to populate a field, how is the
multi-select feature implemented?
(What happens when you select more than one entry?)

If it were not for the multi-select feature, I'd suggest using a combobox,
but I'd need a better understanding of how this listbox works before I'd
want to venture any suggestions.

HTH
- Turtle
 
Hi Macdermott

I hope this will shed some light if I have explained it properly!

Basically the list box will be used so that the user can select one, or more
values, that will update a text box using a semi-colon as a separator [as
per code below]. The source of the list box is a table lk_OS which resides
in a back end database. At the same time I have an inputbox that will allow
users to add items to the source table lk_OS.

I'm quite inexperienced when doing multi-user FE/BE solutions, but I figured
that it would be better for performance to grab recordsets etc. at runtime
rather than use linked tables. Maybe I am wrong?

Essentially my question is: Can the row source property of the list box be
set so that it uses the table lk_OS from the back end database, rather than
using the linked table? If there are no performance hits using linked
tables I am happy to do so.

Again, thanks in advance.

Kevin

*********Code Starts************

Private Sub cmdAdd_Click()
Dim i As Integer
Dim i2 As Integer
Dim vItm As Variant
Dim stWhat As String
Dim stSep As String

Me![Text5] = Null
i2 = Me![List2].ListCount
Debug.Print i2
stSep = "; "
For Each vItm In Me!List2.ItemsSelected
stWhat = stWhat & Me!List2.ItemData(vItm)
stWhat = stWhat & stSep
Next vItm
Me![Text5] = stWhat
End Sub
**********Code Ends*************
 
I doubt seriously that you'll see any significant performance hits by using
a linked table -
after all, that's what Access is built to do.
BTW - what version of Access are you using? What OS?

Be aware, though, that if a user adds something to the source table, it will
not show up on other users' screens until the listbox is requeried -
regardless of whether the source table is linked or not.

HTH
- Turtle

Kevin said:
Hi Macdermott

I hope this will shed some light if I have explained it properly!

Basically the list box will be used so that the user can select one, or more
values, that will update a text box using a semi-colon as a separator [as
per code below]. The source of the list box is a table lk_OS which resides
in a back end database. At the same time I have an inputbox that will allow
users to add items to the source table lk_OS.

I'm quite inexperienced when doing multi-user FE/BE solutions, but I figured
that it would be better for performance to grab recordsets etc. at runtime
rather than use linked tables. Maybe I am wrong?

Essentially my question is: Can the row source property of the list box be
set so that it uses the table lk_OS from the back end database, rather than
using the linked table? If there are no performance hits using linked
tables I am happy to do so.

Again, thanks in advance.

Kevin

*********Code Starts************

Private Sub cmdAdd_Click()
Dim i As Integer
Dim i2 As Integer
Dim vItm As Variant
Dim stWhat As String
Dim stSep As String

Me![Text5] = Null
i2 = Me![List2].ListCount
Debug.Print i2
stSep = "; "
For Each vItm In Me!List2.ItemsSelected
stWhat = stWhat & Me!List2.ItemData(vItm)
stWhat = stWhat & stSep
Next vItm
Me![Text5] = stWhat
End Sub
**********Code Ends*************


MacDermott said:
I'm not sure I have the full picture of what you're doing here:
Is "a field that is held against a record" the same as a field in a
record?
If not, what's the difference?
If you're using the listbox to populate a field, how is the
multi-select feature implemented?
(What happens when you select more than one entry?)

If it were not for the multi-select feature, I'd suggest using a combobox,
but I'd need a better understanding of how this listbox works before I'd
want to venture any suggestions.

HTH
- Turtle

is
in 'on
the
Click to expand...
Click to expand...
 
Hi MacDermott

Thanks for the response. I'm fine with the linked table.

In answer to your questions.........The Access version will be 2000. I'm
not sure what the End Users OS is, I'm doing this for the friend of a
friend.

Hope you don't mind if I ask you this.......With regard to linked tables,
the main tables may get quite large this is a call logging solution. Will
this make any difference to performance?

Thanks
Kevin

MacDermott said:
I doubt seriously that you'll see any significant performance hits by using
a linked table -
after all, that's what Access is built to do.
BTW - what version of Access are you using? What OS?

Be aware, though, that if a user adds something to the source table, it will
not show up on other users' screens until the listbox is requeried -
regardless of whether the source table is linked or not.

HTH
- Turtle

Kevin said:
Hi Macdermott

I hope this will shed some light if I have explained it properly!

Basically the list box will be used so that the user can select one, or more
values, that will update a text box using a semi-colon as a separator [as
per code below]. The source of the list box is a table lk_OS which resides
in a back end database. At the same time I have an inputbox that will allow
users to add items to the source table lk_OS.

I'm quite inexperienced when doing multi-user FE/BE solutions, but I figured
that it would be better for performance to grab recordsets etc. at runtime
rather than use linked tables. Maybe I am wrong?

Essentially my question is: Can the row source property of the list box be
set so that it uses the table lk_OS from the back end database, rather than
using the linked table? If there are no performance hits using linked
tables I am happy to do so.

Again, thanks in advance.

Kevin

*********Code Starts************

Private Sub cmdAdd_Click()
Dim i As Integer
Dim i2 As Integer
Dim vItm As Variant
Dim stWhat As String
Dim stSep As String

Me![Text5] = Null
i2 = Me![List2].ListCount
Debug.Print i2
stSep = "; "
For Each vItm In Me!List2.ItemsSelected
stWhat = stWhat & Me!List2.ItemData(vItm)
stWhat = stWhat & stSep
Next vItm
Me![Text5] = stWhat
End Sub
**********Code Ends*************


MacDermott said:
I'm not sure I have the full picture of what you're doing here:
Is "a field that is held against a record" the same as a field in a
record?
If not, what's the difference?
If you're using the listbox to populate a field, how is the
multi-select feature implemented?
(What happens when you select more than one entry?)

If it were not for the multi-select feature, I'd suggest using a combobox,
but I'd need a better understanding of how this listbox works before I'd
want to venture any suggestions.

HTH
- Turtle

Hello

Is it possible to populate the values in a list box from a table
Click to expand...
Click to expand...
that
is
Click to expand...
using
a
Click to expand...
Click to expand...
 
Before you go too far into this, it's probably a good idea to do a little
quantification on "quite large".
An Access 2000 database is limited to 2GB. If you think you might hit this
limit within a year or two, you might be better off starting with a SQL
Server back-end. On the other hand, if you think it will take 5 or more
years to hit that size, you can probably safely use Access initially, but
warn your friend to start work on the transition well before he hits the
limit.

In a call logging situation, it's rare to need to display multiple records
at once.
I'd suggest designing your form so that its recordsource only pulls one
record at a time from the back end.
Make sure you have an index on every field which might be used for a
sort/filter/lookup; that way only the key values are brought over the
network for the search, and only the complete page for the record in
question.

HTH
- Turtle

Kevin said:
Hi MacDermott

Thanks for the response. I'm fine with the linked table.

In answer to your questions.........The Access version will be 2000. I'm
not sure what the End Users OS is, I'm doing this for the friend of a
friend.

Hope you don't mind if I ask you this.......With regard to linked tables,
the main tables may get quite large this is a call logging solution. Will
this make any difference to performance?

Thanks
Kevin

MacDermott said:
I doubt seriously that you'll see any significant performance hits by using
a linked table -
after all, that's what Access is built to do.
BTW - what version of Access are you using? What OS?

Be aware, though, that if a user adds something to the source table, it will
not show up on other users' screens until the listbox is requeried -
regardless of whether the source table is linked or not.

HTH
- Turtle

Kevin said:
Hi Macdermott

I hope this will shed some light if I have explained it properly!

Basically the list box will be used so that the user can select one,
Click to expand...
or
more
values, that will update a text box using a semi-colon as a separator [as
per code below]. The source of the list box is a table lk_OS which resides
in a back end database. At the same time I have an inputbox that will allow
users to add items to the source table lk_OS.

I'm quite inexperienced when doing multi-user FE/BE solutions, but I figured
that it would be better for performance to grab recordsets etc. at runtime
rather than use linked tables. Maybe I am wrong?

Essentially my question is: Can the row source property of the list
Click to expand...
Click to expand...
box
be
set so that it uses the table lk_OS from the back end database, rather than
using the linked table? If there are no performance hits using linked
tables I am happy to do so.

Again, thanks in advance.

Kevin

*********Code Starts************

Private Sub cmdAdd_Click()
Dim i As Integer
Dim i2 As Integer
Dim vItm As Variant
Dim stWhat As String
Dim stSep As String

Me![Text5] = Null
i2 = Me![List2].ListCount
Debug.Print i2
stSep = "; "
For Each vItm In Me!List2.ItemsSelected
stWhat = stWhat & Me!List2.ItemData(vItm)
stWhat = stWhat & stSep
Next vItm
Me![Text5] = stWhat
End Sub
**********Code Ends*************


I'm not sure I have the full picture of what you're doing here:
Is "a field that is held against a record" the same as a field
Click to expand...
Click to expand...
in
a that values
'on using
Click to expand...
Click to expand...
 
Back
Top