Results 1 to 8 of 8
  1. #1
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171

    Trouble inserting a text into a query

    I have created a query that will be exported to an excel worksheet. on occasion the query has no records. In that case I want to insert a text message into the query so that the recipient of the spreadsheet knows there are no records.
    The code I am using, which does not work is as follows:

    Dim varCheckCount As Integer
    varCheckCount = DCount("[LastName]", "QmatQselBenefitsToTblPermBenefits")
    If varCheckCount = 0 Then
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim strSQL As String
    strSQL = "INSERT INTO QmatQselBenefitsToTblPermBenefits (LastName)" & "VALUES ('No Additions To Report This Month');"
    db.Execute strSQL
    MsgBox "No additions to report this month"

    Else
    MsgBox "These volunteers will be added to the Abenity eligiblity list"
    End If

    DoCmd.RunSavedImportExport "Export-SoinAbenityAdditions"

    Any help appreciated

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,222
    dont use vb code for sql,
    just use a query.

    but:
    strSQL = "INSERT INTO QmatQselBenefitsToTblPermBenefits (LastName) VALUES ('No Additions To Report This Month')"

  3. #3
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,250
    Assumimg QmatQselBenefitsToTblPermBenefits is a query, you can't insert anything into it.

    You could probably more easily ensure that the query returns a different result if it has no records.

    We'd need to see the original query to assist with that though.

    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 ↓↓

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,739
    If you're going to SET something, then be sure to un-set it (SET db = Nothing) when you're done with it. Any object that you SET to a variable occupies machine memory until something happens to release it. Simple procedures don't even need the additional code if you want to avoid SETing anything, and don't need the Else part if the logic dictates that only one other route will be taken if the first is not true:

    Code:
    If DCount("[LastName]", "QmatQselBenefitsToTblPermBenefits") = 0 Then
     CurrentDb.Execute "INSERT INTO QmatQselBenefitsToTblPermBenefits (LastName) VALUES ('No Additions To Report')", dbFailOnError
     MsgBox "No additions to report this month"
    End If
    
    MsgBox "These volunteers will be added to the Abenity eligiblity list"
    
    rest of code...
    Please use code tags and indentation in your posts for more than a couple of lines.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Micron, that code still does not work. How about Minty Hackers comment that you cant insert anything into a query?
    Ranman, how would I use a query to insert the text into another query?

  6. #6
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    Thanks guys. Problem solved. I made the query a maketable query and inserted the message into the table, then exported the table.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    10,739
    Tip: don't just say provided code doesn't work - post your attempt. Easy to leave off one little character, or make a change that you think should work but we might recognize as problematic. As far as updating a query with an append query, I'd have to take his/her comment on faith; can't say I have ever tried. I would have thought the error would have been "Must use an updatable query" or something like that. I took your post title to mean the insert needed to be in a vba sql (query) but it looks like Minty got it right - the target was some other query, not a table.

    What happens next time, delete the table and recreate it every time? Not a great idea; contributes to bloat and/or corruption. If you want to pursue it further you'll need to provide more information as to what you start with and where you want to go 'cause now you're exporting the resulting data.

  8. #8
    Minty is offline VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,250
    Just to qualify what I said/meant, you can only insert to a table, but obviously that insert can use a query joined to another tables or query to base the insert on.
    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 ↓↓

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

Similar Threads

  1. having trouble making a text box equal to a query result
    By NightWalker in forum Programming
    Replies: 16
    Last Post: 02-14-2017, 05:33 PM
  2. Replies: 4
    Last Post: 07-14-2015, 11:41 AM
  3. Inserting text box between two labels
    By BatmanMR287 in forum Reports
    Replies: 4
    Last Post: 07-06-2015, 01:45 PM
  4. Inserting Data into fixed text
    By Earthmover in forum Access
    Replies: 6
    Last Post: 02-09-2012, 11:12 PM
  5. Inserting text from a function
    By Mclaren in forum Programming
    Replies: 3
    Last Post: 03-08-2011, 01:29 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