Programatically Change Recordsource SQL of a form

I am wondering if it is possible to have the record source of a form be the
SELECT query of a table that would be "defined" by the entry the user makes
on a field in the form.

Example - 

3 tables all have the same fields, they are named: 
55091
55104
55116

The user opens the form and enters the ZipCode they are working with in a
field - 55901.  I'd like to program the form so that the record source
becomes SELECT * From 55901.

It seems like that would be something simple ... I would also welcome any
feed back as to whether this would be a wise set up.

A little background on why I'm trying this:  I work for a non-profit with an
old server; we are using Access to schedule donation pickups.  It's a split
DB with the BE on the network and the FE on each PC.  We currently have 300,
000+ addresses in one table and the form to schedule the pick up is very slow.
It works great when I take the BE off the network, so I am wondering if we'd
see better performance if I have a table for each Zip, the max records in a
zip is 15,000 or so.  

Thank you.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200911/1

0
HLCruz
11/19/2009 2:53:44 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1560 Views

Similar Articles

[PageSpeed] 20

HLCruz via AccessMonster.com wrote:

>I am wondering if it is possible to have the record source of a form be the
>SELECT query of a table that would be "defined" by the entry the user makes
>on a field in the form.
>
>Example - 
>
>3 tables all have the same fields, they are named: 
>55091
>55104
>55116
>
>The user opens the form and enters the ZipCode they are working with in a
>field - 55901.  I'd like to program the form so that the record source
>becomes SELECT * From 55901.
>
>It seems like that would be something simple ... I would also welcome any
>feed back as to whether this would be a wise set up.
>
>A little background on why I'm trying this:  I work for a non-profit with an
>old server; we are using Access to schedule donation pickups.  It's a split
>DB with the BE on the network and the FE on each PC.  We currently have 300,
>000+ addresses in one table and the form to schedule the pick up is very slow.
>It works great when I take the BE off the network, so I am wondering if we'd
>see better performance if I have a table for each Zip, the max records in a
>zip is 15,000 or so.  


That is not a good way to do that.  You should have one
table with a zip code field.  If the forms record source
query takes too long to find the records for the specified
zip code, there are a couple of thing that should help.

First, do not open the form with all 300K records.  Instead,
open the form with no records by using code like:

	DoCmd.OpenForm "the form", , , "ZipCode = '00000' "

Use the AfterUpdate event of the control where users enter
the desired zip code to set the form's Filter:

	Me.Filter = "ZipCode = '" & Me.txtZip & "' "
	Me.FilterOn = True

The second thing that should make a big difference is to
create an Index for the table's zip code field.

-- 
Marsh
MVP [MS Access]
0
Marshall
11/19/2009 4:37:37 PM
Reply:

Similar Artilces:

preventing changes to document
How can I protect a Publisher document so no changes can be made Nothing you can do will stop someone from making changes if they want. To stop most people, convert it to a .pdf file. But remember - there are several .pdf editors out there and some of them are inexpensive enough for casual users. You don't need to spend a couple of hundred dollars these days. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a question http://support.microsoft.com/KB/555375 "sleek881" <sleek881@discussions.microsoft.com> wrote in message news:C8710942-CDC6-4F54-8F67-C...

SMTP Server changed
Hello Friends, Our user are using Outlook 2003 to send mail out through echange server 2003. Now our email provided change SMTP from SMTP Server: mail.exampledomain.com To: SMTP Server: smtp.exampledomain.com Right now i am using Microsoft exchange server account in outlook express to send and receive mail . I am not using POP/SMTP account in Outlook Express. Can any one tell me what settings i have to change in exchange to send mail using new smtp server. Regards G. On Fri, 16 Feb 2007 13:51:05 -0800, Gureey <Gureey@discussions.microsoft.com> wrote: >Hello Friends, >...

