Combo box selection gives wrong record - strange

[BigToe]

Hello, if anyone out there can shed light on the fact that when a last name is selected in a combo box, and that name is shared by more than one person, why access is displaying the first record in the list of like names. (i.e. when choosing 'zach smith' , 'andy smith's record will be shown. I assume that it's sorting by first names when last names are the same that doesn't explain why it always shows the record of the first person amoung a group with the same last name when another person is selected within that group. any ideas?

[Scott Price]

Hello, if anyone out there can shed light on the fact that when a last name is selected in a combo box, and that name is shared by more than one person, why access is displaying the first record in the list of like names. (i.e. when choosing 'zach smith' , 'andy smith's record will be shown. I assume that it's sorting by first names when last names are the same that doesn't explain why it always shows the record of the first person amoung a group with the same last name when another person is selected within that group. any ideas?

What is the RowSource and table structure for your combobox?

Regards,
Scott

[BigToe]

What is the RowSource and table structure for your combobox?

Regards,
Scott

Scott,
the rowsource is a querry. the row source type is 'Table/Querry'. the sql for that querry looks like this:

SELECT [Clients].[Last Name], [Clients].[First Name]
FROM Clients
WHERE ((([Clients].[Service Coordinator])=[Forms]![recall client stats]![cboSC])) Or ((([Clients].[Waitlist Region])=[forms]![recall client stats]![cbowaitlist]));

I hope that answers your question.
thanks
tony

[Scott Price]

Scott,
the rowsource is a querry. the row source type is 'Table/Querry'. the sql for that querry looks like this:

SELECT [Clients].[Last Name], [Clients].[First Name]
FROM Clients
WHERE ((([Clients].[Service Coordinator])=[Forms]![recall client stats]![cboSC])) Or ((([Clients].[Waitlist Region])=[forms]![recall client stats]![cbowaitlist]));

I hope that answers your question.
thanks
tony

Hi Tony,

The problem is probably showing up because you aren't using a unique field as a 'control field' in your query. I'm assuming that in your combo box the bound column is the default of 1. This means that the value returned by the combo box choice will be the value from column 1, which in your case is the last name, and being as the last name field contains duplicates, the first of those is being used.

Add your ClientID field into the query as the first item in your select statement, and make sure the bound column is set to 1.

Regards,
Scott

[BigToe]

Hi Tony,

The problem is probably showing up because you aren't using a unique field as a 'control field' in your query. I'm assuming that in your combo box the bound column is the default of 1. This means that the value returned by the combo box choice will be the value from column 1, which in your case is the last name, and being as the last name field contains duplicates, the first of those is being used.

Add your ClientID field into the query as the first item in your select statement, and make sure the bound column is set to 1.

Regards,
Scott

thanks for your patience Scott but this is still not working. I placed the ClientID field first in the sql statement, set the bound column to 1 (it was already) and a). after choosing a name in the combo box the hourglass just hung for about 10 sec. and nothing happened (i.e the record for that client did not show) and b). the ClientID number now appears in the combo box. when i deselect 'show' in the querry builder it deletes the ClientID field in the sql statement all together! is that normal? here's my new sql statement:

SELECT [Clients].[ClientID], [Clients].[Last Name], [Clients].[First Name]
FROM Clients;
Tony

[missinglinq]

I solved this problem by adding a calculated field in my query, then basing the combobox on the calculated field. In a new field in query design, under the field name enter something like

WholeClientName: [LastName] & " " & [FirstName]

then run the Combobox Wizard and select WholeClientName as the field to pull on.

Welcome to TheScripts!

Linq ;0)>

[Scott Price]

thanks for your patience Scott but this is still not working. I placed the ClientID field first in the sql statement, set the bound column to 1 (it was already) and a). after choosing a name in the combo box the hourglass just hung for about 10 sec. and nothing happened (i.e the record for that client did not show) and b). the ClientID number now appears in the combo box. when i deselect 'show' in the querry builder it deletes the ClientID field in the sql statement all together! is that normal? here's my new sql statement:

SELECT [Clients].[ClientID], [Clients].[Last Name], [Clients].[First Name]
FROM Clients;
Tony

Take a good look at Linq's suggestion! Also, whenever you have multiple columns in a combo box, and the first column is an ID number field, make sure that the column widths are set to 0", 1.5" (or whatever width needed to display the data), etc...

As for the problem of the db 'hanging' for 10 sec, what field is the combo box bound to? Now that the bound column is an ID field, the bound field needs to be an ID field also!

