Can Table name be a Public variable 2

[pradh]

Hi,

I have a question with regard to using table names as public variable and use it in the code(in the form).

I have many tables with different names like JhsTbl,JieTbl...
I want to use just one form to edit/add values to the above tables. Is it possible to pass the Table name as a string and pass it to functions like DoCmd.RunSQL, DLookup across all the Subs.

Any help is appreciated.

Thanks
Pradh

 

[Blorf]

I am not sure I know what you mean.

You can have a function that returns a table name, and use that to construct a sql statement when telling a form what to use.

Assuming you have subs in the form also in your dlookups, instead of hard coding a table name you can give it a function to retrieve the table name.

But would it not be easier to have a single table, and code the records with Jhs, or Jie or what not?

Thanks,
ChaZ

 

[TheAceMan1]

How are you pradh . . . . .

A public variable [blue]with same name[/blue] as a table is a [blue]bad Idea[/blue]. A public variable whose [blue]value is a table name[/blue] . . . no problemo!

For what you asking you shouldn't need a public variable. Just pass the name properly as an arguement:

[blue]   Call [b]DoSQL[/b]("[purple][b]TableName[/b][/purple]")

Public Sub [b]DoSQL[/b]([purple][b]tbl[/b][/purple] As String)
   Dim SQL as string, DLK

   SQL = "Select " & [purple][b]tbl[/b][/purple] & ".FieldName1, & [purple][b]tbl[/b][/purple] & ".FieldName2 " & _
         "From " & [purple][b]tbl[/b][/purple] & ";"
   DoCmd.RunSQL SQL

or

   DLK = DLookUp("[FieldName]", [purple][b]tbl[/b][/purple],"Criteria")

End Sub[/blue]

or return properly from a function:

[blue]   SQL = "Select " & [purple][b]GetTableName() [/b][/purple]& ".Field1 " & _
      "From " & [purple][b]GetTableName()[/b][/purple] & ";"

Public Function [purple][b]GetTableName()[/b][/purple] As String
   [green]'Code
   'Code[/green]
   [purple][b]GetTableName()[/b][/purple] = "YourTableName"
End Function[/blue]

See Ya! . . . . . .

 

[MichaelRed]

Assuming the tables all enjoy EXACTLY the SAME structure, you could reassign the recordsource (query) in an external procedure;

e.g.

Form Name == MyForm
RecordSource = MyQry

Public Sub basASgnRecSrc(tbl as String)

   Dim dbs as DAO.Database
   Dim Qdf as QueryDef
   Dim str as string

    strSQL = "Select " & tbl & ".* "From " & tbl & ";"

   Set dbs = Currentdb
   Set qdf = MyQry

   qdf.Sql = strSQL

    set qdf = Nothing
    set dbs = Nothing

End Sub

If this is done from fro form, module of MyForm, you win=ll also need to requery the form. If it is done in some external procedure BEFORE loding the form, it will open with the correct data qithout further acttion.





MichaelRed

 

[TheAceMan1]

pradh . . . . .

There's an error in the Function Code Block:

[blue]   [purple][b]GetTableName()[/b][/purple] = "YourTableName"
should be:
   [purple][b]GetTableName[/b][/purple] = "YourTableName"[/blue]

See Ya! . . . . . .