[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-19
11
Medium Priority
?
363 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:TomBock2004
  • 5
  • 4
  • 2
11 Comments
 
LVL 9

Expert Comment

by:TriggerHappy
ID: 12626302
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
 

Author Comment

by:TomBock2004
ID: 12626760
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
 
LVL 9

Expert Comment

by:TriggerHappy
ID: 12626882
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:TomBock2004
ID: 12627159
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12628159
The issue is that you set Position to Null in the NotInList and then try to use it in the Find.

Steve
0
 

Author Comment

by:TomBock2004
ID: 12628290
SteveBe:

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

Tom
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12629322
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
 

Author Comment

by:TomBock2004
ID: 12629925
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
 
LVL 39

Expert Comment

by:stevbe
ID: 12663948
Try leaving your original code alone and just remove

Me.Position = Null

Steve
0
 

Author Comment

by:TomBock2004
ID: 12664759
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
 
LVL 39

Accepted Solution

by:
stevbe earned 1500 total points
ID: 12668159
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question