Results 1 to 11 of 11
  1. #1
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25

    Using a string to DIM a Recordset

    Hi,

    I would like to DIM a Recordset using a string to provide the table name. Table for the recordset varies depending on users choice from a listbox

    eg
    strAccountID = Me.lbHeader.Column (1)
    tbName = "tb" & strAccountID

    Then I would like to use the tbName string to
    Dim tbName As DAO.Database
    Dim tbName As DAO.Recordset

    But does not work

    have tried

    Dim " & tbName & " As DAO.Database etc but does not work either.

    Any help very much appreciated

    Colin

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear what you're trying to achieve. It would make more sense to use the variable to determine which table the recordset was based on, not the name of the variable.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25
    Hi Paul,
    I am building a program to help with our budgeting process. I create a table for each account using by building the table name as "tb" & "the account number"

    Then on the next tab I want to be able to add, edit or delete items to the account tables.

    Using a combo box I select the account number to work on and using an sql string to define a listbox rowsource which list the items in that particular account.

    My problem is setting a recordset to allow me to add, edit etc as the table name needs to be constructed from the same way as the table was created ie tbName = "tb" & "the account number"


    Option Compare Database
    Public strAction, tbName As String
    Public intHeaderID As Integer


    Private Sub Form_Open(Cancel As Integer)
    Dim WhatsDate As Date
    Dim strYear, strMonth, strPrev, strCurr, strNext As String

    WhatsDate = Date
    strYear = Year(WhatsDate)
    strMonth = Month(WhatsDate)

    If strMonth <= 3 Then
    strYear = strYear - 1
    strPrev = (strYear - 1) & "/" & (Right(strYear, 2))
    strCurr = strYear & "/" & (Right((strYear + 1), 2))
    strNext = (strYear + 1) & "/" & (Right((strYear + 2), 2))
    Else
    strPrev = (strYear - 1) & "/" & (Right(strYear, 2))
    strCurr = strYear & "/" & (Right((strYear + 1), 2))
    strNext = (strYear + 1) & "/" & (Right((strYear + 2), 2))
    End If
    Me.laPreBudget.Caption = strPrev
    Me.laPreActual.Caption = strPrev
    Me.laThisBudget.Caption = strCurr
    Me.laThisForecast.Caption = strCurr
    Me.laNextBudget.Caption = strNext

    End Sub

    Private Sub coAddNew_Click()
    On Error GoTo Err_coAddNew_Click

    txNAccountNo = Null
    txNDescription = Null
    txPrevBud = 0
    txPrevAct = 0
    txCurrBud = 0
    txCurrFor = 0
    txNextBud = 0
    MsgBox "Enter Account Information and Click UpDate Records"
    strAction = "Add"
    txNAccountNo.SetFocus

    Exit_coAddNew_Click:
    Exit Sub

    Err_coAddNew_Click:
    MsgBox Err.Description
    Resume Exit_coAddNew_Click

    End Sub

    Private Sub coEditAcc_Click()
    On Error GoTo Err_coEditAcc_Click

    MsgBox ("Description and Amounts Can Be Changed" & vbCrLf & "If Account Number is Incorrect" & vbCrLf & "Use Delete and Re-enter")
    strAction = "Edit"
    txNAccountNo.Locked = True
    txNextYrBudget.Locked = True

    Exit_coEditAcc_Click:
    Exit Sub

    Err_coEditAcc_Click:
    MsgBox Err.Description
    Resume Exit_coEditAcc_Click

    End Sub

    Private Sub coDeleteAcc_Click()
    On Error GoTo Err_coDeleteAcc_Click

    MsgBox "Click On Account To Delete, Then Click Update Records"
    strAction = "Delete"

    Exit_coDeleteAcc_Click:
    Exit Sub

    Err_coDeleteAcc_Click:
    MsgBox Err.Description
    Resume Exit_coDeleteAcc_Click

    End Sub

    Private Sub coUpDate_Click()
    On Error GoTo Err_coUpDate_Click
    Dim strTableName As String

    Dim tbHeader As DAO.Database
    Dim rstHeader As DAO.Recordset

    Set tbHeader = CurrentDb
    Set rstHeader = tbHeader.OpenRecordset("tbHeader", dbOpenDynaset)
    Select Case strAction
    Case "Add"
    If IsNull(txNAccountNo) Or IsNull(txNDescription) Then
    MsgBox "You Must Enter An Account Number && Description"
    txNAccountNo.SetFocus
    Exit Sub
    Else
    rstHeader.FindFirst "[HAccountNo] = '" & txNAccountNo & "'"
    If rstHeader.NoMatch Then
    With rstHeader
    .AddNew
    !HAccountNo = txNAccountNo
    !HAccDescription = txNDescription
    !CurrentBudget = txCurrBud
    !CurrentForecast = txCurrFor
    !PreviousBudget = txPrevBud
    !PreviousActual = txPrevAct
    !NextYrBudget = txNextBud
    .Update
    .Bookmark = .LastModified
    End With
    strTableName = "tb" & txNAccountNo
    DoCmd.SetWarnings False

    DoCmd.RunSQL "Create Table " & strTableName & " ([AccountID] counter," & _
    "[AccountNo] text(7)," & _
    "[AccountDesc] text(45)," & _
    "[ItemDesc] text(50)," & _
    "[ItemCost] Integer," & _
    "[SpecMonth] text(3));"

    DoCmd.RunSQL "INSERT INTO " & strTableName & " ([AccountNo],[AccountDesc])" & _
    " Values(txNAccountNo, txNDescription);"

    DoCmd.SetWarnings True
    Else
    If MsgBox("Account Number Already Exists, OK To Enter New Number or Cancel To Exit", vbOKCancel, "Duplicate Account") = vbOK Then
    txNAccountNo = ""
    txNAccountNo.SetFocus
    Else
    Exit Sub
    End If
    End If
    End If

    Case "Edit"
    rstHeader.FindFirst "[HeaderID] = " & intHeaderID & ""
    If rstHeader.NoMatch Then
    MsgBox "Record Not Found"
    Else
    With rstHeader
    .Edit
    !HAccDescription = txNDescription
    !CurrentBudget = txCurrBud
    !CurrentForecast = txCurrFor
    !PreviousBudget = txPrevBud
    !PreviousActual = txPrevAct
    !NextYrBudget = txNextBud
    .Update
    .Bookmark = .LastModified
    End With
    txNAccountNo = Null
    txNDescription = Null
    txPrevBud = 0
    txPrevAct = 0
    txCurrBud = 0
    txCurrFor = 0
    txNextBud = 0
    End If

    Case "Delete"
    rstHeader.FindFirst "[HeaderID] = " & intHeaderID & ""
    If rstHeader.NoMatch Then
    MsgBox "Record Not Found"
    Else
    With rstHeader
    .Delete
    intHeaderID = 0
    End With
    End If
    strTableName = "tb" & txNAccountNo
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DROP TABLE " & strTableName & ";"
    DoCmd.SetWarnings True

    End Select

    strAction = ""
    rstHeader.Close
    Set rstHeader = Nothing
    Set tbHeader = Nothing
    Me.lbHeader.Requery

    Exit_coUpDate_Click:
    Exit Sub

    Err_coUpDate_Click:
    MsgBox Err.Description
    Resume Exit_coUpDate_Click

    End Sub


    Private Sub lbHeader_Click()

    intHeaderID = Me.lbHeader.Column(0)
    txNAccountNo = Me.lbHeader.Column(1)
    txNDescription = Me.lbHeader.Column(2)
    txPrevBud = Me.lbHeader.Column(3)
    txPrevAct = Me.lbHeader.Column(4)
    txCurrBud = Me.lbHeader.Column(5)
    txCurrFor = Me.lbHeader.Column(6)
    txNextBud = Me.lbHeader.Column(7)
    If txPrevBud = "" Then
    txPrevBud = 0
    End If
    If txPrevAct = "" Then
    txPrevAct = 0
    End If
    If txCurrBud = "" Then
    txCurrBud = 0
    End If
    If txCurrFor = "" Then
    txCurrFor = 0
    End If
    If txNextBud = "" Then
    txNextBud = 0
    End If

    End Sub

    Private Sub cbAccountID_Click()
    strAccountID = Me.cbAccountID.Column(1)
    strWhichTable = "tb" & strAccountID
    tbName = strWhichTable
    Dim strSQL As String

    strSQL = "SELECT [" & tbName & "].[AccountID], " & _
    " [" & tbName & "].[AccountNo], " & _
    " [" & tbName & "].[AccountDesc], " & _
    " [" & tbName & "].[ItemDesc], " & _
    " [" & tbName & "].[ItemCost], " & _
    " [" & tbName & "].[SpecMonth] " & _
    " FROM [" & tbName & "]" & _
    " ORDER BY [" & tbName & "].[ItemDesc];"

    Me.lbAccountItems.RowSource = strSQL
    Me.lbAccountItems.Requery

    End Sub

    Private Sub lbAccountItems_Click()
    intAccountID = Me.lbAccountItems.Column(0)
    txItemDescription = Me.lbAccountItems.Column(3)
    txItemCost = Me.lbAccountItems.Column(4)
    cbMonth = Me.lbAccountItems.Column(5)

    End Sub
    Private Sub coAddItem_Click()
    On Error GoTo Err_coAddItem_Click

    strAction = "Add"


    Exit_coAddItem_Click:
    Exit Sub

    Err_coAddItem_Click:
    MsgBox Err.Description
    Resume Exit_coAddItem_Click

    End Sub
    Private Sub coEditItem_Click()
    On Error GoTo Err_coEditItem_Click

    strAction = "Edit"


    Exit_coEditItem_Click:
    Exit Sub

    Err_coEditItem_Click:
    MsgBox Err.Description
    Resume Exit_coEditItem_Click

    End Sub
    Private Sub coDeleteItem_Click()
    On Error GoTo Err_coDeleteItem_Click

    strAction = "Delete"


    Exit_coDeleteItem_Click:
    Exit Sub

    Err_coDeleteItem_Click:
    MsgBox Err.Description
    Resume Exit_coDeleteItem_Click

    End Sub
    Private Sub coUpDateItems_Click()
    On Error GoTo Err_coUpDateItems_Click

    'Dim tbName As DAO.Recordset This is where I am having trouble
    'Dim tbName As DAO.Recordset
    'Set tbName = CurrentDb
    'Set rsttbName = tbName.OpenRecordset("tbName", dbOpenDynaset)

    Select Case strAction

    Case "Add"


    Case "Edit"


    Case "Delete"


    End Select

    Exit_coUpDateItems_Click:
    Exit Sub

    Err_coUpDateItems_Click:
    MsgBox Err.Description
    Resume Exit_coUpDateItems_Click

    End Sub

  4. #4
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    Code:
    Private Sub DoSomething()
         Dim rs As DAO.Recordset
         Dim strAccountID As String
    
         strAccountID = Me.SomeControl.Value
      
         Set rs = CurrentDB.OpenRecordset("SELECT * FROM tb" & strAccountID & ";"
         'Do something else
    End Sub
    Question: What is AccountID and why is there a table for every AccountID? Are you creating a general ledger system? At first glance of your post, I think your database lacks in the area of normalization.

    Jan

  5. #5
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    Oh, your reply came ahead of mine. I guess you have this post already solved. Good luck.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And why a table for each account? That violates normalization rules and will be a nightmare to maintain.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25
    Depending on which department is using the system there will be between 20 to 30 account numbers. So in some ways it is like a GL
    Each account table then holds a list of items and associated costs that are then totalled to give a year to date budget amount for each account number.

    The list of items in each table could vary from 1 to a 100 or more.

    Only learning access as I go along so not sure how else to keep each account details separate and easily updated etc.

    Thanks for your help

  8. #8
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    Quote Originally Posted by pbaldy View Post
    And why a table for each account? That violates normalization rules and will be a nightmare to maintain.
    Paul,

    Indeed it does, but I ran across a company the other day that had to maintain separate spreadsheets for each commercial customer they had because the billing procedures were so complex.

    First thing I thought of when I heard that was I'm glad they weren't trying to do that with Access!

  9. #9
    evander is offline Competent Performer
    Windows 7 Access 2003
    Join Date
    Apr 2010
    Location
    Philippines
    Posts
    206
    Just a piece of advice coming from an accountant who does programming for a hobby.
    Usually, a company's management would ask a programmer to create a system, hoping that the system will solve all the process deficiencies in that company.

    As a matter of personal taste, I prefer fixing the company's internal controls first --- that is, processes, policies, documentation, and even accounting knowledge of end-users. IT people call this "business logic" or "business rules." Internal controls have the greatest impact on database design, which an unwary programmer might overlook. Actually, it is not the programmer's responsibility to look into internal controls. The company's employees are presumably experts in their own tasks. But of course, most of the time the programmer is obligated to develop a system that is more intelligent than its intended end-users.

    As for you Colpat, your end-users think that they have explained themselves clearly, when in fact they merely explained what gets printed on paper. Make sure that they understand their own policies and procedures. Try creating a document flowchart and let them review it many times. I can also review the flowchart if you permit me to do so.

    Good luck.

    Jan (Joe)

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by ColPat View Post
    Only learning access as I go along so not sure how else to keep each account details separate and easily updated etc.
    It would help to see what the data looks like, but generally one table with a field for the account. That lets you query one account or many simply by changing query criteria.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    ColPat is offline Novice
    Windows XP Access 2003
    Join Date
    May 2010
    Posts
    25
    Thanks Jan,
    I am a Hotel/Resort Chief Engineer and am writing the program, as a hobby, to make the preparation of my departments annual budget easier for my staff. Currently we use excel with heaps of linked pages/cells etc and it becomes messy for the line staff to follow plus a real pain each year to clear contents and start again.
    By doing it in access and setting up a table for each account the staff then enter a description and a cost for each budget item as they think of it. A process that goes for 3 - 3 months. I can then review their input and either edit, delete or accept their input. At the end of the day I present a total for each account to the FC as the Engineering budget.
    Being an Engineer and someone who enjoys the logic of programming I am always looking at ways to make a program better - like in this program learnt how to create tables by code when maybe I should have just used one table and then used a query to pull the required data from it.

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

Similar Threads

  1. Getting Values out of a recordset
    By theracer06 in forum Programming
    Replies: 6
    Last Post: 09-24-2010, 03:44 PM
  2. ADO Recordset.Open (SQL) does nothing
    By workindan in forum Programming
    Replies: 3
    Last Post: 06-23-2010, 02:07 PM
  3. Replies: 1
    Last Post: 11-13-2009, 03:03 AM
  4. Replies: 1
    Last Post: 07-31-2009, 03:57 AM
  5. ADO.Recordset - Write
    By Marvin in forum Programming
    Replies: 0
    Last Post: 07-17-2009, 07:56 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