Where Clause in ComboBox keeps changing

[BHill]

6 hours spent. Doing something dumb. Pulling my hair out. Any ideas?

TblClients contains pkClientID
TblAccounts contains pkAccountID
TblClients has 1-to-many relat. with TblAccounts

This part works:
In Add mode, client opens frmOrders, picks a client (say TST) from list displayed in fkclientID. This restricts display in 3 column combo-box fkAccountID to only those accounts related to client TST. This works fine. fkAccountID shows bound Account # and 2 unbound descriptor fields (total 3 col’s). But any existing orders that are not for client TST, do not display the contents of fkAccountID.

What seems to be happening as shown below, is the where clause changes when I enter a new client code into fkClientID when entering a new record (which it should – to limit display of the next field, combo-box fkAcountID). But doesn’t allow display of previously input data on previous records that are not TST clients. This part is partially correct; I shouldn’t display AAA accounts for TST clients.

Before I enter a new record the WHERE clause is as follows:
Combo-Box fkAccountID:
Control source is fkAccountID
Rowsource is SELECT [ClientInternalAccount], [pkAccountID], [AccountName] FROM [tblAccounts] WHERE [tblAccounts]![fkClientID]=[fkClientID];

After I enter a record for client TST, the WHERE clause changes to:
SELECT [ClientInternalAccount], [pkAccountID], [AccountName] FROM [tblAccounts] WHERE [fkClientID]=’TST’

How do I restrict the row source “where clause” from changing to a “constant” every time I change records, which I think solves the problem? Or am I way off?

 

[KeaganQuilty]

In event Form_Current()

combobox.rowsource = "SELECT .... FROM [tblAccounts] WHERE [tblAccounts]![fkClientID] = '" & fkClientID & "'"

The thing is that the combobox needs to be requeried everytime you change records.

Keagan Quilty
Kilbride, Newfoundland

 

[BHill]

Thank you so much. I tried Docmd.requery in several places, unsuccessfully, thinking I had to refresh the data.

As a beginner, I still don't know the difference between a Docmd.requery and putting a "combobox.rowsource =........" in Form_Current. I'm afraid I'll waste another 5-10 hours trying to do something which I know conceptually, but not the specifics.
Again, thanks.
Brian