Form using a query to look up values

SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number], 
Reciepts.[Street Name], Reciepts.Amount
FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
Name]);


I have a couple of combo boxes in my form that uses information from this 
query to populate a table.  The query allows me to type in the last name in a 
box on my form, and then fill in the street number and street name using the 
drop down box.  My issue is that once I fill in the form once,  and go to the 
next line, the query still pulls the information from the first time.  How do 
you get it to recheck for each record without leaving the form and coming 
back?
0
Utf
3/29/2010 9:48:01 PM
access.forms 6864 articles. 2 followers. Follow

7 Replies
807 Views

Similar Articles

[PageSpeed] 41

I do not see where the query refers to a field on the form, nor mention of 
how "the drop down box" works off the query (that is, what its Row Source 
is).  We don't have enough information to be of much (or any) help.

 Larry Linson
 Microsoft Office Access MVP

"RA" <RA@discussions.microsoft.com> wrote in message 
news:99025EEC-FF50-43DB-BF3B-708B25FD3711@microsoft.com...
> SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number],
> Reciepts.[Street Name], Reciepts.Amount
> FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] =
> Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street
> Name]);
>
>
> I have a couple of combo boxes in my form that uses information from this
> query to populate a table.  The query allows me to type in the last name 
> in a
> box on my form, and then fill in the street number and street name using 
> the
> drop down box.  My issue is that once I fill in the form once,  and go to 
> the
> next line, the query still pulls the information from the first time.  How 
> do
> you get it to recheck for each record without leaving the form and coming
> back? 



0
Larry
3/30/2010 3:04:16 AM
sorry, wrong query:

SELECT Residents.[Street Number]
FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
Name])
WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));

&

SELECT Residents.[Street Name]
FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
Name])
WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));

Row Source: one refers to one of these queries, the other to the second one. 
 I do have a requery maco identified in the "on Enter" property line.


"Larry Linson" wrote:

> I do not see where the query refers to a field on the form, nor mention of 
> how "the drop down box" works off the query (that is, what its Row Source 
> is).  We don't have enough information to be of much (or any) help.
> 
>  Larry Linson
>  Microsoft Office Access MVP
> 
> "RA" <RA@discussions.microsoft.com> wrote in message 
> news:99025EEC-FF50-43DB-BF3B-708B25FD3711@microsoft.com...
> > SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number],
> > Reciepts.[Street Name], Reciepts.Amount
> > FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] =
> > Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street
> > Name]);
> >
> >
> > I have a couple of combo boxes in my form that uses information from this
> > query to populate a table.  The query allows me to type in the last name 
> > in a
> > box on my form, and then fill in the street number and street name using 
> > the
> > drop down box.  My issue is that once I fill in the form once,  and go to 
> > the
> > next line, the query still pulls the information from the first time.  How 
> > do
> > you get it to recheck for each record without leaving the form and coming
> > back? 
> 
> 
> 
> .
> 
0
Utf
3/30/2010 12:14:01 PM
I've also tried this in the After Update- no luck:

Private Sub Street_Name_AfterUpdate()
    Forms![Reciepts]![Street Name].Requery
End Sub

Private Sub Street_Number_AfterUpdate()
    Forms![Reciepts]![Street Number].Requery
End Sub

"RA" wrote:

> sorry, wrong query:
> 
> SELECT Residents.[Street Number]
> FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
> Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
> Name])
> WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
> 
> &
> 
> SELECT Residents.[Street Name]
> FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
> Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
> Name])
> WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
> 
> Row Source: one refers to one of these queries, the other to the second one. 
>  I do have a requery maco identified in the "on Enter" property line.
> 
> 
> "Larry Linson" wrote:
> 
> > I do not see where the query refers to a field on the form, nor mention of 
> > how "the drop down box" works off the query (that is, what its Row Source 
> > is).  We don't have enough information to be of much (or any) help.
> > 
> >  Larry Linson
> >  Microsoft Office Access MVP
> > 
> > "RA" <RA@discussions.microsoft.com> wrote in message 
> > news:99025EEC-FF50-43DB-BF3B-708B25FD3711@microsoft.com...
> > > SELECT Residents.[Last Name], Reciepts.Date, Reciepts.[Street Number],
> > > Reciepts.[Street Name], Reciepts.Amount
> > > FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] =
> > > Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street
> > > Name]);
> > >
> > >
> > > I have a couple of combo boxes in my form that uses information from this
> > > query to populate a table.  The query allows me to type in the last name 
> > > in a
> > > box on my form, and then fill in the street number and street name using 
> > > the
> > > drop down box.  My issue is that once I fill in the form once,  and go to 
> > > the
> > > next line, the query still pulls the information from the first time.  How 
> > > do
> > > you get it to recheck for each record without leaving the form and coming
> > > back? 
> > 
> > 
> > 
> > .
> > 
0
Utf
3/30/2010 1:40:01 PM
high quality Soccer jerseys NBA Jersey tracksuit and jackets, GHD 
hairstraightener supplier from www.willpa.com

