Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-17-2018, 11:48 PM   #1
Mumsie
Newly Registered User
 
Join Date: Sep 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mumsie is on a distinguished road
SetProperty across two forms not working

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

Mumsie is offline   Reply With Quote
Old 09-18-2018, 12:16 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: SetProperty across two forms not working

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-18-2018, 12:20 AM   #3
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,709
Thanks: 317
Thanked 427 Times in 412 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: SetProperty across two forms not working

I believe you are not taking into account that the control is on a subform.?

See here for how to address controls

http://access.mvps.org/access/forms/frm0031.htm

https://docs.microsoft.com/en-us/off...pi/access.form

Note that you use the name of the subform control, not the subform name. Can be the same, but not always.

BTW If you can use the Builder, that will show you the correct syntax.

HTH

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 09-18-2018, 12:24 AM   #4
Mumsie
Newly Registered User
 
Join Date: Sep 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mumsie is on a distinguished road
Re: SetProperty across two forms not working

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.
Mumsie is offline   Reply With Quote
Old 09-18-2018, 12:32 AM   #5
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,839
Thanks: 55
Thanked 2,183 Times in 2,094 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: SetProperty across two forms not working

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 09-18-2018, 12:40 AM   #6
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,798
Thanks: 140
Thanked 1,565 Times in 1,537 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: SetProperty across two forms not working

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
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 09-18-2018, 12:55 AM   #7
Mumsie
Newly Registered User
 
Join Date: Sep 2018
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Mumsie is on a distinguished road
Re: SetProperty across two forms not working

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!


Mumsie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] SetProperty in Macros miacino Macros 2 06-23-2016 07:25 AM
ACCESS Web App - SetProperty change Back Color cavalli Macros 0 05-19-2016 03:22 AM
DoCmd.SetProperty with NULL value triplee23 Modules & VBA 3 03-30-2014 01:12 AM
SetProperty Visible error ALui Macros 1 05-11-2009 11:14 AM
Working with Sub-Forms Wicklund Forms 1 01-29-2003 01:58 PM




All times are GMT -8. The time now is 09:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World