Create my own 'not in list' event ?

Is it possible to create my own 'not in list' event?
I want to capture the data entered to the table but I don't want it to 
become part of the combo box contents.
What I envisage doing is in the after update event for the combo box, check 
if the value entered is not one of the currently listed values, and if not, 
insert the contents with a special flag that excludes it from the combo box 
query. 
Are the values in a combo box part of a collection I can loop through? The 
intellisense seems to indicate there is no .count property?
0
Utf
11/7/2007 8:40:01 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
613 Views

Similar Articles

[PageSpeed] 40

You can do whatever you like in the NotInList event.  You just have to end it 
with something like the following to stop the event adding to the combo box 
list.

        Me.controlname.Undo
        Response = acDataErrContinue

Before you do those two lines you probably want to open a dialog form that 
the user will use to create the new record.

        DoCmd.OpenForm "frm", _
            DataMode:=acFormAdd, _
            WindowMode:=acDialog, _
            OpenArgs:=NewData


In the new form's onLoad event you capture the new data:

    If Not IsNull(Me.OpenArgs) Then
        strNewData = Me.OpenArgs
        ' Do what you need to do to initialize the new record
    End If

-- 
Pictou


"mscertified" wrote:

> Is it possible to create my own 'not in list' event?
> I want to capture the data entered to the table but I don't want it to 
> become part of the combo box contents.
> What I envisage doing is in the after update event for the combo box, check 
> if the value entered is not one of the currently listed values, and if not, 
> insert the contents with a special flag that excludes it from the combo box 
> query. 
> Are the values in a combo box part of a collection I can loop through? The 
> intellisense seems to indicate there is no .count property?
0
Utf
11/7/2007 10:42:04 PM
The ListCount property of the combo box will return how many records are 
currently loaded into it.

To loop through the records, you'd use something like:

Dim lngLoop As Long

  For lngLoop = 0 To (Me.MyCombo.ListCount - 1)
    Debug.Print Me.MyCombo.ItemData(lngLoop)
  Next lngLoop

That'll return the bound column. If you wanted, say, the third column of the 
combo box, you'd use

Dim lngLoop As Long

  For lngLoop = 0 To (Me.MyCombo.ListCount - 1)
    Debug.Print Me.MyCombo.Column(2, lngLoop)
  Next lngLoop

(the Column collection starts numbering at 0)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"mscertified" <rupert@tigerlily.com> wrote in message 
news:B0CEA563-5610-40BC-8CDD-E859BB941520@microsoft.com...
> Is it possible to create my own 'not in list' event?
> I want to capture the data entered to the table but I don't want it to
> become part of the combo box contents.
> What I envisage doing is in the after update event for the combo box, 
> check
> if the value entered is not one of the currently listed values, and if 
> not,
> insert the contents with a special flag that excludes it from the combo 
> box
> query.
> Are the values in a combo box part of a collection I can loop through? The
> intellisense seems to indicate there is no .count property? 


0
Douglas
11/7/2007 10:48:59 PM
I think you misunderstood what I want. I dont want to use the NotInList 
event, I want to create my own event. This must be completely transparent to 
the user and I dont want any dialog boxes or messages to appear. I just need 
the user to be able to type text into a combo box and have it stored in the 
table that feeds the combo box. However, these values must never appear in 
the combo box list.

"Steve McLeod" wrote:

> You can do whatever you like in the NotInList event.  You just have to end it 
> with something like the following to stop the event adding to the combo box 
> list.
> 
>         Me.controlname.Undo
>         Response = acDataErrContinue
> 
> Before you do those two lines you probably want to open a dialog form that 
> the user will use to create the new record.
> 
>         DoCmd.OpenForm "frm", _
>             DataMode:=acFormAdd, _
>             WindowMode:=acDialog, _
>             OpenArgs:=NewData
> 
> 
> In the new form's onLoad event you capture the new data:
> 
>     If Not IsNull(Me.OpenArgs) Then
>         strNewData = Me.OpenArgs
>         ' Do what you need to do to initialize the new record
>     End If
> 
> -- 
> Pictou
> 
> 
> "mscertified" wrote:
> 
> > Is it possible to create my own 'not in list' event?
> > I want to capture the data entered to the table but I don't want it to 
> > become part of the combo box contents.
> > What I envisage doing is in the after update event for the combo box, check 
> > if the value entered is not one of the currently listed values, and if not, 
> > insert the contents with a special flag that excludes it from the combo box 
> > query. 
> > Are the values in a combo box part of a collection I can loop through? The 
> > intellisense seems to indicate there is no .count property?
0
Utf
11/8/2007 3:04:03 PM
I am not exactly sure what you are trying to do.  However, if you just want
to enable someone to add any item to the list just by typing it in all you
need to do to get what you want is to open the combo box's Properties Sheet,
click on the Data tab, and set the Limit to List property to No. Any new
entry typed in the combo box will then be added to the bound table field of
the combo box.  When the form is opened again or the combo box is requeried,
the new item will be displayed with the rest of the items in the list in the
combo box.

