"Prefilled" Form

[hominamad]

You guys helped me through another problem I was having so I thought I'd throw one more challange out there! I want to have a form that is sort of dynamically built and has placeholders for data, even if the rows are not there yet. Here's an example of what I mean:

I have two tables: Expense_Types, and Expenses. Expense types holds codes and descriptions for the different types of expense there can be, and this table can be added to if need be. Every month, it's required that one expense of each type is entered into the Expense table. So I want a form like this:

Food ________
Travel ________
Hotel ________
etc ________

The lines represent empty textboxes. If I do an unbound form, I'm not sure how I can dynamically generate the list of possible expenses. If it's bound, I run into problems because those blanks are representing rows that don't actually exist yet. Is this sort of thing possible to do in Access?

Thanks!

 

[pbaldy]

The "best" solution probably depends on your table structure. I have a somewhat similar situation, where the user is entering daily data for limousine drivers. They can drive different cars during the day which could have different rates. The users wanted a form that listed all the possible rates. As they enter data, I pre-populate the activity table with records for each rate with a zero quantity, for that driver/date. The user can just go down the line and enter the appropriate quantities. When the form unloads, I run a delete query that deletes any records that still have the zero quantity, leaving me with the correct records in the table.

 

[hominamad]

The "best" solution probably depends on your table structure. I have a somewhat similar situation, where the user is entering daily data for limousine drivers. They can drive different cars during the day which could have different rates. The users wanted a form that listed all the possible rates. As they enter data, I pre-populate the activity table with records for each rate with a zero quantity, for that driver/date. The user can just go down the line and enter the appropriate quantities. When the form unloads, I run a delete query that deletes any records that still have the zero quantity, leaving me with the correct records in the table.

Gotcha. But how do you create the form to begin with? I don't want to hardcode all the expense types on the form because the user can add new types. That's in a lookup table. I need to somehow dynamically create this form.

 

[pbaldy]

It can be dynamic. In my case, it's a continuous subform. There's a "rates" table maintained by the users. They can add/modify/delete rates. There's also an "activity" table, with among other fields, driver, date, hours and rate. My initial process creates entries in the activity table for the date/driver combination and any rates found in the rate table. If there are 5 rates, 5 records will be created.

The subform is populated by a query that gets those records. Thus, the users are presented with a form with all applicable rates. They simply enter through the records, changing the applicable records from zero hours to whatever. Then when they close the form, any remaining zero records are deleted from the activity table, leaving the valid "non-zero" records.