How to bring up a list of tables in form view containing the word "backup"

[Robertf987]

Well, I think I've described what I want to do in the title here.

In the database, I have two main tables that contain the main data for the database. One for group expenditures, another for application details. I have a query for each of them to copy all the contents of the table into a new table, then delete all the data from the main table. I need to do this becasue once a year the data needs to be removed, ready for putting in new data. I couldn't just delete it without backing it up, just incase we need to look at the old data later. The database is all user-proofed, so a user doesn't have access to menus or the database window - so users don't have access to these backup tables.

Basically, I want to be able to have some sort of query or something to bring up a list of all the tables containing the word "backup", becasue at the moment the only way to access the backups is to open the database in design view - which is ok for me, but I can't have other users doing this. I can't just link to the tables through a form, because we don't know the names of the forms (not the full name anyway, there will be new ones added later. Under the format of "AppBackup__dd_ mm_yyyy" and "ExpBackup__dd_ mm_yyyy". I just want to be able to open a form, show a list of tables containing the word "backup", then from the list be able to click a button to show that table's contents (preferably in a form).

Sorry about this. Don't know if anybody can help. I could just have all the backup data in one table, then rather than adding a new table each time we backup, it just appends to the table. but after a while, this table could get huge, plus it then won't clearly show which data corresponds to what year. I'll do the append thing as a last resort if I have to, that's easy to do. but preferably I would like to do what I've asked here, to bring up a list (in form view) of all tables containing the word "backup", then to click one to show that table's contents.

Thanks in advance if anybody can help, and even if you can't, thanks for reading.

[missinglinq]

This will do the job!

  On your form place a Combobox
 When the Combobox Wizard comes up click on Cancel
 Goto the Combobox Properties >> Data 
 Row SourceType = Table/Query
Row Source = SELECT [Name] FROM MSysObjects WHERE [Name] Like '*backup*' AND [Name] Not Like 'MSys*' AND [Type]=1; 

Now in the VBE (code window)

Private Sub YourComboBox_AfterUpdate()
    DoCmd.OpenTable YourComboBox
 End Sub

This will only bring up tables that have "backup" somewhere in their names.

Good Luck!

[Robertf987]

Fantastic, thank you very much, it works great. Can't thank you enough. A cookie for thee shall be thy reward. Thank you.

[missinglinq]

Glad we could help you!