Value entered into combobox vs. value chosen from combobox

[jaworski_m]

Hello,



I have a combobox with 4 columns (0…3). I would like values from columns 1,2,3 to be assigned to other fields ONLY when a value is CHOSEN from combobox. When a value is TYPED into the combobox, the values from columns 1,2,3 should NOT be assigned anywhere.

Is there a way to distinguish data typed into a combobox or selected from the dropdown list?

I tried setting auto filling in to None, but it did not help.

I would appreciated any hints.

Marcin

[nick404]

Problem with the combo box in your situation is... the combo box differs from the classic drop-down in that it is meant to allow textbox + dropdown functions. The 'Auto Expand' property is utilized when they type into the cbo and brings up the closest match, so you could try setting the 'Auto Expand' property to No (it is default = yes).

Otherwise you can deter them from typing...
Otherwise try this code and see if it works better, it will just give the user an error message saying what they typed isn't an option.

Private Sub YourCboName_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode <> 9 Then
       KeyCode = 0
    End If
End Sub

Out of curiosity why would you want to differentiate between values selected and values typed?

[Missinglinq]

...Out of curiosity why would you want to differentiate between values selected and values typed?

I think this is really the important question, here!

That aside...I assume that the Row Source is set to Table/Query, since it generally (but not always) is when the Combobox is multi-columned. Is your assumption that anything entered through the keyboard is not in the list? If so, you could run DCount against the Table/Query to see if the Value for the Combobox already exists in a Record. If the 'typed in data' can be in the Table/Query, I think that Nick's suggestion is the way to go.

Linq ;0)>

[June7]

Why would the data from other columns need to be saved into record? This sounds like duplication of data.

However, to pull the other data from the combobox item, just refer to the column by index. Index begins with zero. So an expression in a textbox can be:

=[comboboxname].[Column](1)

If the combobox LimitToList property is set to Yes then only items in the list can be selected. If set to No then user can enter anything they want. If what they enter does not match any list item, the textbox expression will return an empty string.

[jaworski_m]

Thank you for quick reply.

Out of curiosity why would you want to differentiate between values selected and values typed?

The aim of the database is to record measurement points on PCBs (Printed Circuit Board). A user enters a signal ID (unique value – no problem here) and reference ID of the measured signal.

The question concerns reference ID.
Reference name is not unique and the same value, for ex. “GND”, can be on different PCBs (see attached screenshot) and additionally can be assigned to many signal IDs.

If user types “GND” the first match will GND(1) shown in the screenshot, which isn’t necessarily a correct one. I would like to avoid this situation.

I would like to have the following scenario:

1. User explicitly SELECTS value “GND(2)” from combobox’s columns are populated to relevant fields
2. User types “GND” into the combobox and other columns are NOT populated.

Is there a way to achieve this? I would appreciate any other solutions.

The RowSource of the combox is based on already entered values into a table.

[nick404]

(1) Preventing User From Tpying in Cbo

The RowSource of the combox is based on already entered values into a table.

If this is the case then I would go with the code I suggested in post #2. Try it out, see if you like it, or get any errors, then post back with problems or if it isn't quite what you are looking for.

Private Sub YourCboName_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode <> 9 Then
       KeyCode = 0
    End If
End Sub

The user can still type in the cbo but when they do Access gives an error telling them what they have typed is essentially 'not valid', and they won't be able to submit that. I don't think you can 100% stop a user form typing onto a combo box (since as I said earlier the combo box is meant to allow that), but I could be wrong!

(2) Auto-Populate the Appropriate Textboxes

User explicitly SELECTS value “GND(2)” from combobox’s columns are populated to relevant fields

For this June7 has provided what you need in post#4.
For each text box you want to be populated type [cboName].[Column](#) as the control source-- where # is the column number from the combo box that has the data for that text box, the first column is Column(0) FYI. So for example from your image above... your 'On Schematic' is Column(0), 'Tested Item' is Column(1), 'Cable Number' is Column(2), and 'Location where cable is connected' is Column(3).
First make sure you have each field you want to populate included in the Table/Query that is the row source for the combo box. Otherwise the fields will not show up in the text boxes you are populating.

(3) Requerying the Textboxes if Not Working
If at first the textboxes do not populate as expected: consider clicking the ellipses (...) next to the After Update event in the event properties tab for the combo box, selecting 'Code Builder' and put in a line of code to requery your textboxes to make sure the data gets pulled correctly. Would be something like

Private Sub ComboBox_AfterUpdate()
Me.textbox1Name.Requery
Me.textbox2Name.Reqery
'rest of textbox names
End Sub

I know this is a lot to throw at once, and I hope I have all my info correct... but just take it piece by piece, digest it, and report back on any problems!

[nick404]

CascadingCbo_smpl.zip
^^ Here is a sample database I have made for you. It's based off one I made a while ago I just took out a lot of things and made it simple for our purposes, here. Format is Ac2007

It does not allow user to type in combo box at all, take a look at the properties of the combo box in design view, and the code behind the combo box by pressing Alt and F11 and looking for the code which I had posted above.

Additionally, if you play around with selecting things from the combo on the form you will notice it auto-populates the next two textboxes. By looking at the code again (ALT+F11) in the AfterUpdate event of my combo box I used code to auto fill these other two text boxes. I prefer using the VBA to auto fill.

Please, feel free to glean ideas off the sample and use it to help guide you!