adding additional records via a combo box - please help

[ac512]

Hello
I am using Access 2000, and have a combo box with a row source based on a
simple query (which is obviously based on a table). The 'limit to list'
property is set to yes, and if a user types in a string which doesn't appear
in the combo box, I would like to add this new value to the table behind the
scenes so that they can effectively type new values in the combo box even
though the 'limit to list' property is set. I hope this makes sense.
Any assistance/advice/suggestions would be greatly appreciated.
Thanks in advance
AC

 

[Ken Sheridan]

Here's an example for adding a city to a Cities table, with autonumber column
CityID and text column City:

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 new city 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

Set cmd = Nothing

End Sub


Here's one which also opens a form frmCities so other data can be added to
the new city record, e.g. the state in which the city is located:

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


With the second example the following code is also needed in the frmCities
form's Open event procedure:

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

 

[ac512]

Hi Ken
Thank you very much for your response. I have another question if possible?
My table has 2 fields - eg. State & City (no CityID). Using the 2nd
example, I am trying to update the Cities table with the state on the current
form and the city which has been input. I have tried several things but
can't get it to work. This is the last thing I tried:
If Not IsNull(DLookup("State = State1, "City = """ & _
NewData & """")) Then

State1 is the state on the form (I have used Dim State1 = (value from
form)), and this value assigns as I would like, but I cannot get the right
syntax to add both the State and the City. I hope I have explained myself
clearly?
If you could help with this I would be most appreciative.
Thanks in advance

AC

 

[ac512]

Hi Ken
Sorry to bother you again - I just thought I would let you know that I am
now able to update the frmCities form with the State and the City, however,
the form will not close - despite the 'docmd.close' specified in the code you
offered.
Any suggestions as to why would be greatly appreciated.

Thanks again
Kind regards
AC

 

[ac512]

Sorry - one more thing...unless I actually select the 'State' & 'City' fields
and zoom, the record will not be updated.
Any ideas?
Sorry to bother you again - just noticed this as I was testing.
Thanks again

 

[Ken Sheridan]

Which record are you talking about, the new record in Cities or in the
referencing table?

If the former then if you are passing both the City and State values to the
form and assigning the values to the DefaultValue properties of the controls
it won't be 'dirtied', i.e. no edit will be initiated. You can get round
this by assigning the values to the Value properties of the control, not the
DefaultValue.

To change the subject slightly, are you saying you have both a City and
State column in the referencing table, e.g. a Customers table? If so then
City and State would have to be a composite primary key of the Cities table
(city names can be duplicated), and the referencing table would have City and
State columns as a composite foreign key. You should also have a States
table referenced by the State column in cities as otherwise you cannot
control the integrity of the data in the States column in Cities by enforcing
referential integrity.

I would favour a different model, having a single column CityID foreign key
in the referencing table Customers (or whatever) referencing the CityID
unique numeric key of Cities. Customers would have no State column as this
can be shown in a computed control on a form or report, or in a column in a
query by joining the tables. Cities would also have a City column and a
State column referencing the primary key of States. This can be a 'natural'
key as state names are unique.

You'll find a demo of this sort of model at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


It uses the local administrative areas County, District and parish in my
area, but the principle is exactly the same. It does not include any
functionality for inserting rows into the Parishes, Districts or Cities
tables via the NotInList events, but that could be added easily using code
very similar to that which I posted.

Ken Sheridan
Stafford, England

 

[ac512]

Hi Ken

Thank you very much for your assistance - I have been able to rectify the
problem using your invaluable advice.
Your help is greatly appreciated.

Kind regards
AC