I have spent hours and hours on this problem and tried every solution Google found for me. None have worked.
I have a tabbed form with 5 tabs.
On the first tab, I have a form "F_Enter/EditTenders". On it, is a control called TenderStage which captures the stage at which response is at.
On the second tab, I have an embedded subform named "New Quote". The underlying form is F_Enter/Edit Quote. On it, is a control called InvoiceStatus.
In a perfect world, the quoting and tendering info would have been on the same table, as there is a 1:1 relationship. In reality, that horse has bolted. I am too far down the track to merge the two now.
What I want is the following scenario (using macros, as I don't have much in the way of VBA skills):
When the TenderStage control on tab 1 (F_Enter/Edit Tenders) is changed to "Job Completed", I want an OnChange macro to set the value of the QuoteStatus control on the New Quote subform to "Ready to Invoice".
I have been using the "SetProperty" macro. However, no matter how I refer to the QuoteStatus control, it can't be found. I have tried
Forms![F_Enter/Edit Tenders]![New Quote]![QuoteStatus ]
[New Quote]![QuoteStatus ]
Forms![F_Enter/Edit Tenders]![New Quote].Form.[QuoteStatus ]
Forms(F_Enter/Edit Tenders).Controls(New Quote).Form.Controls(QuoteStatus )
and about a thousand other permutations. All throw an error of some kind, mostly that the control name doesn't exist or is mis-spelled. Oh, and by the way, if I use the AfterUpdate trigger instead of OnChange, same deal. No difference.
So, then I tried using SelectObject first in the macro. The error then is that the InvoiceStatus control does not exist, or is mis-spelled.
I have also tried "GotoControl" - same deal.
According to the SetProperty guidance notes, I don't have to use the full syntax to name the control to be changed, but if it is on another form, I need to use the SelectObject first.
Please can somebody help me? It is doing my head in. As a starting point, this does not work:
If TenderStage="Job Complete" Then
Object Type: Form
Object Name: Forms!F_Enter/Edit Tenders!New Quote
In Database Window: Yes
Control Name: QuoteStatus
Value: Ready to Invoice
I get an error "Microsoft Access cannot find the Forms!F_Enter/Edit Tenders!New Quote you referenced in the Object Name Argument. The macro you tried to run includes a SelectObject action with an invalid name for the Object Name argument." (error 2544)