Updating form with new information

[Guest]

I am creating a form that must allow someone to enter in new data about a new
user. In this case I have a blank form and the user has to select the data
from a combo box. My issue is that when it is a new name and it's not in the
combo box I have a add button so that a new form appears when they click on
add and the person can type in the new name but after that I would like for
them to be able to close the form (that is working) but when it goes back to
the main form the new user information is not being saved immediately and I
would also like for it to be already in the combo box instead of having to
search for the new user.

Any help would be appreciated
Thanks

 

[Jeff Boyce]

Michelle

Take a look at Access HELP on the LimitToList property and NotInList event
of a combobox control. It is possible to start out in your main form and
use these two in conjunction with a combobox to allow the users to find an
existing person AND to start the process of adding a new one (without
needing to use an <ADD> button -- after all, don't you want them to check
first to see if the person is already in the database?).

As part of this process, you will need to create (and use) a form that will
pop up to collect the "new person" information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff for your response but I am new to Access and I understand that
you want me to go to the Access help agent but when type in LimitToList
property it's not giving me what you're talking about, actually where can I
find this property?
Thanks

 

[Guest]

Jeff,

I finally found the LimitToList property and the NotinList property, should
I be setting both of these to yes?

 

[Pieter Wijnen]

You should set the LimitToList Property to Yes
You need to add code to the NotinList *Event*.
There's sample code in the help for the event that does just what you're
after

HTH

Pieter

 

[Guest]

Thanks Pieter for that information but I have an issue. When the user is not
already in the combo box list, I would like for them to be able to add in the
new user name but when I try to do this it gives me the message "The text you
entered is not an item in the list". "Select an item from the list or enter
text that matches one of the listed items." I understand that the LimitToList
Property and NotinList *Event* help make sure that the user is not already in
the list but what should I do if the user is not already in the list and
needs to be added at the time of data entry?

I have created a button on the main form that will open a new form that will
allow them to enter in the new user but I am still getting the error message
and therefor it's not letting me enter in any new users
Thanks,

 

[Jeff Boyce]

Michelle

You'll need to get a bit familiar with Access and VBA to make this work.

Open the form in design view. Click on the combobox and click on
Properties.

If you want to see Access HELP about the LimitToList property, click on that
property, then press F1.

Leave HELP and scroll down the properties of the combobox. Find
"NotInList". Double-click it to get Event Procedure showing, then click to
the right of the box holding "Event Procedure" to see/get the ellipses
(...). Click on that.

You'll be thrown into the bowels of the Visual Basic for Applications Editor
(which you can leave at any time by using the "x" in the upper right hand
corner). Click on Help from the menu bar, then type in NotInList to see how
this is used, and to get an actual example.

Your task, should you decide to accept it, will be to take the code example
shown for NotInList and put it into the NotInList event for the specific
combobox you are working with (i.e., this is the way you opened up the VGA
editor). You'll probably need to change the names of some of the pieces to
correspond to how you've named pieces/objects in your application. For
example, the NotInList example opens a form for data entry ... this is the
one I previously mentioned that you'd need to create to allow adding a new
person. But Access/VBA doesn't know about what you've done until you tell
it how/where to look.

Good luck, Ms Phelps!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Is NotInList and OnNotInList the same property or am I missing something?

 

[Jeff Boyce]

Technically speaking, we're talking about the NotInList event (i.e., Access
notices that something happened -- an 'event'). Both refer to the same
thing for practical purposes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pieter Wijnen]

Here's a sample that I've used for a EmployeeID Field
(Properties / OnNotInlist /Builder button ... / Event Procedure)

Private Sub EmployeeID_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef

Set Db = Access.CurrentDb()

If VBA.IsNumeric(NewData) = False Or VBA.Len(NewData) <> 4 Then
MessageBox "Employee ID must be in #### Format", VBA.vbInformation,
"Incorrect ID"
Response = Access.acDataErrContinue
Exit Sub
End If

If MessageBox("Please Confirm that Your Employee ID (" & NewData & ") is
Correct", VBA.vbInformation + VBA.vbOKCancel + VBA.vbDefaultButton2,
"Confirm Addition") = VBA.vbCancel Then
Response = Access.acDataErrContinue
Else
Set Qdef = Db.CreateQueryDef(VBA.vbNullString)
Qdef.SQL = "INSERT INTO EMPLOYEE(EMPLOYEEID)" & VBA.vbCrLf & _
"VALUES(" & NewData & ")"
Qdef.Execute DAO.dbSeeChanges + DAO.dbFailOnError
Response = Access.acDataErrAdded
End If
End Sub



HTH

Pieter

 

[Guest]

Thank you for all of your help!!
--
Michelle

Here's a sample that I've used for a EmployeeID Field
(Properties / OnNotInlist /Builder button ... / Event Procedure)

Private Sub EmployeeID_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef

Set Db = Access.CurrentDb()

If VBA.IsNumeric(NewData) = False Or VBA.Len(NewData) <> 4 Then
MessageBox "Employee ID must be in #### Format", VBA.vbInformation,
"Incorrect ID"
Response = Access.acDataErrContinue
Exit Sub
End If

If MessageBox("Please Confirm that Your Employee ID (" & NewData & ") is
Correct", VBA.vbInformation + VBA.vbOKCancel + VBA.vbDefaultButton2,
"Confirm Addition") = VBA.vbCancel Then
Response = Access.acDataErrContinue
Else
Set Qdef = Db.CreateQueryDef(VBA.vbNullString)
Qdef.SQL = "INSERT INTO EMPLOYEE(EMPLOYEEID)" & VBA.vbCrLf & _
"VALUES(" & NewData & ")"
Qdef.Execute DAO.dbSeeChanges + DAO.dbFailOnError
Response = Access.acDataErrAdded
End If
End Sub



HTH

Pieter

 

[HELENA SMITH]

hi are you

Thanks Jeff for your response but I am new to Access and I understand that
you want me to go to the Access help agent but when type in LimitToList
property it's not giving me what you're talking about, actually where can I
find this property?
Thanks