NotInList combobox AfterUpdate procedure not working

[p-rat]

I have an underlying table that contains two fields LeaseName and
LeaseNumber. I have a combobox on my form that when a LeaseName is
selected an unbound textbox is autofilled with the LeaseNumber
(AfterUpdate event of my combobox).

When a value is entered that isn't in list (LimitToList = Yes) I have
a pop-up form that has two textboxes that update the underlying table
(OnNotInList event). All looks good and the two textbox values get
saved to the underlying table. The combobox shows the new or requeried
value and that is fine; however the combobox AfterUpdate procedure
that is suppose to autofill my unbound textbox doesn't work because no
value (the second column of my underlying table that has just been
updated) is shown.

If I create a new record of my main form and it then shows the newly
entered value to the combobox list it will then autofill the unbound
text box.

I'm new to Access and have stumbled through most of this, but am
stumped. Any help would be appreciated. Thanks.

 

[Dale Fye]

Post the code in your NotInList event.

Basically, after you have entered the data in your popup form and saved that
record, you need to requery the combo box, set its value to the recently
added name, and then call the combo boxes AfterUpdate event.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[p-rat]

Private Sub LeaseName_NotInList(NewData As String, Response As
Integer)
Dim sqlAddState As String, UserResponse As Integer
DoCmd.OpenForm "NEWLeaseNameandNumber", , , , , acDialog

Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into LeaseNameandNumber ([LeaseName])
values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

 

[p-rat]

I had to add the following line of code to my OnNotInList event and
now my AfterUpdate event works. Thanks for your help.

sqlAddState = "Insert Into LeaseNameandNumber ([LeaseNumber]) values
('" & NewData & "')"

Private Sub LeaseName_NotInList(NewData As String, Response As
Integer)
Dim sqlAddState As String, UserResponse As Integer
DoCmd.OpenForm "NEWLeaseNameandNumber", , , , , acDialog

Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
    If UserResponse = vbYes Then
        sqlAddState = "Insert Into LeaseNameandNumber ([LeaseName])
values ('" & NewData & "')"
        CurrentDb.Execute sqlAddState, dbFailOnError
        Response = acDataErrAdded
    Else
        Response = acDataErrContinue
    End If

End Sub

Post the code in your NotInList event.

Basically, after you have entered the data in your popup form and savedthat
record, you need to requery the combo box, set its value to the recently
added name, and then call the combo boxes AfterUpdate event.
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Dale Fye]

I'm confused.

You mentioned that you had [LeaseName] and [LeaseNumber] in the table. If
you do two separate inserts into the table, one with the name, and the other
with the number, you will have two records (one with only the name, one with
only the number).

What does the code look like now? It may work, but I doubt it is working
the way you actually want it to. Also, what is the data structure of your
LeaseNameAndNumber table (field names, and data types)?

Dale


I had to add the following line of code to my OnNotInList event and
now my AfterUpdate event works. Thanks for your help.

sqlAddState = "Insert Into LeaseNameandNumber ([LeaseNumber]) values
('" & NewData & "')"

Private Sub LeaseName_NotInList(NewData As String, Response As
Integer)
Dim sqlAddState As String, UserResponse As Integer
DoCmd.OpenForm "NEWLeaseNameandNumber", , , , , acDialog

Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into LeaseNameandNumber ([LeaseName])
values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

Post the code in your NotInList event.

Basically, after you have entered the data in your popup form and saved
that
record, you need to requery the combo box, set its value to the recently
added name, and then call the combo boxes AfterUpdate event.
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

- Show quoted text -- Hide quoted text -

- Show quoted text -