Add Item to Table with Lookup Box Roger Bell 21.07.2006 11:41:01 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

Re: Add Item to Table with Lookup Box "tina" <nospam[ at ]address.com> 21.07.2006 12:15:52 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 "Roger Bell" <RogerBell[ at ]discussions.microsoft.com> wrote in message news:30178761-79DE-47C3-94E3-AC382289F0B4[ at ]microsoft.com... [Quoted Text] > 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

RE: Add Item to Table with Lookup Box Ken Sheridan 21.07.2006 17:25:33 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 "Roger Bell" wrote: [Quoted Text] > 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

RE: Add Item to Table with Lookup Box Roger Bell 22.07.2006 07:44:01 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 "Ken Sheridan" wrote: [Quoted Text] > 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 > > "Roger Bell" wrote: > > > 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 >

RE: Add Item to Table with Lookup Box Ken Sheridan 22.07.2006 10:35:01 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 "Roger Bell" wrote: [Quoted Text] > 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 >

RE: Add Item to Table with Lookup Box Roger Bell 22.07.2006 10:52:01 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 "Ken Sheridan" wrote: [Quoted Text] > 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 > > "Roger Bell" wrote: > > > 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 > > >

RE: Add Item to Table with Lookup Box Ken Sheridan 22.07.2006 15:38:01 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 "Roger Bell" wrote: [Quoted Text] > 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 >

RE: Add Item to Table with Lookup Box Roger Bell 23.07.2006 01:55:02 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 "Ken Sheridan" wrote: [Quoted Text] > 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 > > "Roger Bell" wrote: > > > 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 > > >

RE: Add Item to Table with Lookup Box Roger Bell 23.07.2006 03:41:01 Have since got it to work. Your Patience and help has been much appreciated Thank You "Roger Bell" wrote: [Quoted Text] > 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 > > "Ken Sheridan" wrote: > > > 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 > > > > "Roger Bell" wrote: > > > > > 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 > > > > >

RE: Add Item to Table with Lookup Box Roger Bell 23.07.2006 06:29:01 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(Dlookup(:CityID" 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 "Roger Bell" wrote: [Quoted Text] > Have since got it to work. > Your Patience and help has been much appreciated > Thank You > > "Roger Bell" wrote: > > > 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 > > > > "Ken Sheridan" wrote: > > > > > 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 > > > > > > "Roger Bell" wrote: > > > > > > > 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 > > > > > > >

RE: Add Item to Table with Lookup Box Ken Sheridan 23.07.2006 17:48:02 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/forum.aspx?nav=libraryMessages&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 "Roger Bell" wrote: [Quoted Text] > 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(Dlookup(:CityID" 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 > > "Roger Bell" wrote: > > > Have since got it to work. > > Your Patience and help has been much appreciated > > Thank You > > > > "Roger Bell" wrote: > > > > > 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 > > > > > > "Ken Sheridan" wrote: > > >

RE: Add Item to Table with Lookup Box Roger Bell 24.07.2006 03:46:01 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 "Ken Sheridan" wrote: [Quoted Text] > 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/forum.aspx?nav=libraryMessages&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 > > "Roger Bell" wrote: > > > 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(Dlookup(:CityID" 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 > > > > "Roger Bell" wrote: > > > > > Have since got it to work. > > > Your Patience and help has been much appreciated > > > Thank You > > > > > > "Roger Bell" wrote: > > > > > > > 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 > > > > > > > > "Ken Sheridan" wrote: > > > > >

RE: Add Item to Table with Lookup Box Ken Sheridan 24.07.2006 17:04:01 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 "Roger Bell" wrote: [Quoted Text] > 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 >

RE: Add Item to Table with Lookup Box Roger Bell 25.07.2006 02:48:02 Thanks for all your help Ken. I promise I will try not to pester you again Roger "Ken Sheridan" wrote: [Quoted Text] > 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 > > "Roger Bell" wrote: > > > 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 > > >