Comb Box not allowing record sellection

[nsymiakakis]

Hi All, I am hoping someone can please help me on this issue that I am stuck on.
I am creating a Fee generating program for our Hospital's medical offices. You are supposed to type in a number, and it will show you the Medicare Fee associated with that proceedure code (HCPC). There are 4 Fields:
HCPC, Modifier, Description, and Fee.

I created a form, in which I placed a combo box using a basic query, showing the first 3 fields. and a text box for the fee. the idea is to type in the combo box, and when you hit return, the fee will pop up. the reason I used the combo box, is that a record can have up to three modifiers, each with a different fee.
example:
HCPC Modifier Fee
10021 $20.00
10021 56 $25.00
10021 TC $23.00

I can type in, or click on the 10021 and it works fine. The problem I am having is when I try to click on one of the records in the combo box that has a modifier. It keeps defaulting back to the first record with the same HCPC. so if I click on 10021 with the TC Modifier, my Fee only shows $20.00. for some reason it will not allow me to select any records with Modifiers, unless I use my down arrow in the combo box to scroll through the records.

This is a problem, because the enduser needs to make sure they are picking the correct record.

Thank you in advance for any help,
Nick

[missinglinq]

This really doesn't make sense. As set up, when typing in the HCPC code the Auto Expand feature will only take you to the first entry with that code. Continuing to type, i.e. trying to enter

10021 TC

won't move the selection down to that entry; Auto Expand only moves to the first entry of the bound field. But using your mouse to click on the entry for 10021 TC $23.00 certainly should select it and its fee.

I guess we'd better have a look at the code you use to assign the fee value to your record.

BTW, a combobox and the Auto Expand feature can be worked so that it is possible to type in 10021 TC and have that record selected.

Linq ;0)>

[nsymiakakis]

It does work when the enduser types in the CPT code, it does bring them to the first record, then they click on the drop down arrow on the combo box to see if there are any modifiers, if there are, they should be able to click onthe next line down, and that fee should pop into the fee text box. I think the reason it is not allowing me to pick the codes with modifiers, is because the combo box is bound to the HCPC field. I applogize, because I am learning as I go,but how can I show you my code that you had mentioned. I created the Form using the wizard, and I added the combo box. You can email me here and I can send you screen shots of my configuration.
Nick

[nsymiakakis]

Here is a snapshot of my code for the form.
Let me know if this helps.

Private Sub Combo8_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[HCPC] = " & Str(Nz(Me![Combo8], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Nick

[Stewart Ross]

Hi. You need to rethink how you want to achieve this - perhaps you don't see it because you are too close to the problem, but you are selecting the value of one field only from the combo, HCPC, then searching for the first matching record - but the value searched for is not unique. In these circumstances Access cannot tell that the first occurrence it finds is not the one you want - it is the correct value after all.

Unless you combine in the modifier and the fee, there is no way that Access can 'know' that you don't want the first occurrence, you want the next and so on.

In case you are thinking that this is something to do with the combo box, it too is doing what you have asked of it. You are selecting the HCPC value (bound to an underlying field, so if you change it that value is changed) - but if you consider only the sequence of values you are selecting you will see what I mean about non-uniqueness:

10021
10021
10021

It's because these are all the same that neither your FindFirst routine nor your combo can match the other values - you have not set it up to do so. In one way or another you need to combine the values together to make them unique - Access cannot read your mind here, it only does what you tell it to do - and as you can see you have not actually distinguished the values chosen in any way.

By the way, it is not normal practice to use a bound combo box as a means of selecting (or filtering) records; the bound field value is being changed every time you make a selection - is this your intention? The norm is to use an unbound control for this purpose, as any changes you make to it to find or select records will not affect field values in the underlying records of the form concerned.

-Stewart

[missinglinq]

The light dawns! This is going to take a lot more time than I have right now to walk you thru this, but I'll try to get back to it tonight or failing that first thing in the morning with a full explanation.

The problem is that you're not retrieving information from your underlying table, then using it to populate your record, you're actually retrieving the record from the underlying table!

When you used the Wizard to create your combobox you selected the third option "Find a record on my form based on the value I selected in my combobox" and it does just that! This feature is designed, for instance, to enter a patient ID number and have it retrieve that patient's record. For this feature to work correctly, the first, bound field has to be unique for each record, and of course it isn't in this case. The bound field is the HCPC code, and there are several records in your combobox's underlying table that have this same HCPC code. The code used behind the combobox, if you notice, uses FindFirst and that's exactly what it does, it finds the first occurrence of that code number, and retrieves that record. You can click on another row in your combobox, say one with the same HCPC code but a qualifier, but it's still going to only retieve the first one with the correct code number, in this case the one without any qualifier.

What you need to do is to retrieve information from a table other that the one your record is based on, then plug that information into your current record! This is something entirely different!

As I said, I'll get back to you later with more details on what you do need to do to get this thing back on track. For right now, you may as well delete the combobox you have, create a new one, leaving the default choice of "I want Access to look up values in a table or query." in place, then walk thru the rest of the Wizard'd Wonk.

Linq ;0)>

[nsymiakakis]

Thank you everyone for the information. Now I see what I am doing wrong. Thank you for helping me with this. Hopefully I will learn more about access with your help.
Nick

[missinglinq]

Glad we could help, Nick! Let us know if you need any further help!

Linq ;0)>