I think I could solve my problem if I could figure out how to share a recordset between two forms (access and manipulate one recordset from two forms) but I will explain what I am trying to do because it is entirely possible that there is a better solution.
I have a table (funding) with 16 fields one field holds a dollar amount one field is an auto number and the remaining 14 fields are just codes that are used to identify the source and purpose of the money.
Another table (work) holds information about jobs that need to be performed which of course will require funding.
The last table (link) is the link table it contains the job number and the number in the autonumber field from the funding table. In addition it holds a dollar amount because it is possible to use only a portion of the funding from a funding record for a particular job. It is also possible to fund a single job from multiple sources so there can be more that one link record for a single job.
for reasons to complex to explain I don't modify the funding table when I assign funding to a job, remaining funding is calculated by a query.
I have a custom dialog box (frmSelectFunding)that displays all the available funding and returns the key fields after the user selects funding. this form is called by the next form I will describe whenever
When a user opens the Job form (frmEditFunding) it displays the existing funding for the selected job in a list box. The user may want to delete funding(select a line and click delete), add new funding (using frmSelectFunding), or just modify the amount of existing funding(double click to bring up an inputbox that accepts a new dollar amount). After making the changes on screen they can decide to save the changes but they also have the option to drop the changes.
The problem is that if they add or delete funding on frmEditFunding the changes are not reflected in the data available to frmSelectFunding because the changes have not yet been saved to the database. I would like to have a recordset that contained all the available funding PLUS all the funding currently associated with the job record being edited then as funding was added or deleted from the listbox on frmEditFunding the data in the recordset could be updated so that the select funding dialog would always be up to date.
I have a table (funding) with 16 fields one field holds a dollar amount one field is an auto number and the remaining 14 fields are just codes that are used to identify the source and purpose of the money.
Another table (work) holds information about jobs that need to be performed which of course will require funding.
The last table (link) is the link table it contains the job number and the number in the autonumber field from the funding table. In addition it holds a dollar amount because it is possible to use only a portion of the funding from a funding record for a particular job. It is also possible to fund a single job from multiple sources so there can be more that one link record for a single job.
for reasons to complex to explain I don't modify the funding table when I assign funding to a job, remaining funding is calculated by a query.
I have a custom dialog box (frmSelectFunding)that displays all the available funding and returns the key fields after the user selects funding. this form is called by the next form I will describe whenever
When a user opens the Job form (frmEditFunding) it displays the existing funding for the selected job in a list box. The user may want to delete funding(select a line and click delete), add new funding (using frmSelectFunding), or just modify the amount of existing funding(double click to bring up an inputbox that accepts a new dollar amount). After making the changes on screen they can decide to save the changes but they also have the option to drop the changes.
The problem is that if they add or delete funding on frmEditFunding the changes are not reflected in the data available to frmSelectFunding because the changes have not yet been saved to the database. I would like to have a recordset that contained all the available funding PLUS all the funding currently associated with the job record being edited then as funding was added or deleted from the listbox on frmEditFunding the data in the recordset could be updated so that the select funding dialog would always be up to date.
