Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Adding SQL Code to VBA

  1. #1
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46

    Adding SQL Code to VBA

    Good Morning,

    Here is my situation. I have a created a database that will allow a user to enter information on a form once that information has been entered the save button is clicked. After the save button has been clicked the user is allowed to Review Report once that button has been clicked the user will be able to e-mail the report to the correct firm. However some of the things are not working correctly and I need some assistance. I think the my main problem is I wrote a query to create a TempReport_Table using my main Table TrackingSystem3 and in oder for me to populate information in the TempReport_Table i have to double click the TrackingSystem3 Query in order for that to work. Here is the query that I am trying to use
    SELECT TrackingSystem3.FirmNumber, TrackingSystem3.NumberofAccounts, TrackingSystem3.EmployeeID, TrackingSystem3.ErrorCodeDescription, TrackingSystem3.ErrorCodesandCorrections, TrackingSystem3.Agreements, TrackingSystem3.TypeofCommunication, TrackingSystem3.Time, TrackingSystem3.[Opened Date] INTO TempReport_Table
    FROM TrackingSystem3
    WHERE (((TrackingSystem3.Time)=(select Max (Time) from TrackingSystem3)));

    If i had a clue on where to add this query in my current VBA code so the TempReport_Table is populated automaticaly i would be a very happy person but at this point i am very confused and I need some assistance. This is just one of 3 issues that i am dealing with right know if i could get some assisatnce on this one i could deal with the other 2 over the long weekend. Again I am very new to VBA and anything that someone could do to assist me would be appericated. I have attached a copy of my database and again...Thank You so much.....
    Attached Files Attached Files

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    You should be able to execute it with:

    CurrentDb.Execute "QueryName"

    At the appropriate point in your process. Why the temp table though? Typically a query is all you need.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    Thanks PBaldy For the quick response and I appericate it. The reason why i added the TempReport_Table was in my reading on this matter alot of forums stated that a temp table would be the best way to handle the situation. I have several user using this database at one time and they stated that you should use a temp table based on Max Time so that you can determine the current record for that user.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    That wouldn't seem reliable in a multi-user environment. I'd grab the ID value of the record when it's first created and query based on that. A make table query would be my last option. If a temp table was necessary, I'd empty/fill a static table, not create a new one every time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    This may seem like a dumb question and remember I am new to VBA but how would i create a empty/fill Static Table and call it TempReport_Table?

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    Again, I would likely NOT use a temp table at all, but you would create the table once, then run a delete query to empty it and an append query to fill it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    So can that logic be done in the above written query that i already have and if yes can you show me or give me an example how its done.

  8. #8
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    Also i dont know if this makes a difference or not but I already have a permanent Table were all information from the form is posted. The permanent table is called TrackingSystem3 Table.

  9. #9
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    I think i found something will let you see when i finish what I have.

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    For the record, I wouldn't use a temp table, I'd simply query the permanent table with a parameter (like your make table query does). I don't see what the temp table gains you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    What do you think about this? i used two parameter to make sure the the user only pulls there information(I think) and also removed the into TempReport_Table stuff.

    So you are saying run this query in the appropriate location without the insert into TempReport_Table and this should be fine? However I still have two questions?
    I am trying to decide the appropriate location (Private Sub Save_Record_Click()) to add the SQL statement and with me adding the SQL statement to VBA code is there anything special that I need to add to make sure it works? or would you suggest some other location to add the SQL code
    And will this code only allow the multiple users to see their most current record that they created only.

    SELECT TrackingSystem3.FirmNumber, TrackingSystem3.NumberofAccounts, TrackingSystem3.EmployeeID, TrackingSystem3.ErrorCodeDescription, TrackingSystem3.ErrorCodesandCorrections, TrackingSystem3.Agreements, TrackingSystem3.TypeofCommunication, TrackingSystem3.Time, TrackingSystem3.[Opened Date]
    FROM TrackingSystem3
    WHERE (((TrackingSystem3.Time)=(select Max (Time) from TrackingSystem3)))
    and
    (((TrackingSystem3.NumberofAccounts)=(select Max (NumberofAccounts) from TrackingSystem3)));;

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    Like I said, I'd grab the ID of the record when it's created. Relying on the most recent time in my view is subject to problems in a multi-user environment. What if I create a new record and an instant later somebody else does too? I could get their record instead of mine. If you had some sort of user field, that type of thing could work, but why not use the unique ID of the record? At the point your save button is clicked you should be able to get it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    REAbernathy is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Posts
    46
    Trying to run this code and I keep getting Line Number or label or Statement or end of statement and it only happens on the word AND.....What I am doing wrong


    strSQL = "SELECT FirmNumber, NumberofAccounts, EmployeeID, ErrorCodeDescription, "
    ErrorCodesandCorrections , Agreements, TypeofCommunication, Time, ""
    [Opened Date]
    FROM TrackingSystem3
    WHERE Time = "SELECT Max (Time) from TrackingSystem3,"
    And
    NumberofAccounts = "SELECT Max (NumberofAccounts) from TrackingSystem3; "

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    Not sure why you're trying to do it in VBA, but you have to build one string. You started one, then you have a few lines out in the middle of nowhere. Since the SQL is not dynamic, I wouldn't do it in VBA.

    By the way, "Time" is not a good field name. Sooner or later, Access is going to confuse it with the Time() function. Others:

    Reserved Words in Microsoft Access
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,153
    Since I happen to be working on this application, here's an example of building SQL in VBA (even though I don't think you should here):

    Code:
        strSQL = "SELECT TOP 1 DORDate FROM vueOdoReadings " _
               & "WHERE CarNumber = " & ctl.Column(0, varItem) & " AND " _
               & "DORDate >= #" & Me.txtFromDate & "# " _
               & "ORDER BY DORDate"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need VBA code for adding to Outlook calendar
    By geraldk in forum Programming
    Replies: 3
    Last Post: 08-24-2012, 08:38 AM
  2. Adding multiple SQL Statment to code
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 06-20-2012, 01:22 PM
  3. Adding values to a combobox with code
    By jle0003 in forum Access
    Replies: 4
    Last Post: 05-23-2012, 02:15 PM
  4. Adding a field to a table with vba-code
    By Mgomp in forum Programming
    Replies: 4
    Last Post: 03-12-2012, 07:58 AM
  5. VBA code for adding records to a table
    By pwalter83 in forum Forms
    Replies: 3
    Last Post: 12-21-2011, 10:52 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
  •  
Tech Forums: Microsoft Office Forums