Referring to form field in control's RowSource WHERE clause

[Capt. Skinny]

I have a ListBox on an Access 2007 form. Using Query Builder, the
RowSource property of the ListBox is set to:

----
SELECT field1, field2
FROM tRelatedData
WHERE tRelatedData.foreign_key=Forms!MyForm!primary_key
----

The problem is that the where clause is not dynamic; it seems that the
expression "Forms!MyForm!primary_key" in the WHERE clause is only set
once when the form is first loaded. For example, if the primary_key
field of the first record to show up in Form view is 17, then for each
record on my form the list box will only display records for which
tRelatedData.foreign_key=17

Should I be handling some event that fires when I navigate between the
records of a form, and updating the WHERE clause of my RowSource
property each time? None of the Form object's events strike me as an
obvious candidate. Thank you in advance. -cs

 

[Jack Leach]

Should I be handling some event that fires when I navigate between the

records of a form, and updating the WHERE clause of my RowSource
property each time? None of the Form object's events strike me as an
obvious candidate.

The OnCurrent event of the form is run each time the record changes (and on
startup, as you technically navigate to the first record when the form is
opened).

Just be careful going to a new record... the Current event will run but you
won't have any data. Many time's you'll need to check the NewRecord property
of the form and compensate accordingly:

If Me.NewRecord Then
ListBox.Requery
End If

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Jon22]

Perhaps use:
DoCmd.Requery "MyListBox"
in the OnCurrent event of the form?

 

[Dirk Goldgar]

I have a ListBox on an Access 2007 form. Using Query Builder, the
RowSource property of the ListBox is set to:

----
SELECT field1, field2
FROM tRelatedData
WHERE tRelatedData.foreign_key=Forms!MyForm!primary_key
----

The problem is that the where clause is not dynamic; it seems that the
expression "Forms!MyForm!primary_key" in the WHERE clause is only set
once when the form is first loaded. For example, if the primary_key
field of the first record to show up in Form view is 17, then for each
record on my form the list box will only display records for which
tRelatedData.foreign_key=17

Should I be handling some event that fires when I navigate between the
records of a form, and updating the WHERE clause of my RowSource
property each time? None of the Form object's events strike me as an
obvious candidate.

Assuming that you want the list box to show the records from tRelatedData
that correspond to the current record on the form, then you should use the
form's Current event to requery the list box. Here's example code:

'------ start of example code ------
Private Sub Form_Current()

Me.YourListboxName.Requery

End Sub
'------ end of example code ------

Replace "YourListboxName" in the above code with the name of your list box.
If that name contains a space or other nonstandard character, do this
instead:

Me![Your Listbox Name].Requery

 

[Capt. Skinny]

Thank you Jack, Jon and Dirk. Requery updates the ListBox as I move
between records, but in Continuous Forms view, where multiple records
are displayed on the screen, the content of each record's ListBox is
changed to the values associated with the currently selected record.

I don't see an OnCreate or OnLoad event for the ListBox control that I
can use to set the RowSource property for each record's ListBox at
runtime. Can I bind the ListBox to my key field (using ControlSource)
and somehow reference the bound value in my WHERE clause? Thanks
again.

 

[Jack Leach]

As you change move from one record to the next within the continuous form,
the listbox will be updated based on the currently selected record.

Unfortunately, the fact that this listbox will display the same values
amongst all of the records in the continuous form (changing as you give the
focus to various records), is inherent to the design of a continuous form. As
far as I'm aware, there is no way around this. For this particular reason, I
find myself not using continuous forms all that often.

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)