If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Form using a query to look up values
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" wrote in message ... 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? |
#3
|
|||
|
|||
Form using a query to look up values
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" wrote in message ... 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? . |
#4
|
|||
|
|||
Form using a query to look up values
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" wrote in message ... 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? . |
#5
|
|||
|
|||
Form using a query to look up values
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 |
#6
|
|||
|
|||
Form using a query to look up values
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 . |
#7
|
|||
|
|||
Form using a query to look up values
On Fri, 2 Apr 2010 06:13:01 -0700, RA 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] |
Thread Tools | |
Display Modes | |
|
|