List Box Example

I am trying to find a simple example of a list box. When I open a form and 
drop down a list of customers, I would like to see "Add New" at the beginning 
of the listing if the customer isn't there. Then the form for adding the 
customer would popup. As I hav it now, if the customer isn't there, then you 
have to close the current screen go back to the menu and go to a customer 
database and add them. I would like to elimated so many extra steps.
Any simple example would help for a reference. I have search google with no 
success over the past two weeks
Thank you in advance for any help
0
Utf
11/4/2007 9:50:00 PM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
707 Views

Similar Articles

[PageSpeed] 56

Hi

I use this code in a combo box to select people.  If the person is not on 
the list, you type in the new name and another screen is displayed to 
complete the details such as phone, email etc.

Use the Event Not in list to call this sub

Private Sub cmbOwner_NotInList(NewData As String, Response As Integer)
    subNewName NewData, Response
End Sub


Public Sub subNewName(NewPerson As String, Reply As Integer)
    ' If a name is not in a drop down list, prompt to add the name to the 
table
    Dim intAnswer As Integer
    Dim dbs As Database
    Dim rst As Recordset
    Dim oaQry As String
    Dim bolForm As Boolean
    
    On Error GoTo Error_subNewName
    
    'If allowed to update, ask do you want to add a name
         intAnswer = MsgBox("Add " & NewPerson & " to the list of Names?", 
vbQuestion + vbYesNo)
           
           'If they do want to add a name, display the frmPeople form and 
add the name to the form
            If intAnswer = vbYes Then
                Set dbs = CurrentDb
                Set rst = dbs.OpenRecordset("tblPeople", dbOpenSnapshot, 
dbSeeChanges)
                rst.AddNew
                rst!Name = NewPerson
                rst!Active = True
                rst.Update
                Reply = acDataErrAdded       ' Requery the combo box list.
                
                strFormName = "frmPeople"
                strLinkCriteria = NewPerson
                DoCmd.OpenForm strFormName, , , , , , strLinkCriteria
            End If
            
            If intAnswer = vbNo Then
               Exit Sub
            End If

Exit_subNewName:
    rst.Close
    Exit Sub
    
Error_subNewName:
    MsgBox "Error in subNewName: " & Err.Number & " - " & Err.Description
    GoTo Exit_subNewName
    
End Sub


Hope this helps

"To Old To . . ." wrote:

> I am trying to find a simple example of a list box. When I open a form and 
> drop down a list of customers, I would like to see "Add New" at the beginning 
> of the listing if the customer isn't there. Then the form for adding the 
> customer would popup. As I hav it now, if the customer isn't there, then you 
> have to close the current screen go back to the menu and go to a customer 
> database and add them. I would like to elimated so many extra steps.
> Any simple example would help for a reference. I have search google with no 
> success over the past two weeks
> Thank you in advance for any help
0
Utf
11/4/2007 11:13:01 PM
Yes and No. I use a similar code in previous programs, however my customer 
specifically asked for this feature and I would like to be able to do this. I 
have seen it in another Access database but was unable to see how it was done 
(mde).
Thank you for the post.

"NevilleT" wrote:

