Row Source sql changes by itself (1 Viewer)

[PNGBill]

MS Access 2010 accdb.
We have a main form, frmLoanIssueData, and a number of sub forms.
One subform, FrmRefinanceSubForm, has a combo box control with Row Source as this sql

SELECT TBLAPPLOAN.APLPK, TBLAPPLOAN.APLACCDET, TBLLOAN.LDPK, tblLoanIssueStatus.IssueDate, QryLoanTotalToPayResult.SumOfLoanTotalToPay, TblRefinance.RefinanceID
FROM (((TBLAPPLOAN INNER JOIN TBLLOAN ON TBLAPPLOAN.APLACCDET = TBLLOAN.ADPK) INNER JOIN tblLoanIssueStatus ON TBLLOAN.LDPK = tblLoanIssueStatus.LoanID) INNER JOIN QryLoanTotalToPayResult ON tblLoanIssueStatus.LoanID = QryLoanTotalToPayResult.LoanID) LEFT JOIN TblRefinance ON QryLoanTotalToPayResult.LoanID = TblRefinance.OldLoanID
WHERE (((TBLAPPLOAN.APLPK)=[COLOR=red]DLookUp("[APLPK]","TBLAPPLOAN","[APLPK] = " & [Forms]![frmLoanIssueData]![txtAPLPK]))[/COLOR] AND ((TblRefinance.RefinanceID) Is Null))
ORDER BY TBLLOAN.LDPK DESC;

The purpose here is to provide a list of records that fit the sql where field APLPK = txtAPLPK in the main form - refer DLookup in red.

All works perfectly... except sometimes, when you click on the combo box, the DLookup has changed to read frmLoanProcessData instead of frmLoanIssueData.

While typing this, I realise what may be causing the confussion.

Calling the mainform, depending on the status of the Application, either frmLoanIssueData or frmLOanProcessData will be opened.

Is it possible that when one version is opened, the subform remembers the sql and when the next version is opened, the sql hasn't changed.

I guess, what should happen is I make it so the appropriate Row Source is loaded each time the subform is opened rather then have it hard coded.

Sorry for the ramblings but it looks like the issue is down to two different forms using the same subform which is not possible if you hard code an sql in the subform to refer to a field on the main form which of course is not the same main form every time

 

[RainLover]

Try a Requery on the Combo Box.

 

[PNGBill]

Thanks RainLover. Got side tracked with despatching a rooster that was making too much noise.
Back to access..

 

[PNGBill]

Finally got back to this issue.
The subform is used on two different Main Forms and the combo box control Row Source sql refers to a control on the main form.
This means the sql should be different depending on which main form is open.

To access the sub form you need to click a command button (otherwise it is not visible) and I added this code to the command button.

Dim strSQL As String
strSQL = "SELECT ..... " & _
        "FROM .... " & _
        "WHERE (((TBLAPPLOAN.APLPK)=DLookUp(""[APLPK]"",""TBLAPPLOAN"",""[APLPK] = "" & [Forms]![frmLoanProcessData]![txtAPLPK])) " & _
            "AND ((TblRefinance.RefinanceID) Is Null)) " & _
        "ORDER BY TBLLOAN.LDPK DESC;"

and this line

Me!FrmRefinanceSubForm.Form!cboOldLoanID.RowSource = strSQL

The two main forms have this code on the command button Click Event, except the Where clause is different.

This has resolved the issue as the correct Row Source is loaded when the sub form has a record to add or edit.

 

[RainLover]

Me!FrmRefinanceSubForm.Form!cboOldLoanID.RowSource = strSQL

Are you sure this is correct?

I would think that the Control Source for cboOldLoanID should be from the Primary Table that holds only information about the Loan.

Why do you have OLD. There should only be One Table for Old and New. Just tag them as Old or New depending on their Status.

 

[PNGBill]

Here is the full sql

strSQL = "SELECT TBLAPPLOAN.APLPK, TBLAPPLOAN.APLACCDET, TBLLOAN.LDPK, tblLoanIssueStatus.IssueDate, " & _
            "QryLoanTotalToPayResult.SumOfLoanTotalToPay, TblRefinance.RefinanceID " & _
        "FROM (((TBLAPPLOAN INNER JOIN TBLLOAN ON TBLAPPLOAN.APLACCDET = TBLLOAN.ADPK) " & _
            "INNER JOIN tblLoanIssueStatus ON TBLLOAN.LDPK = tblLoanIssueStatus.LoanID) " & _
            "INNER JOIN QryLoanTotalToPayResult ON tblLoanIssueStatus.LoanID = QryLoanTotalToPayResult.LoanID) " & _
            "LEFT JOIN TblRefinance ON QryLoanTotalToPayResult.LoanID = TblRefinance.OldLoanID " & _
        "WHERE (((TBLAPPLOAN.APLPK)=DLookUp(""[APLPK]"",""TBLAPPLOAN"",""[APLPK] = "" & [Forms]![frmLoanIssueData]![txtAPLPK])) " & _
            "AND ((TblRefinance.RefinanceID) Is Null)) " & _
        "ORDER BY TBLLOAN.LDPK DESC;"

You are correct. The Control Source is a record in TBLAPPLOAN but the criteria and joins filter the selection to only display certain records and they must meet the DLookup criteria which uses a main form control value.

Because there is more then one main form the DLookup statement must be worded to reflect the correct main form.

It does work

 

[RainLover]

I assume then this thread is like the Rooster.