I have a form based on a query which includes the fields: accountid, debtorid, debtorname, clientid, clientname, contactid and contactname. Creating a form for new accounts is no problem. My problem is with the edit account form.
When the user selects an account to edit we need to be able to change the client and the contact. So I need a combo box that list all clients, but has the currently selected client preselected when you open the form... that part I have but when I try to select another client from the combo box it doesn't work... I just get a beep.
Also the current contact should show up, preselected when the form opens... I've got that worked out, but like the client combo box, I can't select a new contact.
The other problem is that the contact needs to initial reflect what the contactid is in the tblAccounts, but when the client is changed then the contact combo box should show just the contacts that belong to that client.
And of course... any changes to client or contact need to write back to the account table.
I have been trying for days and maybe I'm trying to do something that's not possible, but I figure if anyone would know how to do this... this group would.
Thanks in advance for your time and attention.
What is the Control Source?
What is the Row Source?
What is the Bound Column?
Do all of your tables have primary keys defined?
If not your recordsouce may not be updatable.
>The other problem is that the contact needs to initial reflect what the
>contactid is in the tblAccounts, but when the client is changed then the
>contact combo box should show just the contacts that belong to that
The contact combo box needs to have it's row source reset in the ClientComboBox's AfterUpdate Event to include the value of the ClientComboBox in a where clause. Something like this:
Private Sub ClientCombo_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT [Contact] " & _
"FROM [Query] WHERE [ClientID] =" & Me!ClientCombo
Me!ContactCombo.RowSource = strSQL
strSQL would just be a modification to the original RowSource .
se my previous remarks about updating the contact combo box during the after event of the client combo box. The query that you need to use must include a join on the client table to the contacts so that you can filter them out.