Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Change Record Source of a form to base it on certain table

    I have a form (Form1) with one field (text box "N1")
    on this form I have a button with on click action related to (text box "Text2")
    when clicking the button a table with a name entered in the text box is created.
    this is the vb code used to create the said table:
    Private Sub Command0_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim tdfNew As TableDef
    Dim fldNew As Field

    '--- set a reference to the database
    Set db = CurrentDb

    '--- create the table

    Set tdfNew = db.CreateTableDef(Text2)

    '--- add integer field
    Set fldNew = tdfNew.CreateField("ClientID", dbInteger)
    '--- save the new field
    tdfNew.Fields.Append fldNew

    '--- add text field (length 20)
    Set fldNew = tdfNew.CreateField("ClientName", dbText, 20)
    '--- save the new field
    tdfNew.Fields.Append fldNew

    '--- save the new table design
    db.TableDefs.Append tdfNew

    '---Initialize your recordset
    Set rs = CurrentDb.OpenRecordset(Text2, dbOpenDynaset)
    ' Add New Record
    '--- Clear memory
    Set fldNew = Nothing
    Set tblNew = Nothing
    Set db = Nothing

    End Sub

    My problem starts when I try to related Form1 to the table created

    i.e. to the table which takes its name from the text box Text2

    any Clue

    P.S.
    I've tried the following in a button on click

    Forms("form1").RecordSource = Text2
    Forms("form1").N1.ControlSource = N1
    Forms("form1").N2.ControlSource = N2

    but it does not work and the RecordSource refusing to be changed and stays blank!!

    thanks in advance

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    First, it should be:
    Code:
    Set tdfNew = Nothing
    and not:
    Code:
    Set tblNew = Nothing
    Second, the following code (adapted from what you posted) works the way you want:
    Code:
    Private Sub Command0_Click()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim tdfNew As TableDef
        Dim fldNew As Field
        
        '--- set a reference to the database
        Set db = CurrentDb
        
        '--- create the table
        
        Set tdfNew = db.CreateTableDef(Me.Text2.Value)
        
        '--- add integer field
        Set fldNew = tdfNew.CreateField("ClientID", dbInteger)
        '--- save the new field
        tdfNew.Fields.Append fldNew
        
        '--- add text field (length 20)
        Set fldNew = tdfNew.CreateField("ClientName", dbText, 20)
        '--- save the new field
        tdfNew.Fields.Append fldNew
        
        '--- save the new table design
        db.TableDefs.Append tdfNew
        
        '---Initialize your recordset
        Set rs = CurrentDb.OpenRecordset(Me.Text2.Value, dbOpenDynaset)
        
        ' Add New Record
        With rs
            .AddNew
            !ClientID = 1
            !ClientName = "John Doe"
            .Update
            .Close
        End With
        
        '--- Clear memory
        Set rs = Nothing
        Set fldNew = Nothing
        Set tdfNew = Nothing
        Set db = Nothing
        
        ' Change RecordSource
        Me.RecordSource = Me.Text2.Value
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    4
    Quote Originally Posted by Sinndho View Post
    First, it should be:
    Code:
    Set tdfNew = Nothing
    and not:
    Code:
    Set tblNew = Nothing
    Second, the following code (adapted from what you posted) works the way you want:
    Code:
    Private Sub Command0_Click()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim tdfNew As TableDef
        Dim fldNew As Field
        
        '--- set a reference to the database
        Set db = CurrentDb
        
        '--- create the table
        
        Set tdfNew = db.CreateTableDef(Me.Text2.Value)
        
        '--- add integer field
        Set fldNew = tdfNew.CreateField("ClientID", dbInteger)
        '--- save the new field
        tdfNew.Fields.Append fldNew
        
        '--- add text field (length 20)
        Set fldNew = tdfNew.CreateField("ClientName", dbText, 20)
        '--- save the new field
        tdfNew.Fields.Append fldNew
        
        '--- save the new table design
        db.TableDefs.Append tdfNew
        
        '---Initialize your recordset
        Set rs = CurrentDb.OpenRecordset(Me.Text2.Value, dbOpenDynaset)
        
        ' Add New Record
        With rs
            .AddNew
            !ClientID = 1
            !ClientName = "John Doe"
            .Update
            .Close
        End With
        
        '--- Clear memory
        Set rs = Nothing
        Set fldNew = Nothing
        Set tdfNew = Nothing
        Set db = Nothing
        
        ' Change RecordSource
        Me.RecordSource = Me.Text2.Value
    
    End Sub
    Thank you for your efforts

    but unfortunately, still the Record source Refusing to change
    it's driving me mad

    thanks again.

Posting Permissions

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