List Box Example

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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.
 
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
 
Back
Top