Form in Edit Mode
Hello,
Not sure where this question belongs, but here it is.
I have a form that opens up onClick from another form in either edit record mode or add new record mode if the record wasn't found. A user would add projects in form A, which would have a unique ID. User then selects that ID and clicks on a command button to get to form B to either enter details about the project that was just entered into project table from Form A or edit the project detail of the project that was already in the underlying table of Form A. If the project is new then the user would create a new record of that project detail using Form B, which is tied to a query to bring in different details related to a given project and one of them is called Status. The field Status, which is a lookup field so the user is already given the choice of either Close or Open in a dropdown combo box comes from a table ProjStatus. Now if the project was new then the user can create a new record with project detail in Form B and assigns it the Status value of Open (since initially new projects have Open status) from the dropdown box, but instead of taking the Open value found in ProjStaus table, the form creates a new record in the linked ProjStatus table with value Open! This should NOT happen, because the user selects the Open value from available choices in the dropdown menu. We do not want a new Open or Close record in the ProjStaus table each time a project detail is created. There could be multiple project details in the ProjDetails table, but each should reference the same Close or Open record from the ProjStatus table, not create a new one each time. Then let's say a couple days later when the project is done so user needs to switch the status to Close. So the user opens that project from Form A then clicks on the command button to open the project detail in Form B to assign it the value of Close in Status field. Now if the user selects Close from the dropdown list, then the field that previously said Open in the ProjStatus is renamed to Close! So instead of assigning the project detail with a different ID, which corresponds to Close and already exist in the ProjStaus table, it rewrites the ID that was previously created by this project detail record as Open to Close or vice versa.
Does anyone know why this happens? We just want to assign the Open or Close IDs already found in the ProjStatus table to each project detail record, not create a new one each time there is a new project detail added. What can we do to ensure that the ProjStatus doesn’t create a new record for each new project detail, but assigns values that are already found in ProjStaus table? Any help would be greatly appreciated.
Thanks much.
|