Criteria = Me!mainform field

  • Thread starter Thread starter Mark R
  • Start date Start date
M

Mark R

I have a listbox on my Mainform that uses a query as a
source. That query joins the main underlying firsttable
to a secondtable on a one to many relationship and it
pulls out all the history for a few fields of interest
that relate to the current record that is in display.

Example:

For the current record Lastname, Firstname, dateofbirth
(these are not key fields, RECORDNUMBER is the key field)

Select secondtable.XYZZZ, firsttable.lastname for any
RECORDNUMBERS that have the same LASTNAME as the current
record

Select secondtable.XYZZZ,
firsttable.lastname,firsttable.recorddate
FROM firsttable INNER JOIN secondtable ON
firsttable.recordnumber=secondtable.RECNUM
WHERE(FIRSTTABLE.Lastname=Me!FIRSTTABLE.Lastname)
ORDER BY firsttable.recorddate

when I open the mainform, I get prompted to supply values
for the variable me!firsttable.lastname

This is not the effect I was seeking. I want the list box
to automatically display history data for the current
record on display. Furthermore, I was going to have
control buttons that would make the listbox visible or
INvisible. I am also considering placing this "feature"
as a subform that contains the listbox. Any suggestions?
 
If I understand correctly - you want the lists source to be controlled by a fields value on the current form
If the source for the listbox is a query name and not the query text then I think you would have to use the forms collection to get to the value
replace frmMyForm with your form name in the saved query..

Select secondtable.XYZZZ,
firsttable.lastname,firsttable.recorddate
FROM firsttable INNER JOIN secondtable ON
firsttable.recordnumber=secondtable.RECNU
WHERE(FIRSTTABLE.Lastname=forms!frmMyForm!Lastname
ORDER BY firsttable.recorddat

If the source for the listbox is the query text then using Me!FIRSTTABLE.Lastname should resolve the value
 
Back
Top