After update Check text box

[lcook1974]

What is wrong with this code?

Private Sub OT_AfterUpdate()
If Me.OT = DLookup("OT", "ObjectiveTitle", Me.OT) Then
MsgBox "This Title already exists, please choose this from the Lookup drop down box", vbOKOnly _
, "Title Already exist"
Me.OT.Undo
Else: Me.cmbLookupTitle.Requery
End If
End Sub

If the title exists, I get the message box but it doesn't "undo" the text box and adds it to the combo box. I want it to "clear" the contents and not add it to the combo box

Larry

 

[boblarson]

You can't undo in the After Update event. You have to use the BEFORE UPDATE event to do this.

 

[lcook1974]

Okay...I put in the Before update event but it still does the same thing and adds it to the combo box.

Private Sub OT_BeforeUpdate()
If Me.OT = DLookup("OT", "ObjectiveTitle", Me.OT) Then
MsgBox "This Title already exists, please choose this from the Lookup drop down box", vbOKOnly _
, "Title Already exist"
Me.OT.Undo
End If
End Sub

It doesn't undo the text in the field

Larry

 

[boblarson]

It looks like you typed your Before update event yourself instead of letting Access do it. Because it isn't correct. It SHOULD look like this;

Private Sub OT_BeforeUpdate([COLOR="Red"]Cancel As Integer[/COLOR])
If Me.OT = DLookup("OT", "ObjectiveTitle", Me.OT) Then
MsgBox "This Title already exists, please choose this from the Lookup drop down box", vbOKOnly _
, "Title Already exist"
[COLOR="red"]Cancel = True[/COLOR]
Me.OT.Undo
End If
End Sub

 

[lcook1974]

Nah, I just typed it in on this code box for the forum. I think you gigged me on that same thing a while ago.

I'll give it a go....

Larry

 

[lcook1974]

Okay that worked, but for some reason it isn't working on the 5 items I have in the table. I am posting the DB for you to check it out.

I know it is something little...(shaking my head in frustration)

so if you choose a new record and type in "consent" you should receive the Msgbox...but I would think it should work with all of the items in the table.

Larry

 

[boblarson]

Okay, a couple of questions...

1. Why is OT bound? Why use a text box instead of the combo box to select?

2. By doing so you complicate things. You can use the combo's Not In List event to add new items if you wish.

 

[lcook1974]

I tried to just use the combo box but it wasn't working. There was a DBA at my old job who showed me this, so I thought it useful.

I am not familiar with the Not In list event.

Trying to get my hand and arm wet in VBA instead of just finger tips...

Larry

 

[boblarson]

Okay, here is your sample revised with the not in list of the combo added.

 

[lcook1974]

Thank you Bob....I'll take a look and learn something something from it.

Larry

 

[lcook1974]

Thanks Bob!! This is a cool Event, I'll have to use it more often.

One question:
When I enter in a "not in list" item, it adds it to the list but then the form shows all the records associated with the first item in the combo box.

so if I entered: "Organ Charts" and added it to the drop down after that I will see "Organ Charts" in the combo box but all records associated with "Consent". The only we so far I have been able to get records to show for the new item is by closing the form and re-opening it. I tried: me.refresh
me.requery
me.me.me

How do I get it to go to a new record based on the new "not in list" item?

Larry