> Hi
> 
> I use this code in a combo box to select people.  If the person is not on 
> the list, you type in the new name and another screen is displayed to 
> complete the details such as phone, email etc.
> 
> Use the Event Not in list to call this sub
> 
> Private Sub cmbOwner_NotInList(NewData As String, Response As Integer)
>     subNewName NewData, Response
> End Sub
> 
> 
> Public Sub subNewName(NewPerson As String, Reply As Integer)
>     ' If a name is not in a drop down list, prompt to add the name to the 
> table
>     Dim intAnswer As Integer
>     Dim dbs As Database
>     Dim rst As Recordset
>     Dim oaQry As String
>     Dim bolForm As Boolean
>     
>     On Error GoTo Error_subNewName
>     
>     'If allowed to update, ask do you want to add a name
>          intAnswer = MsgBox("Add " & NewPerson & " to the list of Names?", 
> vbQuestion + vbYesNo)
>            
>            'If they do want to add a name, display the frmPeople form and 
> add the name to the form
>             If intAnswer = vbYes Then
>                 Set dbs = CurrentDb
>                 Set rst = dbs.OpenRecordset("tblPeople", dbOpenSnapshot, 
> dbSeeChanges)
>                 rst.AddNew
>                 rst!Name = NewPerson
>                 rst!Active = True
>                 rst.Update
>                 Reply = acDataErrAdded       ' Requery the combo box list.
>                 
>                 strFormName = "frmPeople"
>                 strLinkCriteria = NewPerson
>                 DoCmd.OpenForm strFormName, , , , , , strLinkCriteria
>             End If
>             
>             If intAnswer = vbNo Then
>                Exit Sub
>             End If
> 
> Exit_subNewName:
>     rst.Close
>     Exit Sub
>     
> Error_subNewName:
>     MsgBox "Error in subNewName: " & Err.Number & " - " & Err.Description
>     GoTo Exit_subNewName
>     
> End Sub
> 
> 
> Hope this helps
> 
> "To Old To . . ." wrote:
> 
> > I am trying to find a simple example of a list box. When I open a form and 
> > drop down a list of customers, I would like to see "Add New" at the beginning 
> > of the listing if the customer isn't there. Then the form for adding the 
> > customer would popup. As I hav it now, if the customer isn't there, then you 
> > have to close the current screen go back to the menu and go to a customer 
> > database and add them. I would like to elimated so many extra steps.
> > Any simple example would help for a reference. I have search google with no 
> > success over the past two weeks
> > Thank you in advance for any help
0
Utf
11/5/2007 12:58:00 AM
You could cheat by having a text box above the listbox which was made visible 
when you clicked on the list box. The text box could be formatted to look 
like part of the list.  In the box you display "Add New".  If they click on 
the text box and enter a name, you can use the code below to fire up an input 
screen or just write to the table.

"To Old To . . ." wrote:

> Yes and No. I use a similar code in previous programs, however my customer 
> specifically asked for this feature and I would like to be able to do this. I 
> have seen it in another Access database but was unable to see how it was done 
> (mde).
> Thank you for the post.
> 
> "NevilleT" wrote:
> 
> > Hi
> > 
> > I use this code in a combo box to select people.  If the person is not on 
> > the list, you type in the new name and another screen is displayed to 
> > complete the details such as phone, email etc.
> > 
> > Use the Event Not in list to call this sub
> > 
> > Private Sub cmbOwner_NotInList(NewData As String, Response As Integer)
> >     subNewName NewData, Response
> > End Sub
> > 
> > 
> > Public Sub subNewName(NewPerson As String, Reply As Integer)
> >     ' If a name is not in a drop down list, prompt to add the name to the 
> > table
> >     Dim intAnswer As Integer
> >     Dim dbs As Database
> >     Dim rst As Recordset
> >     Dim oaQry As String
> >     Dim bolForm As Boolean
> >     
> >     On Error GoTo Error_subNewName
> >     
> >     'If allowed to update, ask do you want to add a name
> >          intAnswer = MsgBox("Add " & NewPerson & " to the list of Names?", 
> > vbQuestion + vbYesNo)
> >            
> >            'If they do want to add a name, display the frmPeople form and 
> > add the name to the form
> >             If intAnswer = vbYes Then
> >                 Set dbs = CurrentDb
> >                 Set rst = dbs.OpenRecordset("tblPeople", dbOpenSnapshot, 
> > dbSeeChanges)
> >                 rst.AddNew
> >                 rst!Name = NewPerson
> >                 rst!Active = True
> >                 rst.Update
> >                 Reply = acDataErrAdded       ' Requery the combo box list.
> >                 
> >                 strFormName = "frmPeople"
> >                 strLinkCriteria = NewPerson
> >                 DoCmd.OpenForm strFormName, , , , , , strLinkCriteria
> >             End If
> >             
> >             If intAnswer = vbNo Then
> >                Exit Sub
> >             End If
> > 
> > Exit_subNewName:
> >     rst.Close
> >     Exit Sub
> >     
> > Error_subNewName:
> >     MsgBox "Error in subNewName: " & Err.Number & " - " & Err.Description
> >     GoTo Exit_subNewName
> >     
> > End Sub
> > 
> > 
> > Hope this helps
> > 
> > "To Old To . . ." wrote:
> > 
> > > I am trying to find a simple example of a list box. When I open a form and 
> > > drop down a list of customers, I would like to see "Add New" at the beginning 
> > > of the listing if the customer isn't there. Then the form for adding the 
> > > customer would popup. As I hav it now, if the customer isn't there, then you 
> > > have to close the current screen go back to the menu and go to a customer 
> > > database and add them. I would like to elimated so many extra steps.
> > > Any simple example would help for a reference. I have search google with no 
> > > success over the past two weeks
> > > Thank you in advance for any help
0
Utf
11/5/2007 1:19:01 AM
Well as they say, that is thinking outside the box (pun intended) I will 
check that out and see how it looks. Could be a temporary fix until I can 
figure it out.
Thanks for the reply

