combo box not in list trouble

J

jenniferspnc

Slight problem. I have an unbound combo box and "not in list" code tied to
it...see below. The user goes in to add a new entry and gets the prompt to
add...it works but the screen doesn't refresh...meaning the user can click on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there haven't
been parts added). How do I get it to show the new manufacturer and nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
 
K

Klatuu

You need to requery the form to get the new mfg into the form's recordset and
then make it the current record:

If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAddedMe.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End WithElse

--
Dave Hargis, Microsoft Access MVP


jenniferspnc said:
Slight problem. I have an unbound combo box and "not in list" code tied to
it...see below. The user goes in to add a new entry and gets the prompt to
add...it works but the screen doesn't refresh...meaning the user can click on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there haven't
been parts added). How do I get it to show the new manufacturer and nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
 
J

jenniferspnc

I think I might be missing something (probably obvious) but now I just keep
getting the prompt over and over, do I want to add the manufacturer...somehow
stuck in a loop.

Thanks for your help and patience.

Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Response = acDataErrContinue
End If
End Sub

Klatuu said:
You need to requery the form to get the new mfg into the form's recordset and
then make it the current record:

If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAddedMe.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End WithElse

--
Dave Hargis, Microsoft Access MVP


jenniferspnc said:
Slight problem. I have an unbound combo box and "not in list" code tied to
it...see below. The user goes in to add a new entry and gets the prompt to
add...it works but the screen doesn't refresh...meaning the user can click on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there haven't
been parts added). How do I get it to show the new manufacturer and nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
Click to expand...
 
K

Klatuu

Undo the combo box.
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
Me.Combo23.Undo
--
Dave Hargis, Microsoft Access MVP


jenniferspnc said:
I think I might be missing something (probably obvious) but now I just keep
getting the prompt over and over, do I want to add the manufacturer...somehow
stuck in a loop.

Thanks for your help and patience.

Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Response = acDataErrContinue
End If
End Sub

Klatuu said:
You need to requery the form to get the new mfg into the form's recordset and
then make it the current record:

If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAddedMe.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End WithElse

--
Dave Hargis, Microsoft Access MVP


jenniferspnc said:
Slight problem. I have an unbound combo box and "not in list" code tied to
it...see below. The user goes in to add a new entry and gets the prompt to
add...it works but the screen doesn't refresh...meaning the user can click on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there haven't
been parts added). How do I get it to show the new manufacturer and nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

jenniferspnc

Thanks that seems to work, but now for some reason when I click yes to add
it, access pops up with the big error screen (has encountered a problem) and
shuts off. Wouldn't have to do with any other conflicting code I have would
it?

This is the only other code I have tied to that combo box...on the after
update event (which you know pulls the subform info based on the combo box
selection)
Private Sub Combo23_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Manufacturer_ID] = " & Str(Nz(Me![Combo23], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub



Or it's probably unrelated?
So much trouble for such a little thing :(

Klatuu said:
Undo the combo box.
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
Me.Combo23.Undo
--
Dave Hargis, Microsoft Access MVP


jenniferspnc said:
I think I might be missing something (probably obvious) but now I just keep
getting the prompt over and over, do I want to add the manufacturer...somehow
stuck in a loop.

Thanks for your help and patience.

Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Me.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else
Response = acDataErrContinue
End If
End Sub

Klatuu said:
You need to requery the form to get the new mfg into the form's recordset and
then make it the current record:

If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

Me.Requery
With Me.RecordsetClone
.FindFirst "[manufacturer] = """ & NewData & """"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

Else

--
Dave Hargis, Microsoft Access MVP


:

Slight problem. I have an unbound combo box and "not in list" code tied to
it...see below. The user goes in to add a new entry and gets the prompt to
add...it works but the screen doesn't refresh...meaning the user can click on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there haven't
been parts added). How do I get it to show the new manufacturer and nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

I may be being too literal...

You mention that the combobox is unbound, meaning not tied to a field
available in the form.

You don't mention the 'source' for the combobox ... is that a query or a SQL
statement or a value list or ...? Your code seems to imply that there's a
table involved, but I can't be sure.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

jenniferspnc

On my form I started out with a combo box and the record source is
SELECT tbl_manufacturer.Manufacturer_ID, tbl_manufacturer.Manufacturer FROM
tbl_manufacturer ORDER BY tbl_manufacturer.Manufacturer;

When setting up the combo box I selected the option, "find a record on my
form based on the value i selected in my combo box"

Then I have a subform tbl_parts (has manufacturer id, part number, and
description) within that main form with the combo box

The master and child fields are linked on Manufacturer_id.

Hoping this helps. Very puzzled.

Jeff Boyce said:
I may be being too literal...

You mention that the combobox is unbound, meaning not tied to a field
available in the form.

You don't mention the 'source' for the combobox ... is that a query or a SQL
statement or a value list or ...? Your code seems to imply that there's a
table involved, but I can't be sure.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


jenniferspnc said:
Slight problem. I have an unbound combo box and "not in list" code tied
to
it...see below. The user goes in to add a new entry and gets the prompt
to
add...it works but the screen doesn't refresh...meaning the user can click
on
the new manufacturer but it shows a part under it (that's really tied to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there
haven't
been parts added). How do I get it to show the new manufacturer and
nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
Click to expand...
 
J

Jeff Boyce

If the main form and subform both need Manufaturer_ID, can you confirm that
the main form as a field for that? If, as you said, the combobox is
unbound, then you might need a field (even if not displayed) that holds the
Manufacturer_ID.

I seem to recall, at least in Access 2007, that you can use a field from the
form's underlying query/table as a "parent" value even if the field doesn't
have a control on the form... but maybe I'm confuing this with something
else!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

jenniferspnc said:
On my form I started out with a combo box and the record source is
SELECT tbl_manufacturer.Manufacturer_ID, tbl_manufacturer.Manufacturer
FROM
tbl_manufacturer ORDER BY tbl_manufacturer.Manufacturer;

When setting up the combo box I selected the option, "find a record on my
form based on the value i selected in my combo box"

Then I have a subform tbl_parts (has manufacturer id, part number, and
description) within that main form with the combo box

The master and child fields are linked on Manufacturer_id.

Hoping this helps. Very puzzled.

Jeff Boyce said:
I may be being too literal...

You mention that the combobox is unbound, meaning not tied to a field
available in the form.

You don't mention the 'source' for the combobox ... is that a query or a
SQL
statement or a value list or ...? Your code seems to imply that there's
a
table involved, but I can't be sure.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


jenniferspnc said:
Slight problem. I have an unbound combo box and "not in list" code
tied
to
it...see below. The user goes in to add a new entry and gets the
prompt
to
add...it works but the screen doesn't refresh...meaning the user can
click
on
the new manufacturer but it shows a part under it (that's really tied
to
another manufacturer). But close the form and open it then the new
manufacturer shows up with no parts tied (as it should be since there
haven't
been parts added). How do I get it to show the new manufacturer and
nothing
tied to it.

The combo box is in the detail and the parts pull in via a subform.



Private Sub Combo23_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim Msg As String

'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr &
vbCr
Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
If i = vbYes Then
strSQL = "Insert Into tbl_manufacturer ([manufacturer]) " & _
"values ('" & NewData & "');"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Not on list event 1
update form after adding to combo box 20
Need help in NotInList event 5
not in list open a form 15
Not in the List Code Question 3
Proper using of Combobox 3
Help with Not in the list (combobox) 1
Add Record with combo box 13

Top