Synchronize "After Update" and "Not In List" functions

Hi:

I need some help w/ synchronizing the 2 functions... "AfterUpdate" & "NotinList".

Currently, I have a main form plus subform.   On the main form, I placed an UNBOUND combo box which contains the AFTERUPDATE event (see below)  

***********************************************
Private Sub CboMoveTo_AfterUpdate()
   
    If Not IsNull(Me.cboMoveTo) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
       
        Set rs = Me.RecordsetClone
        rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub
***********************************************

When I select a value (Position) from the combo box, its underlying details are displayed in the subform (which works great!).


As of now, I can't add value to the combo box (LimitToList = Yes).  To fix that, I added the function below which prompts me to accept to values.   If I click OK, the new value is added to the table (which also works great).

***********************************************
Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)
 
    Dim MySql As String
    Beep
   
    If MsgBox("'" & NewData & "' is currently not an existing Position. " & vbCrLf _
               & "Would you like to add '" & NewData & "' to the menu?", vbInformation + vbOKCancel, "Info") = vbOK Then
       MySql = "Insert into tblPositions (Position) VALUES ('" & NewData & "')"
       CurrentDb.Execute MySql
       Response = acDataErrAdded
    Else
       Response = acDataErrContinue
       Me.Position = Null
    End If
 
End Sub
***********************************************