"NevilleT" wrote:

> You could cheat by having a text box above the listbox which was made visible 
> when you clicked on the list box. The text box could be formatted to look 
> like part of the list.  In the box you display "Add New".  If they click on 
> the text box and enter a name, you can use the code below to fire up an input 
> screen or just write to the table.
> 
> "To Old To . . ." wrote:
> 
> > Yes and No. I use a similar code in previous programs, however my customer 
> > specifically asked for this feature and I would like to be able to do this. I 
> > have seen it in another Access database but was unable to see how it was done 
> > (mde).
> > Thank you for the post.
> > 
> > "NevilleT" wrote:
> > 
> > > Hi
> > > 
> > > I use this code in a combo box to select people.  If the person is not on 
> > > the list, you type in the new name and another screen is displayed to 
> > > complete the details such as phone, email etc.
> > > 
> > > Use the Event Not in list to call this sub
> > > 
> > > Private Sub cmbOwner_NotInList(NewData As String, Response As Integer)
> > >     subNewName NewData, Response
> > > End Sub
> > > 
> > > 
> > > Public Sub subNewName(NewPerson As String, Reply As Integer)
> > >     ' If a name is not in a drop down list, prompt to add the name to the 
> > > table
> > >     Dim intAnswer As Integer
> > >     Dim dbs As Database
> > >     Dim rst As Recordset
> > >     Dim oaQry As String
> > >     Dim bolForm As Boolean
> > >     
> > >     On Error GoTo Error_subNewName
> > >     
> > >     'If allowed to update, ask do you want to add a name
> > >          intAnswer = MsgBox("Add " & NewPerson & " to the list of Names?", 
> > > vbQuestion + vbYesNo)
> > >            
> > >            'If they do want to add a name, display the frmPeople form and 
> > > add the name to the form
> > >             If intAnswer = vbYes Then
> > >                 Set dbs = CurrentDb
> > >                 Set rst = dbs.OpenRecordset("tblPeople", dbOpenSnapshot, 
> > > dbSeeChanges)
> > >                 rst.AddNew
> > >                 rst!Name = NewPerson
> > >                 rst!Active = True
> > >                 rst.Update
> > >                 Reply = acDataErrAdded       ' Requery the combo box list.
> > >                 
> > >                 strFormName = "frmPeople"
> > >                 strLinkCriteria = NewPerson
> > >                 DoCmd.OpenForm strFormName, , , , , , strLinkCriteria
> > >             End If
> > >             
> > >             If intAnswer = vbNo Then
> > >                Exit Sub
> > >             End If
> > > 
> > > Exit_subNewName:
> > >     rst.Close
> > >     Exit Sub
> > >     
> > > Error_subNewName:
> > >     MsgBox "Error in subNewName: " & Err.Number & " - " & Err.Description
> > >     GoTo Exit_subNewName
> > >     
> > > End Sub
> > > 
> > > 
> > > Hope this helps
> > > 
> > > "To Old To . . ." wrote:
> > > 
> > > > I am trying to find a simple example of a list box. When I open a form and 
> > > > drop down a list of customers, I would like to see "Add New" at the beginning 
> > > > of the listing if the customer isn't there. Then the form for adding the 
> > > > customer would popup. As I hav it now, if the customer isn't there, then you 
> > > > have to close the current screen go back to the menu and go to a customer 
> > > > database and add them. I would like to elimated so many extra steps.
> > > > Any simple example would help for a reference. I have search google with no 
> > > > success over the past two weeks
> > > > Thank you in advance for any help
0
Utf
11/5/2007 2:00:01 AM
Reply:

