I repeat my mail of yesterday evening as it does not seem to have been sent out.
John I finally succeeded! In Properties>Format of the cbos there is a line for the number of columns. This was automatically set to 2. By changing to 1 and also adjusting the width settings correspondingly it all worked. So I have learned a lot. Ove ----- Original Message ----- From: John Viescas To: [email protected] Sent: Friday, August 17, 2007 5:05 PM Subject: RE: [ms_access] Re: [AccessDevelopers] Combobox Entry Data Ove- It should work if you followed my directions below. It sounds like the Row Source of your cboCity does not have the required parameter in it. For review, here are the steps again: 1) tblCities should have fields StateName and CityName, and the combination of the two fields should be the primary key. Also build a non-unique index on CityName. 2) Row Source of cboState should be: SELECT DISTINCT StateName FROM tblCities ORDER BY StateName; 3) Row Source of cboCity should be (assuming the form is named frmEditSomething): SELECT DISTINCT CityName FROM tblCities WHERE StateName = [Forms]![frmEditThings]![cboState] ORDER BY CityName; Replace "frmEditThings" with the actual name of your form. 4) In AfterUpdate of cboState and Current of the form, do: Me.cboCity.Requery John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Ove Billing Sent: Thursday, August 16, 2007 1:15 PM To: [email protected] Subject: Re: [ms_access] Re: [AccessDevelopers] Combobox Entry Data Hello John and Dick, Thanks, John, for the detailed comments that I have used after best understanding but it does not work fully. I have also tried to find similar constructions in my MS Access 2003 Bible but have failed. So I have to bother you once more with some questions and hope you will be kind enough to answer. I use Acc 2002 and WinXP in Swedish and use the described tables and fields as examples in a trial db. It is thus easy for me to change things, redo etc without any troubles. 1. In the form I have a control for the thing name and the two controls for the state name and city name. I have created the cbo controls there using the tools panel's combobox. Correct so far? 2. In the properties pane I have written OriginState and OriginCity respectively at the row for Control Source 3. At the row source there is already a proposal beginning with SELECT. You have used only the field name in the first part, thus omitting the ID field. But I have tried to use both City name and CityID and also to add the table name, like tblCities.City name. Makes no differnce, apparently! The cboState works but not the cboCity. 4. The cbos have been named under the tab All of Properties, cboState and cboCity respectivly. 5. The form event I have created by right clicking the black square to the left of the ruler. You say Current event, in my Swedish edition I have used the words direcly translated as "At new record", the uppermost alterantive. Correct? It is a pity Access does not tell us about grammatical errors at row source designing, it is better in VBA. Dick, you have kindly offered Row source alternatives. You are of course welcome to send me your suggestions. I would highly appreciate to try them. Finally, I saw John has written the book Access Inside Out. Knowing that I already have the Bible, would you think I would gain essentially by bying your book? I am a private retired man, trying idealistically to help poor local homestead museums to register their collected items in Access with up to three pictures of each item. The picture handling has got a very good solution but the cbos in the case above come in both to register the origin and by teeling where they are stored, like House name, Room name and Furniture name (shelf, chest etc) So, sometimes I would need a three step cbo!! - Isn't this a rather common situation, the second choice depending on what has been chosen first? I regret this has become a little lengthy. Best Regards Ove ----- Original Message ----- From: John Viescas To: [email protected] Sent: Monday, August 13, 2007 8:30 AM Subject: RE: [ms_access] Re: [AccessDevelopers] Combobox Entry Data First, Achi's problem. Achi - it sounds like you want the user to pick values from a combo box and have the value selected appear in a memo field - without destroying what is already in the memo field. You could use the AfterUpdate event of cboObv for that: Private Sub cboObv_AfterUpdate() ' Append data to the other field Me.safObv = Me.safObv & Me.cboObv End Sub ============================ Ove- You need only one "lookup" table: tblCities StateName, CityName .. and both fields form the Primary Key. You can also define a Referential Integrity rule from tblCities to tblThings to ensure tblThings always has a valid combination. When I'm building an application for use in one country, I actually create a table that includes the PostalCode (ZIP code in the U.S.) along with the city and state or province. I have the user pick the postal code first and then look up the city and state name. You can find examples of this in several of the sample databases that come with my books. As for filtering cities by the state selected, you can't do that in the table definition, but you can do it on any form that edits tblThings. Let's say you have a form called frmEditThings. Create a combo box named cboState bound to OriginState and give it this Row Source: SELECT DISTINCT StateName FROM tblCities ORDER BY StateName; Create a combo box named cboCity bound to OriginCity and give it this Row Source: SELECT DISTINCT CityName FROM tblCities WHERE StateName = [Forms]![frmEditThings]![cboState] ORDER BY CityName; And finally, in the Current event of the form and the AfterUpdate event of cboState, do this: Me.cboCity.Requery John Viescas, author Microsoft Office Access 2007 Inside Out Building Microsoft Access Applications Microsoft Office Access 2003 Inside Out SQL Queries for Mere Mortals http://www.viescas.com/ (Paris, France) For the inside scoop on Access 2007, see: http://blogs.msdn.com/access/ ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Ove Billing Sent: Sunday, August 12, 2007 10:33 PM To: [email protected] Subject: Re: [ms_access] Re: [AccessDevelopers] Combobox Entry Data Hello Dick, I thougt the conditions in the Achi problems could be transferred to my situation but I cannot come through. I am not enough experienced. So I would very much appreciate to get some help. Should I have a new thread? My problem is in short: In the tblThings I want to have cbos for the origin state and for origin city and I have the states in the tblStates and the cities in tblCities. Having first chosen the state I want to be able to choose among the cities of that state only. (I hope I can have all cities in one single table?) My fields are as follows but I guess I have to add some for the relations: tblThings: ThingsID, Thing-name; cboOrigin State; cboOrigin City tblState: StateID; State-name tblCities: CityID, City-name, StateID or State-name? Thanks in advance! Ove in Sweden ----- Original Message ----- From: Dick Root To: [email protected] <mailto:ms_access%40yahoogroups.com> Sent: Tuesday, August 07, 2007 6:40 AM Subject: [ms_access] Re: [AccessDevelopers] Combobox Entry Data Achi, Ovi, The SQL WHERE clauses with the "cboCustomer" and "cboProduct" work because the required key is in the first position of the prior (superior) Combo Box SQL select. The ROW SOURCE for the Customer Combo Box is; SELECT tblCusts.CustID, tblCusts.CustNumPaw, tblCusts.CustName FROM tblCusts ORDER BY tblCusts.CustNumPaw; The ROW SOURCE for the Product Combo Box is; SELECT Min(tblProducts.ProductID) AS MinOfProductID, tblProducts.ProductName, tblProducts.ProductDesc, tblProducts.CustID FROM tblProducts WHERE (((tblProducts.CustID)=[cboCustomer])) GROUP BY tblProducts.ProductName, tblProducts.ProductDesc, tblProducts.CustID ORDER BY tblProducts.ProductName; The ROW SOURCE for the Container Combo Box is; SELECT Min(tblContainers.ContainerID) AS MinOfContainerID, tblContainers.ContainerName FROM tblContainers WHERE (((tblContainers.ProductID)=[cboProduct])) GROUP BY tblContainers.ContainerName ORDER BY tblContainers.ContainerName; The ROW SOURCE for the Lot Combo Box is; SELECT Min(tblLots.LotID) AS MinOfLotID, tblLots.LotName, tblLots.LotDescription FROM tblLots WHERE (((tblLots.ProductID)=[cboProduct])) GROUP BY tblLots.LotName, tblLots.LotDescription ORDER BY tblLots.LotName, tblLots.LotDescription; In the VBA there is a lot of complexity that I am not showing, whose function is to link to add-functions when an item is not selected from the Combo box. Hope this helps Dick --- In [email protected] <mailto:ms_access%40yahoogroups.com> , "Achi Rachimuddin" <[EMAIL PROTECTED]> wrote: > > Hi Dick, > > > > It could be better you post the code. > > > > My problems actually like this > > > > I have one field name cboObv and the other field name SafObv data type Memo. > > > > When I select a data from the cboObv then to the field safObv. First data > entry has done. I would select the second data by doing the same step in > which Select cboobv and safobv. > > > > What I want is when I entry the second data by selecting the cboobv, the > first data will directly updated to the table. But when we would go back to > the first data by selecting the combobox, the data still exist. > > > > Thank you in advance. > > > > Rgds, > > > Achi > > > > _____ > > From: [email protected] <mailto:ms_access%40yahoogroups.com> [mailto:[email protected] <mailto:ms_access%40yahoogroups.com> ] On Behalf > Of Dick Root > Sent: Monday, August 06, 2007 12:31 AM > To: [email protected] <mailto:ms_access%40yahoogroups.com> > Subject: [ms_access] Re: [AccessDevelopers] Combobox Entry Data > > > > Achi, > Are you trying to code cascading combo boxes - - the selected items in > the first box constrains the list in the second box which constrains > the list in the third box, etc.? > I solved that problem a few years ago (in VBA of course). If that's > what you need, please let me know and I will post the code. > Dick > > --- In [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com, > "Achi Rachimuddin" > <achi.rachimuddin@> wrote: > > > > Any one can help me? > > > > > > > > _____ > > > > From: AccessDevelopers@ <mailto:AccessDevelopers%40yahoogroups.com> > yahoogroups.com > > [mailto:AccessDevelopers@ <mailto:AccessDevelopers%40yahoogroups.com> > yahoogroups.com] On Behalf Of Achi Rachimuddin > > Sent: Thursday, August 02, 2007 7:24 AM > > To: AccessDevelopers@ <mailto:AccessDevelopers%40yahoogroups.com> > yahoogroups.com; belajar-access@ <mailto:belajar-access%40yahoogroups.com> > yahoogroups.com; > > [EMAIL PROTECTED] <mailto:ms_access%40yahoogroups.com> ps.com > > Subject: [AccessDevelopers] Combobox Entry Data > > Importance: High > > > > > > > > Dear all, > > > > > > > > I have a case like this. > > > > > > > > I have a combobox and another field to entry the data. > > > > For example, > > > > > > > > > > > > Private Sub cboObv_AfterUpdate() > > > > If cboObv = True Then > > > > DoCmd.GoToControl "SafObv" > > > > > > > > End If > > > > > > > > End Sub > > > > > > > > I want when I select the next new record the previous data selected > should > > be updated into the table. > > > > > > > > Can anyone help me with this? > > > > > > > > Thanks in advance. > > > > > > > > Rgds, > > > > > > > > Achi > > > > > > > > > [Non-text portions of this message have been removed] Yahoo! Groups Links [Non-text portions of this message have been removed]
