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
857 Views

Similar Articles

[PageSpeed] 14

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:

How do I get a dialog box asking if I want to open a downloaded do
When I download a document, I would like a pop-up box to ask if I want to open the document. On Wed, 24 Feb 2010 18:02:01 -0800, Ron <Ron@discussions.microsoft.com> wrote: >When I download a document, I would like a pop-up box to ask if I want to >open the document. It seems to me that would be a function of whatever program you're using to do the downloading. Is that Word? -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. ...

Drop Down List Question #2
I have a drop down list from 5 cells merged together so as I can read the length easier, I know I cant select a field from merged cells, is there any way that when I select my choice I can get cells either side of my selection A B F 12 Jan Apples $12.00 17 Feb. Pears $14.00 I have B Column as my range in drop down , how can I get the date and price to move as well? What I want to do is select Pears and the date, Item and price would move to say K L P Columns if my drop down list was in L column...

File view doesn't list an expected source file
Hi, File view isn't showing a file it should show. One of the last steps needed to add Help support to an existing project is to compile MyProject.hpj. To do this, msdn says that I should go to File view, open the source files list, highlight MyProj.hpj, and then select "Compile MyProject.hpj" in the Build menu. MyProject.hpj is not in the Source files list, thogh. Can someone tell me why it isn't there? The file exists in my project's MyProject\hlp directory as a result of copying the sample project's hlp directory to my project, and renaming the files so th...

MSN Stock Watch List in Money 2006 Deluxe Portfolio Manager
I inadvertently deleted the MSN Stock Watch list from my Portfolio Manager. Now, despite the list still being in existence under my Passport, I cannot seem to restore it intact into Portfolio Manager via either Money 2006 Deluxe or MSN Money. I'd greatly appreciate any insight from those who may know how to restore this list as an active Portfolio Manager account, which would be much more preferable to creating a new watch list, which would not only not auto-sync with the entries made from the MSN pages, but would also require re-entry of the huge list I have now. I really want m...

Smart List search criteria
It would be helpful if 'ends with' was added as an operator in SmartList's search criteria. ...

