 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / November 2007Help needed with simple field problem (0/1)
Hi, I'm new to Access. I am a programmer, but my only database experience was when I made a medium sized FoxBase application for a small business years ago. The project I've started working on is way to simple to justify writing an application. The database just needs to have client contact information, appointment dates, pending messages, and payments records. I'm using Microsoft Access 2000. I started learning this today. Here's my hang-up: I'm trying to get a field to display a Clients name from another table. When I select the client ID, while adding a new record (with a Combo Box), it will only place the ClientID in the record, and not the name. I tried to do it the way it is in the sample NWIND database (attached here) that comes with Access. Select the Orders table. When you add a new record, there's a drop down for customer name. When you select one, the name is added to the record (as expected...). Now open the ClientDB database (attached here), select the Messages table, and add a new record. When you select a client name (there's only two), it places the ClientID in the Clients name field. I tried changing different things, but I can't get it to work. I don't see a significant difference between what I have and what's in the NWIND database. I've spent a couple hours trying to get it to display the clients name. Any help appreciated. I have attached the two databases to this message. (The NWIND sample database open with 5 errors, but it does what I'm doing here okay.) I have another question. Is there going to be a way to add messages (table) and appointments (table) from the customers table? (Rather than selecting the messages table, and then selecting the customers name from a drop down list.) The person this is for is going to want to be able to do everything from the customer list. (Adding records, that is - I will set up reports he can select. He's going to want to select a customer, and then do whatever with it - add dates, add messgaes.) If there's not going to be a way to do this (other than switching to the given table...), I need to stop here and find another application, rather than learn this one. (If so, suggestions?) The Attachments didn't get included. I just put them on OrbitFiles instead. You can download them here: http://www.orbitfiles.com/download/id2209446471.html (ClientDB) http://www.orbitfiles.com/download/id2209445579.html (ClientDB) http://www.orbitfiles.com/download/id2209448731.html (NWIND) >Hi, > [quoted text clipped - 44 lines] >switching to the given table...), I need to stop here and find another >application, rather than learn this one. (If so, suggestions?) Well, the attachements did show up. I'm using a new newsreader. The attachement are in a seperate message with a 1/1 appended to the subject header. >The Attachments didn't get included. >I just put them on OrbitFiles instead. [quoted text clipped - 51 lines] >>switching to the given table...), I need to stop here and find another >>application, rather than learn this one. (If so, suggestions?) > I'm trying to get a field to display a Clients name from another > table. When I select the client ID, while adding a new record (with a > Combo Box), it will only place the ClientID in the record, and not the > name. Not sure what you're trying to do. But I'll cover all three cases I can think of 1) If you're trying to simply display the client's name in the combo box, which is bound to the client ID, then make the combo box have two columns, with the second column being the name, and set the width of the first column to 0, which will hide the client ID. 2) If you're trying to populate a client name field in the form that also has the client ID field (which would be a non-normalized design), then use the AfterUpdate event of the combo box to populate that field. 3) If (as I think is the case) you want to display information from the Clients table when you select a Client ID in the form bound to the Messages table, then what you do is: a) In the form's recordsource, add the Clients table. So you would have two tables in the recordsource: Clients and Messages, and join them on the ClientID field. b) Bind the client-related controls in your form to the fields from the Clients table. c) Make sure your combo box is bound to the ClientID field in the Messages table. Now when you select a ClientID from the drop-down, the data from the fields from the corresponding record in the Clients table should automatically appear on the form. > I have another question. Is there going to be a way to add messages > (table) and appointments (table) from the customers table? (Rather > than selecting the messages table, and then selecting the customers > name from a drop down list.) The person this is for is going to want > to be able to do everything from the customer list Yes, you can do that one of two ways. Either create subforms in the Customers form for the Messages and Appointments tables (the subforms will be linked on the ClientID field, and Access will manage creating and filtering the records for you); or use pop-ups from the Customers form to enter Messages and Appointments (here, you'd have to manage the records yourself). Obviously, subforms are a lot more convenient, both for you to set up, as well as for the user to use, since they would be right there on the form, and the data is visible at all times. The downside of subforms is form real estate, since they take up room. If you don't have a lot of room to work with; or if you want a lot of information in your subforms, then pop-ups might be better. Subforms also use up more system resources, since they're open all the time. But I don't think that's anything you'd have to worry about, really. . > that is - I will set up reports he can select. He's going to want to > select a customer, and then do whatever with it - add dates, add > messgaes.) If there's not going to be a way to do this (other than > switching to the given table...), I need to stop here and find another > application, rather than learn this one. (If so, suggestions?) I think you'll find Access subforms to be just what you're looking for. Very easy to work with, and a very convenient tool. Neil >> I'm trying to get a field to display a Clients name from another >> table. When I select the client ID, while adding a new record (with a [quoted text clipped - 8 lines] >with the second column being the name, and set the width of the first column >to 0, which will hide the client ID. I guessed and put 0,1 here, which got converted to 0";1" - which solved my problem. Thanks. (I hadn't made any forms yet.) >2) If you're trying to populate a client name field in the form that also >has the client ID field (which would be a non-normalized design), then use [quoted text clipped - 50 lines] > >Neil I set up a form, with a subform. This will works well. Thanks. On the form, I would like a combo box on top, where I can choose a different client from a list, which changes what record I'm looking at - like the arrow buttons at the bottom do (which I'd like to remove). Can this be done with an 'on change' event? No, you'd use the AfterUpdate event of the combo box, and then use FindFirst against the recordsetclone object to find the appropriate record. After finding the appropriate record, set the form's Bookmark property to the Recordsetclone's Bookmark property. But, if you're going to get rid of the navigation buttons at the bottom and are always going to just display one record, then a better approach would be to just reset the form's recordsource after a client is selected: Private Sub MyCombo_AfterUpdate If Not Isnull(Me.MyCombo) Then Me.Recordsource = "Select * From MyClientTable Where ClientID=" & Me.MyCombo End If End Sub You could also refer to a query instead of the table, if you prefer. The subforms should requery themselves automatically. > I set up a form, with a subform. This will works well. Thanks. > > On the form, I would like a combo box on top, where I can choose a > different client from a list, which changes what record I'm looking at > - like the arrow buttons at the bottom do (which I'd like to remove). > Can this be done with an 'on change' event? >No, you'd use the AfterUpdate event of the combo box, and then use FindFirst >against the recordsetclone object to find the appropriate record. After [quoted text clipped - 13 lines] > >End Sub That worked. Thanks again. When new records are added, they don't show up in the combo box until I close the client form and reopen it. >You could also refer to a query instead of the table, if you prefer. The >subforms should requery themselves automatically. [quoted text clipped - 5 lines] >> - like the arrow buttons at the bottom do (which I'd like to remove). >> Can this be done with an 'on change' event? >When new records are added, they don't show up in the combo box until >I close the client form and reopen it. Requery the combo box in the code which adds the new records (the form's AfterUpdate event for example). John W. Vinson [MVP] How are you adding new records? In the above, you stated that you wanted to take out the record selectors and only show one record at a time. If so, then you should not let your users add new records by going to the new record in the form (because then you'll have two records in the form instead of one, when they add a new record). So, how are you adding new records? > That worked. Thanks again. > > When new records are added, they don't show up in the combo box until > I close the client form and reopen it. I added records using the record selectors. I haven't figured out how to delete them yet - they don't appear on the Design View screen. I haven't looked that hard - been doing other things. Once I use the combo box to select a record, the record selector count changes to 1. I can still arrow over to 2 and enter a new record. When I do that the new record doesn't show up in the combo box list (the others still do). If I close the form and reopen it, the combo box sees the new record(s). >How are you adding new records? In the above, you stated that you wanted to >take out the record selectors and only show one record at a time. If so, [quoted text clipped - 6 lines] >> When new records are added, they don't show up in the combo box until >> I close the client form and reopen it. >I added records using the record selectors. I haven't figured out how > to delete them yet - they don't appear on the Design View screen. I > haven't looked that hard - been doing other things. By "record selectors" I assume you mean "navigation buttons" at the bottom of the screen, right? (The record selector is the vertical bar on the left that has an arrowhead in it.) You can turn off the navigation bar and/or the record selector in the form's property. They're both listed there with Yes/No options. > Once I use the combo box to select a record, the record selector count > changes to 1. I can still arrow over to 2 and enter a new record. > When I do that the new record doesn't show up in the combo box list > (the others still do). If I close the form and reopen it, the combo > box sees the new record(s). Well, as John noted, just requery the combo box when a new record is inserted (I'd do the After Insert event of the form). Just do: Me.MyComboBox.Requery That's all. But, as I noted, you shouldn't add records with the navigation buttons if you're only displaying one record at a time. So, here's what you do: 1) In the form's properties, set Allow Additions to No. That will prevent the user from adding a record manually. 2) Add a button to your form for adding records. When the user clicks the button, prompt them for any required field information. If there is no required field information, and if the primary key is automatically generated, then you don't need a prompt. 3) In the button's On Click event, after the form (if any) prompts for values, do the following: ======================================= Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset) rs.AddNew rs!FieldPK = somevalue rs!OtherField = someothervalue 'This is optional rs.Update Me.Recordsource = "Select * From MyTable Where FieldPK="& somevalue rs.close set rs = nothing ========================================= The above is for if you prompt the user for the PK (customer ID?). If the PK is an autonumber PK, then you'd do this instead: ========================================= Dim rs As Recordset Set rs = CurrentDb.OpenRecordset("MyTable, dbopenDynaset) rs.AddNew rs!OtherField = someothervalue 'This is optional. rs.Update rs.bookmark = rs.LastModified Me.Recordsource = "Select * From MyTable Where FieldPK="& rs!FieldPK rs.close set rs = nothing ========================================= 4) While in a code module, go to Tools | References. Uncheck Microsoft ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, then scroll down the list, and check the highest numbered version of it. And that should do it. Neil >>How are you adding new records? In the above, you stated that you wanted >>to [quoted text clipped - 8 lines] >>> When new records are added, they don't show up in the combo box until >>> I close the client form and reopen it. (SNIP) >> Once I use the combo box to select a record, the record selector count >> changes to 1. I can still arrow over to 2 and enter a new record. [quoted text clipped - 6 lines] > > Me.MyComboBox.Requery I've put that in three places (below). The only thing that updates the combo box is closing the form and reopening it. Private Sub Combo36_GotFocus() Me.Combo36.Requery End Sub Private Sub Form_AfterInsert() Me.Combo36.Requery End Sub Private Sub Form_AfterUpdate() Me.Combo36.Requery End Sub (SNIP) >The above is for if you prompt the user for the PK (customer ID?). If the PK >is an autonumber PK, then you'd do this instead: [quoted text clipped - 14 lines] >set rs = nothing >========================================= That worked. Thanks. >4) While in a code module, go to Tools | References. Uncheck Microsoft >ActiveX Data Objects (if it's checked). If "Microsoft DAO" is not checked, [quoted text clipped - 3 lines] > >Neil (SNIP) >(SNIP) >>> [quoted text clipped - 19 lines] > Me.Combo36.Requery >End Sub I usually use the OnEnter event when I want to requery a combo. Wayne Gillespie Gosford NSW Australia Problem with the OnEnter event is that then you're requerying the combo every time you enter it, instead of just when the data changes. > I usually use the OnEnter event when I want to requery a combo. > Wayne Gillespie > Gosford NSW Australia Well, it should work. What's the rowsource of your combo box? Please copy and paste it here exactly. > (SNIP) >>> [quoted text clipped - 57 lines] > > (SNIP) >Well, it should work. What's the rowsource of your combo box? Please copy >and paste it here exactly. Row Source Type: Table/Query Row Source: SELECT [Clients].[ClientID], [Clients].[FirstName], [Clients].[MiddleName], [Clients].[LastName] FROM Clients; I am adding records from a control button on the form now. I tested this again, and now the combo box is updated with Combo36_gotFocus(). I'd prefer to only update that when necessary, not every time a new record is selected... I don't know why that works now. The combo box is still not updated with either Form_AfterInsert() or Form_AfterUpdate(). >> (SNIP) >>>> [quoted text clipped - 57 lines] >> >> (SNIP) Well, if you changed the code to add records from a command button, per the instructions I sent, then that would explain why it's not working from the form's AfterUpdate or AfterInsert event: the form is not inserting the record; you are, though the code. So the AfterUpdate and AfterInsert events wouldn't apply. Just put the combo box requery code at the end of the routine that adds the new record, and you should be fine. >>Well, it should work. What's the rowsource of your combo box? Please copy >>and paste it here exactly. [quoted text clipped - 74 lines] >>> >>> (SNIP) >Well, if you changed the code to add records from a command button, per the >instructions I sent, then that would explain why it's not working from the [quoted text clipped - 4 lines] >Just put the combo box requery code at the end of the routine that adds the >new record, and you should be fine. I tried that, but I don't know how to reference it, and haven't gotten around to searching Google for that. "Me" is not pointing to the form in the add record routine. (There is no "Combo36" in the list after you type "Me." - like there is in the AfterUpdate routine.) I need to get a basic book on using Access (just systax, I know how to program - I've also never used VB before). It took me about 30 minutes searching Google to find how to change a fields value... (you use the "clone"...) Do you know of a good/short online reference? >>>Well, it should work. What's the rowsource of your combo box? Please copy >>>and paste it here exactly. [quoted text clipped - 74 lines] >>>> >>>> (SNIP) OK, we need to back up a second here. You said that the routine I sent to add a record was working. That routine referenced Me in the Me.Recordsource= item. But you write: "'Me' is not pointing to the form in the add record routine." So, if "Me" is not pointing to the form in the add record routine, then how does the Me.Recordsource= line in the add record routine work? If Me works in one line of the routine, it will work in the other line of the same routine. So, I assume that the add record routine is in your button's OnClick event, and it resides in the form's code module, correct? If so, then Me will work. If you're not seeing Combo36, then it's either not in the form's code module, or there's a compile error and that has to be resolved first. Try compiling and see what happens. >>Well, if you changed the code to add records from a command button, per >>the [quoted text clipped - 98 lines] >>>>> >>>>> (SNIP) Your right. I must have made a typo. There's a new problem. The new record is not sorted in the combo box, it's at the top of the list. When Me.Combo36.Requery is in the Combo36_GotFocus() routine, the list is resorted. I did a couple tests, and this behavior is consistent. (I put the requery just before exit sub in the add record routine.) >OK, we need to back up a second here. You said that the routine I sent to >add a record was working. That routine referenced Me in the Me.Recordsource= [quoted text clipped - 112 lines] >>>>>> >>>>>> (SNIP) Where you place the requery command should have no effect on the sorting of the combo box. Is you combo explicitly sorted? If so, then this shouldn't be happening. If not, then there might be some weird glitch with a new record being at the top, etc. Either way, just make sure you have an explicit sort in your combo's rowsource, and you should fine. > Your right. I must have made a typo. There's a new problem. The new > record is not sorted in the combo box, it's at the top of the list. [quoted text clipped - 128 lines] >>>>>>> >>>>>>> (SNIP) Here's the combo's rowsource: SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER BY [Clients].[FullName]; >Where you place the requery command should have no effect on the sorting of >the combo box. Is you combo explicitly sorted? If so, then this shouldn't be [quoted text clipped - 134 lines] >>>>>>>> >>>>>>>> (SNIP) No clue. I have no idea why the new record would be at the top of the list when called from one place, but in the correct sort order when called from another place. That makes absolutely no sense. Sorry I couldn't be more helpful here. > Here's the combo's rowsource: > SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER [quoted text clipped - 151 lines] >>>>>>>>> >>>>>>>>> (SNIP) (I'm reposting this as it never showed up on my newsfeed.) Here's the combo's rowsource: SELECT [Clients].[ClientID], [Clients].[FullName] FROM Clients ORDER BY [Clients].[FullName]; >Where you place the requery command should have no effect on the sorting of >the combo box. Is you combo explicitly sorted? If so, then this shouldn't be [quoted text clipped - 134 lines] >>>>>>>> >>>>>>>> (SNIP) The original showed up, and I replied to it. Here's my reply in case for some reason you didn't see it: No clue. I have no idea why the new record would be at the top of the list when requery is called from one place, but in the correct sort order when called from another place. That makes absolutely no sense. Sorry I couldn't be more helpful here. > (I'm reposting this as it never showed up on my newsfeed.) > [quoted text clipped - 153 lines] >>>>>>>>> >>>>>>>>> (SNIP) No, I'm not familiar with an online reference, though someone else here might be. I would just go to my local Barnes and Noble or Borders and pick up a beginning or intermediate Access programming book. > you type "Me." - like there is in the AfterUpdate routine.) I need to > get a basic book on using Access (just systax, I know how to program - > I've also never used VB before). It took me about 30 minutes > searching Google to find how to change a fields value... (you use the > "clone"...) Do you know of a good/short online reference? >No, I'm not familiar with an online reference, though someone else here >might be. I would just go to my local Barnes and Noble or Borders and pick >up a beginning or intermediate Access programming book. Here are my (incomplete) list of useful online references... Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/accessjunkie/resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP): http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials John W. Vinson [MVP] Thanks for providing that. I'm sure the OP will find that very useful. I'm going to save it for future reference, myself. >>No, I'm not familiar with an online reference, though someone else here >>might be. I would just go to my local Barnes and Noble or Borders and pick [quoted text clipped - 15 lines] > > John W. Vinson [MVP] Thanks for the links. I wish one of them had a short section on globally accessing things that don't happen to to pointed to my "Me." I find myself working around that, regularly, and just looking for solutions that don't need to access things outside of "Me." (I checked the "ten commandments" to make sure I'm not doing something contrary to the design of Access...) With my current problem I can't think of a way around not seeing outside of "Me." I have a check box on a form. If checked, I want the date to be prefilled on a subform when new records are inserted there. I can't see the default value propery of the date field in the subform from the checkbox event on the main form. (I don't know how. I don't see it under anything that comes up when you type 'me.') So I decided to set the default value for the table (not the form) in the pop-up date selector form (the button is on the main form). I get a "runtime error 2465 - Client database can't find the field '|' referred to in your expression." (There is no '|' in the expression...) The offending code is: [Court Dates]![DefaultValue] = Me.DateCtl.Value - so I guess you can't change default values in tables from within forms in Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm used to being able to easily reference anything in the known universe. Another thing is I don't know how to reference things on the form that called the popup from withing the popup. (Another reason I tried changing the default table value. How do you get the selected date back to the form?) A short reference on globally referencing things (all in the same page, not spread out over 50 pages) would be soooo usefull. Any help appreciated. >>No, I'm not familiar with an online reference, though someone else here >>might be. I would just go to my local Barnes and Noble or Borders and pick [quoted text clipped - 15 lines] > > John W. Vinson [MVP] > With my current problem I can't think of a way around not seeing > outside of "Me." I have a check box on a form. If checked, I want the > date to be prefilled on a subform when new records are inserted there. > I can't see the default value propery of the date field in the subform > from the checkbox event on the main form. (I don't know how. I don't > see it under anything that comes up when you type 'me.') "Me" is just a shortcut. You don't have to use it. To reference a form (even the form you're in) you can use the syntax Forms!Formname in place of Me (where Formname is replaced with your formname). To reference a subform from within the main form, you would use: Me.Subformcontrolname.Form to reference the subform (you're basically referencing the Form property of the subform control, which = the subform itself). Using the above substitution for Me, if you prefer, you could instead use Forms!Formname.Subformcontrolname.Form instead of the version with Me. You can also reference the main form from the subform, either by using the Forms!formname syntax (which allows you to open any open form, including the one containing the subform); or you can just use the Parent property of the subform to reference the main form, as in: Me.Parent. (Note that though Forms!formname allows you to reference any open form, you still have to use the other sytax to reference an open subform). So, with your above example, I would just reference the parent from the subform. In the subform's BeforeInsert event, I would add: If Me.Parent.CheckboxcontrolName Then Me.Datecontrolname = VBA.Date End If If you instead want to set the default property for that field when the check box is checked, you would just use the above syntax for a subform, etc. But, for me personally, I prefer the above method of looking at the check box state at the time the record is entered and just going from there. > So I decided > to set the default value for the table (not the form) in the pop-up [quoted text clipped - 5 lines] > Microsoft 2000. Being a 'c' programmer, this is rather maddening. I'm > used to being able to easily reference anything in the known universe. It's just a question of syntax. To change a default table value you'd have to access the table's Tabledef in the Tabledefs collection and go from there. But, obviously, that's not needed, and isn't the best way to go. Really, I understand your frustration. But I think until you sit down and systematically get up to speed with Access syntax and methodologies you're going to continue to experience these frustrations. Every platform is different, as you know. So, even just going through the basics of an intro to VBA Access programming would be very helpful, you'd fine. > Another thing is I don't know how to reference things on the form that > called the popup from withing the popup. (Another reason I tried > changing the default table value. How do you get the selected date > back to the form?) In the above you reference a subform; here you're discussing a pop-up. They're not the same thing. A pop-up form (which is different than a dialog box) is a standalone form, and you can reference it, or it can reference the form that opened it, using the Forms!Formname syntax. > A short reference on globally referencing things (all in the same > page, not spread out over 50 pages) would be soooo usefull. Hopefully the above will help. Neil > Any help appreciated. > [quoted text clipped - 18 lines] >> >> John W. Vinson [MVP] Thanks for the explanation. I had thought parent might be right, but I didn't get a drop-down after typing '...parent.' and the code wasn't working, and I didn't know why. I was using the 'got focus' (and I tried 'on insert') event in the subform, and then setting the default of the date field to the date field on the main form. (Ignoring the check box for now.) That doesn't work. The date field doesn't fill in. I changed it to the 'on double click' event of the date field (setting the actual field and not the default), and now it works. My problem with referencing the form from the popup was I wasn't including the 'Form!' in front of 'FormName!FieldName.' I appreciate your help. >> With my current problem I can't think of a way around not seeing >> outside of "Me." I have a check box on a form. If checked, I want the [quoted text clipped - 92 lines] >>> >>> John W. Vinson [MVP] > Thanks for the explanation. > > I had thought parent might be right, but I didn't get a drop-down > after typing '...parent.' Yes, you won't get the dropdown after parent, since it could be a form or a report. If you absolutely want a dropdown (not saying you do, but if you do), you can assign me.parent to a form object as follows: Dim frm as form set frm = me.parent ... stuff... set frm = nothing 'at the end That would give you the dropdown. If you declare frm as the actual form that's the parent, you'll also get references to the specific controls on the form: Dim frm as form_MyParentFormName set frm = me.parent ... Just fyi. > and the code wasn't working, and I didn't > know why. I was using the 'got focus' (and I tried 'on insert') event > in the subform, and then setting the default of the date field to the > date field on the main form. (Ignoring the check box for now.) That > doesn't work. Yes, because the default value of the field has to be set before the record is added. When I said to use the BeforeInsert event of the subform, I said to just set the field's value in that event, not set its default value. > The date field doesn't fill in. I changed it to the 'on > double click' event of the date field (setting the actual field and > not the default), and now it works. Right, because you're setting the actual field value. You could do that in the BeforeInsert, instead, and save the double-click effort. Either way. > My problem with referencing the form from the popup was I wasn't > including the 'Form!' in front of 'FormName!FieldName.' > > I appreciate your help. No problem. Glad you got it working. Neil >>> With my current problem I can't think of a way around not seeing >>> outside of "Me." I have a check box on a form. If checked, I want the [quoted text clipped - 99 lines] >>>> >>>> John W. Vinson [MVP] Me.NameOfSubformControl.Form!CourtDates.DefaultValue = Me.DateCtl.Value >Thanks for the links. > [quoted text clipped - 49 lines] >> >> John W. Vinson [MVP] Wayne Gillespie Gosford NSW Australia On Nov 23, 4:46 am, D...@NoEmail.com wrote: > I wish one of them had a short section on globally accessing things > that don't happen to to pointed to my "Me." All forms, including subforms can be referenced with Form_NameofForm, if they have a module, or if they have their HasModule Property set to True. I have used these pointers both personally, and in applications which I have sold and which have run flawlessly thousands and thousands of times. The Access community here in CDMA, grudgingly admits that this might work, but almost universally ignores it. Possibly they get paid by the word, and love Forms("ThisForm").Controls("TheSubFromControl").Forms("NameofSubForm").Controls("NameofSUbFormControl").Value . Some even put in some "Bang" nonsense, a device designed to make the syntax even more confusing, and to discourage learners from becoming capable in programming. Possibly they get laid when they can show their lover that they are actually able to traverse this long passage of idiocy. This is their right and yours as well. I will continue to use Form_SuBFormName.ControlName.Value (or BackColor or other Property). Yes, this works with Report_ReportName, but with the caution that it's difficult to change anything on a report while it's being rendered. > On Nov 23, 4:46 am, D...@NoEmail.com wrote: >> I wish one of them had a short section on globally accessing things [quoted text clipped - 23 lines] > Yes, this works with Report_ReportName, but with the caution that it's > difficult to change anything on a report while it's being rendered. It is all well and good that you have found a syntax that works for you, but I fail to see why you must assign queer motives or incompetence to anyone who uses any other. In a simple test... ?Forms!Form1.AllowFilters True ?Form_Form1.AllowFilters Run-time error '2467' The expression you entered refers to an object that is closed or doesn't exist. The form in question IS open and has a module. Admittedly, the following all do work... ?Form_Form1.section(0).Height ?Form_Form1.Width ?Form_Form1.Controls.Count Further testing reveals that the Form_FormName syntax does not work in Queries whereas Forms!FormName does. So it seems that the Form_FormName syntax *often* works, but not always. Just my preference, but given a syntax that work most of the time versus one that works all the time I prefer to use the latter even in specific situations where the former would work. I simply see no reason for introducing an inconsistency. In addition; within the context of these groups answering a question with the Form_FormName syntax (even when it works) is likely to just raise additional questions because of its non-familiarity whereas most people will recognize Forms!FormName. As for "paid by the word" ?Form_Form1.Controls.Count and ?Forms!Form1.Controls.Count differ by exactly one character. You were the one to introduce into the conversation the more verbose Forms("FormName") variation so that is a straw man. I fully concede the point when making references to subforms. In that case Form_FormName is definitely fewer keystrokes, but one can also consider the rare situation where a form could be used as a subform more than once at the same time and I believe in that case the Form_FormName syntax fails since it cannot distinguish between the multiple instances. Finally, if there are people who use Forms!FormName simply because that is what they are familiar with and because that is what they see in all of the help file examples and in the majority of code snippets they see on the internet then just what exactly is wrong with that? Are you basing the superiority of your preference solely on keystroke count? Because it is more modern? Just because I can cook scrambled eggs in a microwave yet still choose to do so in a frying pan doesn't mean I am ignorant or dismissive of microwave technology. It might just mean that I am used to cooking them in a pan and prefer doing it that way.  SignatureRick Brandt, Microsoft Access MVP Email (as appropriate) to... RBrandt at Hunter dot com > ?Form_Form1.AllowFilters > Run-time error '2467' > The expression you entered refers to an object that is closed or doesn't exist. A quick check here: Sub temp() Debug.Print Form_Employees.AllowFilters End Sub True is printed in the Immediate Window. >> ?Form_Form1.AllowFilters >> Run-time error '2467' [quoted text clipped - 7 lines] > > True is printed in the Immediate Window. Suppose you have two instances of a form open and want to refer to the controls on a form contained in a subform control on the form. If you use Form_FormName which instance of the form is being referenced? > >> ?Form_Form1.AllowFilters > >> Run-time error '2467' [quoted text clipped - 12 lines] > in a subform control on the form. If you use Form_FormName > which instance of the form is being referenced? I don't know. Sometime when I consider it either worthwhile to have two instances of a form open (with the form having a subform), or likely that it will happen I'll check into it. How is done using the [long idiotic string from hell] syntax? I generally don't use subforms any more as the use of the syntax I have suggested facilitates using full forms as sub forms ... with none of the limitations of subforms. When I use multiple instances of forms it is through code like: Dim ADetailForms(0 To 1) As [Form_Faculty Details] Public Sub OpenSomeFormInstances() Dim z As Long For z = 0 To 1 Set ADetailForms(z) = New [Form_Faculty Details] With ADetailForms(z) .Visible = True .Caption = "Look Ma Multiple Distinguishable Instances of a Form " & z End With Next z End Sub Public Sub ZapAllThoseFormInstances() Erase ADetailForms End Sub >> Suppose you have two instances of a form open >> and want to refer to the controls on a form contained [quoted text clipped - 4 lines] > two instances of a form open (with the form having a subform), or > likely that it will happen I'll check into it. That'll be great. > I generally don't use subforms any more as the use of the syntax I > have suggested facilitates using full forms as sub forms ... with > none of the limitations of subforms. What are the limitations of subforms? > When I use multiple instances of forms it is through code like: How do you use the form module-name syntax (which is what Forms_FormName is) to refer to those different instances? It's pretty easy to use the form's collection to figure out which one you want to use by checking the caption. But how do you do that with your suggested syntax? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > Sometime when I consider it either worthwhile to have > two instances of a form open (with the form having a subform), or > likely that it will happen I'll check into it. Huh? What about a single form with two copies of the same subform? How do you distinguish those two subforms with your suggested syntax? Adding a second form with a subform doesn't change the problem space at all, though it might very well be more common. I can't believe your applications have such poor UI that you don't use subforms or that you're such a poor programmer that you'd not re-use a form in more than one location if it were appropriate (as it very often is). SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ On Nov 24, 7:51 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote: > > Sometime when I consider it either worthwhile to have > > two instances of a form open (with the form having a subform), or > > likely that it will happen I'll check into it. > > Huh? What about a single form with two copies of the same subform? TTBOMK VBA will search down the stack, so to speak, for the first pointer to the subform; thus it will reference the last created instance of the subform. lyle <lyle.fairfield@gmail.com> wrote in news:daba940c-e3fc-4601-86af-ee995c5d09b3@a39g2000pre.googlegroups.co m: > On Nov 24, 7:51 pm, "David W. Fenton" > <XXXuse...@dfenton.com.invalid> wrote: [quoted text clipped - 12 lines] > pointer to the subform; thus it will reference the last created > instance of the subform. Yes, and then what? How do you utilize your syntax and act upon something other than the default that is returned? SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ On Nov 24, 7:51 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote: > I can't believe your applications have such poor UI that you don't > use subforms or that you're such a poor programmer that you'd not > re-use a form in more than one location if it were appropriate (as > it very often is). I can't think of any case where I have used the same form twice as a subform (on the same parent form) so I can't say how I would deal with that, but I expect it would be smoothly and efficiently, if I used subforms, that is, but, of course, I don't. I think I stopped using subforms five years ago or so; as I roll my own, users do not seem to notice, except that they like to be able to move and size the "sub" form. I'm not sure if you were a regular here in CDMA when I posted in more than one thread about exposing forms in library mdbs, making them available for reuse not only within one application, but within many at the same time. That must be almost ten years ago. I really hope you won't bother yourself with my level of programming expertise. It's been of sufficient quality over the past twenty-five years that I have not had to seek clients actively during that time. No need for you to miss your beauty sleep worrying about that. I don't really expect to convert anyone to my position on this matter, David, and arguing is tedious. So I'm checking out of this thread now. > I don't really expect to convert anyone to my position on this matter, > David, and arguing is tedious. So I'm checking out of this thread now. You're efforts are not wasted, Sir Lyle of Fairfield. I learned of this from you previously, have investigated it further, and am grateful to you for having enlightened me. I just don't use it. Take from that what you will. > On Nov 24, 7:51 pm, "David W. Fenton" > <XXXuse...@dfenton.com.invalid> wrote: [quoted text clipped - 6 lines] > I can't think of any case where I have used the same form twice as > a subform (on the same parent form) I use it all the time for merging records during data cleanup projects. That's both in my own maintenance work for clients as well as in the applications my clients use (which very often need a record merger form). Here's a screen shot: http://dfenton.com/DFA/examples/DuplicateResolution.png [that's actually a very early version of the design, so it's got problems, but that was what was handy without logging onto a terminal server and doing a screen capture there] > so I can't say how I would deal with > that, but I expect it would be smoothly and efficiently, if I used > subforms, that is, but, of course, I don't. How do you use your syntax to refer to the two subforms on the example form above? How do you distinguish the left one from the right one? I do it because the subform controls on the form have different names, delineating the difference between their functions. > I think I stopped using > subforms five years ago or so; as I roll my own, users do not seem > to notice, except that they like to be able to move and size the > "sub" form. How would you implement a record merger form like my example? > I'm not sure if you were a regular here in CDMA when I posted in > more than one thread about exposing forms in library mdbs, making > them available for reuse not only within one application, but > within many at the same time. Yes. I think it's an excellent idea. > That must be almost ten years ago. > I really hope you won't bother yourself with my level of > programming expertise. It's been of sufficient quality over the > past twenty-five years that I have not had to seek clients > actively during that time. No need for you to miss your beauty > sleep worrying about that. I think not using subforms is pig-headed and stupid, and costs your clients a lot of money that they shouldn't have to pay. > I don't really expect to convert anyone to my position on this > matter, David, and arguing is tedious. So I'm checking out of this > thread now. I'd like to hear some kind of rational explanation of why you choose to torture Access by avoiding its most powerful feature, i.e., the subform (particularly the continuous one). SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ > When I use multiple instances of forms it is through code like: > [quoted text clipped - 14 lines] > Erase ADetailForms > End Sub Hi, Lyle. I responded to this in a new thread I started here called "Working With Multiple Form Instances." If you could take a look at it, I'd appreciate it. Thanks! Neil > I wish one of them had a short section on globally accessing > things that don't happen to to pointed to my "Me." "Me" is a reference to the CLASS MODULE of the object the code is in. If you want to refer to a different object, you have to specify it completely. Another form will be Form!frmOtherForm in place of Me. A form that is embedded in the form you're coding in will be Me!subFormControlName.Form. That's all you need to know. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ On Nov 24, 7:46 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote: > D...@NoEmail.com wrote innews:td4dk3hbjljdf1pn052a34ar8b20rpvumi@4ax.com: > "Me" is a reference to the CLASS MODULE of the object the code is > in. Private Sub Form_Load() Debug.Print Me Is Me.Module 'False Debug.Print TypeName(Me), TypeName(Me.Module) 'Form_Issue List, Module Debug.Print VarPtr(Me), VarPtr(Me.Module) '1297044,1296972 Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776 End Sub lyle <lyle.fairfield@gmail.com> wrote in news:00dfcec4-498e-421a-b169-cc7288e0f925@a39g2000pre.googlegroups.co m: > On Nov 24, 7:46 pm, "David W. Fenton" > <XXXuse...@dfenton.com.invalid> wrote: [quoted text clipped - 10 lines] > Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776 > End Sub Me will be of type FORM when it's a form's class module, of type REPORT when a report, and of type CLASS MODULE when in a standalone class module. But this is because someone decided that the class module reference should return an object of the type it is is part of (or, put another way, of the subtype of class module). It's very helpful, I think, to remember that it's the class module that is referred to by Me simply because otherwise, it's hard to explain why it works in a standalone class module but not in a regular code module. SignatureDavid W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ On Nov 24, 7:46 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid> wrote: > "Me" is a reference to the CLASS MODULE of the object the code is > in. Private Sub Form_Load() Debug.Print Me Is Me.Module 'False Debug.Print TypeName(Me), TypeName(Me.Module) 'Form_Issue List, Module Debug.Print VarPtr(Me), VarPtr(Me.Module) '1297044,1296972 Debug.Print ObjPtr(Me), ObjPtr(Me.Module) '1563856, 55509776 End Sub >> I wish one of them had a short section on globally accessing >> things that don't happen to to pointed to my "Me." [quoted text clipped - 6 lines] > > That's all you need to know. I would say Me is a way to reference an instance of an object from within the class module that defines the object. It's all a bit fuzzy in Access since so much of the implementation of the objects defined by the application is hidden. Also, please note how you're adding records. Are you still adding them manually through the form, or did you switch to adding them in code? The requery of the combo box should work either way; but I just need to know which method we're dealing with. > (SNIP) >>> [quoted text clipped - 57 lines] > > (SNIP) It would be redundant to store both the ClientID and ClientName in the second table. What happens if you change the name in one of the two tables: how will you know which name is correct? Only the foreign key (the ClientID) should be stored in the second table. You can join the two tables together in a query and use the query wherever you would otherwise have used the table when you need the name. SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > Hi, > [quoted text clipped - 44 lines] > switching to the given table...), I need to stop here and find another > application, rather than learn this one. (If so, suggestions?) The clients name isn't be stored in the Messages table. I just want it displayed when you are adding messages to the table, like what happens in the sample NWIND database when you add an order. The problem is I can't get it to work like NWIND, and I don't see why. The databases are posted here in a message with the same subject, ending in 1/1 instead of 0/1. >It would be redundant to store both the ClientID and ClientName in the >second table. What happens if you change the name in one of the two tables: [quoted text clipped - 3 lines] >You can join the two tables together in a query and use the query wherever >you would otherwise have used the table when you need the name. |
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.