Changing subforms by code - only works from time to time ...

[Steff_DK]

I am using some labels as controls on a form:

Private Sub Label1_Click()

If Form_frmSubformFA.Label1.Caption = "Edit" And Form_frmSubformFA![SubFA].SourceObject = "frmFALog" Then
Form_frmSubformFA![SubFA].SourceObject = "frmFALogEdit"
Form_frmSubformFA.Label1.Caption = "Save"
Form_frmSubformFA.Label2.Caption = "Undo"
Form_frmSubformFA.Label3.Caption = "Delete records"
Form_frmSubformFA.Repaint

ElseIf Form_frmSubformFA.Label1.Caption = "Edit" And Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetail" Then
Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetailEdit"
Form_frmSubformFA.Label1.Caption = "Save"
Form_frmSubformFA.Label2.Caption = "Undo"
Form_frmSubformFA.Label3.Caption = "Delete records"
Form_frmSubformFA.Repaint

ElseIf Form_frmSubformFA.Label1.Caption = "Save" And Form_frmSubformFA![SubFA].SourceObject = "frmFALogEdit" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
Form_frmSubformFA.Label1.Caption = "Edit"
Form_frmSubformFA.Label2.Caption = "Add new entry"
Form_frmSubformFA.Label3.Caption = "Detailed view"
Form_frmSubformFA.Repaint

ElseIf Form_frmSubformFA.Label1.Caption = "Save" And Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetailEdit" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetail"
Form_frmSubformFA.Label1.Caption = "Edit"
Form_frmSubformFA.Label2.Caption = "Add new entry"
Form_frmSubformFA.Label3.Caption = "Hide Details"
Form_frmSubformFA.Repaint

ElseIf Form_frmSubformFA.Label1.Caption = "Save new entry" And Form_frmSubformFA![SubFA].SourceObject = "frmAddFA" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
Form_frmSubformFA.Label1.Caption = "Edit"
Form_frmSubformFA.Label2.Caption = "Add new entry"
Form_frmSubformFA.Label3.Visible = True
Form_frmSubformFA.Box3.Visible = True
Form_frmSubformFA.Repaint

ElseIf Form_frmSubformFA.Label1.Caption = "Delete selected" And Form_frmSubformFA![SubFA].SourceObject = "frmFADelete" Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
Form_frmSubformFA.Label1.Caption = "Edit"
Form_frmSubformFA.Label2.Caption = "Add new entry"
Form_frmSubformFA.Label3.Caption = "Detailed view"
Form_frmSubformFA.Label3.Visible = True
Form_frmSubformFA.Box3.Visible = True
Form_frmSubformFA.Repaint

Else

MsgBox "No action defined"

End If

End Sub

There are similar procedures for label 2 + 3 ...

I know it looks a bit confusing but it actually works (for the most part) and I like the html look and feel of the labels as opposed to buttons.

My problem is that the labels only work the first time I click one of them.
I can click label1 as the first action after I open the form - and it works.
However, if I click some of the other labels a couple of times to navigate around and thereby change the subforms and what not, and then hit label1, all of a sudden doesn't work anymore!?! I don't even get the Msgbox "No action defined" ... Just a subform with no records at all ...

 

[RuralGuy]

I haven't done a truth table for your code but I rewrote it as a select case structure with additional MsgBox's for all the paths.

Private Sub Label1_Click()

