Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Unanswered: A little guidance

    I'm trying to set up a macro to create a new table. Pretty simple, but I need it to name the new table after an entry in an already existing table. I don't even know where to start, I've been trying to find an answer for two days now. Any help would be very appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    40

    Re: A little guidance

    I am not sure if you are looking for code since you said that making a new table was easy, so.... the logic that you are looking for is:
    You want to read the value from the table first and store it as a string
    Then you want to create the table with the name as the String.
    I have no idea if that is going to help, but if you need code, then you need to send me what you have so far, or at least tell me your prefered method for creating the table

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    Hmmm, I suppose I am looking for the code then. I need to pull a serial number from an entry and copy an existing table renaming it to the serial number. I know some basics of coding, but I really don't know how to manipulate Access with VB. Thanks for any help.

  4. #4
    Join Date
    Nov 2003
    Posts
    40
    Here is the code to create the table, with the input to the function being the Serial Number as a STRING. If you need help getting that too, let me know.

    __________________________________________________ ________
    Function Create_Table (SerialNumber As String)

    'Create blank table with correct title

    DoCmd.RunSQL "CREATE TABLE " & SerialNumber & "(id INTEGER, FIELD1 VARCHAR(150), **Insert all fields here with type**);"


    'Copy data from original table to blank table

    DoCmd.RunSQL "INSERT INTO" & SerialName & " ( FIELD1, FIELD2, FIELD3) SELECT OriginalTable.FIELD1, OriginalTable.FIELD2, OriginalTable.FIELD3 FROM OriginalTable INNER JOIN " & SerialName & " ON OriginalTable.KeyField = " & SerialName & ".KeyField"

    End Function

    __________________________________________________ ________

    Okay, that should help as long as you fill in the generic names with the right ones. Let me know what works and what doesn't and if you need help reading the Serial Number out of a table.

  5. #5
    Join Date
    Dec 2003
    Posts
    6
    Well, I'm quickly learning that I have no clue what I'm doing. I do need to know how to pull the serial number from the table. I really appreciate this help. Thanks

  6. #6
    Join Date
    Nov 2003
    Posts
    40
    I am not exactly sure how you are getting the serial number. So I am going to make some assumptions. I am assuming that you are doing this by clicking a button and I am assuming that you reading this from a text box. So, please let me know if that is wrong so we can make modifications to the code.

    Sub cmdButton_Click ()

    Dim str As String

    str = TextBox1.Value

    Create_Table(str)

    End Sub


    Did the other line of codes work for you?

  7. #7
    Join Date
    Dec 2003
    Posts
    6
    Basically I've got a table in the database with a list of Serial Numbers. I need to have an individual table for each Serial in the original table, and I need them named so that I can create relationships with them.

  8. #8
    Join Date
    Nov 2003
    Posts
    40
    Okay okay. you might need a different solution than I originally though. You have a database..with a table, with serial numbers. You want to make the exact same table for each serial number with the serial number name in order to link them. And you are doing this as a one time thing or you need this done on the fly as users are inputting data???
    Why are they different tables. Why not make one big table with the serial number a foreign key to the serial number table??

    This is not going to make sense but...
    I can help you with what you want to do, but I don't think that what you THINK you want to do is really want you want to do. The structure of your database, the way I am understanding your explanation, is not ideal.
    BUT, if all you want to do is copy the table and rename it, one time as the designer and programmer and not the user, then all you have to do is right click on the table and hit copy and hit paste on the table menu and it will prompt you for a new name.

    I hope I am helping

  9. #9
    Join Date
    Dec 2003
    Posts
    6
    I'd like it done on the fly. I'm not sure how to explain exactly what I'm trying to do, but I don't think it will work without a seperate database for each serial because I'm going to basically be setting up a log for each serial. If I'm wrong please let me know, I still pretty new to this database thing, so any help would be wonderful.

  10. #10
    Join Date
    Nov 2003
    Posts
    40
    If you are setting up a log for each Serial then my original assumption is right. Making a database for each Serial should not be an option.

    What you want to do is make one big table that would store the information about all the serial numbers logs. Add a field to the main table for the serial number and then you can use it as a foreign key to connect to the serial number table. (This will become a relationship.)
    Then you can create queries if need be to view each log for each serial number seperately. Or you could do it by having forms and subforms.

    If you are totally new to the database thing, my best suggestion would be to buy a book. Something that not only tells you how Access works but helps you to build a well structured database.
    I mean no disrepect when I suggest that, it is just that it sounds like not only are you not sure what code needs to be behind the database but you aren't exactly sure what the database is suppose to look like yet and that could get you in trouble in the long run.
    I am sorry that I couldn't really help you as much as you wanted.

  11. #11
    Join Date
    Dec 2003
    Posts
    6
    That sounds like a great idea. Thanks for your help though, I guess I didn't realize that DB's were this complicated.

Posting Permissions

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