Similar Artilces:

Customizing Activities\History LIst
Is there a way to add a date to Activities' History list? It would be nice to know when each activity happened. Thanks, Troy Troy, no way currently, although Microsoft has mentioned it might be an option in version 2.0 (nothing is guaranteed!) A few ISVs have custom products that do it... www.salentica.com www.c360.com Dave "Troy" <anonymous@discussions.microsoft.com> wrote in message news:af6b01c436bd$ce671f00$a001280a@phx.gbl... > Is there a way to add a date to Activities' History > list? It would be nice to know when each activity > happened. >...

Better to "nest" or use multi-level list?
I'm in the process of creating/defining a style(s) for a list that will be numbered in "level 1" and have indented bullets in "level 2." For such a list is it better to define a multi-level list style and linked paragraph styles? Or use one's already defined numbered list and use one of the Word's pre-defined bullet lists when needed for the second level? As part of this question, is is preferable not to have a style "nested" within another style or doesn't it matter? Thank you. -- Norm Hi Norm: I'm not sure why yo...

Distribution List Changes
Hi All, I have a mail enabled distribution list with four users. Only those users plus the Administrator have rights on this group. Yet a seperate user is able to add / edit or remove users to this group even though they do not belong to this group. The user does not even have admin rights and is able to make changes. Let me know of any options I can try. Thanks Waynear wrote: > Hi All, > > I have a mail enabled distribution list with four users. Only those > users plus the Administrator have rights on this group. > > Yet a seperate user is able to add / edit or remov...

Sound shows on custom Annimation list yet no sound heard
When the PP Presentation is saved as a show,some slides have sound and some do not. All sound came from the same folder. Also, when playing the slides while in PPPresentation, swome have sound and some do not despite all being indicated on custom annimation lisy and all startin "after previous." Thanks! Often when one sound plays but another doesn't, it's an issue of the length of the file path. What's the file path to your music? Is it really long? If so, the longer-named songs might just be hitting the length limit. -- Echo [MS PPT MVP] http://www.ec...

