Query Data on a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a form bound to one table. Each entry is based on a site name and
each site name will have multiple entrys but will have the same address. What
I need to do is run a query based on the site name entered on the form and if
the site name is found have the query auto populate the address data on the
form. I have the query created but do not know how to get the data on the
form. Your help is appreciated.
 
If the address is always the same then you don't want to store it in the
table multiple times. What if the address changes? Do you want to update
all the records?

You have a classic one-to-many relationship. You need two tables. One to
store the Site information, and one to store the details.

TblSites
SiteNumber
SiteName
SiteAddr1
SiteAddr2
SiteCity
SiteState
SiteZIP
SitePhone
etc.

TblMain
SiteNumber
TransactionDate (or whatever your other fields are here.)
etc.


The address for each site would only be stored ONE time in your database.
 
Rick,
I will have a number of sites with only 1 data entry. I will also have a
number of sites with the same address data. Unfortunately I need to re-enter
the address data due to the table structure (another database). Thanks Rick
 
What do you mean you will need to enter it multiple times due to another
database? If you need to export it to another database with the address
repeated, then Crete a query that will show the address for each record.

In short, you are trying to build a spreadsheet with auto-complete turned
on. Access is NOT a spreadsheet. If you want a flat file, I would
recommend using Excel.
 
Yes. The Northwind database does this in the Orders form. You select a
customer from a drop-down list, and it takes the customer shipping data and
places it in the various fields. You might go scheck out the code that is
used for that form. It basically uses a multi-column listbox with all the
data, then places the data into other controls on the form. These controls
could then be bound to your underlying table. The code in Northwinds is...


Private Sub CustomerID_AfterUpdate()
' Update ShipTo controls based on value selected in CustomerID combo box.
Me!ShipName = Me![CustomerID].Column(1)
Me!ShipAddress = Me!Address
Me!ShipCity = Me!City
Me!ShipRegion = Me!Region
Me!ShipPostalCode = Me!PostalCode
Me!ShipCountry = Me!Country

End Sub
 
Back
Top