dynamically assign table to form on open

nashken

New member
Local time
Today, 04:49
Joined
Oct 20, 2015
Messages
8
I have a form and subform linked and need to change the form and subform record source on open.

Everywhere I look I see that this should work. I placed the below code in both the form and subform open sub.
Code:
Private Sub Form_Open(Cancel As Integer)
    Me.RecordSource = Environ("USERNAME") + "_Buyback_template_t"
End Sub
But when it opens I get an error that it cannot find the original source which I have renamed. How do I accomplish this? I have tried removing the original record source and leaving it blank but the form still does not update on open with the new table source.

Using Access 2007.

thank you.
 
I may be off here but you shouldn't have a table for each user, just a field in the table to identify which user is using the database?

Is this to restrict records or set up defaults for the form based on the current user ?
 
Just for troubleshooting, I'd declare a string, fill in the string with your environment variable and the file name you plan to use, then use msgbox to show it, just to make sure you have a proper path.

If you've already done this, please disregard.
 
I tested the variable it is good.
I even tried setting it to a table name just to test my sanity. Turns out that this failed as well.

In answer to the other question I am doing this for "business reasons" in other words 1) there is a long story and I don't think anyone wants to really here it. The short version: We're tied to this method at the moment without significant redesign, time and money and all that jazz.
 
Code:
Private Sub Form_Load()
    Dim strFormName As String
    strFormName = Environ("USERNAME") & "_Buyback_Template_t"
    MsgBox strFormName
    'Me.RecordSource = "name_Buyback_template_t"
    CurrentDb.TableDef.SourceTableName = "name_Buyback_template_t"
End Sub
throws a method not found.
 
I stripped most of the sample code from the site I'd liked above, I figure you may need this more than once, so I left in the Sub for opening the file itself.

No, not my code I just copypasta'd from the MSDN but hopefully this will make it a lot more clear what you need.


Code:
Sub ConnectX() 

Dim dbsTemp As Database 

' Open a Microsoft Access database to which you will link  a table. 
 
Set dbsTemp = OpenDatabase("DB1.mdb")  'Name of the database you will open
 
ConnectOutput dbsTemp,  "AccessTable",  ";DATABASE=C:\My Documents\Northwind.mdb", "Employees" 
 
 
Sub ConnectOutput(dbsTemp As Database, strTable As String, strConnect As String, strSourceTable As String) 
 
Dim tdfLinked As TableDef 
Dim rstLinked As Recordset 
Dim intTemp As Integer 
 
' Create a new TableDef, set its Connect and  SourceTableName properties based on the passed 
' arguments, and append it to the TableDefs collection. 
 
Set tdfLinked = dbsTemp.CreateTableDef(strTable) 
 
tdfLinked.Connect = strConnect 
tdfLinked.SourceTableName = strSourceTable 
dbsTemp.TableDefs.Append tdfLinked 
 
Set rstLinked = dbsTemp.OpenRecordset(strTable) 
End Sub
 

Users who are viewing this thread

Back
Top Bottom