make table query with table parameter

[Donkeygirl]

I have automated an entire report process, and I only have this one automation left to accomplish. I have a make table that needs to make the data table for the monthly reports, and it needs to get its data from the table with the most recent date. I am looking to find out if I can set up a make table query or automate a paramet from vb to ask the user what table they want to use for the make table or for the query to choose the table with the most recent date in the name. If anyone has an idea of how this may be done, I will be greatful. It almost sounds as if it is very simple, but I am also not sure that it can be done. If not it will just be that much less automated. Thank you for your help,
ISOperator

 

[BlackKnight]

Hi,
You can have a combobox filled with the table names that you want to have the user select one from. You can then find it in the tabledefs collection and use its datecreated and LastUpdated properties to determine which one to use. An example of the datecreated and lastupdated properties follows:

Sub TimeUpdated()
Dim dbs As Database, tdf As TableDef
Dim strList As String, dtePast As Date

Set dbs = CurrentDb
dtePast = Date - 60
' Enumerate through tableDefs collection.
For Each tdf In dbs.tableDefs
' Evaluate LastUpdated and DateCreated properties.
If (tdf.LastUpdated > dtePast) Or (tdf.DateCreated > dtePast) Then
' Create list.
strList = strList & vbCrLf & tdf.Name
End If
Next tdf
MsgBox strList
Set dbs = Nothing
End Sub

Have a good one!
BK