Select Case Form_frmSubformFA.Label1.Caption

   Case "Edit"
      If Form_frmSubformFA![SubFA].SourceObject = "frmFALog" Then
         Form_frmSubformFA![SubFA].SourceObject = "frmFALogEdit"
         Form_frmSubformFA.Label1.Caption = "Save"
         Form_frmSubformFA.Label2.Caption = "Undo"
         Form_frmSubformFA.Label3.Caption = "Delete records"
         Form_frmSubformFA.Repaint
      ElseIf Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetail" Then
         Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetailEdit"
         Form_frmSubformFA.Label1.Caption = "Save"
         Form_frmSubformFA.Label2.Caption = "Undo"
         Form_frmSubformFA.Label3.Caption = "Delete records"
         Form_frmSubformFA.Repaint
      Else
         MsgBox "No action defined for" & vbCrLf & _
               "'Edit' when SourceObject = [" & _
               Form_frmSubformFA![SubFA].SourceObject & "]"
      End If
   Case "Save"
      If Form_frmSubformFA![SubFA].SourceObject = "frmFALogEdit" Then
         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
         Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
         Form_frmSubformFA.Label1.Caption = "Edit"
         Form_frmSubformFA.Label2.Caption = "Add new entry"
         Form_frmSubformFA.Label3.Caption = "Detailed view"
         Form_frmSubformFA.Repaint
      ElseIf Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetailEdit" Then
         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
         Form_frmSubformFA![SubFA].SourceObject = "frmFALogDetail"
         Form_frmSubformFA.Label1.Caption = "Edit"
         Form_frmSubformFA.Label2.Caption = "Add new entry"
         Form_frmSubformFA.Label3.Caption = "Hide Details"
         Form_frmSubformFA.Repaint
      Else
         MsgBox "No action defined for" & vbCrLf & _
               "'Save' when SourceObject = [" & _
               Form_frmSubformFA![SubFA].SourceObject & "]"
      End If
   Case "Save new entry"
      If Form_frmSubformFA![SubFA].SourceObject = "frmAddFA" Then
         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
         Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
         Form_frmSubformFA.Label1.Caption = "Edit"
         Form_frmSubformFA.Label2.Caption = "Add new entry"
         Form_frmSubformFA.Label3.Visible = True
         Form_frmSubformFA.Box3.Visible = True
         Form_frmSubformFA.Repaint
      Else
         MsgBox "No action defined for" & vbCrLf & _
               "'Save new entry' when SourceObject = [" & _
               Form_frmSubformFA![SubFA].SourceObject & "]"
      End If
   Case "Delete selected"
      If Form_frmSubformFA![SubFA].SourceObject = "frmFADelete" Then
         DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
         Form_frmSubformFA![SubFA].SourceObject = "frmFALog"
         Form_frmSubformFA.Label1.Caption = "Edit"
         Form_frmSubformFA.Label2.Caption = "Add new entry"
         Form_frmSubformFA.Label3.Caption = "Detailed view"
         Form_frmSubformFA.Label3.Visible = True
         Form_frmSubformFA.Box3.Visible = True
         Form_frmSubformFA.Repaint
      Else
         MsgBox "No action defined for" & vbCrLf & _
               "'Delete selected' when SourceObject = [" & _
               Form_frmSubformFA![SubFA].SourceObject & "]"
      End If
   Case Else
      MsgBox "No action defined when the label equals" & vbCrLf & _
            "<" & Form_frmSubformFA.Label1.Caption & "> and" & vbCrLf & _
            "the SourceObject = [" & Form_frmSubformFA![SubFA].SourceObject & "]"
End Select

End Sub

See if using this code shows what is happening better.

 

[Steff_DK]

Thanks RG!
The code looks better with a Case structure, but problem persists.
I narrowed it down to this:

1) I click "Edit" and the subform correctly changes to "frmFALogEdit"
2) I click "Save" and the subform correctly changes back to "frmFALog"
3) I click "Edit" again but the subform now changes to a blank form???

I did a msgbox with the sourceobject for the subform each time and it showed the correct sourceobject even if I could clearly see the form was blank.

Where does the previous form go, when I throw it out, and set a new form as the sourceobj???

Do I need to close the subform sourceobject, and how is this done? -bear in mind that I will have to open it again when it's needed, and that it should open inside the subform and not as a new window ... It opens in a new window when i do a regular OpenForm ... ?????????

 

[RuralGuy]

You might try a Requery on the SubForm itself. What you are describing could be the results of a form without any resords to display.
Form_frmSubformFA![SubFA].Form.Requery. How come you don't use the shorthand Me reference?

 

[Steff_DK]

