Please Help

J

Jaybird

Apologies to everyone for the repost... I'm at my wits end on this. I hope
someone can help me.

This is not how a properly designed database is supposed to work, but our
database is sort of cobbled together and I'm kind of forced to go with the
flow. I have a main form frmInvoice. On it are two subforms, sbfInvoice and
sbfHDRPLAT. sbfInvoice is, as you might guess, a detail of the frmInvoice.
sbfHDRPLAT actually has a sub-subform called sbfLINPLAT that contains the
combobox [KEYNUM] and a textbox [LINETOTL]. Normally, the value of
[LINETOTL] is pretty straightforward, but when [KEYNUM] is equal to
"LINEITEM" I need the textbox [LINETOTL] to derive its value from a
calculation in sbfInvoice. The text box where this calculation takes place
is called txtTtlChrgs ([txtTtlChargs]=Sum([Charge])). The syntax of subform
references always throws me. Can anybody help point me in the right
direction? Corporate requires that this sum of charges be posted to our ERP,
so I'm kind of stuck. frmInvoice is designed to be an Invoice that
references Orders already in our database so that we don't have to use some
secondary or third party software. The subform controls have the same names
as the
subforms within them...

This is my code for the subform control sbfLINPLAT in the On Current event:

Private Sub Form_Current()

If IsNull([KEYNUM]) Then Exit Sub
If [KEYNUM] = "SURCHGE" Then [ACCTLAB] = "SC"
[ACCTPRE] = 3052
If [KEYNUM] = "CERT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
If [KEYNUM] = "LINEITEM" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs
If [KEYNUM] = "INSPECT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "STRAIGHT" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3050
If [KEYNUM] = "AGING" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3058
If [KEYNUM] = "OTHER" Then [ACCTLAB] = "OT"
[ACCTPRE] = 3510
If [KEYNUM] = "SOLUTION" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "FREIGHT" Then [ACCTLAB] = "FR"
[ACCTPRE] = 3521

Upon further reflection, it occurs to me that the On Current event is not
where this should occur. However, I'm not sure where it should go. If I
feed the value of [LINETOTL] in the AfterUpdate event of [txtTtlChrgs] it
seems logical that I would get the most up to date information, since those
calculations take place after most other events. But, I can't seem to get it
to work. Can anybody help me?
 
G

George Nicholson

Since you are using a single-line If..Then construct, you have a lot of code
lines that are not connected to conditions and are always executing.

The result is that, as written, you are actually changing the same
field/control several times. You are ending up with [ACCTPRE] = 3521 and
Me.LINETOTL = 10, regardless of KEYNUM's value, since those are the last
values assigned to them. I suspect that is not your intention.

Below is one approach to multiple conditions:

Select Case [KEYNUM]
Case Null
Exit Sub
Case "SURCHGE"
[ACCTLAB] = "SC"
[ACCTPRE] = 3052
Case "CERT"
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
Case "LINEITEM", "INSPECT", "SOLUTION"
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
..........(other Cases)
..........
Case "FREIGHT"
[ACCTLAB] = "FR"
[ACCTPRE] = 3521
Case Else
' Do nothing or:
MsgBox "Unexpected KEYNUM Value: " & [KEYNUM]
End Select


HTH


Jaybird said:
Apologies to everyone for the repost... I'm at my wits end on this. I
hope
someone can help me.

This is not how a properly designed database is supposed to work, but our
database is sort of cobbled together and I'm kind of forced to go with the
flow. I have a main form frmInvoice. On it are two subforms, sbfInvoice
and
sbfHDRPLAT. sbfInvoice is, as you might guess, a detail of the frmInvoice.
sbfHDRPLAT actually has a sub-subform called sbfLINPLAT that contains the
combobox [KEYNUM] and a textbox [LINETOTL]. Normally, the value of
[LINETOTL] is pretty straightforward, but when [KEYNUM] is equal to
"LINEITEM" I need the textbox [LINETOTL] to derive its value from a
calculation in sbfInvoice. The text box where this calculation takes place
is called txtTtlChrgs ([txtTtlChargs]=Sum([Charge])). The syntax of
subform
references always throws me. Can anybody help point me in the right
direction? Corporate requires that this sum of charges be posted to our
ERP,
so I'm kind of stuck. frmInvoice is designed to be an Invoice that
references Orders already in our database so that we don't have to use
some
secondary or third party software. The subform controls have the same
names
as the
subforms within them...

This is my code for the subform control sbfLINPLAT in the On Current
event:

Private Sub Form_Current()

If IsNull([KEYNUM]) Then Exit Sub
If [KEYNUM] = "SURCHGE" Then [ACCTLAB] = "SC"
[ACCTPRE] = 3052
If [KEYNUM] = "CERT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
If [KEYNUM] = "LINEITEM" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs
If [KEYNUM] = "INSPECT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "STRAIGHT" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3050
If [KEYNUM] = "AGING" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3058
If [KEYNUM] = "OTHER" Then [ACCTLAB] = "OT"
[ACCTPRE] = 3510
If [KEYNUM] = "SOLUTION" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "FREIGHT" Then [ACCTLAB] = "FR"
[ACCTPRE] = 3521