If, as you wrote, you want the item to NEVER appear in the combo box list
then you can do the following:

You can use the Before Update event of the combo box.  You must set your Not
In List Property of the Combo Box to No for this to work.

You could add a not-visible text box to the Form bound to the field for the
new entry.  Here is some code I tested for the Before Update event of the
combo box:

Private Sub myComboBox_BeforeUpdate(Cancel As Integer)
    If Not IsNull(Me.myComboBox) Then
        Me.myTextBox = Me.myComboBox
        Me.myComboBox.Undo
    End If
    Cancel = True
End Sub

This will add the typed in contents to the text box and when you save the
record it will be saved to the table.

The only drawback is the user will no longer see the value in the combo box.
However, you could make the text box that will receive the value visible for
the user to see. 

Patrick Wood

mscertified wrote:
>I think you misunderstood what I want. I dont want to use the NotInList 
>event, I want to create my own event. This must be completely transparent to 
>the user and I dont want any dialog boxes or messages to appear. I just need 
>the user to be able to type text into a combo box and have it stored in the 
>table that feeds the combo box. However, these values must never appear in 
>the combo box list.
>
>> You can do whatever you like in the NotInList event.  You just have to end it 
>> with something like the following to stop the event adding to the combo box 
>[quoted text clipped - 27 lines]
>> > Are the values in a combo box part of a collection I can loop through? The 
>> > intellisense seems to indicate there is no .count property?

-- 
Hunter 57
http://churchmanagementsoftware.googlepages.com/home

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200711/1

0
Hunter57
11/8/2007 5:58:33 PM
Reply:

Similar Artilces:

