Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Not in List question (Acc 97 sr2 on 95b)

    I have a quote form that is tied to a tblQuote table. There is a combo box on the form that is bound to a field in the table called customers. This field is a lookup field to a table called tblCustomers.

    Limit to list is currently set to off so that a quote may be raised for an existing customer or a new customer name can be entered. This new customer name is saved to the customer field in tblQuote but not saved in the tblCustomer table.

    If the quote goes live (becomes a job) then a button is clicked which uses data from the quote form (including the name) to create OpenArgs and the new job form is opened and the values placed.

    In this form, limit to list is set so a new customer name triggers the sub which opens the new customer input form. When this is closed and the new job form becomes active, a requery is triggered so that the new customer name is now accepted.

    ..Sort of......well it almost works.....I have a problem with the last step, the Not in list seems to fire twice, because of the requery I think.

    Rather than ask one of you people to debug my forms, I want to try another way.....

    Does the Not inList function only trigger if Limit to List is set to true?

    I was wondering if I could use Not in list to set a yes/no in another field to flag a new customer, but still have the customer accepted into the quote table? this way, when a quote goes live, I can check the flag and open the New Customer form if needed before opening the New Job form.

    Then again, if anyone wouldn't mind having a look at my forms I would be grateful.
    "Heading for the deep end"

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    If you set Response to acDataErrAdded after the customer form closes, the requery shouldn't be necessary (Access should handle that automatically) and the NotInList event shouldn't fire again.

    In situations like this, I use code resembling the following:

    Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
    If MsgBox("'" & NewData & "' is not a customer yet. Do you want to add it?", vbYesNo) = vbYes Then
    DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acDialog, NewData
    Response = acDataErrAdded
    Else
    CustomerID.Undo
    Response = acDataErrContinue
    End If
    End Sub

    If this doesn't help, post back or do a search on this Forum. There are plenty of threads on NotInList.

    NewData is passed to frmCustomers in the OpenArgs argument. I use this in the OnLoad event of frmCustomers to fill in the corresponding field, so that the user doesn't have to type it again.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in List question (Acc 97 sr2 on 95b)

    Hans forgot to mention that yes, the NotInList event is only triggered if the Limit to List property is set to Yes.
    Charlotte

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    How stupid of me. Must be old age setting on...

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Not in List question (Acc 97 sr2 on 95b)

    You're only as old as you feel, Hans. <img src=/S/granny.gif border=0 alt=granny width=20 height=20>
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    Right, but forgetting to answer questions makes me *feel* old! <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    Well, some of us are just plain <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    Many thanks for the info Hans. I didn't have acDataErrAdded, nor was I using acDialog. I have added both to my event and the whole process now works flawlessly (or so it seems).

    The more I learn, the more I realize I need to learn
    "Heading for the deep end"

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Not in List question (Acc 97 sr2 on 95b)

    Thankyou Charlotte, for clearing up that point. I thought that would be the case, but with my lack of programming skills, lots of things I think impossible I find here on the forum as quite do-able.
    "Heading for the deep end"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •