Unbound Combo Box Question

[wilderfan]

I want to create a combo box which will allow users to select from a list of people's names.

The combo box queries a table which contains all the individuals' names (plus a field called "EffectiveDate").

The complication is that some people have aliases and I only want the combo box to show the latest name where an individual has multiple names.

NOTE: The EffectiveDate field tells us when the individual started using their name.

Any suggestions on how I can design an appropriate query that picks up the "latest" or current names in use?

 

[pbaldy]

This type of thing?

http://www.baldyweb.com/LastValue.htm

 

[wilderfan]

I think that might work, Paul.

I'll give it a try and let you know tomorrow.


Cheers,

Robert

 

[wilderfan]

Paul -

Your SQL coding worked perfectly. Thank you.


One other question -

This little model has 2 forms in it:

1) A form to enter the name & other info about a new person; and

2) A form to select a person's latest (current) name which will then be updated. (The Change Name form).



The first form contains an edit check to make sure the user isn't trying to add a name that was previously entered. Unfortunately the edit check doesn't appear to work (unless the form is closed down and re-opened).

And the second form's combo box (which drops down the latest names in use) doesn't pick up all the latest names unless the user exits the form and re-opens it.


QUESTION: Is there some simple code that I can add to the sub procedure which "saves" the changes made to each form?

Or do users have to close and re-open the forms each time a change is made in order to ensure that the edit check and the combo box work with the latest records in the tables?

Thanks !

 

[pbaldy]

You can force a save with:

If Me.Dirty Then Me.Dirty = False

or

DoCmd.RunCommand acCmdSaveRecord

 

[wilderfan]

I'll give it a try.

Thx, Paul.

 

[wilderfan]

That helped.

For the combo box problem, I used DoCmd.RunCommand acCmdSaveRecord in the coding for the Save command button (together with me.combobox.requery in the coding for the combo box control).

But I still have a validation problem on the other form when entering in a completely new person.

I have some validation coding in the BeforeUpdate event of the unbound text box when a new person is entered. If that name already exists, a message appears to advise the user. When he/she clicks on the OK of the message box, I'd like the incorrect entry to disappear and the focus to remain on the text box.

Currently, I'm using:

Cancel=True
Me.ubdtextbox.Undo

The cursor goes back to the lefthand side of the text box but the original entry remains visible (although the font switches to white and the background to black).

QUESTION: Is there a way to clear / delete the original entry?


PS I tried:

ubdtextbox = null

and also

ubdtextbox = ""

but got an error message both times.

 

[wilderfan]

I think I solved the validation problem with the unbound text box.

Within the If structure, I placed the following code:

MsgBox "This name already exists. Try again."
Cancel = True
Me.ubdtextbox.Undo
DoCmd.RunCommand acCmdDelete

The above coding allows me to eliminate the incorrect entry AND return the focus to the now empty text box.

If anyone sees any potential problems with this code sequence, let me know. For now, it seems to be the solution.

 

[JANR]

If it's a unboundtextbox then this: Me.ubdtextbox = "" should work.

I'v see you tried it, but did you put Me.ubdtextbox = "" or just ubdtextbox = "" ?

If the latter then that would explain the error and why it did'nt work

JR

 

[pbaldy]

JR, I'm curious if you've tested this or are just assuming it will work. In my experience, you can't change the value in the before update event.

 

[JANR]

I'v testet it, but not in the right event :-( and yes Before_update this will not work, and the OP's solution will or just use the ESC button when the focus is set to the control will also do.

JR