Combobox blank on old records 1

[Sloaner]

I have a form with a combobox(unbound) that allows the user to select a Person's name. The rowsource is

SELECT DISTINCTROW [Contact].[PersonID], [Suffix] & " " & [FirstName] & " " & [MiddleName] & " " & [lastname] AS name, [Contact].[LocationID], [Contact].[CompanyID], * FROM Contact;

The afterupdate of the combobox updates 2 other textboxes with company name & location. I'm having a problem with the combobox showing up blank on all old records. If a new selection is made in the combobox, it updates the other textboxes correctly and displays the selection made...but the form is closed and we go back to that record the combobox is empty even thou the other 2 boxes are correctly displayed.
I tried to add Me.Refresh in the On Current and the On Load event procedure... the problem remained...
I also tried PersonId.Requery ... and got nothing..
PersonId is the Name of the combobox

Any suggestions anyone???

P.S. The form's record source is

SELECT DISTINCTROW [Company].[CompanyName] AS Comp_name, [Locations].[LocationName], [Locations].[LocationID], [Company].[CompanyID] FROM Company INNER JOIN Locations ON [Company].[CompanyID]=[Locations].[CompanyID]; 
[code]

 

[willir]

Hi Sloaner

What is the purpose of the unbound combo box for the contact info -- assign a contact person to a company, or display company info for the contact? It is interesting that you seem to be retrieving the entire contact record source into the combo box.

I suspect the link is CompanyID on the Contact and Company tables.

Perhaps you may need to tweak the SQL statement on the record source for the combo box...

SELECT DISTINCTROW [Contact].[PersonID], [Suffix] & " " & [FirstName] & " " & [MiddleName] & " " & [lastname] AS name, [Contact].[LocationID], [Contact].[CompanyID], * FROM Contact
[COLOR=blue]WHERE Contact.CompanyID = [CompanyID]
[/color]

(I am assuming CompanyID is the name of a field on your form)

Note:
If you have problems with the aforementioned, then you can use an Event Procedure, OnCurrent record...

Dim strSQL as String

strSQL = "SELECT DISTINCTROW PersonID, Suffix," _ 
& "FirstName " & " MiddleName " & " lastname AS name, " _
& "LocationID, CompanyID, * FROM Contact " _
& "WHERE CompanyID = " & Me.CompanyID

Me.PersonId.RowSource = strSQL
Me.PersonID.Requery

(I am assuming PersonID is the name of the combo box)

BTW, thanks for providing great information!

Richard

 

[Sloaner]

Hi Richard,

the purpose of the combobox is to look up orders of a particular customer. By selecting a name from the combobox and populating the company & locations the user can visually verify that the order displayed is for the correct customer.

To answer your question ... Yes CompanyID is in Company & Contact table. PersonID is the name of the combobox and CompanyName is the other combobox.I'm bring in CompanyName in the recordsource query of the form as Comp_Name.

Comp_name: CompanyName

I tried your suggestions and still get nothing so far...the combobox still shows up blank.

Any more suggestions?

thanks

 

[willir]

Okay, then simplify.

We know combo boxes work. Start with the bare minimum - get it working, and then add the "features".

If the combo box is blank, I suspect it means that it could not find the matching reocrd.

Oh yea, one other thing, for the combo box, check the properties for...
Data tab - bound coumn
Format tab - number of fields, column widths

They should match your RowSource.

I am curious how your consolidated field ...
Suffix" & "FirstName " & " MiddleName " & " Lastname AS name
...will work. This may even be the problem.

Try using 4 different fields instead of one consolidated field. (Usually LastName, FirstName, PhoneNo is sufficient, and saves from typing the saluatation...
"Mr. John Smith"
vs typing
"Smith"

Richard

 

[Sloaner]

Thanks for all your suggestions.... I got it to work.