Subform data not right... 1

[jpcurrie]

Hi all,

I don't do this stuff much anymore but I have had a database in which I wrote a long time ago.

Anyways I wrote some vb (with the help of this site) that creates a sring and sql based on search criteria and then sets the recordset of the subform to the results.

However something strange has happened and I cannot get my head around it but it's probably very simple!

If I do:

Me.[searchSubForm].Form.RecordSource = "SELECT * FROM FindClient"

I only get the current record of the main form listed in the subform not all records that I was expecting.

So the subform only has limited access to the data? I don't understand why.

Here is the code on the search button click:

Dim sq As String
      
      sq = ""  'set variable
     
     If Not IsNull(SurnameSearch) Then

         sq = "WHERE FindClient.Surname LIKE ""*" & Me!SurnameSearch & "* """
         End If

    If Not IsNull(AddySearch) Then

         sq = "WHERE FindClient.Address1 LIKE ""*" & Me!AddySearch & "*"""
         End If
         
    If Not IsNull(PostCodeSearch) Then
    
        sq = "WHERE FindClient.Postcode LIKE ""*" & Me!PostCodeSearch & "*"""
        End If

    If sq <> "" Then

        sq = "WHERE" & Right$(sq, Len(sq) - 5) '
        sq = "SELECT * FROM FindClient " & sq '  Find records in table & Where etc (variable)
        Me.[searchSubForm].Form.RecordSource = sq  ' set record set based on results
        Me.HelpTip1.Caption = "Search Complete!! Now Click on the corresponding grey box next to the search results!"
                
        End If

    If sq = "" Then
        
        MsgBox "Please input a search term!!!!!", vbExclamation, "User Error!!!"
   
    End If
    
    If Me.[searchSubForm].Form.RecordsetClone.RecordCount = 0 Then 'if no results (very unlikley since using like *?*)
        'Me.Form.RecordSource = "SELECT * FROM Findclient WHERE False;"
        MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
      sq = "SELECT * FROM FindClient"  'Select All
      Me.[searchSubForm].Form.RecordSource = sq
        End If
End Sub

 

[PHV]

IS your subform linked to its mainform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpcurrie]

I don't think so the data source is blank.

How would I check? Brain fart here, sorry

 

[PHV]

I'd check the LinkChildFields and LinkMasterFields properties of your subform.

BTW, what is a 'data source' ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpcurrie]

Apologies I meant "Record Source is blank"

Subforms are linked by way of a SQL statement in hte record source are they not?

In my case that field is blank - I also searched the project for any mention of LinkMasterFields or LinkChildFields and there is nothing.

What am I missing

 

[maxxev]

If you go into "form design" and open up the properties for the subform object (i.e not the properties of the subform but the object on the master form) they are under the "data" tab.

 

[jpcurrie]

Gotcha, but no they are both blank.

Any more ideas why this code is going wrong?

It seems to be only search the current record not the whole table.

 

[PHV]

What is FindClient ?
A query with a where clause referencing a control in your mainform ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpcurrie]

FindClient is a query that is basically a select all of the bookings table.

SELECT Bookings.*
FROM Bookings;

 

[jpcurrie]

Ah right the linkchild & master fields seem to be set once the search button is clicked for some very strange reason.

Could I have corruption?

 

[jpcurrie]

Debugging the code the offending settings are set when the record source is set:

Me.[searchSubForm].Form.RecordSource = sq  ' set record set based on results

 

[jpcurrie]

Cracked it - THANKYOU for pointing me in the right direction

 Me.searchSubForm.LinkMasterFields = ""
 Me.searchSubForm.LinkChildFields = ""

Effectively resets th properties after selecting the records don't know why that started happening but it fixed it.