Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26
  1. #16
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here are a couple more inline query examples that I in code - not exactly the same each time but close
    I generally use the Single-record append query, not Multiple-record append queries.

    1) bNo_401k - this is a boolean. lENDPK is a long integer that is the PK of an employee.
    Code:
       sSQL = "UPDATE employee_name_def SET employee_name_def.end_No_401k = " & bNo_401k
       sSQL = sSQL & " WHERE (((employee_name_def.end_pk)=" & lENDPK & "));"
    
       d.Execute sSQL, dbFailOnError


    2) Here the "v" prefix just lets me know it is a variable. "Me" refers to a control on a form.


    Code:
       vEPDFK = Me.hbh_epdfk
       vFringe = Me.hbh_Fringe
    
                   vBegDate = DateAdd("d", 1, vEffectiveDate)
                   vEndDate = DateAdd("yyyy", 50, vBegDate) - 1
    
                   sSQL = "INSERT INTO employee_fringe_def ( efd_epdfk, efd_fringe, efd_BegDate, efd_Enddate )"
                   sSQL = sSQL & " VALUES ( " & vEPDFK & ", " & vFringe & ", #" & vBegDate & "#, #" & vEndDate & "#)"
                   '               Debug.Print sSQL
                   db.Execute sSQL, dbFailOnError

  2. #17
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by pbaldy View Post
    Using Eval() is simplest at this point since all you have to do is wrap the form references in it. Creating an SQL string and executing it in VBA is more dynamic but can be tricky. This may help if you go that way:

    http://www.baldyweb.com/BuildSQL.htm

    In your original code you'd only get the count for the second execute (after changing to use the dbs variable as someone pointed out). You can either use 2 variables to get the counts for each or accumulate the 2 counts into a single variable.
    Thank you for the tip. After thinking about it over dinner I think I would really only need reporting on rows affected for the INSERT query - the UPDATE one adds a value to the table the referenced form pulls from, so the user will see that result once the whole thing is done and requeried.

    Once the insomnia kicks in I will start writing this out and I will post what I cobble together and share the results. Again, thank everyone for this, it's much appreciated!

  3. #18
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Good morning everyone! I studied a lot of what was shared as solutions and put together the code below. I am running into the parameters issue because the first SQL refers to a control on the open form and, after reading a little I'm not sure how to apply the Eval() function to eliminate this, but maybe I have misunderstood the advice given? The sub stops at the first SQL statement so I am guessing that I will have to treat both the same way.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngType As Long
    Dim strSql1 As String
    
    
    lngType = 1
    
    
    strSql1 = "INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, " & lngType & " AS TransTypeID FROM tblPOLines WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]))"
    
    
    Dim strSql2 As String
    
    
    strSql2 = "UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date()WHERE ((([tblPOLines]![POLineID])=[Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]))"
    Set dbs = CurrentDb()
    
    
    dbs.Execute strSql1, dbFailOnError
    
    
    dbs.Execute strSql2, dbFailOnError
    
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records have been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    End Sub

  4. #19
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could try this. I have commented out the db.execute statements. The intent is to see if the SQL now renders the Form info.
    The resultant values of strSQL1 and 2 should be shown in the immediate window.
    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngType As Long
    Dim strSql1 As String
    
    
    lngType = 1
    
    strSql1 = "INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, " & lngType & " AS TransTypeID FROM tblPOLines WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))"
    
    
    Dim strSql2 As String
    
    
    strSql2 = "UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date() WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))"
    Set dbs = CurrentDb()
    
    debug.print "strSQL1 " & vbcrlf & strSQL1
    'dbs.Execute strSql1, dbFailOnError
    
    debug.print "strSQL2 " & vbcrlf & strSQL2
    'dbs.Execute strSql2, dbFailOnError
    
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records have been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    End Sub

  5. #20
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You don't need the Eval() function if you're building the SQL in code, just concatenate the value as you did with lngType:

    "...WHERE [tblPOLines]![POLineID]= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]

    Adding delimiters as necessary for text and date/time data types as noted in the link I posted earlier.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #21
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by pbaldy View Post
    You don't need the Eval() function if you're building the SQL in code, just concatenate the value as you did with lngType:

    "...WHERE [tblPOLines]![POLineID]= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]

    Adding delimiters as necessary for text and date/time data types as noted in the link I posted earlier.
    Thank you for the link, by the way. I forced myself to study everything that people shared, I tend to suck at studying, but I want to give credit where it is due, I did use your suggestion

  7. #22
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem! Did you get your code working?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #23
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by orange View Post
    You could try this. I have commented out the db.execute statements. The intent is to see if the SQL now renders the Form info.
    The resultant values of strSQL1 and 2 should be shown in the immediate window.
    I ran this through, when I run it from the command I get a dialogue say '0 records have been processed', which is great, that part is obviously working, and the immediate window displays the following:
    Code:
    strSQL1 INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, 1 AS TransTypeID FROM tblPOLines WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))
    strSQL2 
    UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date() WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))
    strSQL1 
    INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, 1 AS TransTypeID FROM tblPOLines WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))
    strSQL2 
    UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date() WHERE ((([tblPOLines]![POLineID])= Eval([Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID])))
    If I am understanding things correctly here the value in the referenced control is supposed to show up where the Eval() function is in each string, right? Also, is it supposed to show each statement twice? I see that it rendered lngType as 1, which is correct also.

    I will also try out the concatenation suggestion from pbaldy as well - I usually only learn new things when I have to, so I am trying to broaden the experience a bit here.

    Thank you so much for helping with this!

  9. #24
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by pbaldy View Post
    No problem! Did you get your code working?
    Yes, after trying the suggested implementation of the Eval function Orange provided I removed the debug portion and concatenated the form reference. I did get an error that was to do with the parentheses, but the error text (Which I did not save) referred to a lineID which matched the record that was open on the form, so I know I was close. After playing with the parentheses which the concatenation messed up I ended up with the code below. I tested it out and it worked! Hooray! I've been at this for two weeks and all that studying paid off for a change. I'll forget everything in another two weeks!
    I do need to add a form requery to this so that the form refreshes after the messagebox is acknowledged, but I checked the tables and everything appears to be writing in the right places.

    I really do appreciate everyone's input on this, and I'll come back and post the final code with the form requery in it once I work that out (I know it's simple compared to this piece, but it's all new to me), so hopefully what I've put into my posts will help others in the future

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngType As Long
    Dim strSql1 As String
    
    
    
    
    lngType = 1
    
    
    strSql1 = "INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, " & lngType & " AS TransTypeID FROM tblPOLines WHERE (([tblPOLines]![POLineID]))= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]
    
    
    
    
    Dim strSql2 As String
    
    
    
    
    strSql2 = "UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date() WHERE (([tblPOLines]![POLineID]))= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]
    Set dbs = CurrentDb()
    
    
    dbs.Execute strSql1, dbFailOnError
    
    
    dbs.Execute strSql2, dbFailOnError
    
    
    
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records have been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    End Sub

  10. #25
    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 khayward View Post
    If I am understanding things correctly here the value in the referenced control is supposed to show up where the Eval() function is in each string, right?
    Not as you have it, no. If you were concatenating yes. At this point it's inside the string so it's just text. As it is would get evaluated by the database engine when you ran it, concatenated it would get evaluated before being sent to the engine. That's why you don't need Eval() if concatenating.
    Last edited by pbaldy; 01-16-2020 at 10:23 AM. Reason: fix grammar goof
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #26
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Here is the final code, thank you again to everyone for your help!

    Code:
    Option Compare DatabaseOption Explicit
    
    
    
    
    Private Sub cmdRCVLine_Click()
    Dim dbs As DAO.Database
    Dim lngType As Long
    Dim strSql1 As String
    
    
    
    
    lngType = 1
    
    
    strSql1 = "INSERT INTO tblTransactions (MaterialID, TransactionQTY, TransactionTypeID )SELECT tblPOLines.MaterialID, tblPOLines.POLineQTY, " & lngType & " AS TransTypeID FROM tblPOLines WHERE (([tblPOLines]![POLineID]))= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]
    
    
    
    
    Dim strSql2 As String
    
    
    
    
    strSql2 = "UPDATE tblPOLines SET tblPOLines.POLineClosedDate = Date() WHERE (([tblPOLines]![POLineID]))= " & [Forms]![frmPurchaseOrders]![subfrmSOLines].[Form]![POLineID]
    Set dbs = CurrentDb()
    
    
    dbs.Execute strSql1, dbFailOnError
    
    
    dbs.Execute strSql2, dbFailOnError
    
    
    
    
    '-------------------Message Affected ----------------
    MsgBox CStr(dbs.RecordsAffected) & " records have been Processed ", vbOKOnly + vbInformation, "Affected Rows"
    End Sub
    
    
    Public Sub RequeryList()
     
        Dim ctlCombo As Control
     
        Set ctlCombo = Forms!frmPurchaseOrders!subfrmSOLines.Form!POLineClosedDate
     
        ctlCombo.Requery
     
    End Sub

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

Similar Threads

  1. Trouble running a Currentdb.Execute Command
    By DaKetch in forum Access
    Replies: 9
    Last Post: 03-18-2019, 10:08 AM
  2. RunSQL vs. QueryDef.Execute vs. CurrentDB.Execute
    By caubetiep1337 in forum Queries
    Replies: 8
    Last Post: 12-16-2015, 05:35 PM
  3. Error in CurrentDb.Execute
    By Stefan Moser in forum Access
    Replies: 5
    Last Post: 04-17-2015, 01:38 PM
  4. vba code help - Currentdb.execute
    By joycesolomon in forum Programming
    Replies: 5
    Last Post: 08-11-2014, 11:25 AM
  5. Update sql using currentdb.execute
    By laavista in forum Access
    Replies: 13
    Last Post: 08-15-2011, 03:51 PM

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