Not In List

G

Guest

I have a form with two cascading combo boxes. The first one is the 50 states
in the U.S., the second one is the corresponding counties in the state that
is selected in the first combo box. Users want an option of adding a venue to
the county combo box when it is not in the list. I have code in NotInList
event of cboCounty:

Private Sub cboCounty_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 Venue...")
If i = vbYes Then
strSQL = "Insert Into tblCounty ([State],[County]) values (Me.State,'" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When I add a new value to cboCounty, I have the following error message:
Run-time error: 3061
Too few parameters. Expect 1

On command "CurrentDb.Execute strSQL, dbFailOnError "

What is wrong? Please help. Thanks in advance
 
D

Dan Artuso

Hi,
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & _
Me.State & "','" & NewData & "')"
 
G

George Nicholson

Try
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & Me.State &
"', '" & NewData & "')"

although I'm not sure that will address a "too few parameters" error, it's
the only thing I see to fix.
(and are you sure the State combo is named State and not cboState?)

If that doesn't do it, set a breakpoint on the Execute line and run your
code until it triggers the breakpoint. Then type ?strSQL in the VBE
Immediate window. Take a look at the string that's been constructed via your
code & see if you can spot why Execute chokes on it. If not, copy the
string into a new query (SQL view) and see if you can make it executeable,
transfering any lessons learned back to your code.

HTH,
 
G

Guest

Thanks a million. It works!

George Nicholson said:
Try
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & Me.State &
"', '" & NewData & "')"

although I'm not sure that will address a "too few parameters" error, it's
the only thing I see to fix.
(and are you sure the State combo is named State and not cboState?)

If that doesn't do it, set a breakpoint on the Execute line and run your
code until it triggers the breakpoint. Then type ?strSQL in the VBE
Immediate window. Take a look at the string that's been constructed via your
code & see if you can spot why Execute chokes on it. If not, copy the
string into a new query (SQL view) and see if you can make it executeable,
transfering any lessons learned back to your code.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Lily said:
I have a form with two cascading combo boxes. The first one is the 50
states
in the U.S., the second one is the corresponding counties in the state
that
is selected in the first combo box. Users want an option of adding a venue
to
the county combo box when it is not in the list. I have code in NotInList
event of cboCounty:

Private Sub cboCounty_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 Venue...")
If i = vbYes Then
strSQL = "Insert Into tblCounty ([State],[County]) values (Me.State,'" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When I add a new value to cboCounty, I have the following error message:
Run-time error: 3061
Too few parameters. Expect 1

On command "CurrentDb.Execute strSQL, dbFailOnError "

What is wrong? Please help. Thanks in advance
Click to expand...
Click to expand...
 
G

Guest

Thanks Dan. It works!

Dan Artuso said:
Hi,
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & _
Me.State & "','" & NewData & "')"


--
HTH
Dan Artuso, Access MVP


Lily said:
I have a form with two cascading combo boxes. The first one is the 50 states
in the U.S., the second one is the corresponding counties in the state that
is selected in the first combo box. Users want an option of adding a venue to
the county combo box when it is not in the list. I have code in NotInList
event of cboCounty:

Private Sub cboCounty_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 Venue...")
If i = vbYes Then
strSQL = "Insert Into tblCounty ([State],[County]) values (Me.State,'" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When I add a new value to cboCounty, I have the following error message:
Run-time error: 3061
Too few parameters. Expect 1

On command "CurrentDb.Execute strSQL, dbFailOnError "

What is wrong? Please help. Thanks in advance
Click to expand...
Click to expand...
 
G

Guest

Thanks George and Dan. It works. But there is a new problem. After I add new
data to the county combo box list, It goes to a new record automatically (the
form goes to a new page).

The reason might be I have the following code in the "After update" event of
cboCounty and cboState:

Private Sub cboCounty_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rsSnap As New ADODB.Recordset
Set cnn = CurrentProject.Connection

' Open Snapshot type
rsSnap.Open "tblCounty", cnn, adOpenStatic
rsSnap.Find "County = '" & Me!cboCounty & "'"
rsSnap.Close

End Sub

Private Sub cboState_AfterUpdate()
Me!cboCounty = Null
Me.cboCounty.Requery
End Sub

