Dlookup syntax and requery help

[mdex]

I have a form (Checks) for auditing passwords. 2 comboboxes on that form, TeamNamecbo and AccountTypecbo referenced from Accounts Table. Within the accounts form I also have a field called PasswordRequirements.



On the Checks form I want an unbound txt box that looks up the PasswordRequirements from accounts when TeamNamecbo and AccountTypecbo are entered.

I have composed the below dlookup which when placed in the txt boxes control source returns the very first PasswordRequirements entry it finds from Accounts table as soon as the form is loaded and combo boxes are blank.

=DLookup("PasswordRequirements", "Accounts", "[TeamName]=" & Forms!checks!TeamNamecbo And "[AccountType]=" & Forms!checks!AccountTypecbo)

Can I place some kind of requery on the combo boxes or place the lookup somewhere else so it updates correctly dependant on the data from the combo boxes?

[Minty]

Simply bring the PasswordRequirements field into the data for the combo as an extra hidden column.

In the after update event of the second combo use something like

Me.YourTextBox = Me.AccountTypecbo .Column(1)

Bear in mind that the column numbering on combo's starts at 0

[mdex]

Excellent.

Much simpler than the method I was trying to use.

Thanks

[LLJ]

Your solution for mdex worked perfectly for me, too - After Update event on Me.BookTitlecbo correctly displays value of Me.BookTitlecbo.Column(2) in Me.Publisher. However, the value is not getting stored in the underlying table. What do I need to do next?

[Minty]

I'm not sure you should be storing a value if you can already look it up ?

Generally speaking if you already have that data stored you shouldn't store it again, unless there is some date based change that might mean that data could change in the future and you had to record the old value as at a specific time. Storing it in two places means if it's wrong or needs editing you have to try and remember to make those changes everywhere.

If your Publisher control is unbound then it wouldn't save as it's not connected to your recordset.

[LLJ]

You're right, of course, about not storing the data twice. But let me be a little more thorough about what I'm trying to do... in a few hours. Should I continue to reply to this solved thread, or start a new one?

[Minty]

Start fresh. It will avoid confusion.