"Windows Firewall>Allowed Programs>Change Settings"
The "Change settings" button is grayed out. I am logged in as administrator. What can I do to enable it so I can make changes? Sounds like this is GPO controlled. Are this client/server part of an domain ? To confirm, you can check with gpresult what settings will be applied on this machine. Regards Ramazan "Tino" <junk_email@exemail.com.au> wrote in message news:eTTj2uXoKHA.5520@TK2MSFTNGP05.phx.gbl... > The "Change settings" button is grayed out. I am logged in as > administrator. What can I do to enable it so I can make chang...

msExchMasterAccountSid
Does anyone have a solution to bulk changes of the msExchMasterAccountSID attribute? KB 278966 and KB 319047 contain information about disabled accounts being unable to receive emails. There are a number of reasons that you would want a disabled account to get mail. Both article used to say if you have a few accounts you need to fix add SELF with Associated External Account permission. If you many accounts you need to change use ldifde to export a list of the accounts, modify the result file, and then use ldifde to import the changes. However both articles now say "For additiona...

Form Code Required
Hi I run Excel 2K Does anyone have some VB code that forces a form to open to the maximum screen size available. Also, where would I put the code if it is available. Thanks This doesn't force it to open to cover everything on the screen, but does open it to cover the displayed spreadsheet: Private Sub UserForm_Initialize() Me.Width = ActiveWindow.Width Me.Height = ActiveWindow.Height End Sub The code goes into the form's Initialize event process. Here's a page that shows how to get the current screen resolution, and you could use it in conjunction with the above cod...

sending one record from a form to a word merge doc
I would like to send the current record to a merge doc in Word with out going through a lot of mail merge stuff. ...

user form drop down box
I am creating a user-form for data entry into a particular worksheet. For one of the fields I'd like to have a drop down box (which I can figure out how to get there) with two choices to pick from (which I can't figure out how to get the choices to show up). Can someone please tell me how to get the choices to show up in the drop down box of a user form? Thank you, Glenn Hi Glenn Private Sub UserForm_Initialize() With Me.ComboBox1 .Style = fmStyleDropDownList .AddItem "Beer" .AddItem "Cerveza" .ListIndex = 1 End With End Sub HTH. Best wishes ...

A97 to SQL?
Hello All I have a split, FE/BE A97 mdb, that I have been 'developing' for about 8 years now: and it works great!! As it has grown, more people are using it: typically 4 or 5 people are now using it at a time (from seperate workstations, each with seperate frontends). Next year this is likely to grow to 6, 7 or 8 concurrent users. Four of the BE tables now have ~500,000 records. I am starting to wonder whether the time has come to move to something bigger, like SQL server. Apart from the cost/time involved in the converstion, I am also concerned about losing the ability to 'd...

SOP Blank Invoice Form: keeps printing old invoice
I've tried searching for a previous answer to this question, but I'm a Great Plains novice, so I'm not even sure what to be searching for! We're on Great Plains 8.00g73 with SQL Server 2000 SP4. Typically, we view an invoice, print it to the screen, and use the Send To button to open the invoice in an email. Previously, this would have the selected invoices information in a PDF with the filename "SOP Blank Invoice Form.pdf". Now, we get the same named PDF, but it always shows up with the same information (from an older invoice). I found this file in: c:\documents...

SQL can't calculate!
Please someone tell me this is possible! I am trying to get the correct calculation from a very simple division: 630/60 which is 10.5. SQL 2008 only displays 10.0. I tried every which way I can think of. Here is all my code: select 630/60 -- should be 10.5 select CAST( 630/60 AS decimal(10,2)) -- should be 10.5 select CONVERT(decimal(10,2), 630/60) -- should be 10.5 select CONVERT(float, 630/60) -- should be 10.5 select CONVERT(numeric(10,2), 630/60) -- should be 10.5 select CONVERT(money, 630/60) -- should be 10.5 I even tried using ROUND but still the same. On my computer ...

