Not In List

[Lily]

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

 

[Dan Artuso]

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

 

[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,

 

[Lily]

Thanks a million. It works!

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.

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

 

[Lily]

Thanks Dan. It works!

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


--
HTH
Dan Artuso, Access MVP

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

 

[Lily]

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

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.

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

 

[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

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

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.

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