Need Help with Stephen Lebans "justification" code used with sub-sub-forms

  • Thread starter Thread starter Bob Howard
  • Start date Start date
B

Bob Howard

I have a list box in the subform of a subform of a form (I know ... I'm
crazy!).

Anyway, I want to right-justify the third column. I built an Access Query
for this as it was too complicated to build by hand and anyway I didn't know
how to build it manually. The Query joins two tables. I put the first two
columns in the Query. For the third column (the "amount" field), I need to
insert something like:

RightJustifiedAmount: JustifyString("form name","list box control name",
.[amount], 3, False, "subform control within subform control")

.... assuming
.[amount] is where the amount field is coming from ...

But I'm a bit confused on how to specify the form's name, and I'm totally
clueless on how to specify the subform within the subform.

The form is named [Deposit Batch Process Form - Batches]

The control on the form that contains the higher level subform is
[Child-Items]

The control on the higher level subform that contains the lower level
subform is [Child-Splits]

The control name of the list box on the lower level subform is [Split List
Box]

Does anyone know how to reflect this in the Query?

Thanks for the help.... (help!)

Bob (@Martureo.Org)
 
Got it!

I went into the module that's distributed as part of "Justification" and
replaced the test for a subform with the following code:

If Len(Sform & vbNullString) = 0 Then
Set UserForm = Forms(myform)
Else
If Len(SSform & vbNullString) = 0 Then
Set UserForm = Forms(myform).Controls(Sform).Form
Else
Set UserForm = Forms(myform).Controls(Sform).Controls(SSform).Form
End If
End If

This added an additional parameter (SSform) to the function.

So with the control on a subform of a subform, the column in the query now
looks like this:

AlignedAmount: JustifyString("Deposit Batch Process Form - Batches","Split
List Box",[Deposit Split Table].[Split
Amount],4,False,"Child-Items","Child-Splits")

Hopefully, Stephen Lebans will want to incorporate something like this in a
subsequent release....

Bob (@Martureo.Org)
 
I usually don't bother updating code that is so old but with your
permission I will add your post to the relevant Web page.
Let me know.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Bob Howard said:
Got it!

I went into the module that's distributed as part of "Justification" and
replaced the test for a subform with the following code:

If Len(Sform & vbNullString) = 0 Then
Set UserForm = Forms(myform)
Else
If Len(SSform & vbNullString) = 0 Then
Set UserForm = Forms(myform).Controls(Sform).Form
Else
Set UserForm = Forms(myform).Controls(Sform).Controls(SSform).Form
End If
End If

This added an additional parameter (SSform) to the function.

So with the control on a subform of a subform, the column in the query now
looks like this:

AlignedAmount: JustifyString("Deposit Batch Process Form - Batches","Split
List Box",[Deposit Split Table].[Split
Amount],4,False,"Child-Items","Child-Splits")

Hopefully, Stephen Lebans will want to incorporate something like this in a
subsequent release....

Bob (@Martureo.Org)

Bob Howard said:
I have a list box in the subform of a subform of a form (I know ... I'm
crazy!).

Anyway, I want to right-justify the third column. I built an Access Query
for this as it was too complicated to build by hand and anyway I
Click to expand...
didn't
know
how to build it manually. The Query joins two tables. I put the
Click to expand...
first
two
columns in the Query. For the third column (the "amount" field), I
Click to expand...
need
to
insert something like:

RightJustifiedAmount: JustifyString("form name","list box control name",
.[amount], 3, False, "subform control within subform control")

... assuming
.[amount] is where the amount field is coming from ...

But I'm a bit confused on how to specify the form's name, and I'm totally
clueless on how to specify the subform within the subform.

The form is named [Deposit Batch Process Form - Batches]

The control on the form that contains the higher level subform is
[Child-Items]

The control on the higher level subform that contains the lower level
subform is [Child-Splits]

The control name of the list box on the lower level subform is [Split List
Box]

Does anyone know how to reflect this in the Query?

Thanks for the help.... (help!)

Bob (@Martureo.Org)
Click to expand...
Click to expand...
 
That's fine. Thanks very much for all the help, by the way. I also use
MouseHook and am delighted with both!

After making the change noted on the forum, I made another change to add an
optional 8th parm which is a Format string used to popssibly format the
result if the control is a number or a date:

In the Function header as the 8th parameter inside the parentheses, I added:

Optional myformat As String = ""

In the body of the code, I replaced:

Case 1 To 6, 7
' It's a number(1-6)
' It's a date (7)
strText = Str$(myfield)

with:

Case 1 To 6, 7
' It's a number(1-6)
' It's a date (7)
If Len(myformat & vbNullString) = 0 Then
' no format passed from the query (convert to a string)
strText = Str$(myfield)
Else
' use format passed from the query
strText = Format(myfield, myformat)
End If

In my particular case, the field is dollars and cents that I want formatted
without the dollar sign, so I simply pass "Standard" as the format
string --- Access interprets that accordingly.

Thanks again!

Your stuff is awesome!

Bob (@Martureo.Org)

Stephen Lebans said:
I usually don't bother updating code that is so old but with your
permission I will add your post to the relevant Web page.
Let me know.

--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.


Bob Howard said:
Got it!

I went into the module that's distributed as part of "Justification" and
replaced the test for a subform with the following code:

If Len(Sform & vbNullString) = 0 Then
Set UserForm = Forms(myform)
Else
If Len(SSform & vbNullString) = 0 Then
Set UserForm = Forms(myform).Controls(Sform).Form
Else
Set UserForm = Forms(myform).Controls(Sform).Controls(SSform).Form
End If
End If

This added an additional parameter (SSform) to the function.

So with the control on a subform of a subform, the column in the query now
looks like this:

AlignedAmount: JustifyString("Deposit Batch Process Form - Batches","Split
List Box",[Deposit Split Table].[Split
Amount],4,False,"Child-Items","Child-Splits")

Hopefully, Stephen Lebans will want to incorporate something like this in a
subsequent release....

Bob (@Martureo.Org)

Bob Howard said:
I have a list box in the subform of a subform of a form (I know ... I'm
crazy!).

Anyway, I want to right-justify the third column. I built an Access Query
for this as it was too complicated to build by hand and anyway I
Click to expand...
didn't
know
how to build it manually. The Query joins two tables. I put the
Click to expand...
first
two
columns in the Query. For the third column (the "amount" field), I
Click to expand...
need
to
insert something like:

RightJustifiedAmount: JustifyString("form name","list box control name",
.[amount], 3, False, "subform control within subform control")

... assuming
.[amount] is where the amount field is coming from ...

But I'm a bit confused on how to specify the form's name, and I'm totally
clueless on how to specify the subform within the subform.

The form is named [Deposit Batch Process Form - Batches]

The control on the form that contains the higher level subform is
[Child-Items]

The control on the higher level subform that contains the lower level
subform is [Child-Splits]

The control name of the list box on the lower level subform is [Split List
Box]

Does anyone know how to reflect this in the Query?

Thanks for the help.... (help!)

Bob (@Martureo.Org)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top