Form/SubForm error while reference the object
I am a novice at Access and do not understand the VB codes. I have created a form (Software Register) with a subform (Licence No). I have created a one to many relationship between Software_ID_No in both forms. The Link Child and Link Master fields are populated with Software_ID_No. My subform (Licence No) now is linked to the main form (software Register) and moves with that form. What I want to do is have the Software_ID_No in the main form populate the Software_ID_No in the subform, and also put it into the Licence No Table. If I enter the number manually, it works. When I c...

SQL Select Problem
Hi all I have a little problem with a select. I would need to have the most simple solution :-) I have the following table: [FK_Candidate], [Key], [Value] 1 'Profession' 'Informatiker' 2 'Profession' 'Informatiker' 1 'Source' 'Quelle1' 2 'Source' 'Quelle2' I need to get all candidates with Profession = Informatiker AND Source =...

Adding SQL 2008 Expr SP1 as a pre-requisite
Please could someone advise on the best way of adding the necessary bootstrap files for SQL Server 2008 Express SP1 (I am running VS 2008 SP1). I have added SQL Server 2008 Express (pre SP1); I think this was following the instructions at the following: http://blogs.msdn.com/sqlexpress/archive/2008/09/12/faq-installing-the-sql-server-2008-express-clickonce-bootstrapper-for-visual-studio-2008-sp1.aspx However, pre SP1 does not support Windows 7 so I need the SP1 files. I can download the installation files: http://www.microsoft.com/downloads/details.aspx?FamilyID=01af61e6-...

