SetProperty across two forms not working (1 Viewer)

Mumsie

New member
Local time
Today, 04:18
Joined
Sep 18, 2018
Messages
3
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
Select Object
Object Type: Form
Object Name: Forms!F_Enter/Edit Tenders!New Quote
In Database Window: Yes
Set Property
Control Name: QuoteStatus
Property: Value
Value: Ready to Invoice
End If


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)



Kind regards
 

Minty

AWF VIP
Local time
Today, 11:18
Joined
Jul 26, 2013
Messages
10,354
You have some genuinely hideous form, field and control names going on there. I can assure you that is not helping you in this instance.

Are both forms subforms on a normal master form, or possibly is this an inbuilt navigation form?

If it's the latter then when you switch tab's the other subforms won't be available, as the inbuilt navigation forms silently "switch out" the other forms. It's why most developers avoid them like the plague, as they don't work like you would expect.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:18
Joined
Sep 21, 2011
Messages
14,038

Mumsie

New member
Local time
Today, 04:18
Joined
Sep 18, 2018
Messages
3
Thanks for your quick reply.

I'm self-taught through necessity, not a developer, so I am using names that mean something to me. I couldn't work with the naming conventions that you devs seem to like. I'm sure there's a logic behind their use: I have used my own logic:(.


I'm not sure I understand your question. I created the form tab 1 where I based it on a table and dragged the fields into Tab 1. Does that mean it is an inbuilt navigation form?


Tab 2 is a true subform. I created a form, and dragged the entire form in, linking the Child/Master with Tab 1.


Does this answer your question.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:18
Joined
May 7, 2009
Messages
19,169
both are subforms.

add an unbound textbox to main form. set its visible prop to no.
add current event on subform on tab1.

private sub subfrm1_current()
me.parent!unboundtextbox=pkFieldName
end sub

now on subfrm2 in tab2, set mast link fields to unbound textbox.
 

Minty

AWF VIP
Local time
Today, 11:18
Joined
Jul 26, 2013
Messages
10,354
Okay, from your description it sounds like a normal form, with a sub form.
In which case I think Gasman is on the correct track to your problem.

Select the subform container on your form, make sure you have the properties window open, and select the other tab. Here you will see the subForm container name.

Change it to something obviously different from your other control names like subcntNewQuote

Now try to use the syntax from the links which in VBA after update event would be along the lines of
Code:
If Me.TenderStatus =  "Job Completed" Then 
      Me!subcntNewQuote.Form!QuoteStatus =  "Ready to Invoice"
End If
 

Mumsie

New member
Local time
Today, 04:18
Joined
Sep 18, 2018
Messages
3
Now try to use the syntax from the links which in VBA after update event would be along the lines of
Code:
If Me.TenderStatus =  "Job Completed" Then 
      Me!subcntNewQuote.Form!QuoteStatus =  "Ready to Invoice"
End If
[/QUOTE]


That worked! Thank you!
 

Users who are viewing this thread

Top Bottom