Add Item to Table with Lookup Box

[Guest]

I am trying to have an Item added to a Table when it is not included in the
Drop Down combo box. Have tried to enter the code in a module as advised on
allenbrowne.com/ser-27.html, but as code is a little strange to me, I do not
understand for eg: dim vfield As Variant 'Name of field to append to.

The Table is a seperate table called "Country of Birth" which is linked to
the main form, via a drop down list. It is not related to the Main table,
has only one field which is primary. Do I need to establish a relationship
between the 2 tables for this to work.
Thanks

 

[tina]

first, hopefully you're not using a Lookup field in the table itself. if so,
i strongly recommend that you get rid of it; for more information on why,
see http://home.att.net/~california.db/tips.html#aTip8.

next, suggest you stop working on your database until you've read up on
tables/relationships. see my response to your previous post titled
"Relational Data Bases".

hth

 

[Guest]

Put some code in the combo box's NotInList event procedure to insert a new
row into the table. Here's an example for adding a City to a table:

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

and here's one which does the same but also opens a form for other data (in
this case the County its in) to be added to the new row in the Cities table:

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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "Add new city to list?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmCities", _
datamode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' 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 either case the new City name is simply typed into the combo box to
trigger the event.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken for your help. Have tried the first suggestion and the first
dialogue pops up asking "Add New City" Clicked Ok and then get the following
error "Run Time Error Syntax error Insert into statement" When i click Debug
it highlights the cmd.Execute line. Any further suggestions where I am going
wrong
Thanks again

 

[Guest]

You need to make sure that the SQL statement constructed with the following
line:

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

is appropriate for the table into which you want to insert the row. What is
the name of the table and what is the name and data type of the column into
which you want to insert the value entered into the combo box?

Ken Sheridan
Stafford, England

 

[Guest]

Sorry Ken, I am such a novice.
The Table Name is: Country_Birth
The Field Name is: Country_Name (Primary Key)
Data Type is: Text
Could you please advise exactly how the strSQL=INSERT INTO etc. should read?
based on the above
Many thanks

 

[Guest]

The following should work:

strSQL = "INSERT INTO Country_Birth(Country_Name) VALUES(""" & _
NewData & """)"

Note that these are entered as two lines, the first being followed by a
carriage return. The underscore character at the end of the first line is a
continuation character and signifies that the two lines execute as a single
line. This is just to keep each line short so its all visible in the VBA
window without having to scroll right.

Also change the prompt to something like the following so it fits the
context of
your control:

strMessage = "Add new country to list?"

Ken Sheridan
Stafford, England

 

[Guest]

Tried that Ken, but now get the error:
Could not find output table "Country_Birth"
Do I need a relationship between the Country_Birth Table and my Main Table?
and Autonumber field?
Thanks again

 

[Guest]

Have since got it to work.
Your Patience and help has been much appreciated
Thank You

 

[Guest]

Ken, I have noticed that if you type a Country not on the list, you now get
the option of adding or not. If this is just an odd one that you want in the
record but not added, when you answer "no" it will not accept this odd one, I
guess because you have to set the properties "Limit List" to yes. Is there
any other way around this?

Secondly, I have also tried the Form option on another combo field which has
the row source to a table called "Postcodes" which has 3 fields: Suburb,
Pcode & Zone. At present when the Suburb is selected the corresponding pcode
and zone is automatically entered by way of an Afterupdate event procedure. I
am not sure what info I need to show in the line "If Not is
Null(DlookupCityID" etc.

At present it allows me to add a new suburb etc into a form, but when the
form is closed it talks about "Item Not in list" but when I check the
Postcodes table it is actually there.
Thanks again

 

[Guest]

Even for just the odd country which may apply only once or twice you should
really be storing the name of the country in the Country_Birth table. Which
brings us to your other question regarding relationships. The answer to that
is that you should have a one-to-many relationship between the Country_Birth
table and your main table, and referential integrity should be enforced.
This will prevent the same country being inadvertently entered in the main
table under slightly different names, which can otherwise easily happen; I
found myself under three different names as the author of technical articles
in one database I worked on!

As regards the suburbs etc you should not really be storing the post code
and zone in the table, only the suburb. This is because the latter two are
determined by the former, so to store them all in the main table constitutes
redundancy. The post code and zone columns are said to be transitively
functionally dependent on the key, which breaks one of the rules of
normalization. As it happens I've posted a demo of how to handle data of
this sort at:


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

If you have records in the main table where you would not want to store a
suburb value but still need to know the post code etc, there should be rows
in the Suburbs table for each post code with a Suburb value of 'N/A' or
similar. You then simply select the relevant row with the value 'N/A' for
the post code relevant to the main table's record. You don't need to include
an 'N/A' row in the Suburbs table for all possible post codes from the start
of course; you can add them as necessary.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks again Ken, Does that mean that I cannot open a form with the code you
supplied to add the Suburb, Pcode & zone with my current structure?
Thank You

 

[Guest]

You'd use code based on the second code example I sent you, which opens a
form to add the county for the city. In your case the code would open a form
to add the postcode for the new suburb. In this form you'd use similar code
for a adding a new postcode to a PostCodes table and so on up the hierarchy
of different tables until you reach the largest area unit you are using, e.g
State or County, if its an international database.

One thing I omitted to mention is that the frmCities form has the following
code in its Open event procedure to set the DefaultValue propery of the City
control to the newly added city:

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

You'll have seen that the code which open the form passes the NewData value
to the frmCities form as its OpenArgs property.

I perhaps should say that what I'm giving you is the correctly normalized
design. In my experience many people do as you do and use a design which is
not properly normalized. Whether you continue to do so is entirely up to
you, but be aware of the potential pitfalls in the form of update anomalies
which this allows. Say a table of addresses has columns for both City and
State. There is nothing in the design to stop San Francisco being in
California in one row of the table and Arizona in another! You can control
this to some extent in day-to-day operations via the user interface, but a
well designed model should be application independent as far as possible.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks for all your help Ken. I promise I will try not to pester you again
Roger