update combo box table ?

[RS Controls]

i have a form that has a city entry point (combobox) i have a table with SOME
city names in it (not all)when i give focus to the combo and type in the
first letter it list the city names acordingly if its in the city table. how
do i update the city table with the new city name i type in ?

 

[Beetle]

Use the Not In List event of the combo box. The Limit To List property
must be set to True for it to work. Here is an example of a simple
Not In List event procedure. Depending on your version of Access and
your References, this may or may not work for you exactly as it is written,
but it should give you an idea of how it works. You can also find info
in Access help or online.


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

Dim strMsg As String
Dim strSQL As String

strMsg = """" & NewData & """ is not in the list" & vbNewLine
strMsg = strMsg & "Do you want to add it?"

If MsgBox(strMsg, vbInformation + vbYesNo, "New City") = vbYes Then
Response = acDataErrAdded
strSQL = "Insert Into tblCities ([CityName]) Values(""" & NewData & """);"
CurrentDb.Execute strSQL, dbFailOnError
Else
Response = acDataErrContinue
End If

End Sub

 

[Ken Sheridan]

Make sure the combo box's LimitToList property is set to True (Yes) and put
code in the combo box's NotInList event procedure. Here's an example which
inserts a row into a Cities table with columns CityID and City. The city
column should not be used as a key as city names can be duplicated (I believe
there are four Staffords in the USA in addition to the original where I am).

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

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

strSQL = "INSERT INTO Cities(City) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

You might of course need to add other data to the new row in Cities, such as
the county, state or region in which the city is located. Here's code which
opens a form in dialogue mode to do this, passing the city name you entered
into the combo box to the form:

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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add " & NewData & " to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "frmCities"
' ensure city has been added
If Not IsNull(DLookup("CityID", "Cities", "City = """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to Cities table."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

End Sub

In frmCities the following code goes in the form's Open event procedure to
set the Defaultvalue property of the bound City control in the form:

Private Sub Form_Open(Cancel As Integer)

If Not IsNull(Me.OpenArgs) Then
Me.City.DefaultValue = """" & Me.OpenArgs & """"
End If

End Sub

Ken Sheridan
Stafford, England

 

[RS Controls]

yep that worked i had to manulipate the proprites a bit but i got it working
..you guys got it going on .Thanks alot

Use the Not In List event of the combo box. The Limit To List property
must be set to True for it to work. Here is an example of a simple
Not In List event procedure. Depending on your version of Access and
your References, this may or may not work for you exactly as it is written,
but it should give you an idea of how it works. You can also find info
in Access help or online.


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

Dim strMsg As String
Dim strSQL As String

strMsg = """" & NewData & """ is not in the list" & vbNewLine
strMsg = strMsg & "Do you want to add it?"

If MsgBox(strMsg, vbInformation + vbYesNo, "New City") = vbYes Then
Response = acDataErrAdded
strSQL = "Insert Into tblCities ([CityName]) Values(""" & NewData & """);"
CurrentDb.Execute strSQL, dbFailOnError
Else
Response = acDataErrContinue
End If

End Sub

--
_________

Sean Bailey

i have a form that has a city entry point (combobox) i have a table with SOME
city names in it (not all)when i give focus to the combo and type in the
first letter it list the city names acordingly if its in the city table. how
do i update the city table with the new city name i type in ?