Private Sub List29_BeforeUpdate(Cancel As Integer)
Private Sub Page2_Click()
yourListName.RowSource = "SELECT MSysObjects.Name"
WHERE (((MSysObjects.Flags) Not In (-2147483648,2,-2147483645)) AND
((MSysObjects.Type) In (1,4)))
ORDER BY MSysObjects.Name;"
When I debug this the bebugger stops at FROM MSysObjects with the Compile error: Sub or function not defined.
Your error is because that's outside the string you started on the row above. Having said that, why create all the extra work? Just make that SQL the rowsource of the listbox in design view. It's not dynamic, so why set it every time? I thought you had this part worked out in post 4.
I've got to be honest; I'm always suspicious when someone is trying to get table names like this. It often means they've got a design problem (tables for each year, etc). Why don't you give us the "big picture" of what you're trying to accomplish.
One main table with thousands of office names. Many office names are exactly the same, or have a very small differences. This is not a keyed table. It's a flat file database. I run queries that perform calculations based on the office names and dollar amounts in this main table.
Then, for each of my clients, I have a small table that list maybe 12 office names to be calculated against the main table of thousands of records. This way, if I lose a client I simply delete their table, or add a small table for a new client.
When the office names in the client table match the same office names in the main table in a specific way calculations are performed only on those matches. It's worked great for 15 years in Paradox. Only in Paradox I have a separate procedure (program) for each client. Not very effecient.
I'm abandoning Paradox for obvious reasons.
I'm running XP PRO with MS Office Professional 2003 1 GIG RAM.
My thinking was this.
Create a 4 tabbed form for each county. I have about four counties that I work in.
When I open (Select) a tab, the list box shows me my client tables for that county.
I select a client table name from the list box and pass that tablename variable(?) to the calculating query.
Then run the report, which is already designed and working well.
(Something new to me is that I can select the report and the query runs with all of the calculations shown on the last page (SubReport?) and the main data in the body context of the report.)
This is the VERY BASIC, but most important part of the works.
.Take table name from list box
.. pass table name to query - run query
... View, and then print report