Adding New items to a combo box on the Fly.

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

Guest

The following code was supplied by Tina, and I found it useful, but I need a
bit of help tweaking it
Private Sub td_empID_NotInList(NewData As String, Response As Integer)

If MsgBox("Do you want to add this employee to the droplist?", _
vbYesNo + vbDefaultButton2, "INITIALS NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frm01Employees_pop", , , , acFormAdd, acDialog, _
UCase(NewData)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
Click to expand...
--

When I tell it I want to add to the list, the data entry form pops open just
fine, and takes the new data. I then save the new data, and close the form.
When I return to the original drop down list, I continue to get the message
....Not on list, although The new entry now appears in the list. If I then
scroll to the item, the form accepts it and I can continue.

Obviously, this is a bit awkward.
How I can add the new item on the popup data entry form, close it, and have
my cursor be returned to the dropdown list with the new data already selected
and ready to go to the next field?

Thanks

Lele
 
Lele,
I think all that's needed is a Requery of your combo or listbox.
But...
This the Code I use in my sample file called NotInList Combobox (on my website below)
This adds the new value automatically... (using [State] as a NotInList value)

Private Sub cboState_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into tblStates ([State]) values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
 
Unfortunately, Al, Lele requires additional information be added to the
table, so it is necessary to open a form.

Lele: I agree with Al that a Requery might solve the problem. Try:

DoCmd.OpenForm "frm01Employees_pop", , , , acFormAdd, acDialog, _
UCase(NewData)
Me.td_empID.Requery
Response = acDataErrAdded


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al Campagna said:
Lele,
I think all that's needed is a Requery of your combo or listbox.
But...
This the Code I use in my sample file called NotInList Combobox (on my
website below)
This adds the new value automatically... (using [State] as a NotInList
value)

Private Sub cboState_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?",
vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into tblStates ([State]) values ('" & NewData
& "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Lele said:
The following code was supplied by Tina, and I found it useful, but I
need a
bit of help tweaking it

--

When I tell it I want to add to the list, the data entry form pops open
just
fine, and takes the new data. I then save the new data, and close the
form.
When I return to the original drop down list, I continue to get the
message
...Not on list, although The new entry now appears in the list. If I
then
scroll to the item, the form accepts it and I can continue.

Obviously, this is a bit awkward.
How I can add the new item on the popup data entry form, close it, and
have
my cursor be returned to the dropdown list with the new data already
selected
and ready to go to the next field?

Thanks

Lele
Click to expand...
Click to expand...
 
Douglas,
That's a good idea. I think I'll add a "go to a 'New Record' form and add a record"
form to my NotInList sample file.
Thanks,
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Douglas J. Steele said:
Unfortunately, Al, Lele requires additional information be added to the table, so it is
necessary to open a form.

Lele: I agree with Al that a Requery might solve the problem. Try:

DoCmd.OpenForm "frm01Employees_pop", , , , acFormAdd, acDialog, _
UCase(NewData)
Me.td_empID.Requery
Response = acDataErrAdded


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Al Campagna said:
Lele,
I think all that's needed is a Requery of your combo or listbox.
But...
This the Code I use in my sample file called NotInList Combobox (on my website below)
This adds the new value automatically... (using [State] as a NotInList value)

Private Sub cboState_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into tblStates ([State]) values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Lele said:
The following code was supplied by Tina, and I found it useful, but I need a
bit of help tweaking it

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

If MsgBox("Do you want to add this employee to the droplist?", _
vbYesNo + vbDefaultButton2, "INITIALS NOT IN LIST") = vbYes Then
DoCmd.OpenForm "frm01Employees_pop", , , , acFormAdd, acDialog, _
UCase(NewData)
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub
--

When I tell it I want to add to the list, the data entry form pops open just
fine, and takes the new data. I then save the new data, and close the form.
When I return to the original drop down list, I continue to get the message
...Not on list, although The new entry now appears in the list. If I then
scroll to the item, the form accepts it and I can continue.

Obviously, this is a bit awkward.
How I can add the new item on the popup data entry form, close it, and have
my cursor be returned to the dropdown list with the new data already selected
and ready to go to the next field?

Thanks

Lele
Click to expand...
Click to expand...
Click to expand...
 
I am trying to add values to my combo box called Patterns, on the fly. My
problem is the table of Patterns I am adding to requires both a PatternName
and a Manufacturer as primary fields for each record.

The code you provided has been very helpful and easy to both use and
understand. Thank-you:

Private Sub PatternName_NotInList(NewData As String, Response As Integer)
Dim sqlAddPattern As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this Pattern to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddPattern = "Insert Into Patterns ([PatternName]) values ('" &
NewData & "')"
CurrentDb.Execute sqlAddPattern, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

My problem is based on the fact that each pattern record requires both a
pattern name and a manufacturer designation. I am trying to do this by
selecting the manufacturer from a combo box before I go to the pattern combo
box. When I tab to the Pattern ComboBox and enter a new pattern name, I get
to the
point where it is ready to add the new name to the list, but if I try to
proceed I get a run time error 3058 primary or index field can not contain a
null value. The code line: CurrentDb.Execute sqlAddPattern, dbFailOnError is
highlighted

How can I modify the code so the manufacturer value i selected is also added
by the code?

Thanks so much for your help!!
--
Lele


Al Campagna said:
Lele,
I think all that's needed is a Requery of your combo or listbox.
But...
This the Code I use in my sample file called NotInList Combobox (on my website below)
This adds the new value automatically... (using [State] as a NotInList value)

Private Sub cboState_NotInList(NewData As String, Response As Integer)
Dim sqlAddState As String, UserResponse As Integer
Beep
UserResponse = MsgBox("Do you want to add this value to the list?", vbYesNo)
If UserResponse = vbYes Then
sqlAddState = "Insert Into tblStates ([State]) values ('" & NewData & "')"
CurrentDb.Execute sqlAddState, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Lele said:
The following code was supplied by Tina, and I found it useful, but I need a
bit of help tweaking it

--

When I tell it I want to add to the list, the data entry form pops open just
fine, and takes the new data. I then save the new data, and close the form.
When I return to the original drop down list, I continue to get the message
...Not on list, although The new entry now appears in the list. If I then
scroll to the item, the form accepts it and I can continue.

Obviously, this is a bit awkward.
How I can add the new item on the popup data entry form, close it, and have
my cursor be returned to the dropdown list with the new data already selected
and ready to go to the next field?

Thanks

Lele
Click to expand...
Click to expand...
 
Back
Top