Opening one form from another

[PaulJK]

Hello.

Within our customer record (frmCustomers) we have a tab which details all proposals. These are contained in a subform (continuous form frmproposals).

Each proposal can have a number of sales payments against it. Therefore there is a separate form (frmSales) which has been set up to open from the frmproposasl. Again frmSales is a continuous form. They are linked by ProposalID.

The two issues I have not be able to resolve are:

1. When to button on the frmproposals is clicked to open frmSales, the records in frmproposal are not saved. I have used the standard open command On Click, and added:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.Minimize

The latter is to minimise the frmCustomers.

If I do a manual save (Ctrt +S) it works fine. I just want to force the save but my code is not working.

2. When the frmSales is opened it is not selecting the ProposalID from frmproposal it is linked to and mimimised. It adds +1 to the ProposalID.

I have placed the ProposalID on this form and made the defalt value =[Forms]![frmCustomers]![frmProposals].[Form]![PropAutoID]

What happens is that for each record added in frmSales, a new record in frmproposals is added with blank data apart from a new ProposalID!

Your guidance would be appreciated.

Thanks & regards,

Paul

 

[DevastatioN]

Hello,

The only few things I can think of that may not be working for you, is that you should be doing the DoCmd.Runcommand acCmdSaveRecord before the link criteria is setup in your code.

On the form that opens, it should most likely be a Proposal Form / Sales Subform, but the Proposal ID is hidden, so the Sales are entered as a subform.

If you don't want it as a form/subform, there are ways where you can enter the linking criteria manually through code, such as,

variable = me.ProposalID
Screen.Activeform.ProposalID = variable

But in this method you have a lot of errors to control with code.

Attached is a sample database of what I think you were trying to do. Hope this helps solve your issue.

 

[PaulJK]

Hello,
Thank you for your reply and example database.
On the first point, i have now resolved this thanks.
On the second, thanks for the suggestion which should work for me. I was hoping that I could just have one form rather than a Proposal Form/Sales Subform.
Once again thanks.
Paul

 

[DevastatioN]

Glad it helped, about having a single form.

You can, but you have to think of a few things:

You need to pass the proposalID to the new form using the OpenArguements in the Form Open Command. Anytime the form is opened it should be filtered to show results only for that ProposalID (like you do now using Link Criteria).

This is where things get dicey. You need to disable the navigation buttons, and create your own buttons for navigating and adding records. With the "new" button, it will set allowadditions = true, dataentry = true, and you need to to automatically fill in the ProposalID with the ID that was sent when the form was opened (OpenArguements), you also need to disable the other navigation buttons and allow the user to "Save" or "Cancel" his current entry, this needs to be done because once you automatically fill in the proposalID the record can be saved according to access. This means the user should also not be able to exit the form until he chooses to save or cancel the current addition.

The save button will save the record and reinstate the navigation buttons, set dataentry = false, allowadditions = false, the cancel button will have DoCmd.RunCommand acCmdUndo, and then cancel the addition to go to a previous record and also set dataentry = false and allowadditions = false.

You also will need an oncurrent event to check to see if there is a SalesID, if there is, then allowadditions should be set to false (this disables the scroll wheel method of adding a record, which would bypass your Add button code). If there is no SalesID that means there were no records when the form was opened, so you have to enable/disable the appropriate buttons and set the ProposalID to the OpenArguements again.

To say the least, by having a single form you have a lot of error catching to do to make sure that the user can only see the correct sales attached to the proposalID, and also be able to add a record to that proposalID (because even though the form is filtered, adding a new record will not attach it automatically to the correct proposal).

Hope this sheds some light on the subject.

 

[PaulJK]

Thanks for your email.
i have gone with your earlier suggestion and all is working fine now.
Once again thanks...Paul