Are you a Retail businessman who bother by the purchase price?  China 
Cheapest TOP wholesale website can help you

we are specialize in replica sport goods manufacturing in china, we can 
offer you all kinds of soccer jersey, NBA jersey,shoes and so on. they are 
the best brand replica goods whih are look the same as the original goods. 
excellent quality and steady supply for them. we have been marketed in Europe 
and American for 3 year. all the goods we offer are AAA quality.  our soccer 
jersey are Thailand style. If any goods you buy from my company have problem, 
we will refund or resend them again. Most of ourProducts have no minimum 
order requirements,soyou can shop retail goods at wholesale prices. if you 
can buy more than 300usd. We offer free shipping. The more you buy the more 
discount for you.

National soccer jerseys: http://www.willpa.com
Club soccer jerseys: http://www.willpa.com
NBA Jerseys: http://www.willpa.com
T-shirt and shirt: http://www.willpa.com
Tracksuit: http://www.willpa.com
Hoody & Jackets: http://www.willpa.com
UGG boots: http://www.willpa.com
Hair style: http://www.willpa.com
shopping Index: http://www.willpa.com

EMS shipping.  7days arrive, paypal accept

want more information pls contact us or check our website: www.willpa.com
0
Utf
3/31/2010 8:29:03 PM
It would help to know something about the database's structure, and the real-
world situation.  If each resident may have several receipts there should be
a Residents table and a related Receipts table.  Unless you need to store
historic address information (which may happen with a shipping address, where
you want to see where a specific order was sent regardless of the current
address), the address information should exist only in the Residents table,
and should not be copied to the receipts table.

In terms of interface there would be a main form based on the Residents table,
with a subform based on the receipts table.

This is guesswork, as there is not a lot to go on.

RA wrote:
>sorry, wrong query:
>
>SELECT Residents.[Street Number]
>FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
>Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
>Name])
>WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
>
>&
>
>SELECT Residents.[Street Name]
>FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
>Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
>Name])
>WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
>
>Row Source: one refers to one of these queries, the other to the second one. 
> I do have a requery maco identified in the "on Enter" property line.
>
>> I do not see where the query refers to a field on the form, nor mention of 
>> how "the drop down box" works off the query (that is, what its Row Source 
>[quoted text clipped - 22 lines]
>> 
>> .

-- 
Message posted via http://www.accessmonster.com

0
BruceM
4/1/2010 2:00:29 PM
This is a very basic database.  

It contains 3 tables: 
1- Residence's names, address, phone number's, emails and the such
2- Reciept - or money collected for our neighborhood project (this is the 
one I'm trying to update, it only contains street number, street name Payment 
type and amount)
3- Cash outflows

What I'm hopeing to do is to be able to just type in a name and have the 
form provide the street number and street name in the combo boxes. 



"BruceM via AccessMonster.com" wrote:

> It would help to know something about the database's structure, and the real-
> world situation.  If each resident may have several receipts there should be
> a Residents table and a related Receipts table.  Unless you need to store
> historic address information (which may happen with a shipping address, where
> you want to see where a specific order was sent regardless of the current
> address), the address information should exist only in the Residents table,
> and should not be copied to the receipts table.
> 
> In terms of interface there would be a main form based on the Residents table,
> with a subform based on the receipts table.
> 
> This is guesswork, as there is not a lot to go on.
> 
> RA wrote:
> >sorry, wrong query:
> >
> >SELECT Residents.[Street Number]
> >FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
> >Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
> >Name])
> >WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
> >
> >&
> >
> >SELECT Residents.[Street Name]
> >FROM Reciepts INNER JOIN Residents ON (Reciepts.[Street Number] = 
> >Residents.[Street Number]) AND (Reciepts.[Street Name] = Residents.[Street 
> >Name])
> >WHERE (((Residents.[Last Name])=[Forms]![Reciepts]![Last Name]));
> >
> >Row Source: one refers to one of these queries, the other to the second one. 
> > I do have a requery maco identified in the "on Enter" property line.
> >
> >> I do not see where the query refers to a field on the form, nor mention of 
> >> how "the drop down box" works off the query (that is, what its Row Source 
> >[quoted text clipped - 22 lines]
> >> 
> >> .
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
4/2/2010 1:13:01 PM
On Fri, 2 Apr 2010 06:13:01 -0700, RA <RA@discussions.microsoft.com> wrote:

>This is a very basic database.  
>
>It contains 3 tables: 
>1- Residence's names, address, phone number's, emails and the such
>2- Reciept - or money collected for our neighborhood project (this is the 
>one I'm trying to update, it only contains street number, street name Payment 
>type and amount)
>3- Cash outflows
>
>What I'm hopeing to do is to be able to just type in a name and have the 
>form provide the street number and street name in the combo boxes. 

You're making a very common mistake: trying to store the same data (street
number, street name) in two different tables. That's not how relational
databases work! The address should exist *ONLY* in the table of residences; it
should not be copied into the Receipts table. Instead you should have only a
"foreign key" - the Residences table should have a primary key (it can be an
autonumber, or a Number that you maintain yourself, just so it's unique and
stable); the Receipts table should contain a field of the same type (Long
Integer if you use an autonumber) as a link to the residences table. There
should be *nothing* else from the first table in the second!

