Full Version: Not On List And After Update Conflict
UtterAccess Forums > Microsoft® Access > Access Forms
aldeb
I will try and describe my issues as best I can.
I have a NotOnList Event on a Combo box.
CODE
Dim strSQL As String
im i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Problem...")
    If i = vbYes Then
      strSQL = "Insert Into TroubleShootingGuideTBL([Problem]) values ('" & NewData & "')"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
    End If


This worked just fine until I added an AfterUpdate Event used for Cascading Combo Boxes.
CODE
   On Error Resume Next
   PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause " & _
            "FROM TroubleShootingGuideTBL " & _
            "WHERE TroubleShootingGuideTBL.Problem = '" & Problem.Value & "' " & _
            "ORDER BY TroubleShootingGuideTBL.PossibleCause;"

When I add something that is not current in the recordsource for the combo I still get the msg box:
"is not on the list, Do you want to add it? click yes or no.
I click yes and then I now get this error:
"The text you entered isn't an item in the list. Select an item from the list or enter text that matches one of the listed items"

HAs I said earlier the NotOnListEvent worked just great until I added the AfterUpdate Event. Any suggestions for me? Thanks in advance
R. Hicks
Are you requerying the second combo in the After Update event ???
e.PossibleCause.Requery
RDH
aldeb
I inserted the code you sent in the AfterUpdate event and received the same errors
aldeb
I have discovered that the TroubleShootingGuideTBL is actually updating. The table that will not update is the Table the Form is populating.
aldeb
HELP! Anyone out there who can help with this problem???
aldeb
Is this post on the wrong forum? If so, how can I get this post moved to a different Forum where I might get some help. Maybe it should be with a VBA Code Forum. Please advise.
kbrewster
Maybe this would work??
CODE
  On Error Resume Next
   Me.PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause " & _
            "FROM TroubleShootingGuideTBL " & _
            "WHERE TroubleShootingGuideTBL.Problem = '" & Problem.Value & "' " & _
            "ORDER BY TroubleShootingGuideTBL.PossibleCause;"
aldeb
Thanks Kristen,
added the Me. to the code but am still receiving the same error.
aldeb
If I remove the Cascade ComboBox Code the NotOnEvent works just great. After I have added the AfterUpdate Cascade ComboBox code is when I get the error.
kbrewster
What is this line...
WHERE TroubleShootingGuideTBL.Problem = '" & Problem.Value & "' "
Is Problem a control on your form? And why are you using .Value?
aldeb
This is my row source for my Problem Field Combo Box on my Form.
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL ORDER BY TroubleShootingGuideTBL.Problem;
This is my code for the NotOnList Event so my ComboBox source will update if the information is not already part of the ComboBox dropdown. This will then add the information automatically to the Source Table for the ComboBox and make it part of the dropdown for future use.
CODE
Private Sub Problem_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
O= MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Problem...")
    If i = vbYes Then
      strSQL = "Insert Into TroubleShootingGuideTBL([Problem]) values ('" & NewData & "')"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
    End If
End Sub

This works just fine.
When I add the code for Cascading CombBoxes is where I get into trouble. The RowSource for the Problem Combo Box changes due to the Code on the AfterUpdate Event.
CODE
Private Sub Problem_AfterUpdate()
   On Error Resume Next
   PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause " & _
            "FROM TroubleShootingGuideTBL " & _
            "WHERE TroubleShootingGuideTBL.Problem = '" & Problem.Value & "' " & _
            "ORDER BY TroubleShootingGuideTBL.PossibleCause;"
End Sub

The RowSource changes with the Code above for Cascading ComboBoxes.
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL WHERE TroubleShootingGuideTBL.SystemGroup='CWT' ORDER BY TroubleShootingGuideTBL.Problem;
I either have to be able to use both RowSource entries or find a different way to Cascade the Combo Boxes or something? Any more help?
kbrewster
You did not answer my question.
My question was...
What is 'Problem.Value'? Is Problem a control on your form?
aldeb
This is the RowSource for my Problem Field Combo Box on my Form.
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL ORDER BY TroubleShootingGuideTBL.Problem;
This is my code for the NotOnList Event so my ComboBox source will update if the information is not already part of the ComboBox dropdown. This will then add the information automatically to the Source Table for the ComboBox and make it part of the dropdown for future use.
CODE
Private Sub Problem_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
O= MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Problem...")
    If i = vbYes Then
      strSQL = "Insert Into TroubleShootingGuideTBL([Problem]) values ('" & NewData & "')"
            CurrentDb.Execute strSQL, dbFailOnError
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
    End If
End Sub

This works just fine.
-------------------------------------------------------------------------------------------------------------------------------------
When I add the code for Cascading CombBoxes is where I get into trouble. The RowSource for the Problem Combo Box changes due to the Code on the AfterUpdate Event.
CODE
Private Sub Problem_AfterUpdate()
   On Error Resume Next
   PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause " & _
            "FROM TroubleShootingGuideTBL " & _
            "WHERE TroubleShootingGuideTBL.Problem = '" & Problem.Value & "' " & _
            "ORDER BY TroubleShootingGuideTBL.PossibleCause;"
End Sub

