Not In List and After Insert (2000)

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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.”

Reply | Quote

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 !!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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. 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.
2. 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.

Reply | Quote

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 ‘ They clicked Yes
DoCmd.OpenForm “frm_Phones”, acNormal, , , acFormAdd
Forms![frm_Phones]![PhoneNumber] = NewData
Forms![frm_Phones]![PhoneType_fk] = 1 ‘ Default type to Home Phone
Forms![frm_Phones]![Caller] = 1
Response = False
Else ‘ They clicked No
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

Reply | Quote

Thanks Wendell, it seems to work now.
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 . Still, I guess I can tell myself it’s cool.

Reply | Quote

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!

Reply | Quote