Custom Combo Box

[Guest]

How can I create a combo box where I can enter in data and have it save the
data I entered for later use? For example. I have a "requested by" field. I
want to enter in Jim Bob, and have it remember Jim Bob so when I need to use
that name again, it is already avalable?

 

[Guest]

You mean "Jim Bob" is a name that is not yet in a list of names that the
combo box could look up, but rather a new name that you would like to add to
such a list?

 

[Guest]

Yes

You mean "Jim Bob" is a name that is not yet in a list of names that the
combo box could look up, but rather a new name that you would like to add to
such a list?

 

[Guest]

You could try to use "Requery Action" - look up the details in Help. If that
works manually, it can probably be automated, too. But you'll need a more
experienced person to help you with that, I'm afraid.

Good luck.

 

[William Hindman]

....the simplest way is to bind the control to a field and set the control's
"Limit to List" property to no ...it will let you type anything into the
box, or select from the current list, and save it so long as it doesn't
conflict with any validation criteria you set.

....next up is to set "Limit to List" property to yes and provide vba code
for the control's NotInList event to control what can be entered and how
....this would normally pop up a dialog giving the user choices and
responding to their input based upon your event code. .

....and if your skill level is up to it, you can use unbound forms to
accomplish the same thing with even more control over what happens.

....hth

William Hindman

 

[Guest]

Ok, I got this to work in a simple test database.

First, I created a query on the table that pulls out the names. Then I set
my combo box to be based on that query. Then I made a macro: the action is
"Requery", and in the Control Name field at the bottom I put "names" - the
name of the field in the query with all the names. After that, I went to the
form that holds the combo box, and opened its property sheet. For the "On
Current" field [first one actually] I selected my macro - "requery", and that
was basically it.
Now I can enter a new name into the combo box, and it will be available for
the next new record.

 

[Guest]

Only trouble is, you'll get a new record every time you enter a name, so you
can end up with a lot of duplicates. I suspect that's not a problem for your
table, but of course you don't want your combo-box to be cluttered. Therefore
you also need to set the "Unique Values" field on your query's property sheet
to "Yes".

Ok, I got this to work in a simple test database.

First, I created a query on the table that pulls out the names. Then I set
my combo box to be based on that query. Then I made a macro: the action is
"Requery", and in the Control Name field at the bottom I put "names" - the
name of the field in the query with all the names. After that, I went to the
form that holds the combo box, and opened its property sheet. For the "On
Current" field [first one actually] I selected my macro - "requery", and that
was basically it.
Now I can enter a new name into the combo box, and it will be available for
the next new record.

You could try to use "Requery Action" - look up the details in Help. If that
works manually, it can probably be automated, too. But you'll need a more
experienced person to help you with that, I'm afraid.

 

[Guest]

Annemarie

Assuming your combo box is called Requested By and your table that holds the
names is called tblNames with a field name of UserName just do the following
:-

Enter tblNames in the RowSource property of the combo box. However, if you
want the names to be sorted you will need to use a query here.
Set the NotInList property to Yes

Enter the code below in the AfterUpdate and NotInList events of the combo box.

Private Sub Requested_By_AfterUpdate()
Requested_By.Requery
End Sub

Private Sub Requested_By_NotInList(NewData As String, Response As Integer)

Response = acDataErrContinue
Requested_By.Undo
If MsgBox("This name is not in the list, would you like to add it to the
list?", vbQuestion + vbYesNo, "Update Names List") = vbYes Then
CurrentDb.Execute "INSERT INTO tblNames (UserName) VALUES ('" &
NewData & "')"
Refresh
Requested_By= NewData
Else
Requested_By= NewData
Refresh
End If
End Sub