Results 1 to 6 of 6
  1. #1
    Join Date
    May 2009
    Posts
    104

    Unanswered: Change Linked Table Source with Combo box

    I have created a simple database for our accounting department that has a table linked to one of the sheets in a workbook they created. This spreadsheet has one sheet for every week of the year. This spreadsheet was not designed to be linked to access so it took some filtering with queries to get the data formatted correctly. Now I would like to have some code behind a combo box that changes the source of the linked table based on the value in the combo box. Each sheet is named for the week ending so it's easy to predict what the naming for the next work week will be, i.e. This work week for us ends on Sunday, 7/12/2015. So they name this sheet 071215. I have done some research for the past couple of days and most of the search results are creating and deleting tables based on dynamic values but i'm not sure that is what i want to do.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what code are you running at present to extract the data?


    incidentally I'd suggest using YYMMDD to name the fuiles as opposed to ddmmyy so that the files re automatically sorted in date order

    usually Id suggest an Excel / Access link should be one way. database stores the information, provides the tools to manipuilate it, the spreadsheet allows users to do whatever analysis they want without any risk or worry of their changes compromising the data (something that is very, very easy to do in a spreadsheet)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2009
    Posts
    104
    I'm not running any code at present. Right now i'm just linking my database to the excel spread sheet and using a query to filter out the data i don't want some users to see. All of the data i want to present in my database is in the same cell range of each sheet of the workbook. When i first linked the workbook to the database the link data wizard asked what sheet i want to link to, i'm wondering if there is a way to dynamically change the variable what sheet to link to and then refresh the data.

  4. #4
    Join Date
    May 2009
    Posts
    104
    I'll take it this is something that is not possible?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    its possible, it perfectly possible.
    is it easy to do, not really
    is it something i have time to help with No

    but in essence
    if you have the linking working well at present,
    then 'all' youneed do is fidn that code and then modify it.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    May 2009
    Posts
    104
    I figured out to accomplish this, it may be the totally wrong way to get this done but it works perfectly. I have some code to delete the linked table and reconnect the linked table to a specific sheet in a workbook based off of data inputted into "Text71" text box. This way i can dynamically change what sheet i'm linking to. I have a text box Text71 formatted for short date. I also formatted the spreadsheets by "mm-dd-yyyy" because the normal format "mm/dd/yyyy" wont work for sheet names. Whatever date is in this field it will link the database to the sheet with the matching name. LtblEmployeeTimes is the name of the linked table and I refresh the data with a few queries.

    Code:
    Private Sub Command48_Click()
    
    On Error Resume Next '---right now i have it ignoring the error where it can't find the table to delete. I'll fix that later
    DoCmd.DeleteObject acTable = acDefault, "LtblEmployeeTimes"
    '-------------------------------------------------------------------
        On Error GoTo Command48_Click_Err
    Dim WeDate As String
    Dim File
    Dim DataRange
    WeDate = Format(Text71, "mm-dd-yyyy")
    '-------------------------------------------------------------------
    Import:
    File = "\\10.1.72.225\DigiDoc\attendance\TuffaloyMfgHours.xlsm"
    DataRange = WeDate & Chr(33) & "A7:I40"
    'MsgBox (WeDate)
    DoCmd.TransferSpreadsheet acLink, 9, _
        "LtblEmployeeTimes", File, False, Range:=DataRange
    '--------------------------------------------------------------------------
    
    DoCmd.SetWarnings False
     DoCmd.Requery
     
    DoCmd.OpenQuery "qryDeleteWeeklyTime", acViewNormal, acEdit ' Clear the current data from the table tblEmployeeTimes
    DoCmd.Close acQuery, "qryDeleteWeeklyTime", acSaveYes
    
    DoCmd.OpenQuery "qryWeeklyTime", acViewNormal, acEdit ' Refresh the data from the table LtblEmployeeTimes to tblEmployeeTimes
    DoCmd.Close acQuery, "qryWeeklyTime", acSaveYes
    
    DoCmd.OpenQuery "qryUpdateNullTime", acViewNormal, acEdit ' update null fields to "0" so formulas work
    DoCmd.Close acQuery, "qryUpdateNullTime", acSaveYes
    
    DoCmd.SetWarnings True
    
    DoCmd.Requery
    
    Command48_Click_Exit:
        Exit Sub
        
    Command48_Click_Err:
            MsgBox ("Please Select A Week Ending Ending Date") ' I doing this so if they pick a date that isn't a week ending date, i'm still trying to figure out how to make it use the week ending date no matter what date they pick
       ' MsgBox Error$
    End Sub

Posting Permissions

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