The Problem ComboBox RowSource changes with the Code above for Cascading ComboBoxes.
Original RowSource:
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL ORDER BY TroubleShootingGuideTBL.Problem;
Becomes:
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL WHERE TroubleShootingGuideTBL.SystemGroup='CWT' ORDER BY TroubleShootingGuideTBL.Problem;
[/b]
I either have to be able to use both RowSource entries or find a different way to Cascade the Combo Boxes or something? Any more help?
aldeb
Sorry, I thought I did. Problem is a ComboBox on my form.
Thanks for your help!
kbrewster
Oh, yeah you did..sorry! There is no need to keep re-posting your code...I can read it from your original post! You are just making it hard to scroll through everything when you have to re-read he code 3 times!
Is the combo box just one column?
kbrewster
Ok how about this...
On Error Resume Next
PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause, TroubleShootingGuideTBL.Problem" & _
"FROM TroubleShootingGuideTBL " & _
"WHERE TroubleShootingGuideTBL.Problem = '" & Me.Problem.Value & "' " & _
"ORDER BY TroubleShootingGuideTBL.PossibleCause;"
aldeb
Sorry, I did not mean to post the code the extra time.
inserted the code you sent to the AfterUpdate Field of the Problem ComboBox and I got the following error:
"The text you entered isn't an item in the list. Select an item from the list or enter text that matches one of the listed items"
For your info:
The TroubleShootingGuideTbl did update with the value that was not part of the dropdown though. The table the Form is populating would not update.
Also, in order for the NotOnEvent code to work the Limit to list has to be yes. I just thought I would provide that info in case it was of value.
Allen
kbrewster
Have you tried refreshing the PossibleCause combo box rowsource?
e.Refresh
OR
Me.PossibleCause.Requery
aldeb
On which event would I insert the code, AfterUpdate or NotOnList?
kbrewster
Use it after the code that adds the new record to the table...which would be the NotInList
aldeb
Me.PossibleCause.Requery give the following error:
You must save the field before you can run the requery action
Me.Refresh would not let me get rid of the MSG Box where I click yes or no
kbrewster
Is there anyway you could post a stripped down copy of your DB with just the table, queries, forms in question?
kbrewster
Ok maybe if you do this you can better trap the error:
Dim strSQL As String

Me.PossibleCause= Null

strSQL = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause, TroubleShootingGuideTBL.Problem
FROM TroubleShootingGuideTBL "
strSQL = strSQL & " WHERE TroubleShootingGuideTBL.Problem = '" & Me.Problem.Value & "'"
strSQL = strSQL & " ORDER BY TroubleShootingGuideTBL.PossibleCause;"
Me.PossibleCause.Rowsource = strSQL
aldeb
Where does this code go or what does it replace?
kbrewster
Just comment out your current code...
On Error Resume Next
PossibleCause.RowSource = "SELECT DISTINCT TroubleShootingGuideTBL.PossibleCause, TroubleShootingGuideTBL.Problem" & _
"FROM TroubleShootingGuideTBL " & _
"WHERE TroubleShootingGuideTBL.Problem = '" & Me.Problem.Value & "' " & _
"ORDER BY TroubleShootingGuideTBL.PossibleCause;"
aldeb
I am still getting the same error. It will update the Combo Source Table but not the Form Table.
I am trying to make the database small enough to post but jeeze I have deleted almost every record
and I think it is still too big.
aldeb
Kristen
think the database just posted.
kbrewster
Ok I see the problem here...
On the Problem combo box you have this as the rowsource...
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL WHERE TroubleShootingGuideTBL.SystemGroup='CWT' ORDER BY TroubleShootingGuideTBL.Problem;
So you are filtering only problems where the SystemGroup = 'CWT', but when you add a the new data to the table, you are not specifying what the SystemGroup is, so it will never show up in the Problem combo box...make sense?
aldeb
Yes it does but that RowSource changes with the code depending on the Cascading ComboBox. It is dependent on what the Value is in the SytemGroup ComboBox. So if the SytemGroup ComboBox value changes to Air Filter then the RowSource will change for the Problem ComboBox accordingly. Is there any way to solve this issue.
llen
And thanks!
kbrewster
Well maybe you should change the Problems rowsource to...
SELECT DISTINCT TroubleShootingGuideTBL.Problem FROM TroubleShootingGuideTBL WHERE TroubleShootingGuideTBL.SystemGroup=[Forms]![WorkUnitsFaultsMainFRM]![SystemGrou
p] ORDER BY TroubleShootingGuideTBL.Problem;
And then refresh the form every time the SystemGroup is changed.
But if you are filtering the Problem combo box based on another combo box you have to give the new data a SystemGroup so that the NotInList message does not keep appearing.
aldeb
How can I give the new data a SystemGroup?
The Problems Combobox is dependent on the SystemGroup ComboBox
The PossibleCause ComboBox is dependent on the Problem ComboBox
kbrewster
You will have to add a line to your INSERT INTO statement...when you add the new Problem to the table you will also have to add the SystemGroup
aldeb
Sorry to be such a pain but how do I do that? I am pretty new to this and really do not know how.
llen
kbrewster
You already have it started....you have to do some of this yourself or you will never learn!!

<font class="small">Code:</font><hr /><pre>strSQL = "Insert Into TroubleShootingGuideTBL([Problem], [SystemGroup]) values ('" & NewData & "'" & "'"[Forms]![TheFormName]![SystemGroup] & "')"
</pre><hr />


I am not sure about the ' and "...I did not test this!
Edited by: kbrewster on Thu Jun 8 15:56:03 EDT 2006.
aldeb
Hello again, I am at wits end!!
I have worked on this issue since last week. I have searched every forum, helps, faq's I can find and I cannot resolve the issue. I am hoping that someone will step up here and help me get this solved. I took the code that Kristen supplied and could not get it to work. I am willing to change the cascading combo code, the NotInList code or both.
PLEASE HELP!!!
aldeb
Frank,
Are you out there? Do you have any help with this issue?
Thanks Allen
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.