How to Refresh List Box - Sounds Simple???

[Patrick Fisher]

I am using the following module code to display in a List Box all
attached tables that start with tblProducts i.e. tblProductsSpain or
tblProductsFrance etc

I have other code which enables the user to enter a new table name,
the code then creates the table in the attached back end database and
automatically links it to the front end.

My problem is that the list box does not display the new tables until
I close the program and restart it. Requery has no effect, is there a
way of forcing a refresh?

Thanks
Patrick


Function ListAllTables(fld As Control, ID As Long, row As _
Long, col As Long, Code As Integer)
Dim Db As Database
Dim tbdf As TableDef
Static tbls(256) As String
Static Entries As Integer
Dim i As Integer
Dim ReturnVal
ReturnVal = Null
Select Case Code
Case LB_INITIALIZE ' Initialize database.
Set Db = DBEngine.Workspaces(0).Databases(0)
Entries = 0
For i = 0 To Db.TableDefs.Count - 1
If Left(Db.TableDefs(i).Name, 11) = "tblProducts" And
Db.TableDefs(i).Name <> "tblProducts" And Db.TableDefs(i).Name <>
"tblProductsHTML" And Db.TableDefs(i).Name <>
"tblProductsEndOfYearArchive" Then
tbls(Entries) = Db.TableDefs(i).Name
Entries = Entries + 1
End If
Next i
ReturnVal = Entries
Case LB_OPEN ' Open.
ReturnVal = Timer ' Unique ID number for control.
Case LB_GETROWCOUNT ' Number of rows.
ReturnVal = Entries
Case LB_GETCOLUMNCOUNT ' Number of columns.
ReturnVal = 1
Case LB_GETCOLUMNWIDTH ' Column width.
ReturnVal = -1 ' Use the default width.
Case LB_GETVALUE ' Get the data.
If Left(tbls(row), 11) = "tblProducts" Then
ReturnVal = tbls(row)
End If
Case LB_END ' End.
For Entries = 0 To 256
tbls(Entries) = ""
Next
End Select
ListAllTables = ReturnVal
End Function

[Allen Browne]

That's correct. The static array will not reinitialize until you close the
form.

The best solution might be to normalize the tables. Instead creating a new
table for every country, consider using a tblProductDetail table with these
fields:
ProductID foreign key to tblProducts.ProductID
CountryID foreign key to tblCountry.CountryID
UnitPrice Currency (the currency symbol will be in tblCountry).
TaxRate Double (because some countries have different tax rates
for different products).
ProductName Text (what the product is called in this country).

Now all you have to do is set the RowSource of the listbox to a new query
statement that retrieves the product names used by the country (WHERE
CountryID = 99), and the prices for that country, using the custom currency
format string you stored in the tblCountry table.

If desired, you can use the AfterUpdate and AfterDelConfirm event procedures
of the form where product details are entered to see if the form with the
list box is open, and if so, Requery the list box so it shows the new
product or changed price.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Patrick Fisher" <in**@psoftuk.com> wrote in message
news:uf********************************@4ax.com...

I am using the following module code to display in a List Box all
attached tables that start with tblProducts i.e. tblProductsSpain or
tblProductsFrance etc

I have other code which enables the user to enter a new table name,
the code then creates the table in the attached back end database and
automatically links it to the front end.

My problem is that the list box does not display the new tables until
I close the program and restart it. Requery has no effect, is there a
way of forcing a refresh?

Thanks
Patrick
Function ListAllTables(fld As Control, ID As Long, row As _
Long, col As Long, Code As Integer)
Dim Db As Database
Dim tbdf As TableDef
Static tbls(256) As String
Static Entries As Integer
Dim i As Integer
Dim ReturnVal
ReturnVal = Null
Select Case Code
Case LB_INITIALIZE ' Initialize database.
Set Db = DBEngine.Workspaces(0).Databases(0)
Entries = 0
For i = 0 To Db.TableDefs.Count - 1
If Left(Db.TableDefs(i).Name, 11) = "tblProducts" And
Db.TableDefs(i).Name <> "tblProducts" And Db.TableDefs(i).Name <>
"tblProductsHTML" And Db.TableDefs(i).Name <>
"tblProductsEndOfYearArchive" Then
tbls(Entries) = Db.TableDefs(i).Name
Entries = Entries + 1
End If
Next i
ReturnVal = Entries
Case LB_OPEN ' Open.
ReturnVal = Timer ' Unique ID number for control.
Case LB_GETROWCOUNT ' Number of rows.
ReturnVal = Entries
Case LB_GETCOLUMNCOUNT ' Number of columns.
ReturnVal = 1
Case LB_GETCOLUMNWIDTH ' Column width.
ReturnVal = -1 ' Use the default width.
Case LB_GETVALUE ' Get the data.
If Left(tbls(row), 11) = "tblProducts" Then
ReturnVal = tbls(row)
End If
Case LB_END ' End.
For Entries = 0 To 256
tbls(Entries) = ""
Next
End Select
ListAllTables = ReturnVal
End Function

[Patrick Fisher]

Thanks for your suggestion Allen, with hindsight that was obviously a
much better way to do it.
Patrick