Subform display fails when sfrm control form recordsource changed with program

[grinnZ]

Private Sub cboSearch_Change()

    On Error GoTo Err_cboSearch_Change
    
    Dim db              As Database
    Dim rst             As DAO.Recordset
    Dim SQL             As String
    
    
    SQL = "SELECT tblAuctions.AucID, tblAuctions.DateListed, tblAuctions.DateClosed, tblAuctions.Qty, tblAuctions.Bid_Price, tblAuctions.BO_Price, tblAuctions.Duration, tblAuctions.Deposit, tblAuctions.AH_Cut, tblAuctions.Status, tblAuctions.Notes, tblItems.Item, tblItems.Level, tblItems.Type, tblItems.Composition, tblItems.Dep48, tblItems.Dep24, tblItems.Dep12, tblItems.InStock "
    SQL = SQL & "FROM (tblItems INNER JOIN tblAuctions ON tblItems.ItemID = tblAuctions.ItemIDLookup) "
    SQL = SQL & "WHERE tblItems.ItemID=" & Me.cboSearch.Column(0) & " and left(tblAuctions.Status,1)='L';"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(SQL)
    
    With Me
        .sfMarkSold.Form.RecordSource = SQL

The main form is completely unbound. The subform as a form is natively saved bound to a query but has no Master/Child links to the main form. When the main form is opened the subform appears and all records in the bound query are displayed. Yet when the code hits the last line of code shown where the subform control's form recordsource is changed it now lists no records even though there are indeed records in the recordset.

I have tested the SQL produced by pausing the code and copying the generated SQL and making a new query. That works fine. Also does it work when you manually input the same SQL statement into the recordsource for the form that is used for the subform. That also displays properly. It is only when I programmatically change the subform control's recordsource that the display fails. What am I doing wrong?

 

[boblarson]

Well, it all looks good to me. Normally you shouldn't need to do it but maybe you should add a

.sfMarkSold.Form.Requery

just after the record source is set.

 

[boblarson]

Oh, I think I just spotted it. Put it in the Combo's AFTER UPDATE event instead of the On Change event.

 

[cheshire_smile]

TRY:

Forms!MAINFORM!sfMarkSold.Form!RecordSource = SQL

 

[grinnZ]

Nope... Code assignment now in After Update with same result.

 

[cheshire_smile]

The Form or Report identifier is optional when referring to control properties. It is necessary, however, when referring to subform or subreport properties.
To refer to a control on a subform, use the following syntax:
Forms![main form name]![subform control name].Form![control name]
To refer to a control on a subreport, use the following syntax:
Reports![main report name]![subreport control name].Report![control name]

 

[boblarson]

Oh, I might have figured it out. SQL is an Access Reserved Word. So, instead use strSQL for your variable name and see if that helps.

 

[grinnZ]

smile...
Recordsource not a field. Your code changed to...

Forms!frmMarkSold!sfMarkSold.Form.RecordSource = SQL

... and still does not work.

 

[boblarson]

The Form or Report identifier is optional when referring to control properties. It is necessary, however, when referring to subform or subreport properties.
To refer to a control on a subform, use the following syntax:
Forms![main form name]![subform control name].Form![control name]
To refer to a control on a subreport, use the following syntax:
Reports![main report name]![subreport control name].Report![control name]

