Set table variable = table? newb in need

[iXPhound]

Hi all,

I am attempting to perform the following which may make no sense at all so I appreciate any help. I am using DAO. I want to Declare a public variable, Agency_Table as tabledef. When a user enters the database I want them to be prompted to choose from a list of options. Depending on what option they choose I was wondering if I could then set agency_table = the table the user selected. Basically i want to be able to say that Agency_Table = tblMyAgency or tblTheirAgency, depending on the user etc. Is there anyway to do this? I apologize for this confused post.

TIA!!!

 

[redapples]

If the tables, tblMyAgency & tblTheirAgency are not to be updated then the best way might be to run a delete query followed by an update query on a third table called (for example) tblSessionAgency. Then use this third table for any uses in the database.

If you require additions/deletions to these tables then you could as you ask use a Public declared string for the table name and then for recordsets in DAO use the string as the e.g. recordset.open db(PublicString).
Similarly you could set the Record Source of a form in code as it opens e.g. Me.RecordSouce = Publicstring.

Having said all of that be careful about the scope of variables. You could assign a value to a table on opening it and delete this record on closure of the database.

Hope I have not confused you.
redapples

 

[redapples]

Was thinking about this over night and the thought that if on the form where the user selects the table they want you can open the form you then need to go to with the table name as the open args. then in the Form Open event set the record source to the relevant table based on the openargs value for example.

If Not IsNull(OpenArgs)Then
    If OpenArgs = "tblMyAgency" Then
        me.recordsource = "tblMyAgency"
    Else
        me.recordsource = "tblMyAgency"
    End If 
End If

From memory this value would be stored in the form when it is closed so if you needed to leave the form and come back to it then the record source should stay the same. You would need to repeat this if other forms needed the same information. NB OpenArgs is a key word.

to assign open args use the

DoCmd.OpenForm "form1", , , , , , (strSelectedTable)

where strSelectedTable is a string containing the table name you want.

have fun.

redapples