At the risk of sounding patronizing, please also make sure that you have compiled, saved, backed up, and compacted/repaired your database (in that order). When developing (I'm sure you know this, but never hurts to say it again...) this process should be done at least once a day, and the compile/save every time you make a change in your code window.

Regards,
Scott

[BigToe]

Take a good look at Linq's suggestion! Also, whenever you have multiple columns in a combo box, and the first column is an ID number field, make sure that the column widths are set to 0", 1.5" (or whatever width needed to display the data), etc...

As for the problem of the db 'hanging' for 10 sec, what field is the combo box bound to? Now that the bound column is an ID field, the bound field needs to be an ID field also!

At the risk of sounding patronizing, please also make sure that you have compiled, saved, backed up, and compacted/repaired your database (in that order). When developing (I'm sure you know this, but never hurts to say it again...) this process should be done at least once a day, and the compile/save every time you make a change in your code window.

Regards,
Scott

Good reminders Scott - thank you. I figured out the spacing of the columns and place the ClientID column first (already set to autonumbers). I also have set the bound column to the first column (clientID). because I have never created a calculated field before I will give that a go tomorrow and let you all know the results.
cheers,
Tony

[BigToe]

Gentlemen, this seems like an exercise in futility. I hope you have the patience to endure another question. I successfully created a calculated field (which I think are extremely cool btw) but my vba code for that combo box doesn't seem to like the new format, i.e. records to not change when a name is selected from the combo box. Unless i'm missing something obvious, I can only assume that there's something inherently wrong with my form. I've removed the calculated field and there is no change. the querry that I once had working no longer works. I have saved and compacted/repaired with no luck. the vba code for that combo box is still the same (Clientstemp is the subform):

Private Sub cboClients_AfterUpdate()
    'Moves to client Name text box and
    'finds the record of whatever name is selected in the combo box
 
    DoCmd.ShowAllRecords
    Me!Clientstemp.SetFocus
    DoCmd.FindRecord Me!cboClients
 
    'just moves the highlighted cursor away from the Last Name after selection is made
    DoCmd.GoToControl "middle name"
 
    'Set value of combo box equal to an empty string
    Me!cboClients.Value = ""
 
End Sub

The form i have created has 3 combo boxes in the header and a subform in the body (below the header). This subform was created with the forms wizard and saved as a form; then imported into this form as a subform.The first and last name text boxes are in the subform with all the other client information broken up under tabs (or pages). This may be extraneous information but if anyone can see a connection to the way this form is set up to the lack of control I have with calling up a record please reveal it to me! I'd be willing to start from scratch if it meant a smoother process for building this database.
thanks again,
Tony

[Scott Price]

Hi, Tony!

You shouldn't need to start from scratch (at least not yet :-)

When referring to subforms the correct syntax is Forms![MainFormName]![SubFormName].Form![ControlName].

I see in your code you are trying to refer to the subform like this: Me![Subformname]![ComboboxName].

Change this over and see what happens.

Regards,
Scott

[BigToe]

Hi, Tony!

You shouldn't need to start from scratch (at least not yet :-)

When referring to subforms the correct syntax is Forms![MainFormName]![SubFormName].Form![ControlName].

I see in your code you are trying to refer to the subform like this: Me![Subformname]![ComboboxName].

Change this over and see what happens.

Regards,
Scott

Hi Scott,
I feel that I'm so close in getting this! This last bit of info was highly interesting. I found this site to compliment what you said:
[HTML]http://www.mvps.org/access/forms/frm0031.htm[/HTML]

there is a glitch that I can't seem to solve. here is my code again:

Private Sub cboClients_AfterUpdate()
    'Moves to client Name text box and
    'finds the record of whatever name is selected in the combo box
 
    DoCmd.ShowAllRecords
    Forms![recall client stats]![Clientstemp].Form![cboClients].SetFocus
    DoCmd.FindRecord Me!cboClients
 
    'just moves the highlighted cursor away from the Last Name after selection is made
    'DoCmd.GoToControl "middle name"
 
    'Set value of combo box equal to an empty string
    'Me!cboClients.Value = ""
 
End Sub

the runtime error 2465 (stops on line 6) states that access can't find the field 'cboClients'. If it makes it any clearer, cboClients exists in the parent form and the record text boxes and info etc. exists in the sub form. I've tried different variations of what you suggested but nothing has worked so far. any ideas?
Tony

[Scott Price]

I think in line 6 you want it to find and setfocus to the text box, don't you?

Regards,
Scott

[BigToe]

I think in line 6 you want it to find and setfocus to the text box, don't you?

Regards,
Scott

Private Sub SuccessIsSweet_AfterMuchFrustration
Dim firstBorn as String
 
firstBorn = "Scott"
 
doCmd.Offspring
If Me.AfterUpdate = True Then
Forms![Hospital].Form![First Name].firstBorn
 
Else: doCmd.keepTrying!
 
end If
end Sub

thanks buddy!

[Scott Price]

I take it that means you solved it??!

Made me laugh :-) LOL

Regards,
Scott