Full Version: How to create editable form based on query with relationships
UtterAccess Forums > Microsoft® Access > Access Forms
I could use some help with form design, and maybe some VBA code that would let me copy a value from a subform to update a control on the parent form.
would like to create a forecasting form to update data in our Items table (tblItems). Fields on this table include ItemID, ProductName, Cost, etc., as well as a forecast field for each of 15 customers.
The items are video games, which have release dates, and the relase dates are always changing, so we track that info in a separate table (tblRelDates). Relevant fields in this table include ItemID, AsOfDate, and RelDate. The AsOfDate field indicates the date we got a change for that item.
We maintain the data on a Master Items form (frmItems) which has a subform with the relase date info from tblRelDates.
Now I would like to be able to create an editable forecasting form that would show only future items, in the order of their upcoming release date. The trouble is that when I create a form based on a query using tblRelDates (actually a query that gives the latest release date for each item in tblItems) and tblItems I cannot edit the data, apparently because of the underlying relatioships of the form data.
I thought that I might add a field in tblItems called LatestRelDate that would hold whatever the most current release date is for that item. With that info I could easily create and update the new forecasting form. But I don't want the person who maintains the relase dates to have to enter the same release date in two places, both on the subform and also on the parent form. Would there be a way to use the AfterUpdate property on the RelDate control on the subform to copy the value that has been entered, and to update the LatestRelDate control on the parent form?
This may not be the most elegant solutions, so any other suggestions on how to handle this would be appreciated.
Thanks in advance,
You should not have a field that repeats the information from another table; it violates normalization rules. Can you provide some more info on the forecasting form and the actual query your are using for the form? If you can zip and post your database (with any sensative data removed), we may be able to help you come to a resolution more quickly.
Thanks, I've uploaded the database. I understand about the normalization rules but didn't know how to get around this issue. The release date of an item is basic item data like the cost, stock#, etc., and it makes sense to store it in the Items table, making it easy for anyone who used the db to query, filter by form, etc, when the release date is in the current table. When it's only stored separately in the tblReleaseDates, a separate query is needed to find the most current release date and it becomes really unmanageable. Thanks for any input on this.
I was able to come up with a solution; see the attached DB. I started the same way as you with a totals query to get the Max release date for each item ID.
SELECT tblReleaseDates.[Item ID], Max(tblReleaseDates.RelDate) AS MaxOfRelDate
FROM tblReleaseDates
GROUP BY tblReleaseDates.[Item ID]
HAVING Max(tblReleaseDates.RelDate)>=Date()
Othen used a version of the above totals query in another query to get the list of items. I also used the DLookUp() function and the query above to pull in the max date. I also had to use the CDate() function to convert the DLookUp() result to a date since it came in as text. I only brought in some of the fields from the Items table for testing purposes
SELECT Items.[Item ID], cdate(DLookUp("MaxOfRelDate","qryUpcomingReleaseDates","[Item ID]=" & [Item ID])) AS ReleaseDate, Items.[Vendor ID], Items.[Product Name], Items.TRUProductName, Items.Description, Items.Comments, Items.[Stock#], Items.UPC12, Items.UPC13, Items.ItemCatID, Items.ItemSubCat, Items.ItemSubCat2, Items.Discontinued, Items.[Date Dropped], Items.Cost, Items.DistribCost, Items.SRP, Items.MAP, Items.MAPexp, Items.Rating, Items.[Rating-Old], Items.Warranty, Items.[Rep Firm ID], Items.ItemInfoURL
FROM Items
WHERE (((Items.[Item ID]) In (SELECT tblReleaseDates.[Item ID]
FROM tblReleaseDates
GROUP BY tblReleaseDates.[Item ID]
HAVING Max(tblReleaseDates.RelDate)>=Date())));
I used this query as the record source for the subform (see main form:frmVendorsWithUpcomingReleases). All of the fields are editable except the Release Date field (since it came in via the DLookUp)
I see how the items relate to the vendors, but I don't see how the items relate back to the customer in order to get a customer forecast. Somehow you have to relate the items to each customer, but I don't see where you do that. I think a juction table might be necessary.
pkCustItemID primary key, autonumber
fkCustID foreign key-->tblCustomers
fkItemID foreign key-->tblItems
I also see some normalization issues with your tables. For example, in your Items table, you have a series of buyer/rep/comments fields that repeat for various stores. These would be repeating groups which indicates that the table is not normalized. Not knowing your application, I'm not 100% sure how I would restructure this, but here is an example of how I would approach this:
First you need a stores table
pkStoreID primary key, autonumber
other store fields
CAn item can be associated with many stores
pkItemStoresID primary key, autonumber
fkItemID foreign key--Items table
fkStoreID foreign key-tblStores
Each Item/Store combination will have a buyer forecast value and a rep forecast value (one to many relationship)
pkItemStoreForecastID primary key, autonumber
fkItemStoresID foreign key-->tblItemStores
fkForecastTypeID foreign key-->tblForecastType
tblForecastType (2 records: Rep and buyer)
pkForecastTypeID primary key, autonumber
Your table structure really needs to be fixed before you go further with forms/queries/reports. There are some excellent references on this site regarding normalization like Jay Noel's post in this
I also noticed that you have lookups at the table level. Although Access has this capability, it is generally recommended to use lookups via list or combo boxes at the form level.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.