Changing a Select statement on a subform from the main form

[Peter Stone]

Novice/XP/2003

I have a large number of forms that share some subforms. On the Open event
of each main form I have some code that puts a Select statement into the Row
source property of a list box (lstSelectREcord) on a SHARED subform
(fsubSelect). E.g. for the main form of frmPageOrder:

Me.fsubSelect.Form!lstSelectRecord.RowSource = "SELECT tblMain.MainID,
tblMain.LocalDestinationID, tblMain.Heading, tblProgress.ProgressID,
tblProgress.Progress, tblPublicationStatus.PublicationStatusID,
tblPublicationStatus.PublicationStatus FROM tblPublicationStatus INNER JOIN
(tblProgress INNER JOIN tblMain ON tblProgress.ProgressID=tblMain.ProgressID)
ON tblPublicationStatus.PublicationStatusID=tblMain.PublicationStatusID WHERE
(((tblMain.LocalDestinationID)=Forms!frmPageOrder!cboLocalDestination));"

Note the reference to frmPageOrder at the end of the code.

When this code is on the Open event of frmText the reference is to frmText
etc.

The problem is that when I open a different form, I get the Access dialogue
asking Enter Parameter Value (e.g., Forms!frmPageOrder!cboLocalDestination).
If I open and close and go to design view without entering the parameter, the
code eventually kicks in and the correct Select statement appears in the Row
Source property of the list box.

How can I do this correctly and prevent the dialogue?

Thank you

Peter

 

[Barry Gilbert]

When the subform is saved, do you have a rowsource filled for the listbox?
You should have the rowsource be blank and only set it dynamically when it
loads. Also, is there any code that might be saving the form when it closes.
like DoCmd.Save?

Barry

 

[Duane Hookom]

Try use:
Me.fsubSelect.Form!lstSelectRecord.RowSource = _
"SELECT tblMain.MainID, tblMain.LocalDestinationID, " & _
"tblMain.Heading, tblProgress.ProgressID, tblProgress.Progress, " & _
"tblPublicationStatus.PublicationStatusID, " & _
"tblPublicationStatus.PublicationStatus " & _
"FROM tblPublicationStatus INNER JOIN " & _
"(tblProgress INNER JOIN tblMain ON " & _
"tblProgress.ProgressID=tblMain.ProgressID) " & _
"ON tblPublicationStatus.PublicationStatusID=tblMain.PublicationStatusID
" & _
"WHERE tblMain.LocalDestinationID= " & _
Forms!frmPageOrder!cboLocalDestination & ";"

This assumes LocalDestinationID is numeric.

 

[Peter Stone]

Thanks Barry
Of course--the row source should be blank.

Thank you Duane for answering the question I hadn't asked, but was thinking
about--Where do I break that line of code?

Bless you all

Peter