Text box disappears on chart
I added a text box to a chart and when I click off of it, the text box disappears (goes behind the chart). I have tried Bring to Front and Bring Forward, and it may or may not stay visible and print. I have clicked on the frame of the text box (it changes pattern) and tried Bring to Front - same problem. Any ideas? Carole O The text box is on the worksheet, not the chart. To fix the problem, Select the text box, and choose Edit>Cut Select the chart (you'll see handles on its corners and sides) Choose Edit>Paste, to paste the text box onto the chart. Click away from the char...

How can I get the selected item number from a combo box?
How can I get the selected item number from a combo box. Say that combo has only two items: (Madam;Mister). If "Madam" is selected, I would like to get item number of Madam < it is zero in this example. Thank you You refer to the value via the name of the control. If it is multi-column, you append .column(number) to the name where number is 0 for the first column. -Dorian "mezzanine1974" wrote: > How can I get the selected item number from a combo box. Say that > combo has only two items: (Madam;Mister). > If "Madam" is selected, I would like to g...

Increasing recently opened files list
Does anyone know how to increase number of files shown in recently opened files list? Mine shows four, and I'd like eight or ten, the same as I get in all my other MS applications. Any auggestions?? Thanks. tabboo wrote: > Does anyone know how to increase number of files shown in > recently opened files list? Mine shows four, and I'd like > eight or ten, the same as I get in all my other MS > applications. Any auggestions?? Thanks. =================================== What is the name and version of the program you are referring to? -- John Inzer return e-mail disabled...

Show Names drop down box not working
I recently graduated to Office 2003 and I'm having a problem I don't know how to get around. I want to send this email to a large number of people, so I want to select several names from one of my contacts folders. But it won't let me. Here are the steps I'm going through: From the Mail Screen, New, To, then I want to click on the "Show Names from the" drop down box, but it won't let me. When I click on the drop down arrow, nothing happens. I have used Outlook in its various versions for years and have always used this feature. These files were imported fr...

Suddenly, to-do list is broken
Good afternoon. Thanks for letting me part of this forum. I am using Outlook 2007. Things have been rolling along very well. Thi morning I closed Outlook while I left for a while. Now that I am back whenever I open Outlook my to-do bar will not display any tasks (no will they show in the task list in the week view of the calendar) Instead, there is a message saying, "The Operation Failed. An objec could not be found." The tasks are still there and I can access the via the tasks tab, but the to-do bar and the calendar are missing th tasks, which is what really made me fall in love w...

API Development Example Angst
This is a "new person question"... I am developing a simple OneNote API and the example found here: http://msdn.microsoft.com/en-ca/library/ms788684.aspx Scroll down until you find the line beginning "The following Visual C# sample shows a complete console application ..." When I copy this code: // Update the section with the new title onApplication.UpdateHierarchy(xmlNode.OuterXml, System.DateTime.MinValue); I get this error: No Overload for method "UpdateHierarchy" takes two arguments. So I thi...

Exchange 5.5 Distribution Lists
I am running in Exchange 5.5/2000 mixed mode and I have noticed that if I change ownership of a distribution list using Exchange Admin, the permissions of that object are not automatically updated (I have to manually give the "owner" the permission needed to add members to the list). Is this some old Exchange 5.5 thing??? ...

delegates permission using a distribution list
I am having problems with permissions on delegates in Outlook. I have a distribution list which has editor rights in a users calendar. When I try and access the calendar it doesn't work. When I go back into the delegates properties again the permission has gone back to NONE. It does however work fine on some clients. Outlook 2000 is being used and Exchange 2003. Convert the DL to a security group. "Gareth Hutchins" <anonymous@discussions.microsoft.com> wrote in message news:29a401c4ad33$4bb88de0$a601280a@phx.gbl... > I am having problems with permissions on d...

How do you create a list with name address phone in a column form.
I have an excel file with last name, first address city state in each column. I want to prepare a two column report with each persons name first then address then phone. Can't remember how to print out a different view of excel. Making it look more like a two column word document. Maybe you can use MSWord's MailMerge. http://www.mvps.org/dmcritchie/excel/mailmerg.htm http://www.mvps.org/word/FAQs/MailMerge The first is from David McRitchie and the second is by Beth Melton and Dave Rado. denise108 wrote: > > I have an excel file with last name, first address city state ...

send/receive dialogue box #2
I have ticked the box to remove details from my send/receive dialogu box. Cannot find how to get it back to show any errors etc. Thanks for any help Philk:confused ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ...

Listing a selection of data from one worksheet on another
At the moment I have a worksheet with a list of client in one column and then in another column that uses an IF formula to display either the number of days to the deadline or text which says No Deadline. What I want to do is enter some kind of formula so that a list of clients with deadlines between 1 and 7 days appears on another worksheet automatically with the number of days to the deadline next to them. To make it even harder the list is currently in name order but on the new worksheet I would like it to automatically appear in days til deadline order with lowest number of days a...

hyperlink list in excel that stays put when it jumps to linked cel
I am trying to use internal hyperlinks in a huge sheet but want the hyperlinks to remain in view - and just for good measure, it has to open with the links visible on other machines. Any suggestions would be much appreciated, TIA ...

Using check boxes to limit a report.
I have a table which lists all possible items for a vendor. I want to create a form that shows all of the items. I want the user to be able to place check marks next to the items they want. I then want to run a report which shows all of the information about the items, but only for the items that had been checked. What is a good way to approach this? I can't seem to find a way to link the check boxes to the item table and don't know how to create a report once they are linked. Can someone please direct me on how to do this? Or at least to somewhere that I can find out how to do ...

Drop Down boxes #8
I want to create a drop down box where if no options are used within the drop down, then you are able to type your own answer You can enter any item in a cell with a data validation list, if you remove the check mark from 'Show error alert after invalid data is entered', on the Error Alert tab in the Data Validation dialog box. The box is shown here: http://www.contextures.com/xlDataVal04.html#Error Brian wrote: > I want to create a drop down box where if no options are used within the drop down, then you are able to type your own answer -- Debra Dalgleish Excel FAQ...

setup a text box as a lined page
I'd like to simulate text on lined paper. Is it possible in Word or Publisher? Thanks Find a lined paper background or graphic, put it on the Master page. On the the publication page create a text box. View, turn on baseline guides. Go to Arrange, Layout guides, baseline tab, adjust it to match your lined graphic. Select the text box, Format, paragraph, check align text to baseline guides. http://images.google.com/images?hl=en&q=lined+paper&gbv=2 -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "tridev" <tr...

Adding the users name to a new list item
Is there a way to create a list so that when the logged in user creates a new item, it automatically insert's their user name into a column? I'm creating a time entry list, and don't want the users to have to type their name in every time they add a time record. The controller will review this list once a week and create reports based on the user, client, etc. Thanks in advance! Gavin Use the Created by column. -- Daniel A. Galant Imagine what we could be... if we could just imagine. "Gavin Steiner" <gavin@interprom.com> wrote in mes...

Outlook 2003: How to show multiple task lists?
Does anyone know if it is possible to show multiple task lists (i.e. a personal list and a work-related list) side by side in the task list view by default? It's simple in the calendar to show two calendars simultaneously, and it seems completely ridiculous not to include the same functionality in the task list view. Any help would be appreciated. No, but you can use the right click on the additional task list you want to display and select "Open in new window" - then resize your windows to show them side by side. Why would one want to show task lists in the same way as cal...

Combo box for related records AND non-related records
Hello, Sorry if this question has been answered, but my searches have not yielded the posts. Here is what I want to do: I have a form with a combo box for City and one for State. I have two lookup tables: lkpCity cityID, City, stateID lkpState stateID, State I have the combo box for City set up, and the one for State to depend on the control for City. These are necessary because we've got a lot of spelling errors. Very often, however, the respondents whose data we are entering fail to provide a city, but list their state. If this happens, I can't get a l...

Can't get a value from a querry into a forms text box
I have a main Shippin Control table, and a main Shipping Control form with description of cargo and quantity text boxes. I have a querry which is a join of tblValues and tblShipping_Control. The SQL is below SELECT Shipping_Control.DescriptionofCargo, Shipping_Control.CargoQty, tblValues.Value, ([Shipping_Control].[CargoQty]*[tblValues].[Value]) AS CommercialValue, Shipping_Control.ShippingDate, Shipping_Control.ShipTo, Shipping_Control.ShipFrom, Shipping_Control.CollectionNoteNo, Shipping_Control.PackingListNo, Shipping_Control.ShipmentID, Shipping_Control.Customer FROM Ship...

Replacing values based on a set list
Hi Could anyone help me with this one: - I have an Excel workbook with two worksheets: sheet1 and sheet2. On sheet1, I have 2 columns of data:- Column 1 - CityID Column2 - CityName On sheet2, I have a list of 500 office locations, some of which are in the same city. Sheet2 has several columns, one of which is the city location of the office. I am trying to prepare the Excel sheet ready to import into Access and want to replace each instance of the city location listed on sheet2 with the CityID number from sheet1 where the city is matched. For example: - Sheet 1 CityID CityName 1...