Results 1 to 5 of 5
  1. #1
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239

    VBA for search form - SQL wrong ?

    Hi,

    I have designed a search form for displaying related records, where user enters keyword in Textbox, and then another form opens which has Subform with datasheet view from desired table.



    I want to display only records that has text string same as keyword, but my SQL statement doesn't work - all records are displayed in subform. Here's my code:
    Code:
    Private Sub CommandSearch_Click()
    
    Dim SearchString As String
    Dim Statement As String
    
    
        SearchString = Me.Text361.Value
        DoCmd.OpenForm "MyFormWithSubform", acNormal
        Task = "SELECT * FROM MyTable WHERE ((MyTableField LIKE ""*" & SearchString & "*""))"
       Me.RecordSource = Task
     
    End If
    
    End Sub
    Where am I wrong, some design problems with subform ?

  2. #2
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    I figured out, nightmare . I had to create recordsource of my subform to desired table (didn't have this before). Then I had to change my code to:

    Code:
    Private Sub CommandSearch_Click()
    
    Dim SearchString As String
    Dim Statement As String
    
    
        SearchString = Me.Text361.Value
        DoCmd.OpenForm "MyFormWithSubform", acNormal
        Task = "SELECT * FROM MyTable WHERE ((MyTableField LIKE ""*" & SearchString & "*""))"
       Forms![MyForm]![MySubform].Form.RecordSource = Task
     
    End If
    
    End Sub

  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    no need to go the long way round via the forms collection - this is all you need

    [MySubform].Form.RecordSource = Task

    Ignore - hadn't realised the subform is in a different form

  4. #4
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Yes, It is on another form. What about passing combobox value to SQL ? This doesn't work for me :

    Code:
    Dim SearchString As String
    Dim Statement As String
    
    
        SearchString = Me.Combo355.Value
        DoCmd.OpenForm "MyFormWithSubform", acNormal
        Task = "SELECT * FROM MyTable WHERE MyTableField=SearchString"
       Forms![MyForm]![MySubform].Form.RecordSource = Task
     
    End If
    
    End Sub

  5. #5
    Lukael is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    239
    Sorry, figured that out too

    Code:
    Task = "SELECT * FROM APO WHERE SiFra_Dobavitelj =[Forms]![APO]![Combo355]"
    Now I'm closing this thread !!

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

Similar Threads

  1. Form Calculation is wrong!
    By Lou_Reed in forum Access
    Replies: 32
    Last Post: 08-04-2015, 02:16 PM
  2. Search form getting wrong results
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 08-22-2013, 06:32 PM
  3. Can not refresh a form - what am I doing wrong?
    By RedGoneWILD in forum Forms
    Replies: 9
    Last Post: 06-26-2012, 04:53 PM
  4. Replies: 1
    Last Post: 04-20-2012, 03:16 AM
  5. Cant see where its wrong - VBA wildcard search.
    By shabbaranks in forum Programming
    Replies: 3
    Last Post: 03-22-2012, 03: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