Help with SQL and Excel.....
Hello friends, I am trying to send data from a store procedure to excel now and I could send the data to be formatted the same (color and bold) but then I run sp operating system displays an error in Excel.exe and do not understand why ... : o (someone has an idea that could be happening? Besides, I need to send the data and to create groups in excel, anyone have any idea how to do that? I was reviewing OLE but even I did not figure the solution to my problem .... heeeeeeeeeeeeellllp me :"( The code I use is: --EXEC ABC123_2 'C:\SUCCESS.xls' --///////////...

How can I pass values from a recordset to an SQL clause?
Hello, I am trying to make this code work using ADO in Access: recordset2.Open "SELECT * FROM Table1 WHERE (((Table1.IDNum) = (recordset1.IDNum)))", CurrentProject.Connection, adOpenKeyset, adLockOptimistic * Table1 contains all records of the database * recordset1 (which already exists) contains IDNums of records that I want to retrieve from Table1. This recordset was fabricated from scratch and contains only one field (IDNums) * recordset2 is the one I want to open: it should contain only those records from Table1 that their IDNum is included in recordset1 I would appreciat...

Change Default Email Layout
In a new email in Outlook if you click View you get a drop down menu with Normal, Web Layout & Print Layout. The default is Web Layout. I know that you can just select a different layout for the current message but how do you change the default to one of the other layouts for all new messages? I can't seem to find the setting. Thanks for any ideas Patrick This is with Word as the e-mail editor, correct? What version of Word/Outlook are you using? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup t...

Access Form Printing
Hi, I have a form in Access 2003 which contains a few title lables. On the screen they look fine, but when I go to print everything looks great except the titles are garbled up and sometimes reduced to a really small size so you can't even see it. I am very confused about why this is happening. Any suggestions will be greatly appreciated. Thanks, Mike Mike If you are trying to print a "form", consider another approach. Forms work great to display information on-screen, and provide users and interactive view of data, but reports in Access are specifically intended to be p...

Average a changing range of values
I need to average utilisation values for server names, but the range of values changes. Please can someone help with a macro for this. Data example: Time Duration Utilisation % Server Name CW001P01-SH-C: CW001P01-SH-C: 2009/09/19 23:56 903 96.6814438 2009/09/20 00:11 899 96.68444105 2009/09/20 00:26 901 96.68782082 2009/09/20 00:56 927 96.69387136 2009/09/20 01:12 872 96.69638761 2009/09/20 01:26 902 96.69934001 2009/09/20 01:41 898 96.70246381 CW001P03-SH-C: CW001P03-SH-C: 2009/09/19 23:56 903 99.20851502 2009/09/20 00:11 900 99.21775174 2009/09...

Change HD Dynamic to Basic
XP SP3 I want to change my Seagate 320GB hard disk from Dynamic to Static. Followed the instructions: http://support.microsoft.com/kb/309044 Deleted the volume Right click the harddisk but cannot find the option of Convert to Basic Disk. No such option in the right click menu. Any idea? Man T wrote: > XP SP3 > I want to change my Seagate 320GB hard disk from Dynamic to Static. > Followed the instructions: > http://support.microsoft.com/kb/309044 > > Deleted the volume > Right click the harddisk but cannot find the option of Convert to Basic ...

Changing the Sales Stage in CRM 4.0
Hi, In CRM 3.0 it is possible to change sales stage (skip one or more) without finishing task(s) from the previous stages (by "change stage" option). Is it also possible in CRM 4.0 ? Thanks, Pawel Whether or not your finish or complete the task to move the next sales stage is controlled via workflow. I don't see the action in 4.0 to change the sales stage process. You could overcome this with a custom workflow that will set the sales stage to a specific stage or advance it by 1. Then you will be able to run the workflow on the opportunity to advance it. -- Christopher Co...

Tab Order in a Contact Form
I created a custom form, adding both buit-in fields and custom fields to the General page. However, when I go to Layout/Tab Order, only the built-in fields that exist on the built-in Contact form show up in the list. Is there any way for me to get the other built-in and custom fields to show up in the Tab Order list? Thanks, Shannon All controls (note: not fields) should appear in the tab order, but if you put some inside a frame, that frame will have its own tab order. Select the frame, then invoked the Tab Order dialog. FYI, there is a newsgroup specifically for Outlook forms issues...

Call subroutine or set property on another form
Hi, I have a form with a tab control on it, with further forms on two tabs (Which become subforms of the main form). One of these further forms has a subform, as follows: frmMain - Main form frmTAB1 - Form on first tab page frmTAB2 - Form on second tab page frmTAB2sub - Subform on frmTAB2 form What I need to do is call a subroutine in frmTAB2sub from frmTAB1. It can be a property let statement even, so long as I can supply 1 variable in the call. I have spent hours trying to get this to work, and continually run into syntax / method not available etc... errors. On other forms I can set...

How do I change the mail format in OWA
I have a Goverment client the stopped all HTML formated mail. I have 50 or so users that only use OWA to send and recieve email. When ever the OWA user sends or replys to email from the Gov site it gets rejected. Is there any way to change the mail format in OWA to RTF or Plain Text? This is going to be a real big issue as the rest of the Gov site do this. Thank you John I didn't test it out myself, but in ESM go to Global Settings\internet message format, set up a policy for the government domain to use plain text only would probably do the trick? -- ---------------------------...

File may have been changed by another user dialog
I have a user who I have just upgraded to Windows SP2. Now, when the user has already saved the file and attempts to close it, they get a dialog stating "The file may have been changed by another user. Do you wish to Save a copy or overwrite changes?". This did not occur before the SP2 update. Has anyone else experienced this problem? Is there a solution to turn this off? Thanks in advance! Hi any chance you have shared this file ('Tools - Share workbook')? -- Regards Frank Kabel Frankfurt, Germany "Robert Smith" <anonymous@discussions.microsoft.co...

Stop Excel changing "i" to "I"
Hello, thought I seen it all but... I'm trying to generate a list enumerated with letters instead of numbers "a,b,c,d..." instead of "1,2,3,4..." I'm fine until I get to "i" in which case Excel capitalizes it for me. I can't undo the capitalization, nor can I find any option to prevent Excel from auto- correcting the text. (Which is what I'm assuming is going on, Excel believes I'm using the pronoun, not the letter by itself) Any way to stop Excel doing this? I'm using Excel 97 Thanks -al Al Go to Tools/AutoCorrect an...