Since I have a lot of forms open at the same time on different tabs, and some hanging in thin blue air (being open, but not to display) I just wanted to be sure that the code knew where to go ... -plus I find it easier for reading when debugging ... (in the maze of main form, subforms and subform's subform)

 

[Steff_DK]

Have tried requery on:

• Main form
• Sub form
• Tab ctl
• Subform window on Tab ctl

Still no luck

 

[WindSailor]

I haven't used subforms in a while, so I had to hit the books.
Try using the (!) 'bang' operator instead of the (.) Dot operator in your code in/for a subform.

 

[Steff_DK]

Okay, WindSailor - will give it a try

In the mean time here is the DB that causes grey hairs along with an example that works ...
Try going to the FA Log tab and hit the Edit/Save/Add new labels a couple of times ...
http://www.iamsteff.com/logbook.zip
http://www.iamsteff.com/Example.zip

-and some show off pics of yours truly
http://www.iamsteff.com/fly.htm

 

[RuralGuy]

Steff,
In your frmSubFormFA under Label1_Click() for "Edit" I replaced:
Form_frmIndex.Requery
with
Form_frmSubformFA![SubFA].Form.Requery

You should try it!

 

[WindSailor]

Looks like you got Steff going RG.

I found this in one of my books regarding syntax in subforms and thought I would post it, although I haven't tested it to see if it works or applies to Access 2003.
This is regarding (!) vs. (.) in subforms.

-----------
Using syntax in subforms you can never use a dot to separate a collection of objects from an object within that collection.

No go...

Forms.FormA.SubFormA
or
Forms!FormA!SubFormA.txtFullName

Go...

Forms!FormA!SubFormA!txtFullName
or
Forms!FormA.SubFormA!txtFullName
-------------

Again I haven't tested it, but I thought I would put it in here for future reference if someone was having problems with the syntax portion of their subforms.

 

[ChrisO]

“Using syntax in subforms you can never use a dot to separate a collection of objects from an object within that collection.”

I don’t know who said that but I think it is incorrect.

The generic reference could be: -
Me.SubFormControlName.Form.ControlName

Me has a collection of controls one of which is the subform control.
The subform control has a collection of properties one of which is the Source Object.
The Source Object is a string that specifies the name of the form contained in that control.
Form is a pointer to the form specified by the form name.
Being a pointer to type Form, Form has a collection of form name properties.
ControlName is a pointer to the control specified by its name.
Being a pointer to type Control Name its has a collection of type Control Name.

So what we are looking at are four serial pointers that is looking at the default property of ControlName, which is its Value.

If we wish to return something other than the default property we need to specify the property within that controls collection of properties. For example its BackColor property would be referred to as: -

Me.SubFormControlName.Form.ControlName.BackColor

That is when moving down the structure one position from Parent to Child.
If we want to move down two levels from Parent to Grand Child we need to add two more pointers as in: -

Me.SubFormControlName.Form.SubFormControlName.Form.ControlName.BackColor

So, when moving down the structure, we need to hard code the name(s) of the SubFormControl(s) and the name of the Control within that specific collection. The reason for this is that a Parent can have many children and a unique reference is needed.

When moving up the Child to Parent structure things are much easier. The reason for this is that a Child only has one Parent and since the Parent is a pointer to the Parent Form it need not be hard coded.

So: -

Me.ControlName.Parent

Returns a pointer to the controls Parent collection.

Me.ControlName.Parent.Name

Returns the Parent Name property.

Me.ControlName.Parent.Parent

Returns a pointer to the grand parent collection.

Me.ControlName.Parent.Parent.Name

Returns the name of the grand parent form.

And: -

Screen.ActiveControl.Parent.Parent.ActiveControl.Name

Returns the name of the subform control in which the active control resides.


Hope that helps.

Regards,
Chris.

 

[WindSailor]

Hi Chris,
That did help.

I should say the earlier statement was in regards to a control on a subform, although I wouldn’t associate a label as a control I thought I would include it just incase the requery didn’t work.

I should include the whole statement to make sure I didn’t perceive it wrong... and definitely open for comments since I don't work with subforms much.

----------Start----------

It’s very tempting to try to use dots (.) to separate the pieces of the subform syntax, but doing so doesn’t work. It’s terribly confusing, but it boils down to this: Access forms make a special case for controls on the form and expose them as properties of the form itself. You can’t delve deeper into the object hierarchy from a control referenced as a property; you can only retrieve and set properties of that control.

You can never use a dot to separate a collection of objects from an object within that collection. (The dot works for controls on forms only because or Access’ special handling.) Therefore, you can use code like this to reference a control:

Forms!frmNestedOrders.frmNestedOrderDetail

‘Or

Forms(“frmNestedOrders”).frmNestedOrderDetail

But you cannot use a dot to reference a form within the Forms collection. This code will fail, for example:

Forms.frmNestedORders.frmNestedOrderDetail

The same problem applies when you attempt to refer to controls within a subform – you’re not actually dealing with a property of the subfrom, so you can’t use a dot. For example, the following code will fail

Forms!frmNestedOrders!frmNestedOrderDetail.txtOrderAmount

But the following code works fine

Forms!frmNestedOrders!frmNestedOrderDetail!txtOrderAmount

As does this:

Forms!frmNestedOrders.frmNestedOrderDetail!txtOrderAmount

(This code works because frmNestedOrderDetail is a control on frmNestedOrders, and is there-fore handled as a property of the form.)

Just remember these rules:

• Dots (.) can only be used to preface properties or methods, not objects within collections. Access makes a special case for controls on forms: It creates properties of the form corresponding to each control on the form, so you can use a dot to preface references to these objects. Doing so can make your code run a bit faster, if it references these objects.

• A bang (!) turns into parentheses/quotes around and object internally, so there’s little reason to use it. In any case, you use bang or parentheses/quotes to refer to an object as an element of the collection containing that object.

-----------End------------

Actually that came from "Access 2002 Desktop Developers Handbook", page 396, and I haven't checked the errata. It also doesn't list or talk about using the 'Me.' preface in the subject.

This is a good discussion and I am all ears…

 

[ChrisO]

"Access 2002 Desktop Developers Handbook"
“Litwin Getz Gunderloy”

They are not speaking crap but may have been taken out of context.

If, as your post suggests, “It also doesn't list or talk about using the 'Me.' preface in the subject.” Then they are talking about absolute reference from a query and not as stated in this thread as relative addressing from behind a form. There is no mention of SQL in this thread, no mention of absolute addressing and therefore no mention of the absolute starting point of addressing the target.

Good programming standards seek the requirement to write code that can be relocated without alteration. Some operating systems require this to be effective at runtime. For such programs one tries to use relative code, code that is relative to some starting point, normally the PSP, a program starting point, that equates to a jump address to code starting point.

Me is a pointer to that structure, not only to the Form and/or Report but also to the code within which that call module it is contained.

Not the same thing.

Regards,
Chris.

 

[WindSailor]

Good point, thanks for the added insight.

 

[MarkK]

Generally, functionality specific to a particular object should be handled by that object. To this end, put your buttons, or in your case labels, on the subforms. Saves you a ton of work on the parent form, and you can use your subforms anywhere in the future, since they know how to handle themselves.

 

[Steff_DK]

Tried changing the . to ! and "Form_frmIndex.Requery" to "Form_frmSubformFA![SubFA].Form.Requery".

When I hit "Save" and "Edit" a couple of times on the FA log tab I get an error in the "Edit" department: It says that the object doesn't exist or was deleted and the highlights "Form_frmSubformFA![SubFA].Form.Requery".

If my DB no longer has the Form_frmSubformFA![SubFA] as an existing object that would explain why the form is left blank, but why doesn't the error occur at the line Form_frmSubformFA![SubFA].SourceObject = "frmFaLog" then???

Still confused ...

But grateful for all your help in this matter!

Steff

 

[ChrisO]

OK Steff this is what I would do.

But first a comment if I may?
There is already too much code that seems to have not been tested before more code was added.
Some of the additional code may cause other problems but who knows?

Suggestion…
Move backwards through the design process till it works correctly, but I think it is already too late for that method.

Create a new minimal database that fills the need to switch subforms as you require. Then test it until it works but only then add something else. When adding something else, make a new copy of the database and work on that. If you go too far and incur an error you can go back to the previous version. Since you are working behind a Form structure try to use the dot rather than the bang.

http://www.advisor.com/Articles.nsf/aid/BAROA06

Hope that helps and good luck.

Regards,
Chris.

 

[WindSailor]

Chris, I didn't get it to fail for me, did you?

 

[ChrisO]

Sure did under A2K

Also QryAccuFA and qryAccuMC queries generate a type mismatch error one of which seemed to imply the SQL string exceeded 2048 characters.

Query qrySumFATim also generates a type mismatch error, if you go into query design view you might see why I wasn’t prepared to track it down.

I do code, not queries, and would therefore leave it up to those that understand SQL.

I really do think that there is already too much, of both code and SQL, to do anything else but ask the OP to back up and rebuild.

Regards,
Chris.

 

[WindSailor]

WOW… ok… now I see.

Steff, you obviously spent some time writing those SQL statements.

This is with Access 2003; the errors are a little different.

Ok… going through this a little more thoroughly than what I did the first time...

Just running the queries by themselves…
qrySumFATim
qrySumICTim
qryTest_kan_slettes
Have errors listed in some of their columns for their results.

Queries…
qryAccuFA
qryAccuIC
QryAccuMC
When trying to view them in ‘Design View’, I come up with the error: The expression you entered exceeds the 1,024-character limit for the query design grid. Good or bad... I don't know, I haven't ran into that one before or wrote one that long before. Which also make one wonder where the limit is or should be.

Originally editing items on the ‘FA Log’ tab and saving them a multitude of times showed no errors. But adding a new record, it comes up with ‘Data type mismatch in criteria expression’. Once you add a new record to ‘frmFALog’ it generates or invokes the error. To address the error I had to give ‘Start’ and ‘Stop’ in table ‘tblLogFA' a default value of ‘0’. Then I had the option to edit those values or variables after I saved the new record.

I like your forms, they look great.