Results 1 to 6 of 6
  1. #1
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368

    Table not updating

    Hey guys,



    Can anyone tell me what is wrong with this code :

    Code:
    Dim strSQL As String
    Dim strUpdate As String
    
    
    strSQL = "SELECT * FROM tblSelectedPostCodes"
    With CurrentDb.OpenRecordset(strSQL)
    strUpdate = "Update tblObjecten3 set [MailingAangemerkt] = 'Ja' where tblObjecten3.[PostCodeObject] ='" & strSQL & "'"
    DoCmd.RunSQL strUpdate
    End With
    The field in tblObjecten3 called 'MailingAangemerkt' is a yes/No field. I tried everything from Dutch (Ja) to English (Yes) as wel as "True". I have similair sytaxis in my db so that should bot be the problem.

    tblSelectedPostCodes holds only one field named "PostCode" and it holds a bunch of postcodes that also exist in tblObjecten3 of course.

    What am i missing here ?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You have 2 SQls here. Make 1 update SQL, then run the docmd. No need for openrecordset.....

    sSql = "update table..."
    docmd.RunSql sSql

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Your SQL isn't doing what you think it is. The where clause is looking for

    tblObjecten3.[PostCodeObject] ='SELECT * FROM tblSelectedPostCodes' ; in other words, it is treating the SQL Select... as a value, not as SQL.

    It's an easy fix, though.

    Try this:

    Code:
    Dim strSQL As String
    Dim strUpdate As String
    
    
    strSQL = "SELECT *FROM tblSelectedPostCodes"
    strUpdate = "Update tblObjecten3 set [MailingAangemerkt] = 'Ja' where tblObjecten3.[PostCodeObject] IN (" & strSQL & ")"
    DoCmd.RunSQL strUpdate
    End With

  4. #4
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Hey John,

    Thanks for your reply. I tried the code but it does nothing, not even an error ..
    Im seriously wondering whats wrong. Its not rocket science what were asking Access to do here.

    A table (tblSelectedPostCodes) with postcodes, update records with matiching postcodes ion another table (tblObjecten3) that holds the same postcodes..

  5. #5
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Maybe there is another way, but i can't seem to make that work either. Perhaps this is a more direct approach :

    The query that updates tblSelectedPostCodes holds the selected postcodes as well.
    I have a filter that shows records in my form, if i can get Access to get that the selected postcodes should be in the recordset then that would work for me as well :

    Something like this :

    Code:
    Private Sub FilterForm()
    
    
        Dim strFilter As String
        Dim blnFilter As Boolean
        
        blnFilter = False
        
        strFilter = " 1=1 "
        If Me.filterplaatsnaam <> "" Then
            strFilter = strFilter + " and plaatsnaamobject like '" & filterplaatsnaam & "*'"
            blnFilter = True
        End If
        
        If Me.fldFilterObject <> "" Then
            strFilter = strFilter + " and NaamObject like '" & fldFilterObject & "*'"
            blnFilter = True
        End If
        
        If Me.kzlFlterObject <> "" Then
            strFilter = strFilter + " and SoortObject like '" & kzlFlterObject & "*'"
            blnFilter = True
        End If
        
        If Me.FilterPostCode <> "" And Me.FilterStraal <> "" Then
    
        DoCmd.OpenQuery ("Distance")
    
        Dim strSQL As String
    
        strSQL = "Select PC2.Postcode from Distance"
    
            strFilter = strFilter + " and PostCodeObject like (" & strSQL & ")"
            blnFilter = True
    
        End If
    
    
        If Me.filtertrefwoord <> "" Then
            strFilter = strFilter + " and (plaatsnaamobject like '*" & filtertrefwoord & "*'" _
        & "OR NaamObject like '*" & filtertrefwoord & "*'" _
        & "OR SoortObject like '*" & filtertrefwoord & "*'" _
        & "OR ContactpersoonLocatie like '*" & filtertrefwoord & "*'" _
        & "OR AdresObject like '*" & filtertrefwoord & "*' " _
        & "OR PostcodeObject like '*" & filtertrefwoord & "*'" _
        & "OR BijzonderhedenObject like '*" & filtertrefwoord & "*')"
            blnFilter = True
        End If
        
        If blnFilter Then
            Me.Filter = strFilter
        Else
            Me.Filter = "1=2"
        End If
       
        Me.FilterOn = True
    
    
    End Sub
    when i try this im getting "the object forms isnt present" (poorly translated from Dutch)

  6. #6
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Right, i solved it myself with a little workaround.
    For future reference ill explain how i combined my filter and the recordset.

    Code:
     
    'Check if my controls (PostCode and Radius) are not empty
      If Me.FilterPostCode <> "" And Me.FilterStraal <> "" Then
    
    'Open the query that calculates distances from the postCode, FilterStraal limits the outcome of the query    
        DoCmd.OpenQuery ("Distance")
        Dim strSQL As String
    'Insert the PostCodes from the query into a table
        strSQL = "Insert into tblSelectedPostCodes(Postcode) Select PC2.Postcode from Distance"
        DoCmd.RunSQL strSQL
     
    'Select the postcodes and checkmark (MailingTemp) them in the database  
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strPostCode As String
        Dim strSQL2 As String
    
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT DISTINCT PostCode FROM tblSelectedPostCodes", dbOpenSnapshot)
     
    rs.MoveFirst
        While rs.EOF = False
            strSQL2 = ""
            strPostCode = rs![PostCode]    'Get the PostCode
    
    
            strSQL2 = "UPDATE tblObjecten3 SET MailingTemp = True WHERE (([PostCodeObject]) = '" & strPostCode & "');"
            db.Execute strSQL2
            
            rs.MoveNext
        Wend
    End If
    
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    
    'Let Access know you want to filter records
    
    FilterForm
    Next ill filter multiple criteria , amongst wich the checkmarked records from the recordset.

    Code:
        Dim strFilter As String
        Dim blnFilter As Boolean
        
        blnFilter = False
        
        strFilter = " 1=1 "
        If Me.filterplaatsnaam <> "" Then
            strFilter = strFilter + " and plaatsnaamobject like '" & filterplaatsnaam & "*'"
            blnFilter = True
        End If
        
        If Me.fldFilterObject <> "" Then
            strFilter = strFilter + " and NaamObject like '" & fldFilterObject & "*'"
            blnFilter = True
        End If
        
        If Me.kzlFlterObject <> "" Then
            strFilter = strFilter + " and SoortObject like '" & kzlFlterObject & "*'"
            blnFilter = True
        End If
        
        If Me.FilterPostCode <> "" And Me.FilterStraal <> "" Then
        strFilter = strFilter + " and MailingTemp = True"
        blnFilter = True
        End If
    
    
        If Me.filtertrefwoord <> "" Then
            strFilter = strFilter + " and (plaatsnaamobject like '*" & filtertrefwoord & "*'" _
        & "OR NaamObject like '*" & filtertrefwoord & "*'" _
        & "OR SoortObject like '*" & filtertrefwoord & "*'" _
        & "OR ContactpersoonLocatie like '*" & filtertrefwoord & "*'" _
        & "OR AdresObject like '*" & filtertrefwoord & "*' " _
        & "OR PostcodeObject like '*" & filtertrefwoord & "*'" _
        & "OR BijzonderhedenObject like '*" & filtertrefwoord & "*')"
            blnFilter = True
        End If
        
        If blnFilter Then
            Me.Filter = strFilter
        Else
            Me.Filter = "1=2"
        End If
       
        Me.FilterOn = True
    This way i can filter and combine basicly everything : Sort, City, Adres, Contactname, and radius from postcode. (using GreatCircle module found online)

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

Similar Threads

  1. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  2. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  3. Replies: 2
    Last Post: 05-19-2013, 07:22 PM
  4. Replies: 2
    Last Post: 03-13-2013, 06:30 AM
  5. Replies: 6
    Last Post: 05-10-2012, 08:20 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