 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2008Automatically Add Entry to Combobox
I have a database which records client addresses. I have a field called Town which is selected via a Combo Box from a Table called Towns. What I would like to do is to automatically add new Towns to the Table and the Combo Box. I believe I need to limit the selections to the list in the table (Combo Box) and then write some code on the NotInList event. If this is correct how do I go about it ? What you're describing is correct. The sample code in the Help file (under NotInList event) is reasonably good, or you can take a look at http://www.mvps.org/access/forms/frm0015.htm at "The Access Web"  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >I have a database which records client addresses. I have a field > called Town which is selected via a Combo Box from a Table called [quoted text clipped - 5 lines] > > If this is correct how do I go about it ? On 26 May, 17:54, "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote: > What you're describing is correct. > [quoted text clipped - 17 lines] > > - Show quoted text - I have looked at the code on the link. I can get it to display the programed message box if I un Dim the Dim db statement. (put a ' in front of it) The example starts with Dim db as DAO.Database. Having got the message box the code errors as it does not like Dim rs As DAO.Recordset and continues to error if I try and un Dim that statement. I am using Access 2000 and my record source is a table named Towns. Should I be attaching something different to these Dim statements. > On 26 May, 17:54, "Douglas J. Steele" > <NOSPAM_djsteele@NOSPAM_canada.com> wrote: [quoted text clipped - 14 lines] > I am using Access 2000 and my record source is a table named Towns. > Should I be attaching something different to these Dim statements. Sounds as though you don't have a reference set to DAO. (Access 2000 doesn't include one by default). Go into the VB Editor and select Tools | References from the menu. Scroll through the list of available references until you find the one for Microsoft DAO 3.6 Object library, select it, then back out of the dialog. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > > On 26 May, 17:54, "Douglas J. Steele" > > <NOSPAM_djsteele@NOSPAM_canada.com> wrote: [quoted text clipped - 27 lines] > > - Show quoted text - Thanks that helps. DAO Objects now recognised. Code sort of works. If I enter a new town it is recognised as new, however if I click yes to enter, it does not get entered onto form but new town appears in Towns table ! If I press No code errors on resetting rs to nothing. > On 26 May, 20:37, "Douglas J. Steele" > [quoted text clipped - 41 lines] > > - Show quoted text - Seem to have cracked it with some help from a download support.microsoft.com/kb/197526/en-us. Similar structure to the code from Access Web. > > On 26 May, 20:37, "Douglas J. Steele" > [quoted text clipped - 47 lines] > > - Show quoted text - I think I am nearly there. When the form is open the code I have written recognises a new entry and allows it to be added to the table and Combo Box selections. I still have a problem when I open the database and the form from scratch I get a message saying the Table (Towns) to feed the query cannot be found. If I check Tables it is there and if I go into the Town field property all appears OK. Source Combo Box entries from an existing Table/Query and SELECT[Towns]:[Town]FROM[Towns]; If I come out of properties back to the form the function works the Combo Box appears including Towns added from a previous session. (ie prior to reopening the database / form) Do you really not have spaces in that SQL Statement? It should be SELECT Town FROM Towns ORDER BY Town SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) >> > On 26 May, 20:37, "Douglas J. Steele" >> [quoted text clipped - 71 lines] > Combo Box appears including Towns added from a previous session. (ie > prior to reopening the database / form) Jan Baird is out of the country until September 20. Every effort will be made to respond to messages, but please be patient. Jan Baird is out of the country until September 20. Every effort will be made to respond to messages, but please be patient. Jan Baird is out of the country until September 20. Every effort will be made to respond to messages, but please be patient. Jan Baird is out of the country until September 20. Every effort will be made to respond to messages, but please be patient. > I have a database which records client addresses. I have a field > called Town which is selected via a Combo Box from a Table called [quoted text clipped - 5 lines] > > If this is correct how do I go about it ? Make sure the Combo Box's LimitToList property is set to Yes. You can use a simple Append query code to add the new data. Code the Combo Box's NotInList event: If MsgBox("The Item Entered is not in database, would you like to add it?", vbYesNo) = vbYes Then CurrentDb.Execute "INSERT INTO Towns(Town) Select " & Chr(34) & NewData & Chr(34) & ";", dbFailOnError Response = acDataErrAdded End If SignatureFred Please respond only to this newsgroup. I do not reply to personal e-mail > > I have a database which records client addresses. I have a field > > called Town which is selected via a Combo Box from a Table called [quoted text clipped - 22 lines] > Please respond only to this newsgroup. > I do not reply to personal e-mail I have tried this and a longer version you posted in September 2003 and cannot get either version to work. If I enter a new town I get a message telling me the town is not on the list and to enter one from the limited list. Any pointers as to where I might be going wrong? Thank you. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.