Upon further reflection, it occurs to me that the On Current event is not
where this should occur. However, I'm not sure where it should go. If I
feed the value of [LINETOTL] in the AfterUpdate event of [txtTtlChrgs] it
seems logical that I would get the most up to date information, since
those
calculations take place after most other events. But, I can't seem to get
it
to work. Can anybody help me?
Click to expand...
 
J

Jaybird

George,

Thank you for your comments. Of course, you are right... I knew that I was
doing it wrong, but everytime I tried to add an else or elseif statement, it
gave me an error. Turns out, my attempts to convert to a block if/then/else
format were half baked. Here's what I finally came up with. It appears to
be doing the trick.

Private Sub Form_Current()
On Error Resume Next
If IsNull(Me![KEYNUM]) Then
Exit Sub
ElseIf Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "CERT" Then
Me![ACCTLAB] = "AL"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 10
ElseIf Me![KEYNUM] = "LINEITEM" Then
Me![ACCTLAB] = "AL"
Me![ACCTPRE] = 3052
Me![LINETOTL] = Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs
ElseIf Me![KEYNUM] = "INSPECT" Then
Me![ACCTLAB] = "AL"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "STRAIGHT" Then
Me![ACCTLAB] = "HT"
Me![ACCTPRE] = 3050
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "AGING" Then
Me![ACCTLAB] = "HT"
Me![ACCTPRE] = 3058
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "OTHER" Then
Me![ACCTLAB] = "OT"
Me![ACCTPRE] = 3510
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "SOLUTION" Then
Me![ACCTLAB] = "AL"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "FREIGHT" Then
Me![ACCTLAB] = "FR"
Me![ACCTPRE] = 3521
Me![LINETOTL] = 0
Else
'do nothing
End If

End Sub
--
Why are you asking me? I dont know what Im doing!

Jaybird


George Nicholson said:
Since you are using a single-line If..Then construct, you have a lot of code
lines that are not connected to conditions and are always executing.

The result is that, as written, you are actually changing the same
field/control several times. You are ending up with [ACCTPRE] = 3521 and
Me.LINETOTL = 10, regardless of KEYNUM's value, since those are the last
values assigned to them. I suspect that is not your intention.

Below is one approach to multiple conditions:

Select Case [KEYNUM]
Case Null
Exit Sub
Case "SURCHGE"
[ACCTLAB] = "SC"
[ACCTPRE] = 3052
Case "CERT"
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
Case "LINEITEM", "INSPECT", "SOLUTION"
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
..........(other Cases)
..........
Case "FREIGHT"
[ACCTLAB] = "FR"
[ACCTPRE] = 3521
Case Else
' Do nothing or:
MsgBox "Unexpected KEYNUM Value: " & [KEYNUM]
End Select


HTH


Jaybird said:
Apologies to everyone for the repost... I'm at my wits end on this. I
hope
someone can help me.

This is not how a properly designed database is supposed to work, but our
database is sort of cobbled together and I'm kind of forced to go with the
flow. I have a main form frmInvoice. On it are two subforms, sbfInvoice
and
sbfHDRPLAT. sbfInvoice is, as you might guess, a detail of the frmInvoice.
sbfHDRPLAT actually has a sub-subform called sbfLINPLAT that contains the
combobox [KEYNUM] and a textbox [LINETOTL]. Normally, the value of
[LINETOTL] is pretty straightforward, but when [KEYNUM] is equal to
"LINEITEM" I need the textbox [LINETOTL] to derive its value from a
calculation in sbfInvoice. The text box where this calculation takes place
is called txtTtlChrgs ([txtTtlChargs]=Sum([Charge])). The syntax of
subform
references always throws me. Can anybody help point me in the right
direction? Corporate requires that this sum of charges be posted to our
ERP,
so I'm kind of stuck. frmInvoice is designed to be an Invoice that
references Orders already in our database so that we don't have to use
some
secondary or third party software. The subform controls have the same
names
as the
subforms within them...

This is my code for the subform control sbfLINPLAT in the On Current
event:

Private Sub Form_Current()

If IsNull([KEYNUM]) Then Exit Sub
If [KEYNUM] = "SURCHGE" Then [ACCTLAB] = "SC"
[ACCTPRE] = 3052
If [KEYNUM] = "CERT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
If [KEYNUM] = "LINEITEM" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs
If [KEYNUM] = "INSPECT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "STRAIGHT" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3050
If [KEYNUM] = "AGING" Then [ACCTLAB] = "HT"
[ACCTPRE] = 3058
If [KEYNUM] = "OTHER" Then [ACCTLAB] = "OT"
[ACCTPRE] = 3510
If [KEYNUM] = "SOLUTION" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052
If [KEYNUM] = "FREIGHT" Then [ACCTLAB] = "FR"
[ACCTPRE] = 3521

Upon further reflection, it occurs to me that the On Current event is not
where this should occur. However, I'm not sure where it should go. If I
feed the value of [LINETOTL] in the AfterUpdate event of [txtTtlChrgs] it
seems logical that I would get the most up to date information, since
those
calculations take place after most other events. But, I can't seem to get
it
to work. Can anybody help me?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Correct Event, please 15
Saving the value of a Calculated Text Box 3
How can I relate these tables? 3
Subforms Properly Related? 0
Subform Reference 6
form is not updatable 0
Syntax for Refreshing Control on Sub-Subform 6
Loading subform with data from main form 6

Top