Okay, here's my problem...
- I select an existing Position... which brings up the underlying details (ok)
- I enter a new value into combo box... I'm prompted to accept the value (ok)
- Then, however, the line [MsgBox "Not found: filtered?" kicks in (makes sense, but's a problem)
- Also, the underlying details from the previously selected Position remain listed

Here's what I'm trying to achieve:
- When accepting the new value in the combo box, I don't want the MsgBox "Not filtered..." appear
- I also want the details to show NO records at all (since no record details have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the form to view 0 records for the new Position value

Does anyone know how I can synchronize the 2 functions?

Thanks in advance,
Tom
TomBock2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TriggerHappyCommented:
Perhaps a boolean flag on your form.
Add this code to the top of your form code

dim boolNewFlag as boolean

then modify your notinlist event

Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)
 
    Dim MySql As String
    Beep
   
    If MsgBox("'" & NewData & "' is currently not an existing Position. " & vbCrLf _
               & "Would you like to add '" & NewData & "' to the menu?", vbInformation + vbOKCancel, "Info") = vbOK Then
       MySql = "Insert into tblPositions (Position) VALUES ('" & NewData & "')"
       CurrentDb.Execute MySql
       boolNewFlag = true
       Response = acDataErrAdded
    Else
       Response = acDataErrContinue
       Me.Position = Null
    End If
 
End Sub

And on your afterupdate event

Private Sub CboMoveTo_AfterUpdate()
   
    If Not IsNull(Me.cboMoveTo) Then
        If Me.Dirty Then
            Me.Dirty = False
        End If
       
        Set rs = Me.RecordsetClone
        rs.FindFirst "[Position] = """ & Me.cboMoveTo & """"
        If rs.NoMatch Then
            if boolNewFlag then
                    me.recordset.filter = "YourFieldName = " & me.cboMoveTo
                    me.recordset.filteron = true
                    boolNewFlag = False
            else
                    MsgBox "Not found: filtered?"
            end if
        Else
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

This of course assumes you use filters to get rid of the details data.  You can do whatever you want of course (if you want it to go to a new record for example just goto addnewrecord)
0
TomBock2004Author Commented:
TriggerHappy:

Thanks for the prompt feedback... your solution is going in the right direction... it works but it also throws an error.

Here is what's going on...
- I add new value... and accept it (ok)
- It does "wipe out" the existing details (ok)
- I add new details and it add new autonumbers to the newly entered position (ok)

Now, where's the problem:
- After I added a 1st new record (like described above) but then add a 2nd new record it throws a runtime error: "Run-Time error 438.   Object doesn't support this property or method."
- Now, when I click on "Debug" the line "Me.RecordSet.FilterOn = True" is highlighted.

Here's what attempted to fix, but I still get the same error:
REPLACE "me.recordset.filter = "YourFieldName = " & me.cboMoveTo"
with
me.recordset.filter = ""[Position] = " & me.cboMoveTo

And I wasn't sure what you meant by your last comment "This of course assumes you use filters to get rid of the details data.  You can do whatever you want of course (if you want it to go to a new record for example just goto addnewrecord)".

Do you have any additional suggestions as to how the runtime error can be fixed?  

Thanks,
Tom







0
TriggerHappyCommented:
OK...so here's my question here...

when you say second record, is that in the combobox or in the detail that you are adding these?

As for my last comment with the details, i think i could have answered that on my own with the bookmarking and findfirst.
So...assuming that you wish to just move to ADD a record to the entire recordset you'll need to change that filter to a move instead.

Change:
me.recordset.filter = "YourFieldName = " & me.cboMoveTo
me.recordset.filteron = true

To:
DoCmd.GoToRecord , , acNewRec

What this will do is go to add a new record if you've just added something to the combobox.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TomBock2004Author Commented:
Okay...

Yes, what I meant to say is "adding a 2nd new record to combo box" throws the run-time error.   On the 1st new record, I had no problems adding multiple details to the 1st newly created position.

I made the changes as you recommended.... I now get this error: "Index or primary key cannot contains a null value".   I think the "DoCmd.GoToRecord , , acNewRec" does not automatically add a new value to the autonumber field of the Details table.

Below is the relationship between the 2 tables (tblPosition & tblDuties):

SELECT tblPositions.PositionID, tblPositions.Position, tblJunction.PositionIDfk, tblJunction.DutiesIDfk, tblDuties.DutiesID, tblDuties.Duties FROM tblPositions INNER JOIN (tblDuties INNER JOIN tblJunction ON tblDuties.DutiesID = tblJunction.DutiesIDfk) ON tblPositions.PositionID = tblJunction.PositionIDfk;

I believe (not sure) that the cause for the initial runtime error was also based on the "creating of autonumber in tblDuties".   Although it worked fine for the 1st new value in the combo box on the mainform, adding a 2nd new value in the combo box and then adding details to the tblDuties did not create the autonumbers in the tblDuties.  Little bit odd since it worked for the 1st new combo box value... but that's what seemed happened.

Do you have any additional suggestions?

Tom



0
stevbeCommented:
The issue is that you set Position to Null in the NotInList and then try to use it in the Find.

Steve
0
TomBock2004Author Commented:
SteveBe:

Thanks for chipping in... is this something which can be fixed?   If yes, how would I go about it?

Tom
0
stevbeCommented:
don't reset to null ... either use .Undo or leave it alone and let the user deal with it.

    Else
       Response = acDataErrContinue
       'do nothing
            'or
       Me.cboMoveTo.Undo
    End If
0
TomBock2004Author Commented:
SteveBe:

Thanks... I now get the "RunTime error 438... object does not support this property or method" again.

Also, just like mentioned in one of the previous threads, the line "Me.Recordset.FilterOn = True" is highlighted in the AfterUpdate function when clicking on DEBUG.

Do you have any additional suggestions?


Thanks,
Tom
0
stevbeCommented:
Try leaving your original code alone and just remove

Me.Position = Null

Steve
0
TomBock2004Author Commented:
SteveBe:

Thanks for checking in again...

I removed the "Me.Position = Null"... which causes the originally mentioned error:

- When accepting the new value in the combo box, I don't want the MsgBox "Not filtered..." appear
- I also want the details to show NO records at all (since no record details have been entered for the new Position yet).
- Currently, again, I get the unwanted msg box & I must close/reopen the form to view 0 records for the new Position value

Tom
0
stevbeCommented:
The AfterUpdate will not fire until NotInList has finished and a different value is entered so why don't we get that working the way you want it to before working on the NotInList. I added 128 as the options parameter to CurrentDB.Execute so if there is anything wrong in executing the SQL an error will be raised.

Private Sub cboMoveTo_NotInList(NewData As String, Response As Integer)

On Error GoTo ERR_cboMoveTo_NotInList

    If MsgBox("'" & NewData & "' is currently not an existing Position. " & vbCrLf _
               & "Would you like to add '" & NewData & "' to the menu?", vbInformation + vbOKCancel, "Info") = vbOK Then
       CurrentDb.Execute "Insert into tblPositions (Position) VALUES ('" & NewData & "')", 128
       Response = acDataErrAdded
    Else
       Response = acDataErrContinue
    End If

EXIT_cboMoveTo_NotInList:
    Exit Sub

ERR_cboMoveTo_NotInList:
    Response = acDataErrContinue
    Msgbox "Unexpected error " & Err.Number & ": " & Err.Description
    Resume EXIT_cboMoveTo_NotInList
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.