If you're using table datasheets with combo boxes... don't. Table datasheets
aren't designed for data interaction, and are very limited. Instead you can
use a Form based on Residences, with two subforms - one based on Receipts and
(if appropriate) the other based on Outflows, using the residence ID as the
master/child link field. You can have a combo box on the mainform to
*navigate* to a particular residence; the subform will display receipts for
that residence, and you can see the address on the mainform in conjunction
with the receipts on the subform.
-- 

             John W. Vinson [MVP]
0
John
4/2/2010 3:53:24 PM
Reply:

Similar Artilces:

Hiding records on reports by checkbox on form
Hi, I'm in the proccess of buliding a new database. Its purpose is to store various organisation contact details and then to produce reports based on this that can be printed onto sticky labels. This much or basically finished. However, we naturally don't want to print the whole database every time, so I'm using a checkbox on the interface form that will filter the report so that only checked records are prepared for printing. Can anyone tell what code I need to use to do this, and where? I've tried various methods, but nothing seems to work. I'm using accesss 2003 by the w...

Using variable names for cells
I seem to remember a technique where I could assign a variable name to the contents of a cell so that whenever I wanted to use the contents, all I had to do was call up the variable name. Unfortunately I cannot find the way to set up the process. Any suggestsions or ideas would be appreciated. Thanks and a Happy New Year. -- Take out the trash to reply '05 FLHTCUI Hi dim rng as range set rng=activesheet.range("A1") msgbox rng.value -- Regards Frank Kabel Frankfurt, Germany Ultraglide wrote: > I seem to remember a technique where I could assign a variable name to >...

How can I access a public calendar from a custom form
I am trying to create a custom form that would allow a employee to request a vactation time frame. This form would then be mailed to a supervisor who could approve or disapprove the request. I am having difficulty getting the response posted to a public calendar vs. the managers private calendar. Help! -Jeff ...

Best way to design tables for cascading on my form
My company inspects damaged cars for insurance purposes. Up to now, (relating to this issue) I've had tblDamageArea populate a list box on my form where the user can select the various parts of the car that were damaged. I've had this list include parts for multiple kinds of cars and I'd like to make this list cascade to include only those parts relevant to the car type inspected. I've created a tblVehType to separate Sedan, Coupe, 4DoorTruck, SUV, etc. which populates a cbo on my form where the user will select the type which will then be used as the crite...

How do I use excel names with INDIRECT with charts
Hello, I want to create a Chart that does not directly reference cell-ranges (i.e. A1:A6), but excel-names that make the reference sheet-independent. My aim is to be able to copy one chart to other worksheets, which have their dataareas at the same places like the source-sheet. Problem: This works fine in cells but not in charts Excel name definition: =INDIRECT("R3C2";0):INDIRECT("R3C5";0) Any idea about this? Thanks in advance, Holger. You have to include the sheet name in the final formula that you want XL to use. -- Regards, Tushar Mehta www.tushar-mehta.co...

SBS2003 + Internet Explorer
We have a customer who has an SBS 2003 Premium server with ISA 2004 They have just installed a bolt on to Outlook which searches their mailboxes or something. They have discovered that they cannot get this to work unless the untick the 'Use automatic configuration script' in Internet Explorer. Trouble is each time they re-boot a PC the tick comes back, so sounds like it's a policy setting somewhere. What is this ? Where is it configured and what are the implications of turning it off ? Could it be that rather than turn it off whatever it is ought to be con...