What can I do to prevent it from happening? Thanks in advance!

Lily
George Nicholson said:
Try
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & Me.State &
"', '" & NewData & "')"

although I'm not sure that will address a "too few parameters" error, it's
the only thing I see to fix.
(and are you sure the State combo is named State and not cboState?)

If that doesn't do it, set a breakpoint on the Execute line and run your
code until it triggers the breakpoint. Then type ?strSQL in the VBE
Immediate window. Take a look at the string that's been constructed via your
code & see if you can spot why Execute chokes on it. If not, copy the
string into a new query (SQL view) and see if you can make it executeable,
transfering any lessons learned back to your code.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Lily said:
I have a form with two cascading combo boxes. The first one is the 50
states
in the U.S., the second one is the corresponding counties in the state
that
is selected in the first combo box. Users want an option of adding a venue
to
the county combo box when it is not in the list. I have code in NotInList
event of cboCounty:

Private Sub cboCounty_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 Venue...")
If i = vbYes Then
strSQL = "Insert Into tblCounty ([State],[County]) values (Me.State,'" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When I add a new value to cboCounty, I have the following error message:
Run-time error: 3061
Too few parameters. Expect 1

On command "CurrentDb.Execute strSQL, dbFailOnError "

What is wrong? Please help. Thanks in advance
Click to expand...
Click to expand...
 
D

Dan Artuso

Hi Lily,
From what you posted I can't tell why it's going to a new record.
The code in your cboCounty_AfterUpdate event opens a recordset but
really doesn't do anything with it as far as I can see. Did you leave out some code?
Is there a line similiar to:
Me.Bookmark = rsSnap.Bookmark
??

The requery in the cboState_AfterUpdate event I assume populates the
cboCounty combo with the filtered records.

--
HTH
Dan Artuso, Access MVP


Lily said:
Thanks George and Dan. It works. But there is a new problem. After I add new
data to the county combo box list, It goes to a new record automatically (the
form goes to a new page).

The reason might be I have the following code in the "After update" event of
cboCounty and cboState:

Private Sub cboCounty_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rsSnap As New ADODB.Recordset
Set cnn = CurrentProject.Connection

' Open Snapshot type
rsSnap.Open "tblCounty", cnn, adOpenStatic
rsSnap.Find "County = '" & Me!cboCounty & "'"
rsSnap.Close

End Sub

Private Sub cboState_AfterUpdate()
Me!cboCounty = Null
Me.cboCounty.Requery
End Sub

What can I do to prevent it from happening? Thanks in advance!

Lily
George Nicholson said:
Try
strSQL = "Insert Into tblCounty ([State],[County]) values ('" & Me.State &
"', '" & NewData & "')"

although I'm not sure that will address a "too few parameters" error, it's
the only thing I see to fix.
(and are you sure the State combo is named State and not cboState?)

If that doesn't do it, set a breakpoint on the Execute line and run your
code until it triggers the breakpoint. Then type ?strSQL in the VBE
Immediate window. Take a look at the string that's been constructed via your
code & see if you can spot why Execute chokes on it. If not, copy the
string into a new query (SQL view) and see if you can make it executeable,
transfering any lessons learned back to your code.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Lily said:
I have a form with two cascading combo boxes. The first one is the 50
states
in the U.S., the second one is the corresponding counties in the state
that
is selected in the first combo box. Users want an option of adding a venue
to
the county combo box when it is not in the list. I have code in NotInList
event of cboCounty:

Private Sub cboCounty_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 Venue...")
If i = vbYes Then
strSQL = "Insert Into tblCounty ([State],[County]) values (Me.State,'" &
NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

When I add a new value to cboCounty, I have the following error message:
Run-time error: 3061
Too few parameters. Expect 1

On command "CurrentDb.Execute strSQL, dbFailOnError "

What is wrong? Please help. Thanks in advance
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

Add Record with combo box 13
Not In List: SQL adds info, need more information 3
convert vbProperCase when saving new entry in combox 2
item not found in this collection 1
NotInList problem -- get custom entry box AND generic message -- Access 2002 3
Help with Not in the list (combobox) 1
NotInList Issue 1
Not on list event 1

Top