From bounce-access-l-2656781@groups.ittoolbox.com Wed Jun 23 10:43:48 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5NEhk9k010828 for ; Wed, 23 Jun 2004 10:43:47 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7202FEB00B4; Wed, 23 Jun 2004 10:43:44 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Wed, 23 Jun 2004 10:43:38 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Teresa Dickson via access-l" To: kermit@polaris.net Subject: [access-l] RE: Cannot crete or edit reports in shared database Date: Wed, 23 Jun 2004 08:47:21 -0400 Organization: IMARK Group, Inc. Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.4510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Importance: Normal X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by nexus.polaris.net id i5NEhk9k010828 Status: O You need to make sure that other users are not in the database. If everyone is out of the database and it is still giving you that message then check to make sure that a copy of the .ldb file is not still out there. If it is delete it and then go back to open the database in exclusive mode. You can do that by opening the database with the open dialog box - click on the drop down arrow next to open and choose open exclusive. -----Original Message----- From: Barrena, Francisco (CORP, CIAT) via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Tuesday, June 22, 2004 11:09 PM To: Teresa Dickson Subject: [access-l] Cannot crete or edit reports in shared database # Professor Warns of Oracle Monopoly # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28301 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 I am trying to create a new report in a shared database and it says me if i don't have exclusive access to the database i cannot save any changes. I have done this in the past in other databases and it allowed me to do it. What should i do, what do i need to change in order to be able to edit / create reports and forms in a shared database? Thanks. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Wed Jun 23 10:43:54 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5NEhq6I010834 for ; Wed, 23 Jun 2004 10:43:53 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7251A8800CA; Wed, 23 Jun 2004 10:43:49 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8F2E288F0926E18C.9FB8250928CFE7EA@gateway1.nettally.com> for ; Wed, 23 Jun 2004 10:43:43 -0500 Subject: [access-l] RE: Loading form with many records To: kermit@polaris.net X-Mailer: Lotus Notes Release 5.0.11 July 24, 2002 Message-ID: From: "dramse... via access-l" Date: Wed, 23 Jun 2004 09:22:46 -0400 MIME-Version: 1.0 X-MIMETrack: Serialize by Router on Cleveland/Maytag(Release 5.0.11 |July 24, 2002) at 06/23/2004 09:22:47 AM, Itemize by SMTP Server on NWTSMTPO/Maytag(Release 5.0.11 |July 24, 2002) at 06/23/2004 08:22:48 AM, Serialize by Router on NWTSMTPO/Maytag(Release 5.0.11 |July 24, 2002) at 06/23/2004 08:23:38 AM, Serialize complete at 06/23/2004 08:23:38 AM Content-type: text/plain; charset=us-ascii Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Stinger Takes Flight # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28364 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 thanks Rod. I to have tables over 200,000+ records and I'm doing everything you mentioned except I'm not sure about what you said regarding "not populating the form with the full recordset". Can you explain that in more detail? Wnen I say it is slow I mean something on the order of 5 to 10 seconds. The bad thing was that the "running query" message was visible in the status bar and the users were confused by that. thanks again for the info and any further help you can provide Dan Ramsey Production Engineer Maytag Cleveland Cooking "Rod via access-l" oolbox.com> cc: Subject: [access-l] RE: Loading form with many records 06/23/2004 07:39 AM Please respond to access-l # Professor Warns of Oracle Monopoly # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28301 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 One thing I try to do with access is to treat it like a web interface. With a web interface you want to restrict the amount of information passed to the client as much as possible. So. Have a look at your combo box. Can you divide the records up into categories? (I.E. Starting letter, or code group, etc) Get the user to select a category and then get and display the list of options in that category in your combo. Make sure that the sql your combo uses as its source only returns the fields you are using (avoid "select * from..."). Usually this will be two fields. An index and a value. Don't use the full recordset as your source for the form. Populate your form with a single record once the user has selected it from the combo box. 11,000 is not a lot of records. I have databases with 200,000+ records that perform quite well using the above model. Rod. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 23 June 2004 11:36 To: Rod Subject: [access-l] Loading form with many records Hello, I have a form that runs off a table that has 11,000 records. Once the form loads the user uses comboboxes to filter or drill down to the data they would like to view. The problem is the performance is slow both opening the form, and the On Change events in the comboboxes. It runs StrSql after the combos are updated. Any thoughts on how to speed up the opening of the form and/or the speed? All of the appropriate fields are indexed already. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. NOTICE: This Maytag Corporation e-mail message (including any file attachment) is intended only for the use of the individual or entity to which it is addressed, and may contain information that is privileged and/or confidential. If you are not the intended recipient, any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by reply e-mail or a collect telephone call and delete or destroy all copies of this message and any file attachment. Thank you! *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Wed Jun 23 12:24:42 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5NGOfRi013731 for ; Wed, 23 Jun 2004 12:24:41 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AEC634BF001E; Wed, 23 Jun 2004 12:24:38 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8C5515B7DF4BF45C.BD60BE5D49AFEE3F@gateway1.nettally.com> for ; Wed, 23 Jun 2004 12:24:29 -0500 Message-ID: From: "james_mersinger@medco.com via access-l" To: kermit@polaris.net Subject: [access-l] RE: Loading form with many records Date: Wed, 23 Jun 2004 10:51:19 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Building Trust in Your Organization # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28365 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 I have done what you have recommended and have seen some improvement. However when the form first loads the default recordsource still loads the whole recordset. Choices are not available until the form loads. Then the drill down of the combo boxes begins. It's almost like I need to turn off the recordsource while the form is loading. Can that be done? Thanks -----Original Message----- From: Rod via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Wednesday, June 23, 2004 7:39 AM To: Mersinger; James Subject: [access-l] RE: Loading form with many records # Professor Warns of Oracle Monopoly # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28301 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 One thing I try to do with access is to treat it like a web interface. With a web interface you want to restrict the amount of information passed to the client as much as possible. So. Have a look at your combo box. Can you divide the records up into categories? (I.E. Starting letter, or code group, etc) Get the user to select a category and then get and display the list of options in that category in your combo. Make sure that the sql your combo uses as its source only returns the fields you are using (avoid "select * from..."). Usually this will be two fields. An index and a value. Don't use the full recordset as your source for the form. Populate your form with a single record once the user has selected it from the combo box. 11,000 is not a lot of records. I have databases with 200,000+ records that perform quite well using the above model. Rod. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 23 June 2004 11:36 To: Rod Subject: [access-l] Loading form with many records Hello, I have a form that runs off a table that has 11,000 records. Once the form loads the user uses comboboxes to filter or drill down to the data they would like to view. The problem is the performance is slow both opening the form, and the On Change events in the comboboxes. It runs StrSql after the combos are updated. Any thoughts on how to speed up the opening of the form and/or the speed? All of the appropriate fields are indexed already. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. ----------------------------------------- This e-mail message and any attachments contain confidential information from Medco. If you are not the intended recipient, you are hereby notified that disclosure, printing, copying, distribution, or the taking of any action in reliance on the contents of this electronic information is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender by reply message and then delete the electronic message and any attachments. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 10:10:37 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5OEAZ7q018167 for ; Thu, 24 Jun 2004 10:10:35 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A0D93FF100B6; Thu, 24 Jun 2004 10:10:33 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <96DCEA6DDCAF1DB7.A2E274CC05041E36@gateway1.nettally.com> for ; Thu, 24 Jun 2004 10:10:22 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] Active Control Date: Thu, 24 Jun 2004 15:03:03 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Stinger Takes Flight # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28364 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Please can somebody help me with the following: My main form is called frmEmployee. I have a subform within this form called sfrmAddress. This subform contains 2 tabs: The caption for these tabs are "home" and "work". The recordsource for the subform needs to change according to the tab selected. e.g. if the user selects the home tab then the recordsource is: "Select * From tbladdress Where end_date is null and add_type = 'home';" .. whereas if the user selects the work tab then the recordsource will change to: "Select * From tbladdress Where end_date is null and add_type = 'work';" How does my program know which tab the user has selected. I have tried the following code: If Me.activecontrol.Parent.Caption = "home" then 'set Recordsource to look at home addresses Else 'set Recordsource to look at work addresses End if Sometimes this works but othere times it generates an error. I beleive the problem is caused by the fact that the controls on the form are sometimes not active. Any suggestions please???!! Many thanks. Lee *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 11:10:03 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5OFA243020126 for ; Thu, 24 Jun 2004 11:10:02 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AEC8868B0022; Thu, 24 Jun 2004 11:10:00 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <95B9C4199CB98802.8C56588C812C48A3@gateway1.nettally.com> for ; Thu, 24 Jun 2004 11:09:55 -0500 Message-ID: From: "james_mersinger@medco.com via access-l" To: kermit@polaris.net Subject: [access-l] RE: Active Control Date: Thu, 24 Jun 2004 10:54:28 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: RO # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 You could just drag a subform onto each tab. Each subform having its own query. Then when you select each tab they will be available. -----Original Message----- From: Lee Aldrich via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Thursday, June 24, 2004 10:03 AM To: Mersinger; James Subject: [access-l] Active Control # Stinger Takes Flight # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28364 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Please can somebody help me with the following: My main form is called frmEmployee. I have a subform within this form called sfrmAddress. This subform contains 2 tabs: The caption for these tabs are "home" and "work". The recordsource for the subform needs to change according to the tab selected. e.g. if the user selects the home tab then the recordsource is: "Select * From tbladdress Where end_date is null and add_type = 'home';" .. whereas if the user selects the work tab then the recordsource will change to: "Select * From tbladdress Where end_date is null and add_type = 'work';" How does my program know which tab the user has selected. I have tried the following code: If Me.activecontrol.Parent.Caption = "home" then 'set Recordsource to look at home addresses Else 'set Recordsource to look at work addresses End if Sometimes this works but othere times it generates an error. I beleive the problem is caused by the fact that the controls on the form are sometimes not active. Any suggestions please???!! Many thanks. Lee *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. ----------------------------------------- This e-mail message and any attachments contain confidential information from Medco. If you are not the intended recipient, you are hereby notified that disclosure, printing, copying, distribution, or the taking of any action in reliance on the contents of this electronic information is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender by reply message and then delete the electronic message and any attachments. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 11:10:05 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5OFA4kg020129 for ; Thu, 24 Jun 2004 11:10:04 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AEC9602200CA; Thu, 24 Jun 2004 11:10:01 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Thu, 24 Jun 2004 11:09:54 -0500 From: "Gabriella Rekasi via access-l" To: kermit@polaris.net Subject: [access-l] RE: Active Control Date: Thu, 24 Jun 2004 07:56:11 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Lee, The tab control has pages (Page1, Page 2, etc.) and all have properties individually. So if you would just simply use the on click property, it would trigger the desired action. But you need 2 subforms placed each on Page1 and Page2, so when you click on one of the tabs the control would "know" which subform is active. I never tried placing the same subform on 2 tabs, but it is probably possible if you name them differently. My opinion is not to use tab control, but 2 tab-like buttons instead. When you click on the "Home" or "Work" buttons the code would be "Me.SubForm.ControlSource = . Me. Requery Gabi *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 17:22:38 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5OLMas3001174 for ; Thu, 24 Jun 2004 17:22:37 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A61A654000DA; Thu, 24 Jun 2004 17:22:34 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Thu, 24 Jun 2004 17:22:31 -0500 Message-ID: From: "Kevin via access-l" To: kermit@polaris.net Subject: [access-l] Re: Textbox Control Source Date: Thu, 24 Jun 2004 14:19:50 -0700 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2656.59) Content-Type: text/plain; charset="iso-8859-1" Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Excellent, Phil! I was able to solve it with DLookUp(), see below. When I said 'part of the logic', there is also logic for "OPEN" and "OVERDUE" status. But I figured if I could get part of the logic, then I could apply it to the rest. With your help it worked. Partial logic for status = "CLOSED"..... =IIf([CARRequired]="YES" And DLookUp("[IssueDate]","[tblCATS]","[CaseNumber] = " & [Forms]![frmPAAA]![CaseNumber]) Is Not Null, "CLOSED", Null) Full logic for status = "OVERDUE" or "OPEN" or "CLOSED"..... =IIf([CARRequired]="YES" And DLookUp("[IssueDate]","[tblCATS]","[CaseNumber] = " & [Forms]![frmPAAA]![CaseNumber]) Is Null And Date()-[ReviewRequestDate]>=30,"OVERDUE",IIf([CARRequired]="YES" And DLookUp("[IssueDate]","[tblCATS]","[CaseNumber] = " & [Forms]![frmPAAA]![CaseNumber]) Is Null,"OPEN",IIf([CARRequired]="YES" And DLookUp("[IssueDate]","[tblCATS]","[CaseNumber] = " & [Forms]![frmPAAA]![CaseNumber]) Is Not Null,"CLOSED",Null))) Thanks, Kevin -----Original Message----- From: Phil Marcus via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Thursday, June 24, 2004 11:10 AM To: Kevin Subject: [access-l] Re: Textbox Control Source # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Kevin, You speak of "part of the logic," so we don't see the entire picture. Still, I can see one apparent difference. A query returns a SET of rows, which Acces displays in a datasheet. A textbox requires a SINGLE value. In the form, could you use DLookup() instead of Select to focus on a single row? Also, your expression returns in some circumstances NULL, which is a symbol for "Not known" Would it be OK to return "OPEN" instead of NULL? HTH Phil From: "Kevin via access-l" To: Phil Marcus Subject: [access-l] Textbox Control Source Date sent: Thu, 24 Jun 2004 09:43:43 -0700 Send reply to: access-l@Groups.ITtoolbox.com > # Optimizing the mysqld variables > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > I have 2 tables (tblPAAA and tblCATS) that have a common field called > "CaseNumber". Part of the logic for a 'Status' textbox on a form based on > tblPAAA uses data in the tblCATS table. > > The following statement works in a query based on tblPAAA: > > IIf(CARRequired = "YES" And (SELECT tblCATS.IssueDate FROM CATS WHERE > tblPAAA.CaseNumber = tblCATS.CaseNumber) Is Not Null, "CLOSED", Null) > > The same statement as the control source for a textbox on a form based on > tblPAAA does not display. I get a #Name? in the textbox on Form View. > > =IIf(CARRequired = "YES" And (SELECT tblCATS.IssueDate FROM CATS WHERE > tblPAAA.CaseNumber = tblCATS.CaseNumber) Is Not Null, "CLOSED", Null) > > > I appreciate your suggestions. > > Kevin *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 17:33:42 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5OLXfG1001437 for ; Thu, 24 Jun 2004 17:33:41 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A8B3176C00CC; Thu, 24 Jun 2004 17:33:39 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Thu, 24 Jun 2004 17:33:35 -0500 Content-Class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; Subject: [access-l] data type mismatch X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Date: Thu, 24 Jun 2004 17:28:45 -0400 Message-ID: Thread-Topic: data type mismatch thread-index: AcRaMkwOhxFcwpquRWKAX4/4HdZyUQ== From: "Katz, Mark via access-l" To: kermit@polaris.net Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by nexus.polaris.net id i5OLXfG1001437 Status: O I have a report based on a table. I have a filter (in the properties window of the report) which says Type = "Reg" which works. However, when I remove this filter, or change Filter On from yes to no, or change Reg to Con (the other choice of type), I get an error which says "Data type mismatch in criteria expression." What I want to do, and I've done for other reports, is have the user select from a variety of criteria using list boxes to run this report. What's is selected in the list box ends up in the filter in the report. Like I say, I've done all that with other reports, but this report doesn't seem to like it except if Type = Reg for the filter. Do you know why this report only seems to work when I select Type = Reg, and not when I have other criteria? Thanks, ~Mark *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Thu Jun 24 19:59:26 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5ONxO5R005898 for ; Thu, 24 Jun 2004 19:59:25 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AADB6C1C00E4; Thu, 24 Jun 2004 19:59:23 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Thu, 24 Jun 2004 19:59:19 -0500 From: "JHill7777... via access-l" Message-ID: Date: Thu, 24 Jun 2004 19:56:18 EDT Subject: [access-l] RE: Bound Combo Box Problem To: kermit@polaris.net MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII"; X-Mailer: 8.0 for Windows sub 6032 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Thanks Kevin. Thanks for the information. After checking some things out I have come to this conclusion. If a field in a table is defined as Long, then whenever you press the delete key and entirely delete the value in the text box or combo box, you get this error about the type is not a variant and therefor cannot be null. So I've decided to change my code such that the primary fields in my tables will be defined as text rather than long. This allows a null to be entered in the combo or text box, and I can trap it. However a different problem for me has emerged. Even though the fields are defined as text, I would like to be able to sort them numerically as if they were numbers. Right now, since the original input was long, I have values like 1, 2, 3,4,5,11,22, etc. When I query them or sort them numerically I now get 1,11,2,3,4,5, etc. That is all the values starting with 1 come first, then the 2's etc. Is there a way to tell the queries to right justify the fields that are being sorted prior to the sort. Or is there anyway to left pad the input fields with blanks so that the data is right-justified rather than left-justified? Thanks for your help. John *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 00:16:25 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5P4GNQ0012431 for ; Fri, 25 Jun 2004 00:16:24 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A716629800F2; Fri, 25 Jun 2004 00:16:22 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <96EBDBB64BDE6D1C.886AFC8E4C296C0E@gateway1.nettally.com> for ; Fri, 25 Jun 2004 00:16:13 -0500 From: "Phil Marcus via access-l" Organization: TTG Services/Amberway Associates To: kermit@polaris.net Date: Fri, 25 Jun 2004 00:12:08 -0400 MIME-Version: 1.0 Subject: [access-l] Re: data type mismatch Message-ID: Priority: normal X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 First, I would not use the name "Type" for a field, and if I did I would say: [Type] = "Reg" Second if the data type for [type] is a string then it s/b [type]="Reg" as you say but also [type]="con" and so forth. It is not clear whether your code to use items chosen from a list box include the quote marks. (BTW, sometimes the easest way to add such marks is with chr(34).) HTH Phil Subject: [access-l] data type mismatch Date sent: Thu, 24 Jun 2004 17:28:45 -0400 From: "Katz, Mark via access-l" To: Phil Marcus Send reply to: access-l@Groups.ITtoolbox.com > > I have a report based on a table. I have a filter (in the properties > window of the report) which says Type = "Reg" which works. However, > when I remove this filter, or change Filter On from yes to no, or change > Reg to Con (the other choice of type), I get an error which says "Data > type mismatch in criteria expression." > > What I want to do, and I've done for other reports, is have the user > select from a variety of criteria using list boxes to run this report. > What's is selected in the list box ends up in the filter in the report. > Like I say, I've done all that with other reports, but this report > doesn't seem to like it except if Type = Reg for the filter. > > Do you know why this report only seems to work when I select Type = Reg, > and not when I have other criteria? > > Thanks, > > ~Mark > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 01:25:57 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5P5PuHh014050 for ; Fri, 25 Jun 2004 01:25:56 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A76395C900EA; Fri, 25 Jun 2004 01:25:55 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <91BCF8AA36E5B823.BAE3B3DF0B631CD0@gateway2.nettally.com> for ; Fri, 25 Jun 2004 01:25:51 -0500 From: "Uttam via access-l" To: kermit@polaris.net Subject: [access-l] RE: Bound Combo Box Problem Date: Fri, 25 Jun 2004 10:50:14 +0530 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Importance: Normal X-OriginalArrivalTime: 25 Jun 2004 05:19:57.0392 (UTC) FILETIME=[0E2F8D00:01C45A74] Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 to sort on numerical order, sort the field on VAL([fieldname]). Padding will work correctly only with monospace fonts. flg. function will left-pad a given string to a given length e.g. lpad("abcd",10) will return " abcd": Function lpad(str As String, strlen As Byte) As String If Len(str) > strlen Then lpad = str Else lpad = Space(strlen - Len(str)) & str End If End Function Also, you can restrict the characters that can be entered in the field using field's InputMask property. HTH Regards, -----Original Message----- From: JHill7777... via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 05:26 To: Uttam Subject: [access-l] RE: Bound Combo Box Problem # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Thanks Kevin. Thanks for the information. After checking some things out I have come to this conclusion. If a field in a table is defined as Long, then whenever you press the delete key and entirely delete the value in the text box or combo box, you get this error about the type is not a variant and therefor cannot be null. So I've decided to change my code such that the primary fields in my tables will be defined as text rather than long. This allows a null to be entered in the combo or text box, and I can trap it. However a different problem for me has emerged. Even though the fields are defined as text, I would like to be able to sort them numerically as if they were numbers. Right now, since the original input was long, I have values like 1, 2, 3,4,5,11,22, etc. When I query them or sort them numerically I now get 1,11,2,3,4,5, etc. That is all the values starting with 1 come first, then the 2's etc. Is there a way to tell the queries to right justify the fields that are being sorted prior to the sort. Or is there anyway to left pad the input fields with blanks so that the data is right-justified rather than left-justified? Thanks for your help. John *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 04:49:43 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5P8ngsB018426 for ; Fri, 25 Jun 2004 04:49:42 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7248A3400B4; Fri, 25 Jun 2004 04:49:40 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8D1CB20A7508EAB5.9FFF2EE2F3E8EEBE@gateway1.nettally.com> for ; Fri, 25 Jun 2004 04:49:33 -0500 Message-ID: From: "Rod via access-l" To: kermit@polaris.net Subject: [access-l] RE: Bound Combo Box Problem Date: Fri, 25 Jun 2004 09:46:36 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 In Access I would not change a primary key from long to text. If you want to use the key value as text in a list box, I would add another field to your table, make it a text field and then write some code that makes the new field = to the ID field when a new record is created. You would then in effect have two ID fields , the real one which is a long and the one you use for list/cbo which is text. This way you can still have an autonumber as your primary, you can sort on this which will give you the order that the records where created and the sort will be a numeric sort. I would not do a convert on a text field that contains a long because this will slow down queries and record access in forms. The more records you have the slower it will be. Rod. -----Original Message----- From: Uttam via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 25 June 2004 06:20 To: Rod Subject: [access-l] RE: Bound Combo Box Problem # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 to sort on numerical order, sort the field on VAL([fieldname]). Padding will work correctly only with monospace fonts. flg. function will left-pad a given string to a given length e.g. lpad("abcd",10) will return "abcd": Function lpad(str As String, strlen As Byte) As String If Len(str) > strlen Then lpad = str Else lpad = Space(strlen - Len(str)) & str End If End Function Also, you can restrict the characters that can be entered in the field using field's InputMask property. HTH Regards, -----Original Message----- From: JHill7777... via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 05:26 To: Uttam Subject: [access-l] RE: Bound Combo Box Problem # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Thanks Kevin. Thanks for the information. After checking some things out I have come to this conclusion. If a field in a table is defined as Long, then whenever you press the delete key and entirely delete the value in the text box or combo box, you get this error about the type is not a variant and therefor cannot be null. So I've decided to change my code such that the primary fields in my tables will be defined as text rather than long. This allows a null to be entered in the combo or text box, and I can trap it. However a different problem for me has emerged. Even though the fields are defined as text, I would like to be able to sort them numerically as if they were numbers. Right now, since the original input was long, I have values like 1, 2, 3,4,5,11,22, etc. When I query them or sort them numerically I now get 1,11,2,3,4,5, etc. That is all the values starting with 1 come first, then the 2's etc. Is there a way to tell the queries to right justify the fields that are being sorted prior to the sort. Or is there anyway to left pad the input fields with blanks so that the data is right-justified rather than left-justified? Thanks for your help. John *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 07:16:09 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PBG74K021407 for ; Fri, 25 Jun 2004 07:16:07 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A9741FB60020; Fri, 25 Jun 2004 07:16:04 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 07:16:02 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] Re: Active Control Date: Fri, 25 Jun 2004 12:10:08 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Phil, James & Gabi, Thanks for the help - very much appreciated. Knew there had to be an easier way! Again, many thnaks. Lee -----Original Message----- From: Phil Marcus via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 24 June 2004 15:10 To: Lee Aldrich Subject: [access-l] Re: Active Control # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Lee, You are trying to do this the hard way. Let's make it easy. Each of the tabs gets its own form, albeit perhaps two copies of the same. Each gets as a recordsource the appropriate query. Done. If you have trouble placing a form (subform) on a tab page come back - we have helped on that before. Phil From: "Lee Aldrich via access-l" To: Phil Marcus Subject: [access-l] Active Control Date sent: Thu, 24 Jun 2004 15:03:03 +0100 Send reply to: access-l@Groups.ITtoolbox.com > # Stinger Takes Flight > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28364 > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > Please can somebody help me with the following: > > My main form is called frmEmployee. > > I have a subform within this form called sfrmAddress. > > This subform contains 2 tabs: > > The caption for these tabs are "home" and "work". > > The recordsource for the subform needs to change according to the tab > selected. > > e.g. if the user selects the home tab then the recordsource is: > > "Select * From tbladdress Where end_date is null and add_type = 'home';" > > .. whereas if the user selects the work tab then the recordsource will > change to: > > "Select * From tbladdress Where end_date is null and add_type = 'work';" > > > How does my program know which tab the user has selected. > > I have tried the following code: > > If Me.activecontrol.Parent.Caption = "home" then > > 'set Recordsource to look at home addresses > > Else > > 'set Recordsource to look at work addresses > > End if > > Sometimes this works but othere times it generates an error. I beleive the > problem is caused by the fact that the controls on the form are sometimes > not active. > > Any suggestions please???!! > > Many thanks. > > Lee > > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 07:16:34 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PBGXXe021423 for ; Fri, 25 Jun 2004 07:16:33 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A98E9A2D00E2; Fri, 25 Jun 2004 07:16:30 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 07:16:25 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] RE: Active Control Date: Fri, 25 Jun 2004 12:10:06 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Thanks Gabi. The OnClick event on the Page only takes effect when I click on the actual page (i.e. form) as opposed to the actual tab part of the page. But I think you are perhaps right about having 2 sepearte subforms on each page and identifyign the recordsource that way. Thanks -----Original Message----- From: Gabriella Rekasi via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 24 June 2004 14:56 To: Lee Aldrich Subject: [access-l] RE: Active Control # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Lee, The tab control has pages (Page1, Page 2, etc.) and all have properties individually. So if you would just simply use the on click property, it would trigger the desired action. But you need 2 subforms placed each on Page1 and Page2, so when you click on one of the tabs the control would "know" which subform is active. I never tried placing the same subform on 2 tabs, but it is probably possible if you name them differently. My opinion is not to use tab control, but 2 tab-like buttons instead. When you click on the "Home" or "Work" buttons the code would be "Me.SubForm.ControlSource = . Me. Requery Gabi *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:15:15 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCFEGQ022743 for ; Fri, 25 Jun 2004 08:15:14 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A74F3A3500D0; Fri, 25 Jun 2004 08:15:11 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:14:54 -0500 From: "Mersinger, James via access-l" To: kermit@polaris.net Date: Fri, 25 Jun 2004 12:14:03 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] Prompt to save work Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Has anyone ever noticed or expeienced while desinging a database there are no promts to save your work when you close a form, query, report etc. I have some DBs that do prompt and others that don't. I'm not selecting any options like that on the different files so I'm trying to figure out the source. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:17:07 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCH6lu022837 for ; Fri, 25 Jun 2004 08:17:06 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7BF45EA00DC; Fri, 25 Jun 2004 08:17:03 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:16:57 -0500 Message-ID: From: "Rod via access-l" To: kermit@polaris.net Subject: [access-l] RE: Prompt to save work Date: Fri, 25 Jun 2004 13:16:15 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: text/plain; charset="iso-8859-1" Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Yes, I get the same behaviour. I have never bothered to find out what causes it, but I think that there is a global database setting. Rod. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 25 June 2004 13:14 To: Rod Subject: [access-l] Prompt to save work # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Has anyone ever noticed or expeienced while desinging a database there are no promts to save your work when you close a form, query, report etc. I have some DBs that do prompt and others that don't. I'm not selecting any options like that on the different files so I'm trying to figure out the source. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:26:38 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCQajn023065 for ; Fri, 25 Jun 2004 08:26:36 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A9F967EF00C6; Fri, 25 Jun 2004 08:26:33 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:26:21 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Russell Grimshaw via access-l" To: kermit@polaris.net Subject: [access-l] RE: Prompt to save work Date: Fri, 25 Jun 2004 08:22:43 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-BTCNet-MailScanner-Information: Please contact MailScanner@btcnet.com for more information X-BTCNet-MailScanner: Found to be clean X-BTCNet-MailScanner-SpamCheck: not spam (whitelisted), SpamAssassin (score=-2.101, required 3, BAYES_00 -4.90, RCVD_IN_DYNABLOCK 2.60, RCVD_IN_RFCI 0.10, RCVD_IN_SORBS 0.10) X-MailScanner-Env-From: rgrimshaw@eaglenestconsulting.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Sometimes when I am working on a database and I am turning the warnings on and off, I forget to run them on and the prompt for saving a form etc. goes away. Russell Grimshaw EagleNest Consulting, Inc. Office : 419.866.6513 Cell: 419.346.3486 -----Original Message----- From: Rod via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:16 AM To: Russell Grimshaw Subject: [access-l] RE: Prompt to save work # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Yes, I get the same behaviour. I have never bothered to find out what causes it, but I think that there is a global database setting. Rod. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 25 June 2004 13:14 To: Rod Subject: [access-l] Prompt to save work # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Has anyone ever noticed or expeienced while desinging a database there are no promts to save your work when you close a form, query, report etc. I have some DBs that do prompt and others that don't. I'm not selecting any options like that on the different files so I'm trying to figure out the source. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:30:58 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCUvxB023136 for ; Fri, 25 Jun 2004 08:30:57 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AAFE101D00A0; Fri, 25 Jun 2004 08:30:54 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:30:52 -0500 Message-ID: From: "Jiles via access-l" To: kermit@polaris.net Subject: [access-l] RE: Prompt to save work Date: Fri, 25 Jun 2004 08:29:58 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Check the settings under tools>Options>Edit/Find>Confirm, they should all be checked. Also you may have set warnings to false someplace in your code and failed to change set warnings back to true at the end of the procedure or if the code fails and does not reach the setwarnings = true statement the warnings will remain false until you restart the application or some code resets setwarnings to true. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:14 AM To: Jiles Subject: [access-l] Prompt to save work # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Has anyone ever noticed or expeienced while desinging a database there are no promts to save your work when you close a form, query, report etc. I have some DBs that do prompt and others that don't. I'm not selecting any options like that on the different files so I'm trying to figure out the source. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:36:19 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCaHlR023295 for ; Fri, 25 Jun 2004 08:36:18 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AC3E3AC800D0; Fri, 25 Jun 2004 08:36:14 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:36:13 -0500 From: "ajwz via access-l" To: kermit@polaris.net Date: Fri, 25 Jun 2004 12:32:36 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] opens to a function instead of Access UI Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by nexus.polaris.net id i5PCaHlR023295 Status: O Why is it when opening a Access2000 database it opens to a function in vb and highlighted instead of Access UI? I tried clearing the break point and saving it but it still goes to the code when it opens up. This is a copy from my machine and I don't have this problem. Can anyone explain this? Thanks! *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 08:44:10 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PCi9M2023542 for ; Fri, 25 Jun 2004 08:44:09 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AE16690200C6; Fri, 25 Jun 2004 08:44:06 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 08:43:59 -0500 Message-ID: From: "Jiles via access-l" To: kermit@polaris.net Subject: [access-l] RE: opens to a function instead of Access UI Date: Fri, 25 Jun 2004 08:43:01 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 go to debug and compile the code, if compile is not avaiable make a change to the code then change it back to make the compile option avaible. Compact and repair the database. This will normally fix the problem -----Original Message----- From: ajwz via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:33 AM To: Jiles Subject: [access-l] opens to a function instead of Access UI Why is it when opening a Access2000 database it opens to a function in vb and highlighted instead of Access UI? I tried clearing the break point and saving it but it still goes to the code when it opens up. This is a copy from my machine and I don't have this problem. Can anyone explain this? Thanks! *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 09:03:00 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PD2wSH024081 for ; Fri, 25 Jun 2004 09:02:59 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A27F3F9F00BE; Fri, 25 Jun 2004 09:02:55 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <847CDAE78E94FD37.80DC9D06571FE359@gateway3.nettally.com> for ; Fri, 25 Jun 2004 09:02:51 -0500 From: "Phil Marcus via access-l" Organization: TTG Services/Amberway Associates To: kermit@polaris.net Date: Fri, 25 Jun 2004 09:00:24 -0400 MIME-Version: 1.0 Subject: [access-l] Re: opens to a function instead of Access UI Message-ID: Priority: normal X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 It is breaking there because that line contains a function or other reference to an object that must be created from a recipe (so to speak) in a library (a .dll, .tlb or other file) that is present on the machine where the code was written but not on yours. Stop the code while in the editor by clicking on the icon which is a solid square. Now go to Tools>References and highlight each reference until you find one that shows as Missing in the lower pane. You must install that file. How depends on what it is. HTH Phil From: "ajwz via access-l" To: Phil Marcus Date sent: Fri, 25 Jun 2004 12:32:36 (GMT) Subject: [access-l] opens to a function instead of Access UI Send reply to: access-l@Groups.ITtoolbox.com > > Why is it when opening a Access2000 database it opens to a function in vb and highlighted instead of Access UI? I tried clearing the break point and saving it but it still goes to the code when it opens up. This is a copy from my machine and I don't have this problem. > > Can anyone explain this? > Thanks! > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 09:25:40 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PDPcYO024756 for ; Fri, 25 Jun 2004 09:25:39 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7CF6B6C00C6; Fri, 25 Jun 2004 09:25:35 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 09:25:26 -0500 Message-ID: From: "james_mersinger@medco.com via access-l" To: kermit@polaris.net Subject: [access-l] RE: Prompt to save work Date: Fri, 25 Jun 2004 09:22:31 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: text/plain Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 That was it, I had set warnings in a few places and never turned them back on. I though they would stay local to the sub. Thanks! -----Original Message----- From: Jiles via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:30 AM To: Mersinger; James Subject: [access-l] RE: Prompt to save work # IBM signs $14.3m deal with ATO # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28432 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Check the settings under tools>Options>Edit/Find>Confirm, they should all be checked. Also you may have set warnings to false someplace in your code and failed to change set warnings back to true at the end of the procedure or if the code fails and does not reach the setwarnings = true statement the warnings will remain false until you restart the application or some code resets setwarnings to true. -----Original Message----- From: Mersinger, James via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:14 AM To: Jiles Subject: [access-l] Prompt to save work # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Has anyone ever noticed or expeienced while desinging a database there are no promts to save your work when you close a form, query, report etc. I have some DBs that do prompt and others that don't. I'm not selecting any options like that on the different files so I'm trying to figure out the source. Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. ----------------------------------------- This e-mail message and any attachments contain confidential information from Medco. If you are not the intended recipient, you are hereby notified that disclosure, printing, copying, distribution, or the taking of any action in reliance on the contents of this electronic information is strictly prohibited. If you have received this e-mail message in error, please immediately notify the sender by reply message and then delete the electronic message and any attachments. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 11:30:36 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PFUZNW028699 for ; Fri, 25 Jun 2004 11:30:35 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A518851600F2; Fri, 25 Jun 2004 11:30:32 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 11:30:15 -0500 From: "ajwz via access-l" To: kermit@polaris.net Subject: [access-l] Re: opens to a function instead of Access UI Date: Fri, 25 Jun 2004 11:29:02 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Spam-Processed: applerubber.com, Fri, 25 Jun 2004 11:40:24 -0400 (not processed: message from valid local sender) X-MDRemoteIP: 206.214.8.194 X-Return-Path: alicew@applerubber.com X-MDaemon-Deliver-To: access-l@Groups.ITtoolbox.com X-MDAV-Processed: applerubber.com, Fri, 25 Jun 2004 11:40:27 -0400 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Hi Phil and Jiles, Thanks for your replies. Jiles, I didn't your see thread until later so I didn't get to try your suggestion. Phil, I scrolled through the reference library all of them are pointing to some path. I have a temporary fix on this problem but I know this problem will happen again. I created a new Access database and imported the objects and it is fine. The problem I am having is that when the database opens up it is not opening to a full screen. Do you know why? Thanks! -----Original Message----- From: Phil Marcus via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:00 AM To: ajwz Subject: [access-l] Re: opens to a function instead of Access UI # Optimizing the mysqld variables # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28433 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 It is breaking there because that line contains a function or other reference to an object that must be created from a recipe (so to speak) in a library (a .dll, .tlb or other file) that is present on the machine where the code was written but not on yours. Stop the code while in the editor by clicking on the icon which is a solid square. Now go to Tools>References and highlight each reference until you find one that shows as Missing in the lower pane. You must install that file. How depends on what it is. HTH Phil From: "ajwz via access-l" To: Phil Marcus Date sent: Fri, 25 Jun 2004 12:32:36 (GMT) Subject: [access-l] opens to a function instead of Access UI Send reply to: access-l@Groups.ITtoolbox.com > > Why is it when opening a Access2000 database it opens to a function in vb and highlighted instead of Access UI? I tried clearing the break point and saving it but it still goes to the code when it opens up. This is a copy from my machine and I don't have this problem. > > Can anyone explain this? > Thanks! > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 11:31:14 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PFVCex028723 for ; Fri, 25 Jun 2004 11:31:13 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A53DA10200EA; Fri, 25 Jun 2004 11:31:09 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <90D0E4186CF7EB34.BA66460398583220@gateway1.nettally.com> for ; Fri, 25 Jun 2004 11:31:01 -0500 From: "ajwz via access-l" To: kermit@polaris.net Date: Fri, 25 Jun 2004 15:29:27 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] database doesn't open up to a full screen Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 I created a new Access database and imported the objects and it is fine. The problem is that when the database opens up it is not opening to a full screen. Do you know why? Thanks! *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 11:41:56 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PFfsZT029003 for ; Fri, 25 Jun 2004 11:41:54 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7C03F6100CC; Fri, 25 Jun 2004 11:41:52 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <9B8BDF224206EB8E.A99296B50FB5AEAD@gateway3.nettally.com> for ; Fri, 25 Jun 2004 11:41:44 -0500 Message-ID: From: "BJC via access-l" To: kermit@polaris.net Subject: [access-l] Re: Loading form with many records Date: Wed, 23 Jun 2004 11:56:24 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Authentication-Info: Submitted using SMTP AUTH LOGIN at web02-imail.rogers.com from [24.43.153.238] using ID at Wed, 23 Jun 2004 11:55:53 -0400 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Here's a link to some code I found On Allen Brownes Site that I had to modify. But once I got it figured out, it works great. http://members.iinet.net.au/~allenbrowne/ser-32.html and this is the main site for tips.http://members.iinet.net.au/~allenbrowne/tips.html Good Luck... Hoping all is as well as can be. Brad ----- Original Message ----- From: "Mersinger, James via access-l" To: "BJC" Sent: Wednesday, June 23, 2004 10:36 AM Subject: [access-l] Loading form with many records > > Hello, > I have a form that runs off a table that has 11,000 records. Once the form loads the user uses comboboxes to filter or drill down to the data they would like to view. The problem is the performance is slow both opening the form, and the On Change events in the comboboxes. It runs StrSql after the combos are updated. > > Any thoughts on how to speed up the opening of the form and/or the speed? > > All of the appropriate fields are indexed already. > > Thanks > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 12:52:09 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PGq86a001035 for ; Fri, 25 Jun 2004 12:52:09 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A836446000CC; Fri, 25 Jun 2004 12:52:06 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 12:52:02 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Peter Barton via access-l" To: kermit@polaris.net Subject: [access-l] Returning control Types using VBA (ComboBox, ListBox, TextBox etc) Date: Fri, 25 Jun 2004 17:51:30 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Importance: Normal X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 What's the VBA syntax for returning the Type of a control on an Access form? Easy enough using error trapping or other contrived methods but there must be a more elegant way of doing it! TIA Peter Barton *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 16:30:44 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PKUhAG008031 for ; Fri, 25 Jun 2004 16:30:43 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AB7127B50020; Fri, 25 Jun 2004 16:30:41 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 16:30:24 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] Declaring and setting a form Date: Fri, 25 Jun 2004 20:03:46 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Hello. Can somebody help me with the following code e.g. Dim frm as Form Dim lstrformname as string 'Set the form object to the form whose name is contained in the string variable Set Form = 'lstrformname Thanks *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 16:30:44 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PKUh1p008032 for ; Fri, 25 Jun 2004 16:30:44 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AB71966B00D8; Fri, 25 Jun 2004 16:30:41 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <9ABDDF1018DD37F1.81B0831B35763B71@gateway2.nettally.com> for ; Fri, 25 Jun 2004 16:30:19 -0500 From: " james_tibbe... via access-l" To: kermit@polaris.net Date: Fri, 25 Jun 2004 20:03:31 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] Radio button or check box on form Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by nexus.polaris.net id i5PKUh1p008032 Status: O I have a form that has a drop down combo box that lists members. When I select a member, that members work cycles are displayed in the subform from another table. Is there a way that I can use a radio button or check box to indicate that I only want the data for specific work cycles? You should know that I have a command button that sends the member id to a Make Table query which currently retrieves all of the members work cycles into one table, but I would prefer to only capture certain cycles. I've tried using the wizard but had no luck. I set the default to 0 and when I select the button it selects all of the buttons for every cycle. Any help would be appreciated as I've spent way too much time on this already. I am a newbie. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 16:40:44 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PKeh55008231 for ; Fri, 25 Jun 2004 16:40:44 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id ADC96C2C00BA; Fri, 25 Jun 2004 16:40:41 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 16:40:36 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Russell Grimshaw via access-l" To: kermit@polaris.net Subject: [access-l] RE: Radio button or check box on form Date: Fri, 25 Jun 2004 16:37:47 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-BTCNet-MailScanner-Information: Please contact MailScanner@btcnet.com for more information X-BTCNet-MailScanner: Found to be clean X-BTCNet-MailScanner-SpamCheck: not spam (whitelisted), SpamAssassin (score=-2.101, required 3, BAYES_00 -4.90, RCVD_IN_DYNABLOCK 2.60, RCVD_IN_RFCI 0.10, RCVD_IN_SORBS 0.10) X-MailScanner-Env-From: rgrimshaw@eaglenestconsulting.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 I have the same situation for printing information about certain employees. I added a field that is call print and on the sub form I check it if I want that individual to print. For your application, add a field to the work cycles table and display it on the sub form and when printing use a query where Print = True. I hope this helps! Russell Grimshaw EagleNest Consulting, Inc. Office : 419.866.6513 Cell: 419.346.3486 -----Original Message----- From: james_tibbe... via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 8:04 PM To: Russell Grimshaw Subject: [access-l] Radio button or check box on form I have a form that has a drop down combo box that lists members. When I select a member, that members work cycles are displayed in the subform from another table. Is there a way that I can use a radio button or check box to indicate that I only want the data for specific work cycles? You should know that I have a command button that sends the member id to a Make Table query which currently retrieves all of the members work cycles into one table, but I would prefer to only capture certain cycles. I've tried using the wizard but had no luck. I set the default to 0 and when I select the button it selects all of the buttons for every cycle. Any help would be appreciated as I've spent way too much time on this already. I am a newbie. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 17:47:48 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PLlkG0010018 for ; Fri, 25 Jun 2004 17:47:47 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AD7F30D400A0; Fri, 25 Jun 2004 17:47:43 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 17:47:37 -0500 Message-ID: From: "Reinaldo da Silva Garcia via access-l" To: kermit@polaris.net Subject: [access-l] Re: Declaring and setting a form Date: Fri, 25 Jun 2004 18:43:23 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Hi You can do in this form Dim frmMyForm as frmTheFormTemplate For create new instance call Set frmMyForm = New frmTheFormTemplate Regards ----- Original Message ----- From: "Lee Aldrich via access-l" To: "Reinaldo da Silva Garcia" Sent: Friday, June 25, 2004 4:03 PM Subject: [access-l] Declaring and setting a form > # View from the Top - Part 1 > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > Hello. > > Can somebody help me with the following code > > e.g. > > Dim frm as Form > Dim lstrformname as string > > 'Set the form object to the form whose name is contained in the string > variable > Set Form = 'lstrformname > > Thanks > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 18:55:26 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5PMtPYf011795 for ; Fri, 25 Jun 2004 18:55:26 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AD5CA2E800F2; Fri, 25 Jun 2004 18:55:24 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 25 Jun 2004 18:55:18 -0500 From: "JHill7777... via access-l" Message-ID: Date: Fri, 25 Jun 2004 18:51:17 EDT Subject: [access-l] RE: Bound Combo Box Problem To: kermit@polaris.net MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII"; X-Mailer: 8.0 for Windows sub 6032 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Thanks Rod. I'll check the seconf field key idea to see how it works. But let me ask. I have found a number of ways to determine what the value in the bound control box was before the user changes it. Simply stated, like the oldvalue property, is there some property or anyway to look at what the current value in the textbox or control box is while the user is changing it but before the record is updated, and something other than the beforeupdate event. I've tried the exit event, the dirty event, the change event, and no matter what, I can only determine the before value not the current value on the screen. That is, if there is a number 12345 and the user deletes the 1, i can see 2345 on the screen. The record has not been updated yet, but I can't find any way to programatically display a message to myself that the current value is 2345. And if the user deleted the 2 and I caould see 345 in the combo box, is there any way I could determine what the current value is? This seems simple enough but I have not been able to figure it out. Thanks for any thoughts you might have. John *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jun 25 22:27:52 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5Q2Rpbc017425 for ; Fri, 25 Jun 2004 22:27:52 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AF258F8F00B0; Fri, 25 Jun 2004 22:27:49 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8365EFA527B30ECB.A65215D7126DA98E@gateway1.nettally.com> for ; Fri, 25 Jun 2004 22:27:43 -0500 From: "Phil Marcus via access-l" Organization: TTG Services/Amberway Associates To: kermit@polaris.net Date: Fri, 25 Jun 2004 22:25:53 -0400 MIME-Version: 1.0 Subject: [access-l] Re: Declaring and setting a form Message-ID: Priority: normal X-mailer: Pegasus Mail for Windows (v4.12a) Content-Type: text/plain; charset=US-ASCII; Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 If I understand your question, Dim frm as Form Dim lstrFormName as string [more code that assigns a value to lstrFormName ] 'Set the form object to the form whose name is contained in the string variable Set Form = currentdb().forms(lstrFormName ) HTH Phil From: "Lee Aldrich via access-l" To: Phil Marcus Subject: [access-l] Declaring and setting a form Date sent: Fri, 25 Jun 2004 20:03:46 +0100 Send reply to: access-l@Groups.ITtoolbox.com > # View from the Top - Part 1 > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > Hello. > > Can somebody help me with the following code > > e.g. > > Dim frm as Form > Dim lstrformname as string > > 'Set the form object to the form whose name is contained in the string > variable > Set Form = 'lstrformname > > Thanks > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 04:07:48 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5Q87lE8024862 for ; Sat, 26 Jun 2004 04:07:47 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AED1A15300EC; Sat, 26 Jun 2004 04:07:45 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <9E06283B37F237E7.9A02197896F4A216@gateway3.nettally.com> for ; Sat, 26 Jun 2004 04:07:34 -0500 From: "Uttam via access-l" To: kermit@polaris.net Subject: [access-l] RE: Refresh & Requery methods Date: Sat, 26 Jun 2004 12:38:34 +0530 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook CWS, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Importance: Normal X-OriginalArrivalTime: 26 Jun 2004 07:08:15.0311 (UTC) FILETIME=[59A90DF0:01C45B4C] Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 Refresh: The Refresh method shows only changes made to records in the current set. Refresh method doesn't actually requery the database, the current set won't include records that have been added or exclude records that have been deleted since the database was last requeried. Nor will it exclude records that no longer satisfy the criteria of the query or filter. Microsoft Access refreshes records automatically, based on the Refresh Interval setting on the Advanced tab of the Options dialog box, available by clicking Options on the Tools menu. ODBC data sources are refreshed based on the ODBC Refresh Interval setting on the Advanced tab of the Options dialog box Requery: Syntax: recordset.Requery newquerydef where newquerydef is optional and and existing query's name from the database. This method re-populates the current Recordset by using either the current query parameters or the new ones supplied by the newquerydef argument. Example: Suppose a form is bound to query "Select * from employees where level<3". Supposing while the form is opened by you, flg. changes take place simultaneously: 1.In one of the records, you change the level of an employee to 3. 2.Meanwhile, another user enters a record for a new employee with level 1. 3.Meanwhile another user updates the level of an employee form 1 to 2. If your use refresh method, only change at sl. no. 3 will be visible. Because refresh methods only refreshes records that are present in recordset. If you use requery method, all changes will become visible, i.e. record at sl. no.1 will be removed, recod at sl. no. 2 added and record and sl. no. 3 updated. Regards, Uttam -----Original Message----- From: Lee Aldrich via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Friday, June 25, 2004 23:25 To: Uttam Subject: [access-l] Refresh & Requery methods # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Can somebody please explain the difference between the 2 methods mentioned above and when each should be used. Many thanks, Lee *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 09:36:37 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QDaaIj001829 for ; Sat, 26 Jun 2004 09:36:37 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id ABE1648400A0; Sat, 26 Jun 2004 09:36:33 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Sat, 26 Jun 2004 09:36:30 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Peter Barton via access-l" To: kermit@polaris.net Subject: [access-l] RE: Bound Combo Box Problem Date: Sat, 26 Jun 2004 11:59:43 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 The .Text property of the combo holds the current value (unlike the default .Value which is not updated until the Update event). You can use .Text in conjunction with the Change event to monitor each keystroke. Peter Barton > -----Original Message----- > From: bounce-access-l-1833841@groups.ittoolbox.com > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of > JHill7777... via access-l > Sent: 25 June 2004 23:51 > To: Peter Barton > Subject: [access-l] RE: Bound Combo Box Problem > > > # Dell's Database System Up and Running at Yamagata University ... > # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 > > # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 > > Thanks Rod. > > I'll check the seconf field key idea to see how it works. But let me ask. I > have found a number of ways to determine what the value in the bound control > box was before the user changes it. Simply stated, like the oldvalue > property, is there some property or anyway to look at what the > current value in the > textbox or control box is while the user is changing it but before > the record is > updated, and something other than the beforeupdate event. I've tried the > exit event, the dirty event, the change event, and no matter what, I can only > determine the before value not the current value on the screen. That is, if > there is a number 12345 and the user deletes the 1, i can see 2345 on > the screen. > The record has not been updated yet, but I can't find any way to > programatically display a message to myself that the current value is > 2345. And if the user > deleted the 2 and I caould see 345 in the combo box, is there any way I could > determine what the current value is? This seems simple enough but I have not > been able to figure it out. > > Thanks for any thoughts you might have. > > John > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 09:36:48 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QDaln4001834 for ; Sat, 26 Jun 2004 09:36:47 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id ABEC335F0020; Sat, 26 Jun 2004 09:36:44 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <889458BAD52464B2.9B4F8DDD04D6F63A@gateway1.nettally.com> for ; Sat, 26 Jun 2004 09:36:40 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Peter Barton via access-l" To: kermit@polaris.net Subject: [access-l] RE: Was: Returning control Types using VBA Now: Access Documenter Date: Sat, 26 Jun 2004 11:59:42 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Thanks Phil but no joy - Access won't accept .type as a valid property of a control (or an object for that matter). Let me step back one. Can anyone recommend an Access Documenter? I need to trace all dependencies in a complex database - from tables thru queries to forms and reports. I have written some code that outputs each field's relevant properties to an Excel workbook and from there you can use Excel's Trace-Dependencies function. But it's balls-aching work and I am looking for a quicker solution. TIA Peter Barton > -----Original Message----- > From: bounce-access-l-1833841@groups.ittoolbox.com > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of Phil > Marcus via access-l > Sent: 25 June 2004 18:47 > To: Peter Barton > Subject: [access-l] Re: Returning control Types using VBA (ComboBox, > ListBox, TextBox etc) > > dim ctl as control, lngType as long > set ctl = me.mycontrol > lngtype= ctl.type > set ctl=nothing > > untested > > HTH > > Phil > > Send reply to: access-l@Groups.ITtoolbox.com > From: "Peter Barton via access-l" > > To: Phil Marcus > Subject: [access-l] Returning control Types using VBA > (ComboBox, ListBox, TextBox etc) > Date sent: Fri, 25 Jun 2004 17:51:30 +0100 > > > # View from the Top - Part 1 > > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 > > > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > > > What's the VBA syntax for returning the Type of a control on an Access form? > > Easy enough using error trapping or other contrived methods but > there must be a > > more elegant way of doing it! > > > > TIA > > > > Peter Barton *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 09:37:25 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QDbNk6001852 for ; Sat, 26 Jun 2004 09:37:24 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AC11649D00A0; Sat, 26 Jun 2004 09:37:21 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Sat, 26 Jun 2004 09:37:14 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] Report Recordsource Date: Sat, 26 Jun 2004 14:03:07 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Please can somebody help with the following: I have a report whose recordsource is based upon as query. This query lists various details relating to my organisation's members. This detail includes: name, tel, mobile, email address, grade, job title, & home address etc. The home address is actually made up of several fields including: add1 add2 town county postcode country I do not want to change the above structure for varuous reasons. I list these fields (in the same way as above) to display the address on my report. The problem that I experience is that on some records the [add2] field contains no data. Therefore when the report is run the address sometimes appears messy: eg. as opposed to: 30 St. Georges Rd Europa House Church Street Wimbledon Old Isleworth London ` Middx SW10 3BT TW7 6DG Any suggestions as to how I get around this problem. One idea was to have unbound text boxes and to then check each field (i.e. add1, then add2, etc.) to see if the field was null before binding it to the text box. e.g. If isnull(me.add1) = false then me.txt1 = me.add1 elseif isnull(me.ad2) = false then me.txt1 = me.add2) ..and so on end if However, when I try to reference me.[add2] in my code, an error message appears and says that this field cannot be found - even though it is part of the repoort's recordsource! Please help!!! *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 11:06:58 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QF6u99003681 for ; Sat, 26 Jun 2004 11:06:56 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A10E7A6900CC; Sat, 26 Jun 2004 11:06:54 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Sat, 26 Jun 2004 11:06:50 -0500 From: "Gabriella Rekasi via access-l" To: kermit@polaris.net Subject: [access-l] RE: Report Recordsource Date: Sat, 26 Jun 2004 08:04:28 -0700 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # View from the Top - Part 1 # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 You can use the fields' Can Shrink property set to Yes, but only if there is no label attached to the fields (label cannot shrink). Also, set the same property of the report's Detail section. Gabi *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 20:11:12 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5R0BBx0017207 for ; Sat, 26 Jun 2004 20:11:12 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A08EF19100E2; Sat, 26 Jun 2004 20:10:54 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Sat, 26 Jun 2004 20:10:46 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] RE: Report Recordsource Date: Sun, 27 Jun 2004 00:41:35 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Thanks Peter but... the field [add2] is part of the underlying recordsource and yet this field is not recognised when my code runs. Any ideas why? Thanks, Lee -----Original Message----- From: Peter Barton via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 26 June 2004 16:06 To: Lee Aldrich Subject: [access-l] RE: Report Recordsource # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Try something like: strOutput="" If Not Isnull(me.name) AND me.name <> "" Then strOutput = strOutput & me.name & VbCr If Not Isnull(me.add1) AND me.add1 <> "" Then strOutput = strOutput & me.add1 & VbCr If Not Isnull(me.add2) AND me.add1 <> "" Then strOutput = strOutput & me.add2 & VbCr ...etc me.txt1 = strOutput If you still have problems include 'On Error Resume Next' before each line followed by a final 'On Error Goto 0' to cancel the error trap. Peter Barton > -----Original Message----- > From: bounce-access-l-1833841@groups.ittoolbox.com > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of Lee > Aldrich via access-l > Sent: 26 June 2004 14:03 > To: Peter Barton > Subject: [access-l] Report Recordsource > > > > # Dell's Database System Up and Running at Yamagata University ... > # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 > > # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 > > Please can somebody help with the following: > > I have a report whose recordsource is based upon as query. This query lists > various details relating to my organisation's members. > > This detail includes: name, tel, mobile, email address, grade, job title, & > home address etc. > > The home address is actually made up of several fields including: > > add1 > add2 > town > county > postcode > country > > I do not want to change the above structure for varuous reasons. > > I list these fields (in the same way as above) to display the address on my > report. > > The problem that I experience is that on some records the [add2] field > contains no data. Therefore when the report is run the address sometimes > appears messy: > > eg. as opposed to: > 30 St. Georges Rd Europa House > Church Street > Wimbledon Old Isleworth > London ` Middx > SW10 3BT TW7 6DG > > Any suggestions as to how I get around this problem. > > One idea was to have unbound text boxes and to then check each field (i.e. > add1, then add2, etc.) to see if the field was null before binding it to the > text box. > > e.g. > If isnull(me.add1) = false then > > me.txt1 = me.add1 > > elseif isnull(me.ad2) = false then > > me.txt1 = me.add2) > > ..and so on > > end if > > However, when I try to reference me.[add2] in my code, an error message > appears and says that this field cannot be found - even though it is part of > the repoort's recordsource! > > Please help!!! > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 12:05:21 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QG5INZ005113 for ; Sat, 26 Jun 2004 12:05:19 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AEBB832C00BE; Sat, 26 Jun 2004 12:05:15 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Sat, 26 Jun 2004 12:05:10 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Peter Barton via access-l" To: kermit@polaris.net Subject: [access-l] RE: Report Recordsource Date: Sat, 26 Jun 2004 17:05:49 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Try something like: strOutput="" If Not Isnull(me.name) AND me.name <> "" Then strOutput = strOutput & me.name & VbCr If Not Isnull(me.add1) AND me.add1 <> "" Then strOutput = strOutput & me.add1 & VbCr If Not Isnull(me.add2) AND me.add1 <> "" Then strOutput = strOutput & me.add2 & VbCr ..etc me.txt1 = strOutput If you still have problems include 'On Error Resume Next' before each line followed by a final 'On Error Goto 0' to cancel the error trap. Peter Barton > -----Original Message----- > From: bounce-access-l-1833841@groups.ittoolbox.com > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of Lee > Aldrich via access-l > Sent: 26 June 2004 14:03 > To: Peter Barton > Subject: [access-l] Report Recordsource > > > > # Dell's Database System Up and Running at Yamagata University ... > # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 > > # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 > > Please can somebody help with the following: > > I have a report whose recordsource is based upon as query. This query lists > various details relating to my organisation's members. > > This detail includes: name, tel, mobile, email address, grade, job title, & > home address etc. > > The home address is actually made up of several fields including: > > add1 > add2 > town > county > postcode > country > > I do not want to change the above structure for varuous reasons. > > I list these fields (in the same way as above) to display the address on my > report. > > The problem that I experience is that on some records the [add2] field > contains no data. Therefore when the report is run the address sometimes > appears messy: > > eg. as opposed to: > 30 St. Georges Rd Europa House > Church Street > Wimbledon Old Isleworth > London ` Middx > SW10 3BT TW7 6DG > > Any suggestions as to how I get around this problem. > > One idea was to have unbound text boxes and to then check each field (i.e. > add1, then add2, etc.) to see if the field was null before binding it to the > text box. > > e.g. > If isnull(me.add1) = false then > > me.txt1 = me.add1 > > elseif isnull(me.ad2) = false then > > me.txt1 = me.add2) > > ..and so on > > end if > > However, when I try to reference me.[add2] in my code, an error message > appears and says that this field cannot be found - even though it is part of > the repoort's recordsource! > > Please help!!! > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 15:23:45 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5QJNiKI010533 for ; Sat, 26 Jun 2004 15:23:44 -0400 (EDT) Received: from gateway2.nettally.com [199.44.114.220] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AD3C764300A0; Sat, 26 Jun 2004 15:23:40 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway2.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8878B70BC4C7A82D.8CE46DDDDB6AB3BC@gateway2.nettally.com> for ; Sat, 26 Jun 2004 15:23:31 -0500 From: "Phil Marcus via access-l" Organization: TTG Services/Amberway Associates To: kermit@polaris.net Date: Sat, 26 Jun 2004 15:12:32 -0400 MIME-Version: 1.0 Subject: [access-l] RE: Was: Returning control Types using VBA Now: Access Documenter Message-ID: Priority: normal X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Sorry, it is .controltype. There is a built in documenter, BTW, which may or may not help you. Under tools menu, but has varied exactly where form version to version. HTH Phil Send reply to: access-l@Groups.ITtoolbox.com From: "Peter Barton via access-l" To: Phil Marcus Subject: [access-l] RE: Was: Returning control Types using VBA Now: Access Documenter Date sent: Sat, 26 Jun 2004 11:59:42 +0100 > # Dell's Database System Up and Running at Yamagata University ... > # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 > > # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 > > Thanks Phil but no joy - Access won't accept .type as a valid property of a > control (or an object for that matter). > > Let me step back one. Can anyone recommend an Access Documenter? > I need to trace all dependencies in a complex database - from tables thru > queries to forms and reports. > I have written some code that outputs each field's relevant properties to an > Excel workbook and from there you can use Excel's Trace-Dependencies function. > But it's balls-aching work and I am looking for a quicker solution. > > TIA > > Peter Barton > > > -----Original Message----- > > From: bounce-access-l-1833841@groups.ittoolbox.com > > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of Phil > > Marcus via access-l > > Sent: 25 June 2004 18:47 > > To: Peter Barton > > Subject: [access-l] Re: Returning control Types using VBA (ComboBox, > > ListBox, TextBox etc) > > > > dim ctl as control, lngType as long > > set ctl = me.mycontrol > > lngtype= ctl.type > > set ctl=nothing > > > > untested > > > > HTH > > > > Phil > > > > Send reply to: access-l@Groups.ITtoolbox.com > > From: "Peter Barton via access-l" > > > > To: Phil Marcus > > Subject: [access-l] Returning control Types using VBA > > (ComboBox, ListBox, TextBox etc) > > Date sent: Fri, 25 Jun 2004 17:51:30 +0100 > > > > > # View from the Top - Part 1 > > > # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28490 > > > > > > # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > > > > > What's the VBA syntax for returning the Type of a control on an Access form? > > > Easy enough using error trapping or other contrived methods but > > there must be a > > > more elegant way of doing it! > > > > > > TIA > > > > > > Peter Barton > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sat Jun 26 22:49:19 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i5R2nIT8021302 for ; Sat, 26 Jun 2004 22:49:18 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A5AC709100D0; Sat, 26 Jun 2004 22:49:16 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <8123C406BD3AB910.B7FD8B98FF96A0DD@gateway3.nettally.com> for ; Sat, 26 Jun 2004 22:49:15 -0500 Reply-To: access-l@groups.ittoolbox.com From: "Richard Killey via access-l" To: kermit@polaris.net Subject: [access-l] RE: Report Recordsource Date: Sat, 26 Jun 2004 21:44:47 -0400 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Lee: Am I correct in stating that you cannot refer to a field in the VBA of a report (me.fieldname) unless that field is actually ON the report layout? Now, it does not have to be visible, but it has to be there. Not enough to just have it in the recordsource. ------------------------ Richard "Manxman" Killey MS Access Developer http://www.accessdatabasetips.com -----Original Message----- From: Lee Aldrich via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: Saturday, June 26, 2004 7:42 PM To: Richard Killey Subject: [access-l] RE: Report Recordsource # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Thanks Peter but... the field [add2] is part of the underlying recordsource and yet this field is not recognised when my code runs. Any ideas why? Thanks, Lee -----Original Message----- From: Peter Barton via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 26 June 2004 16:06 To: Lee Aldrich Subject: [access-l] RE: Report Recordsource # Dell's Database System Up and Running at Yamagata University ... # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Try something like: strOutput="" If Not Isnull(me.name) AND me.name <> "" Then strOutput = strOutput & me.name & VbCr If Not Isnull(me.add1) AND me.add1 <> "" Then strOutput = strOutput & me.add1 & VbCr If Not Isnull(me.add2) AND me.add1 <> "" Then strOutput = strOutput & me.add2 & VbCr ...etc me.txt1 = strOutput If you still have problems include 'On Error Resume Next' before each line followed by a final 'On Error Goto 0' to cancel the error trap. Peter Barton > -----Original Message----- > From: bounce-access-l-1833841@groups.ittoolbox.com > [mailto:bounce-access-l-1833841@groups.ittoolbox.com]On Behalf Of Lee > Aldrich via access-l > Sent: 26 June 2004 14:03 > To: Peter Barton > Subject: [access-l] Report Recordsource > > > > # Dell's Database System Up and Running at Yamagata University ... > # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28489 > > # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 > > Please can somebody help with the following: > > I have a report whose recordsource is based upon as query. This query lists > various details relating to my organisation's members. > > This detail includes: name, tel, mobile, email address, grade, job title, & > home address etc. > > The home address is actually made up of several fields including: > > add1 > add2 > town > county > postcode > country > > I do not want to change the above structure for varuous reasons. > > I list these fields (in the same way as above) to display the address on my > report. > > The problem that I experience is that on some records the [add2] field > contains no data. Therefore when the report is run the address sometimes > appears messy: > > eg. as opposed to: > 30 St. Georges Rd Europa House > Church Street > Wimbledon Old Isleworth > London ` Middx > SW10 3BT TW7 6DG > > Any suggestions as to how I get around this problem. > > One idea was to have unbound text boxes and to then check each field (i.e. > add1, then add2, etc.) to see if the field was null before binding it to the > text box. > > e.g. > If isnull(me.add1) = false then > > me.txt1 = me.add1 > > elseif isnull(me.ad2) = false then > > me.txt1 = me.add2) > > ..and so on > > end if > > However, when I try to reference me.[add2] in my code, an error message > appears and says that this field cannot be found - even though it is part of > the repoort's recordsource! > > Please help!!! > > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp > *Manage Subscriptions: http://My.ITtoolbox.com > *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com > *Need Subscription Help? mailto:Listmaster@ITtoolbox.com > *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm > *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jul 2 22:20:15 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i632KEgd000673 for ; Fri, 2 Jul 2004 22:20:14 -0400 (EDT) Received: from gateway3.nettally.com [199.44.114.226] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A7DDC0AE0028; Fri, 02 Jul 2004 22:20:13 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway3.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id <9F5E85B0812E681B.9226CD81A0F35E13@gateway3.nettally.com> for ; Fri, 02 Jul 2004 22:20:05 -0500 From: "Harry Turkewitz via access-l" To: kermit@polaris.net Date: Sat, 03 Jul 2004 02:17:38 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] RE: Deleting or creating realtionships between tables via VBA Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Differences between package, function, and procedures # Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28831 # View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 ---------------Original Message--------------- >Can somebody please help with the following: > >How can I delete and create realtionships between tables (liek they are >displayed in the Relationship window in Access?) > >Thanks, > >Lee > >Which form of VBA do you want to use. There are two forms DAO Code which is older code or ADO code which is newer code. Once I know which form of VBA Code you would like to use I will beable to give you a routine that you can use. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Fri Jul 2 22:35:50 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i632ZnKe001016 for ; Fri, 2 Jul 2004 22:35:49 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id AB8038BD00CA; Fri, 02 Jul 2004 22:35:44 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.612.2) with ESMPT id for ; Fri, 02 Jul 2004 22:35:38 -0500 From: "Harry Turkewitz via access-l" To: kermit@polaris.net Date: Sat, 03 Jul 2004 02:33:18 (GMT) X-MSMail-Priority: Normal X-mailer: AspMail 4.01 (SMT45D2EFF) Subject: [access-l] RE: apiCopyFile Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Message-ID: Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Acquisitions Essential, Oracle CEO Asserts # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28830 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 ---------------Original Message--------------- >I have a MS Access application that is using the following function to >copy a file from one location to another, but I'm not understanding >exactly what it's doing. Can anyone please help explain? >=20 >Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _ > (ByVal lpExistingFileName As String, _ > ByVal lpNewFileName As String, _ > ByVal bFailIfExists As Long) As Long >=20 >My brief understanding is that this is used at a module level to declare >references to external procedures in a DLL (dynamic link-library). Is >there any way I can see what the member, CopyFileA, actually does? >Please help. > The API copy file copies an existing file to a new file. This is the as the VB filecopy command. The difference is that you can abort the copy if the destination file already exists. the lpExistingFilename is the name of the file to be copied. the lpNewFilename is the name of the destination file the bFailIfExists should be set to true if you don't want to overwrite the existing file or false if you do want to write the existing file. this function returns a zero value if an error occurred and a non-zero value if the function was successful. You can use the GetLastError Function to find out what the error was. Don't forget this function is a windows function and ms Kernel32 library is where this function is housed. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. From bounce-access-l-2656781@groups.ittoolbox.com Sun Jul 4 09:21:25 2004 Return-Path: Received: from mxfs.nettally.com (mxfs.nettally.com [199.44.114.227]) by nexus.polaris.net (8.12.11/8.12.11) with ESMTP id i64DLOi8013212 for ; Sun, 4 Jul 2004 09:21:24 -0400 (EDT) Received: from gateway1.nettally.com [199.44.114.221] by mxfs.nettally.com with ESMTP (SMTPD32-8.03) id A451FA1000B0; Sun, 04 Jul 2004 09:21:21 -0400 Received: from groups3.openitx.com (207.153.199.41) by gateway1.nettally.com (Alligate(TM) SMTP Gateway v1.4.629.2) with ESMPT id for ; Sun, 04 Jul 2004 09:21:16 -0500 From: "Lee Aldrich via access-l" To: kermit@polaris.net Subject: [access-l] RE: Deleting or creating realtionships between tables via VBA Date: Sun, 4 Jul 2004 12:09:18 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Reply-To: access-l@groups.ittoolbox.com X-Alligate: Not subscribed X-Declude-Status: Waiting for activation code Status: O # Acquisitions Essential, Oracle CEO Asserts # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28830 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 Harry, many thanks for that - a great help. Can you let me know how I delete an existing One-To-Many relationship between tables. I've tried: ' Declare object variables Dim cat As ADOX.catalog Dim fk As ADOX.Key Dim wktbl As ADOX.table Dim keys As ADOX.keys ' Get the Catalog Set cat = New ADOX.catalog cat.ActiveConnection = CurrentProject.Connection ' Get the Keys Collection Set wktbl = cat.Tables("tblnotes") Set keys = wktbl.keys ' Create the foreign key Set fk = New ADOX.Key fk.Name = "rec_id" fk.Type = adKeyForeign fk.RelatedTable = "tblemployee" 'Delete relationship wktbl.keys.Delete fk.Name ... this does not work. Get an error: "Item cannot be found in the collection corresponding to the requested name or ordinal. In this particular code I don't want to create a relationship; I simply want to delete the current relationship. My 2 tables are: tblemployee & tblnotes. The primary &* foreign key are both called rec_id. Also (just out of interest, why do you use 'wktbl' as the variable name for the 'Many side' table - i.e. if tbl is short for table then what is 'wk' short for?) Can you help? Again, many thanks. Lee -----Original Message----- From: Harry Turkewitz via access-l [mailto:access-l@Groups.ITtoolbox.com] Sent: 03 July 2004 21:07 To: Lee Aldrich Subject: [access-l] RE: Deleting or creating realtionships between tables via VBA # Acquisitions Essential, Oracle CEO Asserts # Read News: http://db.ITtoolbox.com/r/hdr.asp?r=28830 # View Group Archive: http://ITtoolbox.com/hrd.asp?i=808 To enforce cascading updates or cascading deletes set the properties of the foreign key object. Set the Keys UpdateRule property to adRICascade to enforce updates. Set the keys DeleteRule property to adRICascade to enforce deletes as well. Function CreateRelationship() Dim cat as ADOX.Catalog Dim fk as ADOX.key Dim wktbl as ADOX.Table Dim keys as ADOX.keys Get the catalog Set cat = New ADOX.catalog Cat.ActiveConnection = CurrentProject.Connection Get keys from the many side Set wktbl = cat.Tables(ManySideTableName) Set keys = wktbl.keys Create the foreign key Set fk = New ADO.key fk.Name = ForeignKeyNameYouWantToAssign fk.Type = adKeyForeign fk.RelatedTable = OneSideTableName Set cascading deletes fk.DeleteRule = adRICascade Add column to key fk.Columns.Append ForeignKeyFieldName Set Related Column fk.Columns(FoeignKeyColumnName) = PrimaryKeyFieldName Save Key wktbl.keys.Append fk CreateRelationship = True Cleanup objects Set fk = Nothing Set keys = nothing Set wktbl = nothing Set cat = nothing End Function Please note I did not add any error handling to this function. Make sure that you check to see if the relationship already exists in your error handler. If the relationship exists then delete it in the error handler and try again. wktbl.keys.delete fk.Name resume The 2 statements above will delete the relationship if it already exists. Make sure you put this statements in your error handler. Make sure that you set the library references for ADO (Which ever version you have) and ADOX (ADO Extensions for DDL and Security) ---------------Original Message--------------- >ADO please > >thank you > >-----Original Message----- >From: Harry Turkewitz via access-l >[mailto:access-l@Groups.ITtoolbox.com] >Sent: 03 July 2004 01:18 >To: Lee Aldrich >Subject: [access-l] RE: Deleting or creating realtionships between >tables via VBA > > ># Differences between package, function, and procedures ># Read Paper: http://db.ITtoolbox.com/r/hdr.asp?r=28831 > ># View Group Archive: http://db.ITtoolbox.com/hrd.asp?i=808 > > >---------------Original Message--------------- >>Can somebody please help with the following: >> >>How can I delete and create realtionships between tables (liek they are >>displayed in the Relationship window in Access?) >> >>Thanks, >> >>Lee >> >>Which form of VBA do you want to use. There are two forms DAO Code which >is older code or ADO code which is newer code. Once I know which form of >VBA Code you would like to use I will beable to give you a routine that you >can use. > > >*Archives: http://Groups.ITtoolbox.com/g/access-l.asp >*Manage Subscriptions: http://My.ITtoolbox.com >*Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com >*Need Subscription Help? mailto:Listmaster@ITtoolbox.com >*Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm >*Copyright (c) ITtoolbox and message author. No redistribution. > > *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution. *Archives: http://Groups.ITtoolbox.com/g/access-l.asp *Manage Subscriptions: http://My.ITtoolbox.com *Leave Group: mailto:leave-access-l@Groups.ITtoolbox.com *Need Subscription Help? mailto:Listmaster@ITtoolbox.com *Terms of Use: http://www.ittoolbox.com/help/termsofuse.htm *Copyright (c) ITtoolbox and message author. No redistribution.