How to Link list Box with Form 03-15-08
Greeting, I have a form for entering and search for info. In this form there are text box for search and listbox for showing search results. What I want to do is once search results appear, the user will dbl click on the results he wants and the form fields show the details of that item. I put the following sql in the form control source : SELECT utilizing.* FROM utilizing; And the following sql for list box SELECT utilizing.idn, utilizing.founame, utilizing.proname, utilizing.utilizdate, utilizing.lumpvalue, utilizing.notes FROM utilizing WHERE (((utilizing.founame) Like "*"...

Want to create a database and report of purchase order
I want to create a database in msaccess for purchase order. How to do that? I have basic knowlege of msaccess. ...

Distribution List with External Addresses Only
OK folks, I'm new to Exchange 2003. I've read a couple of the threads in here, but I'm still missing something. I need to create a distribution list in our Global address book that contains the e-mail addresses of people outside our company only. I have created the individual e-mail contacts in Active Directory. I've also created the distribution group and made them a member of it. So now what? How do I get the group to show up in Outlook? I didn't give the group an e-mail address in our domain (one of the options when creating the group), as it will not be re...

Create node under a childless node
Hi, I have the following problem when I try to add a new node under a tree parent node. ok, I will give you a example: Node1 -childnode1 Node2 -childnode2 Node3 I have a context menu, that appear on Right Click mouse, on a node, so from this menu I can add new node. For example right click on Node1 will create a new node (child) under Node1. pNENode is a object fill with some data HTREEITEM parent = m_ctlNavigationTree.GetSelectedItem(); HTREEITEM hItem = m_ctlNavigationTree.InsertItem ( pNENode->GetDisplayText(),m_icons.GetIconKey(pNENode->GetIcon()), m_icons.GetIcon...

Can I list my workbook sheets?
I have an excell workbook in which there is a pricelist sheet for ingredients and a (soon to be) large number of recipe sheets. Is it possible for me to devise a list of recipe sheets from which I can choose the recipe I wish to work on? Also, can I have the user use a form for data entry, not the general one on the Data menu? Thank you for your help. Jenny ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Software! http://www.ozgrid.com/Services/excel-software-categories.htm ** Hi for the second question see: http://j-walk.com/ss/dataform/index.htm -- Regards ...

need to create a formula to create a timesheet but haven't a clue
Timesheet Formula: I am trying to create a formula to calculate whether the hours used are either time and a half between x-y hours. Completely useless on Excel. Depends upon the rules. For instance, if anything over 8 hours is overtime then =MAX(end_time-start_time-TIME(8,0,0),0) If it is any hours outside ofv pre-set start and end times, say 08:00 and 16:00 then =MAX(TIME(8,0,0)-start_time,0)+MAX(end_time-TIME(16,0,0),0) -- HTH RP (remove nothere from the email address if mailing direct) "AHurd" <AHurd@discussions.microsoft.com> wrote in message news:5FF79B03-92...

Create Report On the Fly
I have a form with several multi list boxes. Once a row(s) is selected, a WHERE string is built, deletes an existing Query, and then builds a new Query1 with that Where string criteria. I then build a Crosstab query with the Query1 criteria. This works very well and vey fast. Instead of opening the Crosstab Query (as code shows), I would like to display the retrieved data in an AutoReport: Tabular design. Because the row values in my lstGroup box will change (required selection; have yet to code), I will need to delete/create/replace the existing report with the new values in t...

print recipient list
How do you set up Outlook 2002 to print the list of recipients on a e-mail message? ...

How do i change my database to list view
How do you change the position of the field names in a database to the top, instead of the side. I need to change them from the side to the top. Copy and paste special and select transpose, this assumes you don't have more than 256 headers. It might be a good idea to paste into a new worksheet Regards, Peo Sjoblom "m" wrote: > How do you change the position of the field names in a database to the top, > instead of the side. I need to change them from the side to the top. > ...

creating custom "objects" document/view
hi in CAD softwares, there are *objects* there that we can drag and drop to a form, like a resistor/diode/electronic symbol. with that cad software are able to create electronic schematic design. what is the nature of the form that holds such object? is that an activeX container? and what is that *object*? young_leaf ...

Why does my form create duplicate records?
I have a form that is based on a query. When I edit a record a new duplicate record is created. Why is this happening? The query is set to Dynaset (Inconsistent Updates) TIA Tony Hi Tony, I'm not an expert so my answer may not be as complete or succinct as it might otherwise be. This happens to me when I have a table in a query where there is more than one record linked to the main table. e.g. if there are two addresses for one customer and I use the address table in a query with the invoice table I might get two invocie records instead of one. The way I get around it is by makin...

Create Requisitions Programmatically
Hi I need some help creating Requisitions programmatically. Here is the process that we want to follow. 1.. Vendors drop their files in a directory 2.. The program reads the files and performs some validations. 3.. A new Requisition should created automatically Can someone please give me some guidance and where possible some sample code to perform the above. I am strong in .NET development but is a newbie in GP. I have the development environment setup and is going through eConnect documentation. All help is greatly appreciated. TIA walterd ...

Distribution Lists #6
I've set the "allow only from Authenticated users" setting on the distribution lists on our Exchange 2003 server. (which co-exists with Exchange 5.5) Perhaps, I don't understand what authenticated user means, but if I send from a hotmail, or some other outside account to the distribution list, I am able to. Should this setting keep all external email users from sending to distribution lists on our internal network and only allow authenticated exchange users the ability to send to DLs? Our exchange server is behind an SMTP gateway, so all mail is routed by the gate...

Default Global Address List #2
Have a few Contacts in the AD that are external to the domain. Getting ready to add more and want to separate them from the GAL. Have established a new Address List, with the appropriate filter to just show accounts with an external address. I am now wanting to edit the default GAL so it only shows internal accounts, but the modify button is not active when I go to the properties of the default GAL. Any ideas? Thanks. Thats wierd. I don't remember seeing this. The query/filter is stored in the purportedSearch attribute of the GAL object in AD. You can just edit the filter an...

Custom address list in Exchange 2003
Would like to create a custom address lists in Exchange 2003, for spesific users and mailenabled groups/DL I'm able to filter the users and groups separately but not the combination, how can i make a filter that creates an address lists that shows spesific users and groups. (criteria can be group-membersip or email-domain -address) Can this be done through a LDAP search? In that case, can someone give me an example of that LDAP search? /Harald Create each filter and look at the corresponding LDAP query. You can the do a logical OR of the two queries (cleaning up any redundancies)...

How do I publish a workbook with a list?
When I publish it I get the error message: For type list the absence of a <cellrangelist> tag implies the relative cell references in the list which is not allowed. The workbook is protected but has areas that are unlocked for editing by users. This function (editing of cells) is not working, not only for cells connected to the list but also for cells with no list attached. ...

Wrong icon order in Folder List
Hello. We're using Outlook 2000 here at work, and one user's installation of Outlook is displaying her Folder List icons in the wrong order, and we can't seem to change this. By default, she'd like to have her Outlook Today icon (containing subtrees of her Inbox, Sent Items, etc.) at the top...and the other icons (Personal Folders and Public Folders) underneath Outlook today in the Folder List. But the Folder List insists on displaying the icons in this order: Public Folders, Personal Folders, Outlook Today. Does anyone know if there's a way to change this order/b...

Runtime Error 429 "ActiveX Component can't create object"
Hi all, My VB6 exe as a service is running flawless on my dev computer w. XP Pro. I have created a PDW install and installed it on a WindowsEmbedded (XP) system. There is no Form, so the only reference I have to any .ocx is: Public WithEvents mComm As MSComm Private Sub Class_Initialize() Set mComm = CreateObject("MSCOMMLIB.MSCOMM") End Sub Private Sub Class_Terminate() Set mComm = Nothing End Sub Private Sub mComm_OnComm() Comm1_OnComm End Sub All dll's, tlb's and mscomm32.ocx are registered and in SharedDLLs. What else could cause ...

Event ID 565
Hi, This is a repost as i didn't have much luck previously. I am running SBS2003 standard edition. Daily i have a massive amount of failed security events - see below. Today for example there was 17000 events of this nature! I have investigated some MS docs, ie http://support.microsoft.com/default.aspx?scid=kb;en-us;813229 & http://support.microsoft.com/default.aspx?scid=kb;en-us;810929 but these have not helped really. I was wondering if anyone can point me to a useful resource. Alternatively i am considering a call to PSS as it's been pestering me for months. I appreciate an...

Outlook 2000 losing distribution list
I have a person who has several distribution lists that are part of a larger distribution list. He is trying to update the email address on one of the contacts. If he goes into the larger distribution list and tries to click on the distribution list within it, it says it can not find the list. I verified both lists are in Contacts. What is missing here? I exported the file and then reimported it to no avail. Using Corporate/Workgroup DL's are not robust and cannot exist for very long without becoming corrupt. Nested DL's are even worse. Most users gave up on DL's long ago....

Extender-Create a new data entry window
Is it possible to create a new extender form or window (not sure of the terminology) to enter data into Dynamics? Specifically the Sales Forecast Window in Manufacturing is terrible for entering data, everytime you move to the right, it the scrolling window jumps back to the top and you have to find the item again. Anyway, I would like a new window to enter a sales forecast, is this possible? thanks -- Doug Unfortunately Extender data is stored in Extender tables and cannot be stored in the Sales Forecast tables. I agree with you that the data entry screen is horrible. I am tal...

How to create a dropdown list that populates with another list
Hello, let's say in a column, I have the following colors: red yellow blue green orange Then, for every cell in another column, I'd like a dropdown list to be available with those 5 colors available to be selected, and you can select only one. You select the color, and then the cell gets populated. I've seen this done, but I just can't figure out how to do it myself despite numerous searches. Any assistance would be appreciated. On Nov 15, 1:49=A0pm, eggman2001 <sod...@gmail.com> wrote: > Hello, let's say in a column, I have the following colors: > red > ...

Need to create a chart with 4 parameters
JAN FEB MAR Oranges Apples Oranges Apples Oranges Apples Adam 2 3 4 5 6 7 Bern 1 7 8 9 1 1 Collin 3 4 5 8 1 1 Dave 5 6 7 1 2 4 How do i draw a chart to show the number of apples & oranges for Adam , Bern , Collin and Dave in the months of Jan, Feb ... in one graph. Please help. -- will appreciate your help Pia, This is what I did. Started at A1 - Blank, B1 - Jan, D1 - Feb, F1 - Mar A2 - Blank, B2 - Oranges, C2 - Apples, D2 - Oranges, E2 - Apples, F2 - Oranges, G2 - Apples A3 - Adam, B3 - 2,C3 - 3, D3 - 4, E3 - 5 F3 - 6, G3 - 7 A4 - Bern, B4 - 1,C4 - 7, D4 - 8, E4 - 9, F4 - 1, G4...

Picture/Object in List
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do I associate a picture(object) to a specific cell in a list, so I can sort, filter, etc. (Normally objects just &quot;float&quot; on a spreadsheet and if you sort, objects will not move with the row they are placed.) <br><br>Thank you, Basically, this doesn't work in Excel 2008. If an image is wholly contained within the cell that contains its top left corner, and its properties are set to "Move and size with cells", then if you move the cell the picture will move. If y...

Cannot Create a new Company #2
Hi: I'm trying to create a new company in GP, we already have three up and running since November 2009, now i need to add six new companies, just for financial records. While running the utilities, in the 'Load Store Procedures' it gives me: "The following SQL statement produced an error: Grant Execute ON dbo.SE_Get_Acc_Detail_Open TO DYNGRP" I clicked 'Ok' then it gives me: "ERROR [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'SE_Get_Acc_Detail_Open', because it does not exist or you do not have permission." I click...