Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not In List and After Insert (2000)

    I'm using a combo box to select a client name for a project.
    The record source for the combo is a query on the table tblClientList, containing only an ID number (ClientID) and name (ClientName).
    Making a selection from the combo store sticks the ClientID value in the table containing project information (tblSites).

    I've used some code from the Access Developer's Handbook (chapter 7) to deal with the Not InList event for a combo box. Their version uses an unbound control so I've had to modified the properties of the combo box slightly (changing the bound column to the ClientID value instead of the name) as my control is bound to the ClientID field in my table tblSites.

    I'm also running an update query triggered by the After Insert event for the form.

    Data is entered on different pages on a tab control on the form.

    It seems to work but with a slight oddity.

    If I pick a value from the combo box (a client name) a new record (in tblSites) is added, the After Insert event is fired and my query is run (populating another table with information which can then be edited on a different page on the tab control).

    However when I add a new value (new client) through the combo, although a new record appears to be created (a number appears in the SiteID control -an autonumber field), the After Insert event does not appear to be triggered. Closing and re-opening the form or typing a value in another text box triggers this event.

    I'm guessing I'm missing something simple like a requery, but I'm not sure.
    Alternatively I've got the whole concept of bound columns in combo boxes wrong, any suggestions?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    Here we go, replying to my own post.
    It now works but only if:

    I put a control on the form (a text box) whose control source is the ClientID in the main table (tblSites)


    I still think I'm doing something a bit wierd. Here's some more information.

    Form: frmSiteInfoDataEntry
    Control Source: tblSites

    Name: cboClientList
    Control Source: ClientID (in tblSites obviously)
    Row Source Type: Table/query
    Row Source: SELECT [tblClientList].[ClientName], [tblClientList].[ClientID] FROM tblClientList ORDER BY [tblClientList].[ClientName];
    Bound Column: 2

    Is my problem (is it a problem?) because the row source is from a different table to the control source for the control?

  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 and After Insert (2000)

    A combobox commonly has a rowsource from a different table than the control source.

    Why are you running an update query? Are you using the combobox to add just a new client name to a table and then letting them fill in the rest of the information on the tab control? Why not pop up a form from the NotInList event and let them add the additional information at the time the client record is created? Selecting the item from the combobox handles updating the table your form is bound to. So what does the update query do?

    And why use the AfterInsert event? That only applies to new records, while the AfterUpdate event applies to both new records and changes to existing records.
    Charlotte

  4. #4
    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 and After Insert (2000)

    I think the issue may be in the way you are adding a new record. Your first post indicated you were running some code after the insert to do requery - how are you initiating the addition of a site record? There are several ways - I would probably put the form into Data Entry mode which initiates an add. As to your combo, it appears to be OK. One thing you might look at is switching your field order in the Row Source, making your bound column the first one, and then making that column width 0 so the Client Name always appears. Access can be a powerful but confusing beast!
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    The form I am using is not in Data Entry mode, it allows additions, edits, deletions. i.e. the default settings.
    I think I may have confused both you and Charlotte, mentioning my update query.
    What this does is add a set number of rows to another table on the 'many' side of a one-to-many relationship with the site table. I need to add the same values everytime I add a new site. This is nothing to do with what is in the combo box (how do I include a link to previous postings on this subject?)
    At some point in the future I may want to allow the user to choose from a list of values to be added - but that isn't in the scope of this post. The query runs once, as a new record (After Insert) is added to the site table (by typing something on the form).

    Without the combo oddity what happens is this.
    I open the data entry form, type something, normally the site name (a new record is created), the After Insert event triggers a query, records are added to another table, which is then displayed via a sub form on another page on a tab control ( the subform wasn't appearing at first until I added the [sfrmSiteReviewCriteriaDataEntry].Requery to the After Insert event).

    If I use the combo box first to add a piece of information (the client name), the same thing happens, no problem. Hope you are with me so far.

    What is wierd is if I add a new client name via the combo box, using the Not In List event. Even though a new client name is added to the right table and a new site record seems to be added, if I move to the page with the subform I can't see anything (i.e it is blank, empty, no controls can be seen).

    If I then enter some information (in any text box whose control source is in the main table) and move to the page with the subform I can see everything I expect.
    Have just noticed something else. Not In List takes me to a popup form to add the new client. Clicking OK on that form closes it and the combo on the main form displays the new value but no control actually has the focus.
    I've just clicked inside the combo box and this seems to trigger the query correctly.

    As I said it also works ok if I have a text box displaying the ClientID. What I'm worried about is fudging something to make it work when I could be missing something simple. I don't want to get into bad habits!!



    Beast is the right word.

  6. #6
    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 and After Insert (2000)

    Oh yes - I forgot about the subform you mentioned in a previous post. The trick with subforms is that the record being added has to be saved before the subform can display it. And I think the problem is that the combo box doesn't have focus when you return from it, so the form doesn't yet know it is linked to the new record just added, so the subform can't link to it just yet. I believe just setting focus on the combo box when you close the pop-up form will do the trick.

    Subforms cause lots of people problems. Access does lots of stuff "under the covers" which make them a very powerful control, but they are much more complex than they initially appear. However that is one of the real advantages of Access. People who do serious development in Access will tell you that it typically takes a third of the time to develop applications in Access compared to any other method, and subforms are a large part of that advantage. Trying to duplicate subform in other enviroments, e.g. VB, is a substantial undertaking.

    I think you are on track here - it's a bit of a challenge to know when and where to Set Focus, Refresh or Requery but that will come with experience. The reward is users who say "That's really cool."
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    On the right track but so far off it it's untrue.
    I figured on using cboClientName.SetFocus and I've been trying it before I read your last post. Just can't figure out where to put the thing.

    Here's the code I'm using (a function and event procedure triggered by the Not In List Event)

    ' From Access 2000 Developer's Handbook, Volume I
    ' by Getz, Litwin, and Gilbert (Sybex)
    ' Copyright 1999. All rights reserved.

    Private Function IsLoaded(strName As String, _
    Optional lngType As AcObjectType = acForm) As Boolean
    IsLoaded = (SysCmd(acSysCmdGetObjectState, _
    lngType, strName) <> 0)
    End Function

    Private Sub cboClientName_NotInList(NewData As String, Response As Integer)
    Dim mbrResponse As VbMsgBoxResult
    Dim strMsg As String

    strMsg = NewData & _
    " isn't an existing client. " & _
    "Add a new client ?"
    mbrResponse = MsgBox(strMsg, _
    vbYesNo + vbQuestion, "Invalid Client")
    Select Case mbrResponse
    Case vbYes
    DoCmd.OpenForm "frmClient", _
    DataMode:=acFormAdd, _
    WindowMode:=acDialog, _
    OpenArgs:=NewData

    ' Stop here and wait until the form
    ' goes away.
    If IsLoaded("frmClient") Then
    Response = acDataErrAdded
    DoCmd.Close acForm, "frmClient"
    Else
    Response = acDataErrContinue
    End If
    Case vbNo
    Response = acDataErrContinue
    End Select

    End Sub

    If I try to put the line cboClientName.SetFocus anywhere in or after the Select Case bit it keeps re-triggering the Not In List event and the program is stuck in an unbreakable loop I can't get out of !! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    Darsha,
    The cause of all your trouble is that your code never gets to process the requery of the combobox. The way the NotInList event is coded the Response = acDataErrAdded stmt is never executed, since your frmClient will never be loaded at the point in your code where you check for it

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    OK, now I'm really baffled.
    Does anyone have a better example of dealing with Not In List, using a popup form to input the new item. I beginning to think the example I've used is way over my head.

  10. #10
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    Darsha,

    From your description I read that tblClients has two fields only: ClientID and ClientName. I assume that ClientID is an autonumber.
    If true, replace the code from DoCmd.OpenForm through End If (before Case vbNo) with this:

    Dim dbs as Database
    Dim rst as RecordSet
    Set dbs=CurrentDb
    Set rst=dbs.OpenRecordset("tblClientList")
    rst.AddNew
    rst!ClientName=NewData
    rst.Update
    rst.close
    Response=acDataErrAdded

  11. #11
    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 and After Insert (2000)

    After looking at your code, I think Claus is right. We usually use an actual po-up form to add a record based on the response from a user and let the code finish to completion from the Not In List. Then when the pop-up form is closed, we insert the record, do the requery, etc, and actually populate the combo and set the focus to it. I'll dig up an example and comment it so it is hopefully readable. You should have it by the time you awaken tomorrow. Unfortunately I need to go meet with a client.
    Wendell

  12. #12
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    Darsha,

    If you choose to stick to the idea of popping up the frmClient form to add a new client - here is how you can use your original setup (and NotInList event) with a minor modification to your frmClient code.

    Your posted code sequence in the NotInList event will work fine if your frmClient will act like this:
    1. <LI>If the new client record is accepted make sure you don't close the frmClient form - just hide it. The stmt to do this is: Me.Visible=False. Do a Me.Refresh or Docmd.RunCommand acCmdSaveRecord stmt before that in order to ensure that the record is saved. This should be handled by the code behind an "Add Client" button.
      <LI>If the client entry is cancelled use a Me.Undo stmt and close the form. This should be handled by the code behind a "Cancel" button.
    Either case will return control to the stmt following your DoCmd.OpenForm "frmClient" stmt in your NotInList event. The trick is that the form (frmClient) will still be open (but invisible) if the client was added and your code will operate correctly.

    Make sure that the only way to leave the frmClient form is via one of the two buttons described in 1 and 2 by setting the CloseButton property on the form's property sheet to False.

  13. #13
    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 and After Insert (2000)

    Here are the procedures I promised you earlier today:

    This procedure is executed when the No In List event is triggered on a combo box called HomePhone_fk that is located on a form called frm_Selector

    Private Sub HomePhone_fk_NotInList(NewData As String, Response As Integer)
    Dim msgResult As Integer

    msgResult = MsgBox("It appears you are trying to add a new phone number or _
    email address - is that your intent?" & vbCr & "If you want to change an _
    existing phone number, click no and Double Click on the phone number.", _
    vbYesNo, "Add New Phone/eMail?")
    If msgResult = 6 Then <font color=448800>' They clicked Yes</font color=448800>
    DoCmd.OpenForm "frm_Phones", acNormal, , , acFormAdd
    Forms![frm_Phones]![PhoneNumber] = NewData
    Forms![frm_Phones]![PhoneType_fk] = 1 <font color=448800>' Default type to Home Phone</font color=448800>
    Forms![frm_Phones]![Caller] = 1
    Response = False
    Else <font color=448800>' They clicked No</font color=448800>
    MsgBox "Then please select one of the existing entries.", vbOKOnly, "Select Existing Entry"
    Response = True
    End If

    End Sub


    When the phone number or email address and other information on the form have been entered, they click a button that invokes the code that follows. Note that the form frm_Phones can be called from several forms which is why the IsLoaded function tests to see who called it. In the case of frm_Selector, there are actually two combos that can be used to call the same form, so it sets a hidden control on frm_Phones called Caller so frm_Phones knows which control to set and requery. Also note that the IsLoaded function may have to be added to your database
    Wendell

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not In List and After Insert (2000)

    Thanks Wendell, it seems to work now. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Right now I don't need options in the If isLoaded bit but its handy to see what to do when I need to do it in the future.

    Regarding one of your previous comments about users saying 'that's cool'. If only!
    Even if I manage to do anything bordering on 'cool' the users seem to think its something built in to Access, not appreciating the blood, sweat and tears I have shed <img src=/S/weep.gif border=0 alt=weep width=21 height=16>. Still, I guess I can tell myself it's cool.

  15. #15
    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 and After Insert (2000)

    Prehaps our users are less sophisticated than yours. We do still get the "Gee, I didn't know a computer could do that" response on occasion. It happens most often with automation stuff where we generate a Word document from Access, or create a Task with reminder in Outlook based on data entered in Access. On the other hand, if we screw up they aren't so accomodating! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>
    Wendell

Posting Permissions

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