Look up names/addresses for specific category
I want to mail to clients in a specific category - how do I pull up names/address for a specific category? sandy <sandy@discussions.microsoft.com> wrote: > I want to mail to clients in a specific category - how do I pull up > names/address for a specific category? In your Contacts, switch to the By Category view, select all the addresses from the desired category, right-click the selection and choose "New Message to Contact". -- Brian Tillman ...

Calculating or Adding Values that are 0
I have three fields that I need to total. When i do run the total nothing appears because one of the fields has a 0 value. The expression is not counting 0 values, how do I work around this? Is the value actually zero or Null? If it is null then use the NZ function like this -- Nz([Field1], 0) + Nz([Field2], 0) + Nz([Field3], 0) -- Build a little, test a little. "blanch2010" wrote: > I have three fields that I need to total. When i do run the total nothing > appears because one of the fields has a 0 value. > > The expression is not ...

to extract multiple values from an array
Hi, I am into Image processing industry, for each job we create unique code in excel, we Process 20 jobs in a day, I want to list all the job Code in "summary of the day sheet" that we complete for the particular day . On Dec 30, 4:56=A0am, ratan h <ratha...@nextgenalbums.com> wrote: > Hi, > > I am into Image processing industry, for each job we create unique > code in excel, we Process 20 jobs in a day, I want to list all the job > Code in "summary of the day sheet" that we complete for the particular > day . Not enough info but, assuming you are...

can cvs files be opened using excel viewer
i have a user who cannot open cvs files with excel viewer but can ope with the full version of excel...anyone experienced this problem and i so, can you pls post the resolution -- darrie ----------------------------------------------------------------------- darriel's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2388 View this thread: http://www.excelforum.com/showthread.php?threadid=37522 There is nothing in the description of the Excel Viewer to suggest that it can read anything other than true XLS files. http://support.microsoft.com/default.aspx?scid=kb;...

Display value of cell
Using Excel 2000 In a cell the formula shows instead of the value/result, how do I correct this? I want to see the formula. Reply to kdfoxca@yahoo.com Thanks. Are all formulas cells show the formulas or just one? Maybe you have a space before the = (Excel think it is text now) Or your cell is format as text -- Regards Ron de Bruin (Win XP Pro SP-1 XL2002 SP-2) www.rondebruin.nl "Kenya" <anonymous@discussions.microsoft.com> wrote in message news:050b01c3942b$e28b7240$a301280a@phx.gbl... > Using Excel 2000 > In a cell the formula shows instead of the value/resul...

Forms and Reports from CrossTab queries
Hi All, I have read a few postings in this area, but they all seem to be very specific to particular situations. Can someone please guide me towards the best solution to my dilema? I have a form with two list boxes which contain many variables (answers to specific questions from a survey). When two different questions are slected I run a cross tab query resulting in a dymanic result of rows and columns based on the selections. I now want to display the result as a datasheet on my form and in a similar fashion as a report. Any help is, of course, greatly appreciated. Regards, John D...

Hiding a field from a sub form.
I know that this looks easy (and it probably is) but this is what I need to do. I have this code that I can hide a field within the same form ' NI.Visible = Not NB And this works just fine. what I would like to do is Hide a Field from a Sub Form and I am hitting a wall. I tried this, so please tell me where I am brain dead. frmDateEntry!product.visible = not NB Thanks in advance, -- -The Novice Learn Today, Teach Tomorrow Great Success is ones ability to ask for Help. On Fri, 18 May 2007 14:50:00 -0700, TheNovice <TheNovice@discussions.microsoft.com> wrote: >I know tha...

new to making forms on access
Ok I have a table whihc has column name title and some survey question. Now i want to create a form so that when i select Name ( I guess this will be combo box) I dont know how to do that even where i can select name ? but when i select name the person title auto populates and so does the relevant answers to the questions for example i choose John and the title should auto populate and question 1 should populate also and so on but if i pick adam then everything related to adam populates. Your help will be really appreciated The table look like this Name Title Qu...

is it possible to have datasheet smart tags appear in a form?
is it possible to have datasheet smart tags appear in a form? Thanks ...

