T
Tony Vrolyk
I am very tired these last few days so I am probably overlooking something
simple, but here it goes.
I wanted to add a procedure to the Not On List event of a combo box to add
the new data to the underlying table. I copied the code that I used elswhere
in the same app, and adjusted it accordingly. Below is what I came up with
but it doesn't work.
When I type something not on the list it prompts me to add it as it should.
But if I choose yes it immediately comes back with the standard "what you
typed is not on the list" and I get stuck in a loop. I can again try to
leave the field, I am prompted to add the new item, I click yes and I again
am given the not on list error.
The data IS getting added to the table. Immediately after I click yes to the
first prompt, I can go to the table and see that the new data is there. If I
go back to design view I can look at the combo box row source in the query
builder, execute the query and the new data is there. If I then re-open the
form the new data is now on the list. It is just not showing up in the
rowsource of the combo box suring the not on list event.
I thought about adding a requery to the combo box somewhere in the event,
but that seems silly since I do not have to do it in the other form where I
first copied this code from.
**code start**
Dim db As DAO.Database, rst As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Out Of Pocket. Do you want
to add it to the list? Click Yes to add or No to re-type it."
If NewData <> "" And Not IsNull(NewData) Then
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new benefit option?") =
vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Lookup_PlanDesign_Coinsurance",
dbOpenDynaset)
On Error Resume Next
rst.AddNew
rst!CoInsurance = NewData
rst!OutOfPocketMax = -1
rst!CoinsuranceSort = 9999
rst.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Else
DoCmd.CancelEvent
Me.INOutOfPocketInd = Me.INOutOfPocketInd.OldValue
End If
**code end**
Any thoughts
Tony Vrolyk
simple, but here it goes.
I wanted to add a procedure to the Not On List event of a combo box to add
the new data to the underlying table. I copied the code that I used elswhere
in the same app, and adjusted it accordingly. Below is what I came up with
but it doesn't work.
When I type something not on the list it prompts me to add it as it should.
But if I choose yes it immediately comes back with the standard "what you
typed is not on the list" and I get stuck in a loop. I can again try to
leave the field, I am prompted to add the new item, I click yes and I again
am given the not on list error.
The data IS getting added to the table. Immediately after I click yes to the
first prompt, I can go to the table and see that the new data is there. If I
go back to design view I can look at the combo box row source in the query
builder, execute the query and the new data is there. If I then re-open the
form the new data is now on the list. It is just not showing up in the
rowsource of the combo box suring the not on list event.
I thought about adding a requery to the combo box somewhere in the event,
but that seems silly since I do not have to do it in the other form where I
first copied this code from.
**code start**
Dim db As DAO.Database, rst As Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available Out Of Pocket. Do you want
to add it to the list? Click Yes to add or No to re-type it."
If NewData <> "" And Not IsNull(NewData) Then
If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new benefit option?") =
vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("Lookup_PlanDesign_Coinsurance",
dbOpenDynaset)
On Error Resume Next
rst.AddNew
rst!CoInsurance = NewData
rst!OutOfPocketMax = -1
rst!CoinsuranceSort = 9999
rst.Update
If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
End If
Else
DoCmd.CancelEvent
Me.INOutOfPocketInd = Me.INOutOfPocketInd.OldValue
End If
**code end**
Any thoughts
Tony Vrolyk