Object Source dependent on user 1

[Roblin]

Hi,

I have a subform that I would like to set the Object Source based on what user is in the database. I have a backend DB that contains tables that store data for all the users. The frontend points to the correct user's table based on a function that grabs their network ID. I have a subform on the form they use to enter data that will display what they have entered previously. Is there a way to make the object source of the subform dependent on what user is in the DB? I tried this:

Dim vTableName
vTableName = "tbl" & ap_GetUserName
Me.SourceObject = vTableName

It doesn't work.
ap_GetUserName is the function that grabs the network ID. All tables on the backend are named by tbl+networkID.
Does anyone know how to accomplish this?

 

[Remou]

Hi
Are you thinking of record source? If so, you can add code to the On Open event of the subform:

Dim vTableName
vTableName = "tbl" & ap_GetUserName
Me.RecordSource = vTableName

Or if you need to use particular data from the table:
Me.RecordSource = "Select * From " & vTableName & " Where <where criteria>"

 

[Roblin]

Hello again Remou,

I don't want to enter data onto the subform. The subform just displays past entries completed by the users so they can see what they have entered as they are entering data on the main form. Doesn't the record source tell the subform where to store data? The second field in the properties of the sub form is "Source Object". That seems to be where the subform draws what is displayed from. I just want the subform to display what is in the table that is the record source for the main form. The table that the main form is recording the entries to depends on the network ID funciton and the subform will depend on the same. Does that make sense? I tried your suggestion, but it doesn't seem to be working. Any other ideas? You've really been saving me on these problems. Thanks again.

 

[Remou]

Hi Roblin
How's it going?
The source object is a property of the subform control, not a property of the form within the control. It would show the name of the subform, eg tblArea subform.
You must set the record source, if you want to display records. If you do not want users tampering, either set the form properties for the subform to 'No' for Allow Edits / Allow Deletes; or set the subform control property Locked to Yes.

 

[Roblin]

Ok Remou,

You did it again. Thanks a bunch.