Passing the form name to a Select statement

[Peter Stone]

Novice/XP/Access 2003

I have a combo (cboLocalDestination) on the header of frmText that selects
records by destination.

I have a list box (lstSelectRecord) on a subform (fsubSelectRecord).

The Row Source property of lstSelectRecord contains a Select statement that
populates lstSelectRecord with all the records for the destination selected
in cboLocalDestination:

SELECT tblMain.MainID, tblMain.LocalDestinationID, tblMain.Heading,
tblProgress.ProgressID, tblProgress.Progress,
tblPublicationStatus.PublicationStatusID,
tblPublicationStatus.PublicationStatus FROM tblProgress INNER JOIN
(tblPublicationStatus INNER JOIN tblMain ON
tblPublicationStatus.PublicationStatusID=tblMain.PublicationStatusID) ON
tblProgress.ProgressID=tblMain.ProgressID WHERE
(((tblMain.LocalDestinationID)=Forms!frmText!cboLocalDestination));

I am going to copy and rename frmText many times. Can I replace
Forms!frmText! (at the end of the query) with the name of the form? This will
save having a separate subform and query for each form.

Thank you

Peter

 

[John Vinson]

I am going to copy and rename frmText many times. Can I replace
Forms!frmText! (at the end of the query) with the name of the form? This will
save having a separate subform and query for each form.

Ummmmm...

Why many (apparently identical) forms!? That seems VERY strange.

If you're going to do so, you'll need to use the Form's Load event to
create the SQL string for the query. In VBA code you can use Me! to
refer to the current form, but you cannot do so in SQL.

John W. Vinson[MVP]

 

[Peter Stone]

That was obvious. Thank you very much. You guys have a lot of patience.

I didn't think of using the Load event. I was fixated on somehow modifying
the Select statement using the current form name (couldn't see the forest for
the trees).

The forms aren't exactly identical, each one has one-or-two extra tab pages
containing data joined 1-to-1 to the main table.