Open New Form and Populate Fields Based on the Old Form

[RFreund]

I have a "proposal form" that has a bunch of information such as project name, client name, price, and some other subforms with other information. I want to create a button that opens a "projects form" that links to the proposal table and client table. I have fields for proposalID and clientID in the project table, but I seem to have trouble populating these fields. I want this button to create a new project record in the project table with the proposalID and clientID populated. Unless of course the project has already been created then it would just open the form. I seem to be missing the forest for the trees on this as I know this is a fairly basic task.
Should I handle this with VBA and use record set after checking to see if the values already exist? Or is there a more straightforward way?
Thanks!

 

[Mark_]

Please post how your tables are related. From the description, a proposal should be for a project and a project would be a child to a client.

 

[DHookom]

If you want to "send" a value from the first form to a newly opening form, you can use OpenArgs in the DoCmd.Openform method. The sent value can be used in the new form's On Open event to set the value of a control. I'll leave it to you to search the web for OpenArgs.

If you need to determine if there is already a project from the proposal, you can use VBA code with DCount().

 

[RFreund]

Thanks for the responses.
@DHookom - Thanks for confirming, this is essentially what I was discovering. It just seemed like maybe there was a better way to do it.

@Mark_ - Client table has a one to many relationship with Proposals table. The workflow is typically to start with the proposals form and pick a client from a combobox. The proposal is created and sent to the client. If they accept the proposal, I want to "create" a project. So I want a new form to open where some of the proposal information is displayed to the user and they can input project related data.

Part of me thinks maybe I should create a Query with all the tables (client, proposal, project). Then have a proposal tab and a project tab. That way all the data related to the project is easily accessible. But I might make this a separate form once the project has been "created". Not sure which makes the most sense.

 

[June7]

Shouldn't have to repeat ClientID in Projects.

Really sounds like a 1-to-1 relationship. Alternative is to just have Projects table with a field for status: Proposed or Accepted. Or just make it a date field: DateAccepted - no date means never accepted.

Build form/subform arrangement or review https://www.fmsinc.com/MicrosoftAccess/Forms/Synchronize/LinkedSubforms.asp#:~:text=We synchronize the subforms using the Link Child,based on a control on the main form.

 

[The_Doc_Man]

Another way you might consider doing this is, if the info in the proposal form is locked in once it becomes a project, you can include the Proposal ID in the OpenArgs of the project form and allow that form to just pick that up from the Proposal. If you are dealing with a new record, you can test this (test: is Me.NewRecord = True?) in the project form's Form_Current routine to decide if this is the 1st time the project becomes "current." Then just do a couple of DLookups from the proposal table one time. That way if you really wanted to copy the data, you would know that you needed to.

Another way would be to create a sub-form that links to the original proposal in your proposal table and just displays the desired info in a little section of the project form in some way or another, linked parent/child via the proposal ID.

Copying data is normally something you don't want to do too often because that violates normalization rules to some degree or another. (Not that we haven't done it before, but usually it is to be avoided.)

If the proposal info CAN be edited after-the-fact, you have some strategy decisions on whether you want to see those edits on the project form later. It opens up its own can of worms in that case.

 

[Mark_]

So Proposals belong to Clients. Proposals get Client ID as a foreign Key. Projects belong to Proposals, so they get the Proposal ID as a foreign key.

Are you planning to use sub forms? If so, proposal subform on your clients form would allow you to pass the Client ID by linking them. Same with Projects to Proposals.

Yes, this would allow you to have a query on Projects that links back to Proposals and back to clients.

You would ONLY need both client and proposal ID in a project if the project table links Proposals to clients. This would assume that proposals don't have a client.

 

[Pat Hartman]

Having your code dirty a form leads to confusion if the user closes the piopup without finishing the data entry. One simple way to handle this problem and to avoid user confusion is to pass in the FK in the openArgs. In the Open Event of the form, set the Default value of the FK to the OpenArgs.

Me.MyFK.DefaultValue (I think) = Me.OpenArgs. That doesn't dirty the form but as soon as the USER dirties the form, the correct FK value will populate and the records will be connected as if the popup were a subform. If you want some of the data fields from the calling form to be visible, you can set their control properties to:

=Forms!CallingForm!fld1
=Forms!CallingForm!fld2
etc.

Now your validation code can assume that the user is trying to add a new record and validate the data that was entered.

 

[arnelgp]

maybe you can implement same technique here.
open frmProposal form.

 

[MarkK]

Create a table called tProject with a field called AwardedDate. Then, write two queries, one called qProposal...

SELECT * FROM tProject WHERE AwardedDate Is Null;

...and the other called qProject...

SELECT * FROM tProject WHERE AwardedDate Is Not Null;

Now create two forms, fProposal and fProject. Set fProposal.RecordSource to "qProposal" and set fProject.RecordSource to "qProject". On form fProposal, provide functionality and subforms relevant to a proposal, and on form fProject provide subforms and functionality relevant to a project.

Finally, to create a project from a proposal, simply set the AwardedDate.