Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2004
    Posts
    6

    Unanswered: variable table name

    here is my code:

    Function GetComputerName()

    Dim strUser As Object

    Set strUser = CreateObject("WSCRIPT.Network")

    GetComputerName = Trim(strUser.ComputerName)

    I have a Form in Access with a textbox. I have a table named GetComputerName (I am creating this table with the code above along with other code)

    so, for example my computer name is kkondrat1, the table is kkondrat1...simple enough

    here is my issue- I have an ID number in this table, each time I open the form the text box should have the ID number set inside the textbox as the value.

    how do I write the code to have the textbox set up this way?

    I want to open the form, the textbox control searches for the tablename that matches the GetComputerName Function and returns the ID number??

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That made ZERO sense what-so-ever.

    Why are you dynamically creating keyed tables on the fly? Pablo no understand.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Posts
    6

    oh no

    that's not good, zero sense?

    here is all of my code, I am creating random numbers, then assigning the random numbers into "virtual" tables based on users on the network.........maybe you can help me out with a better way to do this......

    Function GetComputerName()

    Dim strUser As Object

    Set strUser = CreateObject("WSCRIPT.Network")

    GetComputerName = Trim(strUser.ComputerName)

    End Function
    Private Sub Command0_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim strTableName As String
    Dim Temp As String

    strSQL = "SELECT tblStaff.LoanNum " & _
    "INTO tblTemp " & _
    "FROM tblStaff;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblTemp")
    Set fld = tdf.CreateField("RandomNumber", dbDouble)

    tdf.Fields.Append fld

    Set rst = db.OpenRecordset("tblTemp", dbOpenTable)

    rst.MoveFirst
    Do
    Randomize
    rst.Edit
    rst![RandomNumber] = Rnd()
    rst.Update
    rst.MoveNext
    Loop Until rst.EOF

    rst.Close
    Set rst = Nothing

    strTableName = GetComputerName
    strSQL = "SELECT TOP 1 tblTemp.LoanNum " & _
    "INTO " & strTableName & " " & _
    "FROM tblTemp " & _
    "ORDER BY tblTemp.RandomNumber;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    db.TableDefs.Delete ("tblTemp")
    DoCmd.OpenForm "QC FORM"
    DoCmd.Close acForm, "Admin", acSaveNo

    End Sub

  4. #4
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by Teddy
    That made ZERO sense what-so-ever.

    Why are you dynamically creating keyed tables on the fly? Pablo no understand.
    Juan no understand also...

    Soo you are making a table with your comp name, and it has an ID field,which I am assuming that it is an autonumber...What else is in the table? Because if you just have a table with one autonumber field, it will never increase/populate, because an auto needs another field to start initialize the record. If the table's key is just your computer name, that just makes my head hurt. But if that is the case why go through the trouble of making a table? Can't you make a function that gets the computer name then assign it to that textbox?
    Ryan
    My Blog

  5. #5
    Join Date
    Sep 2004
    Posts
    6
    No, no autoNumber is being used.

    Yes, I can assign the computername to the textbox, that is not a problem, but I don't want the computer name to appear in the textbox. I want the IDNUM to appear in the textbox.

    The logic behind this is I have users selecting random numbers from a table that is static. one random number is spit out into a table, and the tablename is the users computer.

    here is what the table looks like:

    TableName = Kkondrat1 (my computername)
    IDNUM = 65786876 (random number)

    so when a user pulls up the form the form will recognize that Kkondrat1 is the computer so it will pull up the IDNUM in the textbox.

    This is probably a stupid way of doing this that's why I am asking for help, thanks.

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    ok so what does this ID represent? A random number can shoot out things like you have, but I have seen a random number shoot out like 452310453E-51. Do you think that would an acceptable ID and your user wouldn't go huh when they see that their ID is 452310453E-51? If it's to ID the user or computer why do you just use the computer name? Or even better if the users have ID's, the table is created and the computer name is looked up, then the person is asked for ID. Then when he/she reopens it the computer name is already in with th ID...
    Ryan
    My Blog

  7. #7
    Join Date
    Sep 2004
    Posts
    6
    let me help you out, the IDnum is not a random number that is being generated....it is a random field that is already set.

    example:

    IDNUM Lname FName
    1234 smith John
    3234 Kerry Jack


    The code above pulls out randomly the IDNUM 1234 or 3234 then puts this random field into a table. The table is named after the computername.

    so my result could be:
    kkondrat1.1234

    Now if I were to use this database on a different computer I would have a different computername so the result could be-

    Jackson.3234

    all I want to do is have a form that can recognize that the computername is Jackson and read the IDNUM

    so if I were to pull up the form on my current computer (computername=kkondrat1) I want ID Num 1234 to show up in a textbox. If I was on the Jackson computername ID num 3234 will show up in the textbox.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Soo.. are you trying to deal with session management? Is that what you're saying? You are FAR better off having a field in an already existing table that you push a session id into then creating temporary tables..
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Sep 2004
    Posts
    6
    I just want to know if it is possible to do this:

    Have a textbox control - the textbox recognizes the computername- then looks up the IDNum in that table.

    select IDNUM FROM GetComputerName() is something like this allowed?

  10. #10
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Yeah. It looks like you are already doing something similar in your second post.

    strSQL = "SELECT IDNUM FROM " & GetComputerName()

    Then open strSQL as a recordset.

    With less code you can use DLookup, but it is usually slower.

  11. #11
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    So why don't you have a table with log in ID's (ID, LName, FName), then use that table to manage the users. Then use what Ted, DCKunkle (and you yourself kind of said it) mentioned? You may want to put in a temp table to maybe track who all signs in?
    Ryan
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •