Combo Box help needed ASAP

[authorsami]

I have a problem with one of my forms.

It has a combo box that is used to find and display the data for a particular client....

My problem is that if I have more than one customer with the same last name I only get the data for the first one. This will NOT due. Can anyone offer suggestions as to what the problem is and how to correct the problem

HELP
Sharon
sharon@comptutor.net

 

[NSDS]

If you ran the Wizard to setup the combobox, and used the only the last name of the client, this will result in a find of the first client with the selected last name.

Try using a "ClientID" or "RecordID" along with the last name. If this is not possible, try setting up a filter rather than a find on the afterupdate event of the combobox.

 

[JasonPerdue]

Sounds like the combo box's Row Source is being fed a SELECT DISTINCT statement. If that's the case you might want to change it to something like this:

Select Distinct ([Last_Name] & ", " & [First_Name]) As [Customer Name] From TableName

 

[authorsami]

NSDS: I originally had just the first and last name os the customer in the query (row source) I then added SSN. This is what the SQL code looks like now.


SELECT qryPatientToCalcAge.LastName, qryPatientToCalcAge.FirstName, qryPatientToCalcAge.SSN
FROM qryPatientToCalcAge;

JasonP: As you can see I did put in both first and Last name plus the SSN of the client...


Any ideas?
I'm new to SQL and coding so you might have to talk down to me, I will not be offended..

Sharon

 

[JasonPerdue]

What does qryPatientToCalcAge look like? Switch to SQL View and post a snippet if you don't mind. It is possible that the query itself is limiting the results in the combo box.

 

[authorsami]

Jason, here it is.... hope it makes sense

SELECT tblPatient.Salutation, tblPatient.FirstName, tblPatient.LastName, tblPatient.Address1, tblPatient.Address2, tblPatient.Address3, tblPatient.City, tblPatient.State, tblPatient.Zip, tblPatient.HomePhone, tblPatient.WorkPhone, tblPatient.CellPhone, tblPatient.OtherPhone, tblPatient.NoMail, tblPatient.DOB, IIf(DatePart("m",[tblPatient]![DOB])=DatePart("m",Date()) And DatePart("d",[tblPatient]![DOB])=DatePart("d",Date()),CInt((DateDiff('d',[tblPatient]![DOB],Date())/365.25)),CInt((DateDiff('d',[tblPatient]![DOB],Date())/365.25)-0.5)) AS Age, tblPatient.Sex, tblPatient.[DL#], tblPatient.SSN, tblPatient.Phone, tblPatient.GeneralRemarks, tblPatient.AudiologistInititals, tblPatient.HAPatient, tblPatient.ReferalSource, tblPatient.Insurance, tblPatient.AltAddr1, tblPatient.AltAddr2, tblPatient.AtlCity, tblPatient.AltSt, tblPatient.AltZip, tblPatient.BillingAddress, tblPatient.BillingCity, tblPatient.BillingSt, tblPatient.BillingZip, tblPatient.Active
FROM tblPatient
ORDER BY tblPatient.LastName;

 

[JasonPerdue]

Based on the SQL statement above, I'm not seeing any reason why your combo box would be limited to only one last name. I figured there would be an underlying DISTINCT in the SELECT statement - so my theory is blown out of the water.

Try setting the RowSource of the combo box to something like this:

SELECT [tblPatient].[SSN], ([tblPatient].[LastName] & ", " & [tblPatient].[FirstName]) As [Patient_Name] FROM tblPatient ORDER BY [tblPatient].[LastName]

Set the combo box properties as:
ColumnCount=2
ColumnWidth=1";1"
BoundColumn=1

By adding a command button to your form or using the combo box's OnChange Event you can change the forms RecordSource to:

SELECT tblPatient.Salutation, tblPatient.FirstName, tblPatient.LastName, tblPatient.Address1, tblPatient.Address2, tblPatient.Address3, tblPatient.City, tblPatient.State, tblPatient.Zip, tblPatient.HomePhone, tblPatient.WorkPhone, tblPatient.CellPhone, tblPatient.OtherPhone, tblPatient.NoMail, tblPatient.DOB, IIf(DatePart("m",[tblPatient]![DOB])=DatePart("m",Date()) And DatePart("d",[tblPatient]![DOB])=DatePart("d",Date()),CInt((DateDiff('d',[tblPatient]![DOB],Date())/365.25)),CInt((DateDiff('d',[tblPatient]![DOB],Date())/365.25)-0.5)) AS Age, tblPatient.Sex, tblPatient.[DL#], tblPatient.SSN, tblPatient.Phone, tblPatient.GeneralRemarks, tblPatient.AudiologistInititals, tblPatient.HAPatient, tblPatient.ReferalSource, tblPatient.Insurance, tblPatient.AltAddr1, tblPatient.AltAddr2, tblPatient.AtlCity, tblPatient.AltSt, tblPatient.AltZip, tblPatient.BillingAddress, tblPatient.BillingCity, tblPatient.BillingSt, tblPatient.BillingZip, tblPatient.Active
FROM tblPatient
ORDER BY tblPatient.LastName
WHERE tblPatient.SSN= & ComboBoxName.Value
(ComboBoxName.Value may require quotes)

 

[JasonPerdue]

Hopefully what I said makes sense...It has been a long day.

Jason

 

[Eupher]

Sharon,
In your combo's property sheet, which is the bound column? If you didn't change it from the default, it's probably column 1, which according to your SQL would be lastname. Just including the SSN (the record's unique identifier) in the combo's row source isn't enough. Either 1) (my preferred method) open the query grid for the combo and move the SSN field so that it becomes the first (and bound) column OR 2) change the bound column in the combo's property sheet to match the SSN column.

Can you show your code? Might need to tweak it based on the column changes...

Ken S.

 

[authorsami]

Eupher,
I went into the query grid and moved the SSN to the first column. It now shows in the drop down combo box but the first name does not. Is there a way to show only the first and last name and not the SSN in the drop down combo box but have the query use the SSN to pull the data???

Thanks
Sharon

 

[JasonPerdue]

Set the combo box properties as:
ColumnCount=3 ' ssn, lastname, firstname
ColumnWidth=0";1";1" ' hide, 1 inch, 1 inch
BoundColumn=1 ' bind value to SSN

Doing so will display last name & first name and bind the hidden SSN value

 

[KenReay]

Hi

What has not been asked thus far is:

"what does the code look like which actually locates the record to dislay"

You nee dto be doing a findfirst using the unique id (SSN ?), not LastName, which will simply return the first LastName (if that makes sense) of (possibly) 'n' identical LastNames

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK