Update a textbox from data entered in two combo boxes

[Guernsey Gaz]

Hi All
Continuing with a form,
I have a form based on a query, this form has two combo boxes Last Name and First Name. When the user selects a Last Name the second combo offers a list of first names that match the last name. (Thanks to Martin Green for this) Thes two boxes are from the ClientDetails Table.
I also have a textbox on the form, what I want is, when the user has chosen the Last Name and First Name the textbox updates with the InvestorName. The Investor Name is from the InvestorDetails table.

Any help is greatly welcome

Gaz

 

[Kempes]

firstly, how are your tables linked?
secondly, how are you getting around clients with the same first and last name? (it may not have happened yet, but could happen)

 

[Guernsey Gaz]

Hi Kempes
My tables are linked by IvestorID as a PK in the tblInvestorDetails and InvestorID as a FK in the tblClientsDetails.
TblInvestorDetails
InvestorID
InvestorName
Add1
Add2
City
Country
PostCode
Phone

tblClientID
ClientID
InvestorID
FirstName
LastName
Add1
Add2
City
Country
PostCode
Phone

qryCorrespondence
ClientID
LastName
FirstName
Investor Name
And a load of other field from a tblCorrespondence

I hav not yet covered the posibility of two people having the same LN and FN

Hope you can help

 

[georgedwilkinson]

Dang, this is so much work. Why don't you just do it in one combo box using a query that gives you names in "first last" order and in "last, first" order? You can link to your investor name by using your junction table as the basis for your subform.

This is not normalized. No problem on day one, but it won't be long.

What if a Client is also an Investor? What if one becomes the other? What if one of them actually has more than one address? Over time? Same with phone?

 

[Kempes]

Hi,

what I would advise is, do not use FN and LN, as the combination of them are not unique.
You do have a unique field within this table which is clientID. You will need to use this to determine which investor will be displayed.

Looks like your query is already set up, so it should be fairly simple from here.
Make sure that clientid in the query comes from tblCorrespondence.

Get rid of your combo boxes and add a new one.
Where it asks for row source, click on the dots which will open what looks to be a query.
This will drive your combo box values.
Add the client table in and select as many columns as you want the user to see in the combo box, but you must ensure the first column is the clientid.
I would choose clientId,FN,LN as a starter. (sort by FN)
Save and close this query.
within the properties of the combo, set your column count to however many columns you have chosen.
I usually hide the client id by choosing col width of 0, then the remaining 2 to whatever you want (If you do this, the user will see only the first and last names in the list).
When a value is chosen, the client ID will be written to the table, and the investor field(which the query should be telling it to pull from the investor table where the clientid matches) should display.

let me know if you need any more guidance with this.

 

[Guernsey Gaz]

Hi Kemps
OK I need help?
I have tried and tried to get this db to work.
The client wants the two combo boxes on the form Last Name and First Name
Also they want the Investor Name as a combo but it must only return those Investor Names that match the Last and First Names
On the frmCorrespondence1 I can get the cboLastName as no duplicated list and then the cboFirstName offers a list of only those First names that match the Last Name
I can get txtInvestorName to come back with the investor name once I revisit the cboLastName, they want a combo that only returns Ivestors Names where the last and First Names match

I have taken the liberty of attaching the DB as I am sinking deeper and deeper and getting quite lost with it all

 

[Kempes]

Hi Gaz,

Could you post an acc 97 version please?

Thanks

 

[Guernsey Gaz]

Hi Kempes
Sorry have been at work all day
I have converted to 97 format
Hope this helps?

 

[Guernsey Gaz]

Cascading Comboboxes

Hi,
Havn't had a reply to the problem yet, so here is the code and a reminder of what is going on

I have a form called frmCorespondence that is used to enter details into a table, no problems if using just the lookups, but that is not what the client wants. In the real DB there are 1000's of duplicated Last Names and First Names so in the cboLastName.Rowsource I have used SELECT DISTINCT to only show each name once. No problems
I have used the first part of the code below (cboLastName_Afterupdate() ) to get just the first names that match the last names again no problem. I then have the code cboInvestorName to match those companys that only match Last and First Names, here is the problem
I can select the last name and the first name, then when I use the combo for the company name it is blank, but if I click in the blank and then reselect the Last Name and the First Name the combo gives me the answer. How can I get it to flow so that the company name is there once I have selected the other two combos rather than having to go through the process twice


Option Compare Database
Private Sub cboLastName_AfterUpdate()
On Error Resume Next
Me.cboFirstName.RowSource = "Select tblContact_Details.FirstName " & _
"FROM tblContact_Details " & _
"WHERE tblContact_Details.LastName = '" & Me.cboLastName.Value & "' " & _
"ORDER BY tblContact_Details.FirstName;"
Me.cboFirstName.Requery
Me.cboInvestorName.RowSource = "Select tblContact_Details.CompanyName " & _
"FROM tblContact_Details " & _
"WHERE tblContact_Details.LastName = '" & Me.cboLastName.Value & "' " & _
"AND tblContact_Details.FirstName = '" & Me.cboFirstName.Value & "' " & _
"ORDER BY tblContact_Details.CompanyName;"
Me.cboInvestorName.Requery

End Sub

Any answers would be greatful

Kind Regards

Gaz

 

[Kempes]

sorry,

I have been really busy recently.

I've had a quick look and created a copy of your form (attached, called kempes1).

Does this do what you want it to do?

I'm not entirely sure that your database structure is sound, but hopefully this should get you started with what you want to do.

You really should normalise your db where possible.

Let me know if I have missed the point on this anyway.

Cheers
Kempes

 

[Guernsey Gaz]

Hi Kempes
Excellent, Just what the doctor ordered

Many Thanks

Gaz