Publish Form Changes without restarting IIS?
Is there another way to publish form changes without restarting IIS? We run a 24x7 IT shop and taking down a production system for every change is a pain. thank you. Mike Presumably as you are 24/7 you have multiple CRM servers, so you only need to IISRESET one CRM server at a time, thus users will not notice any loss of service "BWIT" <user1@bwinc.com> wrote in message news:ObSXFVvWFHA.4036@TK2MSFTNGP10.phx.gbl... > Is there another way to publish form changes without restarting IIS? We > run a 24x7 IT shop and taking down a production system for every chang...

Import Adobe Form into Access for Report
I am trying to import a PDF into Access to use as a report and populate the fields with a query I have created. I can not find any help in getting this to work. Can someone help me? -- Terry Foster Tax Trilogy ...

How to prevent the same program from running twice using vc?
Try this: http://www.codeguru.com/Cpp/misc/misc/article.php/c299 -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:#gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > Checkout Bob's tip on this subject at http://bobmoore.mvps.org/Win32/w32tip7.htm -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "LeeTow" <fbjlt@pub3.fz.fj.cn> wrote in message news:%23gTQPBglEHA.2612@TK2MSFTNGP15.phx.gbl... > > See also my essay on my MVP Tips site. joe On Wed, 8 Sep 2...

Manufacturing BOM Where Used query
Winthin manufacturing there is a where-used inquiry window that will show all BOMs that a given item appears in. You can also expand that view to see what BOMs and item's parent is used in. Some of our BOMs are 10 layers deep. In our environment we need to know which category of finished goods each purchased item is used in. Currently we maintain this manually and store the info in an inventory user-defined field. Over time as the number of our finished goods and purchased parts has increased this has become an unmangeable process. I need to find a way to query the BOM tables a...

Encode(quote) attribute value
Hi there, I need to encode the value of an XML attribute, e.g. string myXml = "<element myAttr=\"" + encodeURIComponent("my \"value") + "\"" ...."; Is there an equivalent of encodeURIComponent() javascript function as part of the framework (1.1 or 2.0) which will encode special characters, e.g. double quotes? Thanks, -Oleg. For .NET languages, include the namespace System.Web.HttpServerUtility and call the HtmlEncode method of the Server object. The results are equivalent to the javascript escape() method. ie: for VB.NE...

Create field from append query based on linked table name
Here's the setup: Two linked tables called 'PHD' and 'XANS' bring in daily data from two CSV files. A union table-query puts the common data in both into the same name fields. This table-query is called 'SOLS_DATA_MERGE'. I then created a new table called 'SOLS_MAIN' and I ran an append query called 'SOLS_DATA_APPEND' to append the data in the table-query, 'SOLS_DATA_MERGE' into the new table, 'SOLS_MAIN'. The main reason for this was so that I could assign my data a primary key. Even though I have achieved my goal of merging the da...

Utilizing the Print Preview without using a printer
How can I use the Print Preview function and make any necessary changes to my worksheet?..I currently do not have a printer installed, but would still like to be able to have access to the Print Preview functions before I actually print my worksheet. Just install *any* print driver that might be resident on your system, and XL will then enable the "PrintPreview" feature, even though no actual printer is present. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ==========================================...

Input formulas in a defined data range and convert results as valu
Hi, I have a dataset for users to view data. It has the following format till year 2010 and have portions of topics like forecast, shipment, aging .... and the whole dataset can go very long vertically. Within each topics is the type of products measured, below is an extracts of my dataset: Forecast Accuracy Jan 2007 Feb 2007 Mar 2007 Apr 2007 Prod A x x x x Prod B x x x x Prod C ...

Microsoft Office X cannot start because MS Office is already in use
Sometimes Entourage 10.1.4 shows the message: "Microsoft Office X cannot start because MS Office is already in use. A Office program is being used by MYNAME. Your installation exceeds..." It's crazy because I'm the only user with OSX (10.3.2) and Office X is installed only on my Mac!!! It seems that it happens when Entourage run scheduled actions (i.e. Send and Receive). Pls help me. Thanx. -- Buz ...

Using a value from a cell inside a formula!
Hi! I've got two cells that contain the start and end row of a matrix in sheet. I want to use these row numbers in a =COUNT.IF formula to count th number of specified instances in this matrix. But my problem is this; how can I use the values in the two cell inside the COUNT.IF formula?? I should look something like this; =COUNT.IF(F"cell1":F"cell2";"=argument") As you can see, the column (F) is specified in the formula but the ro number needs to be fetched from cell1 and cell2. Can anyone help me with the correct syntax? Thanks! -M -- marsupilam --...