Drop Down List and Check Box
Hi...I was search for this topic but don`t found the solve...Anyway I have a drop down list like this [image: http://www.geocities.com/augurtrade/excel_checkbox.txt] and this [image: http://www.geocities.com/augurtrade/excel_checkbox2.txt] ok my questions is what is (Blanks) and (NonBlanks)...? Is it possible for me to see only all the check box that was checked or vice versa...??? How to make a total below the checkbox i mean when someone check the check box, a total number appear below the last check box [image: http://www.geocities.com/augurtrade/excel_checkbox3.txt] [image: http://www.g...

how reliable is the "Who has you in their contact list" list?
Because someone who I think has recently (within the last week) deleted me is still on this list (I don't think she blocked me), but when I check who deleted me on blockstatus.com, he shows up. could it be it's not updated? which of the two would be more reliable? -- maykk Greetings, Do not use these websites, they will steal your identity and sell it. You should change your password immediately. That said, in general, the "who has..." list within Messenger is accurate. There have been instances over the last six-months or so where the list (and m...

Selecting from contacts list
I have all my contacts in the Contacts List and I want to delete all those with a common extension i.e. all those who have email addresses @hotmail. I am using Outlook 2007 Can you do a search or advanced search which will show the contact you want to delete? You should be able to highlight & delete them from the results. "GrahamC" <GrahamC@discussions.microsoft.com> wrote in message news:6AC1880D-9632-4ED9-BAE2-7083690B9FA7@microsoft.com... > I have all my contacts in the Contacts List and I want to delete all those > with a common extension i.e. all t...

Check Box to display records with completed status only
Hi, I have a query whereby a record is lablelled complete if the "Completed date" field has a date in it, otherwise it is blank. I want to check box on a form when ticked, to display a report with completed records only. Can anyone help? Thanks, B/ On your form, you have a command button to open the report, and a check box named (say) chkCompleteOnly. If the check box is checked, you want to open the report to show only the records that have a date in the [Completed date] field. The event procedure for the command button's Click event would look like this: Priv...

Script to list access right
Hi I am trying to find any script which would generate list of login users with certain access level (select, write etc.) at one particular database. Is there something like that? Thanks. Justin ...

Smart List export to Excel #2
Hi, We are using Dynamics 9.0 and Office 2003. When we try to do a Smart List export to Excel, we keep getting an "Exception_Class_Object_Exception" that references varying object errors, such as 'Cells' or 'Value'; the object errors will change each time we try an export. As a workaround, I've found that closing Excel before doing the export allows the export to complete successfully. However, that solution isn't acceptable, as our Dynamics users would like to be able to work in Excel, doing other tasks, while the export is processing. Does anyone...

a list of lists
currently i use CArray to keep track of a list of objects, but I don't know MFC classes well enough to keep track of a list of lists. I'd appreciate some suggestions "wanwan" <ericwan78@yahoo.com> ha scritto nel messaggio news:1187535523.924671.143350@j4g2000prf.googlegroups.com... > currently i use CArray to keep track of a list of objects, but I don't > know MFC classes well enough to keep track of a list of lists. I'd > appreciate some suggestions You might want to embedd your list based on CArray into a class (e.g. CListOfSomething), and then ...

List of different values in data area
Hi excel specialists, How Can I automatically get the list of different values from the dat area and to find out their frequence? INDIVIDUALLY MEASURED VALUES: 3,5 4 3,5 4 3 3,5 4 4 3 3,5 4 3 3,5 3 3 3,5 4 4 Thanks for your help in advance. Balcovja -- balcovj ----------------------------------------------------------------------- balcovja's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2623 View this thread: http://www.excelforum.com/showthread.php?threadid=39572 Take a look at FREQUENCY in Hep -- HTH RP (remove nothere from the email address if m...

Can data in one text box automatically appear in another?
I am trying to design a form in Publisher, and would like to link text boxes so that data entered in one will automatically appear in another on the same page. Is this feasible? How will this form be used? "G.O.G." <G.O.G.@discussions.microsoft.com> wrote in message = news:E4C951DF-7D4F-4FC1-8F6B-2C0B4EB8882A@microsoft.com... > I am trying to design a form in Publisher, and would like to link text = boxes=20 > so that data entered in one will automatically appear in another on = the same=20 > page. Is this feasible? Maybe with some programming... -- Mary Saue...

Global Address List and Handhelds
We use Ipaqs throughout the company, and I was wondering if there is anyway to have exchange add the GAL to the outlook contacts on the workstation, so that when they sync up, they will retrieve the Global Address List? In news:%23TkYtT0$GHA.3560@TK2MSFTNGP04.phx.gbl, Flip <phil.atkinson@beltek.com> typed: > We use Ipaqs throughout the company, and I was wondering if there is > anyway to have exchange add the GAL to the outlook contacts on the > workstation, so that when they sync up, they will retrieve the Global > Address List? No (and this wouldn't really be an ...

Fields in items list-- add/delete
Hai, I am using RMS version 1.2. In Stores Operations Manager it displays list of items when click on Database-->Items. It displays only five columns. Now i need to add / delete some fields in this list. Is it possible to add or delete the fields? if yes how can i add the fields? Thanks in advance, Nagendra On Wed, 27 Apr 2005 01:22:02 -0700, "Nagendra" <Nagendra@discussions.microsoft.com> wrote: >Is it possible to add or delete the fields? if yes how can i add the fields? I don't think so antonio ...

remove small list from large list
I have two mail lists on excel. The first has 16,000 names, the second has about 6,000. I need to remove the 6,000 from the 16,000 as they all appear in the big list as well. How do I extract those 6,000 entries from the larger list? My expected result is a mailing list with 10,000 names. Chip Pearson has some techniques at: http://www.cpearson.com/excel/duplicat.htm Howie J. wrote: > > I have two mail lists on excel. The first has 16,000 names, the second has > about 6,000. I need to remove the 6,000 from the 16,000 as they all appear > in the big list as well. How do ...

Jpeg's appearing as black boxes...
Version: 2008 I am new to using Word for Mac - having recently transferred from a PC. I am finding that some (not all) some documents are displaying the jpeg's on them as black boxes. Instead of the image the entire are is just black and prints this way also. The image displays correctly in the finder, but once the document is full size, the image is a black box. <br> Help please! How do I get my images back? <br><br>Thanks <br><br>Amber Hi Britamy: Chances are they are CMYK JPEGs. Word (or any other Microsoft Office application) can't handle CM...

To-do list
In Outlook 2007, I can't find a way to show a short summary of my e-mail inbox on my Calendar page, similar to the way Outlook shows a To-do list of calendar items on my Mail page. It would save me a lot of switching back and forth. If there is a way, please tell me. If not, maybe you could consider adding it to the next version of Outlook. Otherwise, I love the program. -- BB ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the mes...

exception list
where can I find the exception list for intelligent message filtering? Global Settings in ESM Nue "jlconley" <jlconley@discussions.microsoft.com> wrote in message news:DDF580F1-71C4-4A59-B502-5736266E74AD@microsoft.com... > where can I find the exception list for intelligent message filtering? Not sure what you mean - there's no exception list as such. You can insert IP addresses of known/trusted hosts (from customers/partners and such.. ) in Connection Filtering's Global Accept/Deny lists and these will bypass all other filters. Will need to enable Connect...

Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...

Email Distribution List
When I send a email form my Distribution List - All contacts in that list will get email 7-15 times, untill I deleat from the outbox. How do I fix? "Steve" <Steve@discussions.microsoft.com> wrote in message news:385F5F26-5C60-467C-A6D7-FBD57E7F82EB@microsoft.com... > When I send a email form my Distribution List - All contacts in that list > will get email 7-15 times, untill I deleat from the outbox. How do I fix? This is usually caused by scanning your mail with an antivirus program. Uninstall your AV program and reinstall it without the mail scanning feat...

Excel bounding boxes on chart data sources
Why don't Excel bounding boxes appear on some chart data sources? When I click on some charts, they appear, while other charts they don't. What setting controls this? Hi John, By "bounding boxes" I assume that you mean the highlight around the source data. One reason might be what you are selecting - for example, if you select the chart gridlines the highlight will not appear. Another reason - if you select a series on a pivot chart the corresponding range does not highlight. Also, if the source range is complex, for example a single series using non-contiguous...

Modify the Edit List
I need to know if I modify how the Payables Transaction Edit List prints out will it make the same changes for the Payables Transaction Posting Journal? -- TC TC, Two different reports, two different modifications, two different security settings. Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "TC" wrote: > I need to know if I modify how the Payables Transaction Edit List prints out > will it make the same changes for the Payables Transaction Posting Journal? > -- > TC Thank you Mariano. ...

Pivot Field List
I was wondering if there is an easy way to locate a specific field in the pivot field list in Excel 2007 without having to scroll the list of fields and find the one you are looking for if you have many? I know in 2003 you were able to start typeing the word or numeric and it would find the first match as you typed. Excel 2007 PivotTable Expanded, auto-complete PivotItem drop-down list in RowField. With macro. http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_14_10.xlsm Pdf preview: http://www.mediafire.com/file/mewgzmiwzyd/04_14_10.pdf ...

How to extract/filter unique records from 2 lists?
I have 2 lists, say List A with 300 entries and List B with 320 entries. List A's entries are all in List B and I want to know what are the 20 different entries in List B. Short of manually eyeballing both lists, how do I extract or filter out those 20 unique records? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Enter this formula in C1 to see if the value in B1 is there in the list A1:A300 =IF(ISERROR(MATCH(B1,$A$1:$A$300,0)),"Not There","There&...