cheshire_smile - You don't need to use the full Forms! or Reports! reference. The code is on the referenced form so you can use ME. And since it isn't being evaluated by the SQL (it is outside of the sql string having its VALUE concatenated in, then it can follow the VBA format and not the query format of having to refer to the control using the long naming.

Also, just a little bit for you about the .Form. or the .Report. part. If the subform control (control on the parent form which houses the subform, or report if on a report) is named exactly the same as the subform (or subreport) then you do not need to use the .Form. or .Report. as it will use that as the default. However, if the subform control is not named the same as the object it is housing, then you must use it.

 

[grinnZ]

strSQL ... nope

 

[boblarson]

Then I think we need to have you post your database so we can see what is there. Make sure to not post sensitive data if you do, use bogus data.

And one last thing before you do, the subform doesn't have its Data Entry property set to YES anywhere does it?

 

[grinnZ]

Bob...

Open frmMarkSold. Use combo to select item. To select a different item click Search. Rinse and repeat.

Note: The Search button does not reassign the recordsource back to original. Once this works properly the subform will not be visible until after the user selects the item.

 

[grinnZ]

Here's another interesting observation...

        .cmdNavGotoFirst.Visible = False
        .cmdNavGotoLast.Visible = False
        .cmdNavGotoNext.Visible = False
        .cmdNavGotoPrev.Visible = False
        .lblRecordNo.Visible = False
        
        .sfMarkSold.Form.RecordSource = "qryMarkSold"   '<< ADD THIS LINE

    End With
    

Exit_Form_Load:
    Exit Sub

Err_Form_Load:
    Call LogError(Err.Number, Err.Description, "Private Sub Form_Load")
    Resume Exit_Form_Load
    
End Sub

Private Sub cmdSearch_Click()

    Call Form_Load
    
End Sub

Add the recordsource assignment to the end of the Form_Load event (cmdSearch_Click calls this routine). Now notice that instead of getting all the query records you now only get one. Comment the line, close, and reopen the form and the complete query is displayed.

 

[boblarson]

Nowhere did you reassign the original recordsource so yes, you must add this line:

Me.sfMarkSold.Form.RecordSource = "qryMarkSold"

to either the form load or the search button's click event. Since the subform already has that as its record source when the form loads, I would add it just above the call you make to the form's load event in the search button's click event.

 

[grinnZ]

Nowhere did you reassign the original recordsource so yes, you must add this line:

Me.sfMarkSold.Form.RecordSource = "qryMarkSold"

to either the form load or the search button's click event. Since the subform already has that as its record source when the form loads, I would add it just above the call you make to the form's load event in the search button's click event.

Bob, please note post #13 in this thread where I do indeed add that statement back to the code. In that try I added the statement to the end of the Form_Load yet even when I move it to the beginning of the cmdSearch_Click as you advised do I get the same result. When the form loads with coded assignment to the query it displays but one line of the query. Comment it out, save, close, and reopen and the full query is displayed.

I even went so far as to create a brand new subform control for testing and to that control made one property change. I manually assigned the control's recordsource to the query and received the same results as the subform control in question. Without coding the entire query displayed. As soon as coding is involved it displays one line.

I see this database has been downloaded a few times. Have results about which I write been duplicated?

 

[boblarson]

Bob, please note post #13 in this thread where I do indeed add that statement back to the code. In that try I added the statement to the end of the Form_Load yet even when I move it to the beginning of the cmdSearch_Click as you advised do I get the same result. When the form loads with coded assignment to the query it displays but one line of the query. Comment it out, save, close, and reopen and the full query is displayed.

I even went so far as to create a brand new subform control for testing and to that control made one property change. I manually assigned the control's recordsource to the query and received the same results as the subform control in question. Without coding the entire query displayed. As soon as coding is involved it displays one line.

I see this database has been downloaded a few times. Have results about which I write been duplicated?

It worked fine for me putting it in the Search click event BEFORE the load form call. It showed all records just like it showed when I opened the form initially.

 

[grinnZ]

Well I'm not necessarily happy to hear that but it is what I needed to hear. Let me do some more noodling to see what I can see.

 

[grinnZ]

So this is the question that begs to be asked... Why would the same program behave differently on two different systems? Why if we are both editing the code in the same place and in the same fashion would one see one set of records and the other a more complete set?

 

[boblarson]

Good question. What operating system are you using (including Service Pack level) and what Office version are you using (including Service Pack level)?

 

[grinnZ]

Bob....

It's not Access and it's not the op sys. That I know. Here is what I did...

I created an entirely new database. In that DB I created a small six record / two field table (ID & Name). On that I created a query (SELECT * FROM tbl). That query I made the record source for a subform in a new form with two buttons, one to load the entire query and the other to load records with ID<4. That worked. From there I imported just my auctions tables from the original and updated the query in the test database to point now to the auctions table. Again the form worked. So I did it the other way. I imported the test table, query, and form into the database that you now have and again it worked even in the original database. Therefore, there must be something amiss with, in my humble guess, the frmMarkSold form. I can't be definitive on that without more testing but they are my findings so far.

--------------------
Computers are like women... gotta love 'em, gotta hate 'em even after you marry 'em.