Well, I've been tinkering with this problem for a while now, but I can't figure out what I'm doing wrong, so I hope someone brighter than I am can steer me in the right direction. And by "steer," I mean full on GPS navigation to the end result! LOL
I'm building my first Access web app. It has three major tables: Households, Resources, and Skillsets. The household table contains names and addresses, etc. The Resources table contains things owned by the household, and the Skillsets table contains things the people in the household are capable of doing. Each table has roughly 400 or so records, neatly joined together and working properly (imported from an access database). If I look at the skillset list view, for example, I see a list of the household names that I can filter, and selecting any existing name shows me the skillsets associated with that name. Ditto with the Resources list view. I can edit any of the households, resources or skillsets, and all the changes stick appropriately.
However, I cannot figure out how to create a functioning NEW household record. I mean, I can create the new household record just fine! But when I go to the Skillset or Resource table list view, I cannot see the newly created household to assign skills or resources. I'm sure this is pretty lame (my access database works perfectly, but man oh man I'm having a devil of a time with the web app!
Here's what I've tried:
- Adding a calculated name field to the household table, then creating combo box on the skillset form that points to that field. (Result: I can look up the newly created household name, but any changes I make to the skillsets are being made to the first Skillset record - NOT to the newly created name. And no new skillset ID is created, so there's still no tie in between the two tables.
- Added the primary key from the skillset table to the Household list view. (Result: The household list view is no longer editable, and no new records can be added).
- Added lookup fields to both the Skillset and Resource tables, pointing to the HouseholdID (Result: Not really sure. Nothing useful happened)
- Added lookup fields to the household table pointing to the ID's of the skillset and resource tables. (Result: Nada).
- Added a new "join" table, with lookups to all three of the tables, then added subforms to each list view pointing to those tables. (Result: Nothing exciting happened).\
- And eleventy other wrong ways, besides!
So... what sort of voodoo sacrifice must I make to be able accomplish the following in the web app?
1) Create a new household. Enter exciting new data, such as names and address.
2) Click on the Skillset tab, and be able to find the newly created household among the 400 existing households I can already see.
3) Click the nifty "Edit" button and begin assigning Skills to the newly created household.
4) Click on the Resource tab, and be able to find the newly created household and add resources.
5) Be able to look up the new household later, and find the resources and skillsets still appropriately assigned.
That sounds SOOOOO easy! And it probably is, but I'm not able to figure it out. As near as I can figure, my issue is that at no time during the creation of the household is any sort of skillset ID created, and without that ID, no link exists between the two records.