Results 1 to 12 of 12
  1. #1
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35

    Populating a Subform from a Temporary Table

    Hello there everyone.


    As the title suggests, I'm trying to populate a subform with information taken from a temporary table (pretty much like a datagridview on vb.net)

    Within runtime, after the user selects an Excel file from the Office.Dialog object, all the columns are written into a temporary table to create the fields.
    After the fields have been created, I fill them with the values that are read from all of the rows of the excel file.

    Finally, after having the temporary table built I use it as the record source for a subform.
    Below is the code for all I've described:

    Code:
    Private Sub btnBrowse_Click()    Dim varItem As Variant
        Dim f As Office.FileDialog
        Set f = Application.FileDialog(3)
        f.AllowMultiSelect = False
        If f.Filters.Count > 0 Then
            f.Filters.Clear
        End If
        
        f.Filters.Add "Todos os ficheiros", "*.*"
        f.Filters.Add "Ficheiros Excel", "*.xlsx"
        f.Filters.Add "Ficheiros Excel (Versão 97-2003)", "*.xls"
        
        Dim fso As New FileSystemObject
        Dim fileName As String
        
        'FileDialog shows up for the user. User selects the Excel file to import.
        If f.Show Then
            If f.SelectedItems.Count = 1 Then
                For Each varItem In f.SelectedItems
                    fileName = Left(fso.GetFileName(varItem), (InStrRev(fso.GetFileName(varItem), ".", -1, vbTextCompare) - 1))
                    If fileName = "Geracao Etiquetas Caixa SB" Then
                        strFile = fso.GetFileName(varItem)
                        strFilePathTmp = fileName & "_" & Format(Now(), "yyyy-mm-dd hh:mm:ss") & "." & fso.GetExtensionName(varItem)
                        strFileToExport = fso.GetFile(varItem)
                        fileSelected = True
                        Exit For
                    Else
                         MsgBox "O nome do ficheiro escolhido não é valido." & vbCrLf & "O ficheiro deve conter o seguinte nome: Geracao Etiquetas Caixa SB", vbOKOnly, "Erro: Importação de Ficheiro"
                    End If
                Next varItem
            Else
                MsgBox "É necessário seleccionar um ficheiro", vbOKOnly, "Importar Ficheiro"
            End If
        Else
            Exit Sub
        End If
        
        'Access will now read the Excel file and create the temporary table from it (columns and rows).
    
    
        Dim db As DAO.Database
        Dim tmpTbl As TableDef
        Dim tmpTblFld As Field
        
        Set db = CurrentDb
        Set tmpTbl = db.CreateTableDef("Tabela_Temp_Etiq_Caixa")
        
        Dim xlApp As Excel.Application
        Set xlApp = New Excel.Application
        
        Dim xlWorkBook As Excel.Workbook
        Set xlWorkBook = xlApp.Workbooks.Open(strFileToExport)
        
        Dim xlWorkSheet As Excel.Worksheet
        Set xlWorkSheet = xlWorkBook.Worksheets(1)
        
        Dim xlColumnIndex As Integer
        Dim xlRowIndex As Integer
        
        For xlColumnIndex = 1 To 4
            If xlColumnIndex = 1 Then
                Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbText, 10)
            ElseIf xlColumnIndex = 2 Then
                Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbText, 40)
            ElseIf xlColumnIndex = 3 Then
                Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbLong)
            Else
                Set tmpTblFld = tmpTbl.CreateField(xlWorkSheet.Cells(1, xlColumnIndex).Value, dbLong, 13)
            End If
            
            tmpTbl.Fields.Append tmpTblFld
            tmpTbl.Fields.Refresh
        Next xlColumnIndex
    
    
        For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count
            DoCmd.RunSQL "INSERT INTO " & tmpTbl.Name & " (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _
            "('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
            ", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
        Next xlRowIndex
        
        'At this point I'll define the RecordSource property of the Subform.
        'dgvBoxLabelsToPrint is the name of the Subform.
        Me.dgvBoxLabelsToPrint.Form.RecordSource = "SELECT * FROM Tabela_Temp_Etiq_Caixa"
        
        DoCmd.GoToControl "txtXlsFilePath"
        Me.txtXlsFilePath.Text = strFileToExport
        Me.btnGenerate.Enabled = True
        DoCmd.GoToControl "btnGenerate"
    End Sub
    Now, my problem is that Access, for some reason that I'm not able to understand isn't recognizing the temporary table that is created on run-time, giving out the following error:


    • Could not find output table 'Tabela_Temp_Etiq_Caixa'


    This error occurs on the following block of code:

    Code:
    For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count        DoCmd.RunSQL "INSERT INTO " & tmpTbl.Name & " (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _
            "('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
            ", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
        Next xlRowIndex
    I checked the immediate window for the tmpTbl.Name propery and it does recognize it, with the name of the table Tabela_Temp_Etiq_Caixa.

    What am I missing here?
    Last edited by Simbiose; 11-10-2020 at 11:33 AM.

  2. #2
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Daft question but what happens if you hard code the table name? (It isn't going to change after all?)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35
    Quote Originally Posted by Minty View Post
    Daft question but what happens if you hard code the table name? (It isn't going to change after all?)
    I'm sorry, but I don't understand what you mean.
    Can you clarify?

  4. #4
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Sure - try this

    Code:
    For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count        
    DoCmd.RunSQL "INSERT INTO Tabela_Temp_Etiq_Caixa (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _ 
           "('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
            ", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
        Next xlRowIndex
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35
    Quote Originally Posted by Minty View Post
    Sure - try this

    Code:
    For xlRowIndex = 2 To xlWorkSheet.UsedRange.Rows.Count        
    DoCmd.RunSQL "INSERT INTO Tabela_Temp_Etiq_Caixa (Cod_Artigo, Descricao, Quantidade, EAN) VALUES " & _ 
           "('" & xlWorkSheet.Cells(xlRowIndex, 1).Value & "', '" & xlWorkSheet.Cells(xlRowIndex, 2).Value & "', " & xlWorkSheet.Cells(xlRowIndex, 3).Value & _
            ", " & xlWorkSheet.Cells(xlRowIndex, 4).Value & ")"
        Next xlRowIndex
    Oh... that's what you meant. Well, I feel dumb now...
    Yes, I did do that, that was actually my original code, but I decided to change to tmpTbl.Name, because this is what I also debug on the immediate window.
    Anyway, the result is the same either way.

  6. #6
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    - we've all been there.

    In that case the answer almost certainly is that your tabledef statement isn't creating the table you are expecting it to.
    As you aren't deleting the tabledef at the start or end of the routine, and you haven't reported getting a table already exists error then this also points to this.

    Try adding a
    Code:
    tmpTbl .Append
    to save the table?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35
    Oh God... you've got to be kidding. I can't believe I didn't see that.
    Thanks for the help Minty.
    Now, I'm having an error on the following line:


    • Me.dgvBoxLabelsToPrint.Form.RecordSource = "SELECT * FROM Tabela_Temp_Etiq_Caixa"


    And the error is as follows:


    • The expression you entered refers to an object that is closed or that does not exist.


    I think I know what I've got to do. I'll be back soon with an update.

  8. #8
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35
    Damn it, I thought the solution was to define a recordset and set the recordsource of the subform as that recordset, but it doesn't work that way...
    Any ideas as how I should set the recordsource?

  9. #9
    Simbiose is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2015
    Posts
    35
    Ok, I'm posting again to bring this thread to a closure.
    I've just learnt that you cannot set the recordsource for unbound forms and this is why I'm getting that last error.
    Basically I had to forget the temporary table approach and actually create a 'real' table that will hold temporary records...
    Thanks for all the help. Cheers.

  10. #10
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's a bit odd, I do something almost identical to display a timetable from a cross tab.
    The only thing different is that I haven't created the table on the fly, I simply delete the contents of the table as the columns are fixed but pivoted.

    Do you have a fixed number of columns but with changing names?

    If so you could just call them Col1, Col2 etc and rename them in the display based on sticking the names into an array for later use ?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by Simbiose View Post
    Ok, I'm posting again to bring this thread to a closure.
    I've just learnt that you cannot set the recordsource for unbound forms and this is why I'm getting that last error.
    Basically I had to forget the temporary table approach and actually create a 'real' table that will hold temporary records...
    Thanks for all the help. Cheers.
    Glad you resolved it, even if not the way you wanted to.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by Simbiose View Post
    I've just learnt that you cannot set the recordsource for unbound forms and this is why I'm getting that last error.
    This is not true. You can set the Recordsource for a unbound form but you have to bound correctly the ControlSource of its controls to the corresponding fields.
    Also, should run a refresh on TableDefs collection before the Recordsource setting:
    Code:
    db.TableDefs.Refresh
    Quote Originally Posted by Simbiose View Post
    Damn it, I thought the solution was to define a recordset and set the recordsource of the subform as that recordset, but it doesn't work that way...
    Any ideas as how I should set the recordsource?
    Maybe you will find useful this post:
    https://www.accessforums.net/showthr...391#post465391

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 05-17-2019, 09:21 AM
  2. VBA To Add Primary Key To Temporary Table
    By lccrews in forum Programming
    Replies: 4
    Last Post: 05-25-2018, 12:38 PM
  3. Replies: 5
    Last Post: 01-10-2018, 04:16 PM
  4. Populating a table from a form/subform
    By Fish218 in forum Forms
    Replies: 6
    Last Post: 02-09-2012, 08:52 AM
  5. Replies: 1
    Last Post: 08-13-2011, 12:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums