NotInList

T

thersitz

Hi,

access2002, xp

Looking for ideas on the best way to add an item to a combo box by double
clicking on it. I believe I can do this somehow using the NotInList
property.

I have a comboBox that pulls in all US Cities from tblCities using sql in
the rowsource property. I don't yet have all cities for all states in the
tblCities, so want to add them as the need arises. Can anyone point me to a
help file?
 
T

thersitz

Thanks Al. I am trying that code now, but I am adding something to it
because I want to update 2 fields in the relevant table, not just the one
tied to the comboBox.

I created a new variable

strNewState -- and then tried to grab the value of the stateID field --
which is also a combo box that pulls all stateIDs and statenames from
tblStates.

Here is how I am tring to grab it:

strNewState = Screen!ActiveForm!cboBState.Column(0)

I am getting a new error.

runtime 438
object does not support this property ot method
 
T

thersitz

Can anyone tell me why this code won't work. I get an error that sez:

the number of query values and destination fields are not the same. Here's
the code:



Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim intNewStateID
Let intNewStateID = Forms!frmHPersons.cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Women's History Consortium"
Response = acDataErrContinue
End If
cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub
 
K

Klatuu

You are missing single quotes after NewData & "
This:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
Should be:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "', '" & intNewStateID & "');"


thersitz said:
Can anyone tell me why this code won't work. I get an error that sez:

the number of query values and destination fields are not the same. Here's
the code:



Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim intNewStateID
Let intNewStateID = Forms!frmHPersons.cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Women's History Consortium"
Response = acDataErrContinue
End If
cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub



thersitz said:
thanks ruralFella -- I will definitely give it a read.
Click to expand...
Click to expand...
 
T

thersitz

Thanks. I fixed the syntax as you suggested and I no longer get that error.
At first it seems as if the procedure has run and I have written new values
to the table, but then after I get the:

A new city has been added to your list

I then get the default NotInList error and so, after all, no record was
actually written. I checked the table and that is indeed the case. I tried
to requery the control before the Else statement -- and that didn't work. Do
you think it is because I am adding 2 fields to the underlying table and the
2nd field is not part of the NotInList(arguments) -- but is a separate
variable I created a few lines later?

Here's the code again:

Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim strNewStateID
Let strNewStateID = Forms!frmHPersons!cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?", vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "','" & strNewStateID & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list.", vbInformation,
"Women's History Consortium"
Response = acDataErrAdded

Else
MsgBox "Please choose a new city from the list.", vbInformation,
"Women's History Consortium"
Response = acDataErrContinue
End If


cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub



Klatuu said:
You are missing single quotes after NewData & "
This:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
Should be:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "', '" & intNewStateID & "');"


thersitz said:
Can anyone tell me why this code won't work. I get an error that sez:

the number of query values and destination fields are not the same.
Here's
the code:



Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim intNewStateID
Let intNewStateID = Forms!frmHPersons.cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Women's History Consortium"
Response = acDataErrContinue
End If
cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub



thersitz said:
thanks ruralFella -- I will definitely give it a read.

Maybe this link will help.
http://www.fontstuff.com/access/acctut20.htm

thersitz wrote:
Hi,

access2002, xp

Looking for ideas on the best way to add an item to a combo box by
double
clicking on it. I believe I can do this somehow using the NotInList
property.

I have a comboBox that pulls in all US Cities from tblCities using sql
in
the rowsource property. I don't yet have all cities for all states in
the
tblCities, so want to add them as the need arises. Can anyone point me
to
a
help file?
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

There should be no problem adding as many fields as you want. Are you sure
that Forms!frmHPersons!cboBState.Column(0) has a value? You might break
just before the Docmd.RunSQL to see what value it has. If frmHPersons your
current form or is it another form?

Looking at your code, I don't see anything obvious. If the data is not
getting added to the table, there is a problem with the data. I would check
the validation requirements of the table fields.

It has no bearing on this problem, but I would suggest you replace these
lines of code:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

with this

CurrentDb.Execute strSQL, dbFailOnError

The Execute method is faster because it goes directly to Jet and, because it
goes to Jet without passing through the Access UI, you don't have to bother
with the SetWarnings.
thersitz said:
Thanks. I fixed the syntax as you suggested and I no longer get that error.
At first it seems as if the procedure has run and I have written new values
to the table, but then after I get the:

A new city has been added to your list

I then get the default NotInList error and so, after all, no record was
actually written. I checked the table and that is indeed the case. I tried
to requery the control before the Else statement -- and that didn't work. Do
you think it is because I am adding 2 fields to the underlying table and the
2nd field is not part of the NotInList(arguments) -- but is a separate
variable I created a few lines later?

Here's the code again:

Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim strNewStateID
Let strNewStateID = Forms!frmHPersons!cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?", vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "','" & strNewStateID & "');"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list.", vbInformation,
"Women's History Consortium"
Response = acDataErrAdded

Else
MsgBox "Please choose a new city from the list.", vbInformation,
"Women's History Consortium"
Response = acDataErrContinue
End If


cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub



Klatuu said:
You are missing single quotes after NewData & "
This:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
Should be:
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "', '" & intNewStateID & "');"


thersitz said:
Can anyone tell me why this code won't work. I get an error that sez:

the number of query values and destination fields are not the same.
Here's
the code:



Private Sub cboBCity_NotInList(NewData As String, Response As Integer)
On Error GoTo cboBCity_NotInList_Err
Dim intAnswer As Integer
Dim intNewStateID
Let intNewStateID = Forms!frmHPersons.cboBState.Column(0)
Dim strSQL As String
intAnswer = MsgBox("The city " & Chr(34) & NewData & _
Chr(34) & " is not a city currently listed in the Cities Table.
Please be sure to spell the city correctly if you choose to add it." &
vbCrLf & _
"Would you like to add " & Chr(34) & NewData & Chr(34) & " to the
list now?" _
, vbQuestion + vbYesNo, "Acme Oil and Gas")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblCities([city], [stateID]) VALUES ('" &
NewData & "," & intNewStateID & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new city has been added to the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrAdded
Else
MsgBox "Please choose a job title from the list." _
, vbInformation, "Women's History Consortium"
Response = acDataErrContinue
End If
cboBCity_NotInList_Exit:
Exit Sub
cboBCity_NotInList_Err:
MsgBox Err.description, vbCritical, "Error"
Resume cboBCity_NotInList_Exit
End Sub



thanks ruralFella -- I will definitely give it a read.

Maybe this link will help.
http://www.fontstuff.com/access/acctut20.htm

thersitz wrote:
Hi,

access2002, xp

Looking for ideas on the best way to add an item to a combo box by
double
clicking on it. I believe I can do this somehow using the NotInList
property.

I have a comboBox that pulls in all US Cities from tblCities using sql
in
the rowsource property. I don't yet have all cities for all states in
the
tblCities, so want to add them as the need arises. Can anyone point me
to
a
help file?
Click to expand...
Click to expand...
Click to expand...
 
Top