ComboBox dropdown.

[Codebug]

Is there an event I can use when a user slelects the dropdown from a ComboBox on a form, either by clicking it or pressing F4?

[sierra7]

Hi
It's usually the After_Update event

What are you trying to do ?

S7

[Codebug]

I want to run a function based on the text typed into the combobox, so that the user can select a valid input. The field has a suffix at the end indicating a unique entry based on how many times it has been entered.

I already have a function that will return the correct result in the ComboBox List, I just need it to be called if the user decides to pull down the ComboList to select the next in the seqence, otherwise the user has to manually work out the next in the sequence.

ie the records are stored as follows:

SN = "1234"

SN = "1234-1"

SN = "1234-2"

so if the user next types "1234", it will display "1234-3" in pull down list.

The function is simply an SQL that opens records matching "1234*" and then based on the record count.

[sierra7]

Hi
It sounds like you have done the difficult bit, to write the function to 'get next value'. If you want to display this in the combo box you can use the After_Update event ;-

 Private Sub Combo0_AfterUpdate()
Me.Combo0 = getNextValue(Me.Combo0)
End Sub

It's as simple as that so long as the FIRST column in the combo is VISIBLE and the property 'Limit to List' (on the Data tab) is set to 'No'

I hope I have understood correctly

BTW, I trust you know where you are going with this data structure? You are seemingly generating a 'compound' field which BREAKS THE FIRST RULE of database design and normalization.

By keeping your '1234' in one column (field) it can remain numeric, so faster to index and search, and the '4'th occurance can also be stored as a number in an adjacent column. etc, etc. If you want to display '1234-4' you can.

Ofcourse, I've no idea whether this is what you are already doing !

S7

[Codebug]

Yes thanks for your help,


yes that is correct, the data for this field is generic from a serial number of a product. And the data structure was something I had inherited when I took on the design of it.

Every time the product is returned for repair it is entered into the database, with a unique indexed field.

the serial number can be alphanumeric ie S124-2 (Second Time product was returned).

[Codebug]

Ok, still having problems


After_Update event not working as the user may sometimes want to drop down before moving to another input field (When the field gets updated)

have tried a routine using on change, but cannot get the data the user has typed into the combobox, the combobox value only returns the last updated value, not whats displayed in the combobox

[sierra7]

Well I wasn't too happy with what you were doing anyway! You would have to put in some check to stop a user casually dropping the box and creating a 'new part' or up-issuing and old one, when not intentional. But it is difficult from this side of the screen not knowing the full story.

I think the 'On_Change' event fires as soon as a change is detected without waiting for the full change, whereas 'After_Update' does what it says on the tin.

It sounds to me that you really need a command button saying "Get Next Issue"; it might not be a bad idea to make it have a message box saying "Are you sure?" You could still display the compound value back in the combo box.

I hear what you say about your SN numbers but I have 'been through the hoop' with Drawing Offices and seen most of the numbering sequences they invent, particularly for keeping traceability of assemblies and the like. Hats off to them because they managed without computers for a hundred years. I'd bet the 'S' means something to somebody; if they are still alive! But if you do your data analysis properly you should bottom out all these issues so that you can use today's tool effectively. You can still display what they want to see; it's what's under the hood that counts.

Sometimes you have to take a step back in order to go forward.
S7

PS That wasn't meant as a rant but does have good intentions.