Row Source - Table/Query

[goinesj]

I have several different tables and lots of fields on the form, but will
limit my example to the two giving my problems.

DistTab
DistID AutoNumber
DistName Text
DistStatus Yes/No

UsrTab
UsrID AutoNumber
UsrName Text
UserStatus Yes/No
DistID Number

Relationships - DistTab/DistID = UsrTab/DistID

The form has a field called DistID that displays the list of active DistName
to be selected. The SQL statement:
SELECT DistTab.DistID, DistTab.DistName FROM DistTab WHERE
(((DistTab.DistStatus)=No)) ORDER BY DistsTab.DistName;

The next field is called UsrID. This field is suppose to display the list
of UsrName who are active and part of the district selected in the DistID
field. The list is always empty. The SQL statement:
SELECT UsrTab.UsrID, UsrTab.UsrName, UsrTab.DistID FROM UsrTab WHERE
(((UsrTab.UsrStatus)=No) AND ((UsrTab.DistID)=[DistID])) ORDER BY
UsrTab.UsrName;

If I change part of the statement to be ((UsrTab.DistID)=12)) it works fine.

How do I reference the number value of the DistID field for comparison to
the ID storeded on the UsrTab.DistID?

 

[Barry Gilbert]

I have several different tables and lots of fields on the form, but will
limit my example to the two giving my problems.

DistTab
DistID AutoNumber
DistName Text
DistStatus Yes/No

UsrTab
UsrID AutoNumber
UsrName Text
UserStatus Yes/No
DistID Number

Relationships - DistTab/DistID = UsrTab/DistID

The form has a field called DistID that displays the list of active DistName
to be selected. The SQL statement:
SELECT DistTab.DistID, DistTab.DistName FROM DistTab WHERE
(((DistTab.DistStatus)=No)) ORDER BY DistsTab.DistName;

The next field is called UsrID. This field is suppose to display the list
of UsrName who are active and part of the district selected in the DistID
field. The list is always empty. The SQL statement:
SELECT UsrTab.UsrID, UsrTab.UsrName, UsrTab.DistID FROM UsrTab WHERE
(((UsrTab.UsrStatus)=No) AND ((UsrTab.DistID)=[DistID])) ORDER BY
UsrTab.UsrName;

If I change part of the statement to be ((UsrTab.DistID)=12)) it works fine.

How do I reference the number value of the DistID field for comparison to
the ID storeded on the UsrTab.DistID?

When you say the form has a field, I assume you mean it has a combobox or
listbox with the values.

Instead of referencing the field DistId, reference the combobox. (You should
always rename your controls so that they're different from the underlying
fields.) For example, if your first combobox is call cboDistId, change the
rowsource to:

SELECT UsrTab.UsrID, UsrTab.UsrName, UsrTab.DistID FROM UsrTab WHERE
(((UsrTab.UsrStatus)=No) AND ((UsrTab.DistID)=cboDistID)) ORDER BY
UsrTab.UsrName;

Also, you probably need to requery the second combobox after updating the
first. Put this in the first combo's After Update event:
cboUserId.Requery

Barry