Form using a query to look up values

R

RA

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?
 
L

Larry Linson

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
 
R

RA

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 said:
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 said:
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?
Click to expand...



.
Click to expand...
 
R

RA

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 said:
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 said:
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 said:
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?
Click to expand...



.
Click to expand...
Click to expand...
 
T

tom_willpa

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
 
B

BruceM via AccessMonster.com

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.
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
Click to expand...
[quoted text clipped - 22 lines]
.
Click to expand...
Click to expand...
 
R

RA

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 said:
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.
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
Click to expand...
[quoted text clipped - 22 lines]
.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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.
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Writing data to table from listbox/text box 1
Form to look for similar addresses 7
Lookup records using Combo Box 3
Creating a Search using a combo box and then creating a report. 4
Link to Google Maps? 2
Request Form - Alert Team Member 0
Auto populate controls on a form 4
Complex Form-Query 0

Top