Correct Event, please

[Guest]

If I want to make a the value of a textbox dependent upon the selection of a
combobox on the same record of a continuous form, what is the proper event to
fire this so that the records remain independent of one another. Everything
I've tried so far changes more than one record. Here's an example of the
code I'm using:

Private Sub Form_Current()
On Error Resume Next

If IsNull([KEYNUM]) Then Exit Sub

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

End Sub

I have a tendency to be unclear about what I'm asking, so please let me know
if you need more detail. I can't tell if my if/then structure is to blame,
if I'm trying to use the wrong event, or if my form isn't refreshing
properly. I've tried firing this up in the After Update event of my combobox
and the Before Update event of my textbox, but I can't seem to get it right.
It's funny, because the textboxes [ACCTLAB], and [ACCTPRE] update just fine
depending on the value of the combobox. But the value of [LINETOTL] tends to
be "sticky". That is, it stays in the textbox even when the value of the
combobox [KEYNUM] changes and is requeried and/or refreshed. I think this is
because it has already been passed on to the underlying table and my changes
do not overwrite it. Please give me a clue!

 

[Stefan Hoffmann]

hi Jay,

If I want to make a the value of a textbox dependent upon the selection of a
combobox on the same record of a continuous form, what is the proper event to
fire this so that the records remain independent of one another. Everything
I've tried so far changes more than one record.

This is not possible by design.

Please give me a clue!

You can only address all controls or the actual field, e.g.
txtValue.BackColor = clRed will colorize all the textboxes or you may
use Me![fieldBehindControl] which will only address the actual value.




mfG
--> stefan <--

 

[Guest]

Stefan,

Are you saying that my idea is wrong, or that I am going about it the wrong
way?
--
Why are you asking me? I dont know what Im doing!

Jaybird

hi Jay,

If I want to make a the value of a textbox dependent upon the selection of a
combobox on the same record of a continuous form, what is the proper event to
fire this so that the records remain independent of one another. Everything
I've tried so far changes more than one record.

This is not possible by design.

Please give me a clue!

You can only address all controls or the actual field, e.g.
txtValue.BackColor = clRed will colorize all the textboxes or you may
use Me![fieldBehindControl] which will only address the actual value.




mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jay,

Are you saying that my idea is wrong, or that I am going about it the wrong
way?

Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--

 

[George Nicholson]

Don't know if this will help but:

If [KEYNUM] = "CERT" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052 ' ** Always executes
Me.LINETOTL = 10 ' ** Always executes
If [KEYNUM] = "LINEITEM" Then [ACCTLAB] = "AL"
[ACCTPRE] = 3052 ' ** Always executes
Me.LINETOTL = Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs ' **
Always executes

Since you are using a single-line If..Then construct, the 4 lines marked
above are not part of any condition and will *always* execute regardless of
KEYNUM's value. They are in no way "attached" to the conditional statement
preceding them.

If you intend them to be part of the condition, there are several
alternatives. Below is one. A "Select Case" construct would be another, and
similar to this:

If [KEYNUM] = "CERT" Then
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = 10
ElseIf [KEYNUM] = "LINEITEM" Then
[ACCTLAB] = "AL"
[ACCTPRE] = 3052
Me.LINETOTL = Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs
Else
' Do nothing
End If

HTH,

If I want to make a the value of a textbox dependent upon the selection of
a
combobox on the same record of a continuous form, what is the proper event
to
fire this so that the records remain independent of one another.
Everything
I've tried so far changes more than one record. Here's an example of the
code I'm using:

Private Sub Form_Current()
On Error Resume Next

If IsNull([KEYNUM]) Then Exit Sub

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

End Sub

I have a tendency to be unclear about what I'm asking, so please let me
know
if you need more detail. I can't tell if my if/then structure is to
blame,
if I'm trying to use the wrong event, or if my form isn't refreshing
properly. I've tried firing this up in the After Update event of my
combobox
and the Before Update event of my textbox, but I can't seem to get it
right.
It's funny, because the textboxes [ACCTLAB], and [ACCTPRE] update just
fine
depending on the value of the combobox. But the value of [LINETOTL] tends
to
be "sticky". That is, it stays in the textbox even when the value of the
combobox [KEYNUM] changes and is requeried and/or refreshed. I think this
is
because it has already been passed on to the underlying table and my
changes
do not overwrite it. Please give me a clue!

 

[Guest]

Stefan,

I have done as you suggested, renaming my text boxes on the form. I was
unaware of my confusion concerning the names of the fields vs the names of
the textboxes. If I understand correctly, cboKEYNUM would refer to the value
of the combobox on the form, where Me![KEYNUM] would refer to the value of
the field being stored in the underlying table? In any case, I have renamed
my form text boxes. I have also attempted to incorporate George's suggestion
of correcting my If, Then, Else statements. However, I am still on the wrong
track, I think. Here is my revised code:

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

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

Jaybird

hi Jay,

Are you saying that my idea is wrong, or that I am going about it the wrong
way?

Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jay,

I have done as you suggested, renaming my text boxes on the form. I was
unaware of my confusion concerning the names of the fields vs the names of
the textboxes. If I understand correctly, cboKEYNUM would refer to the value
of the combobox on the form, where Me![KEYNUM] would refer to the value of
the field being stored in the underlying table?

Yes, that's correct.

In any case, I have renamed
my form text boxes. I have also attempted to incorporate George's suggestion
of correcting my If, Then, Else statements. However, I am still on the wrong
track, I think. Here is my revised code:

Private Sub Form_Current()

This event is only called when a record is made the current one. It is
not called when you change the value of a combo box, as you mentioned in
your first mail.

So the best way here, is to calculate the values in the forms record source.

Otherwise, if you like to change the values as a reaction on the combo
box change, you must use the event of this control:

Private Sub cboYourCombo_Change

If Not IsNull(cboYourCombo.Value) Then
Select Case cboYourCombo.Value
Case Is = "SURCHGE"
Me![ACCTLAB] = 0
Case Is = "LINEITEM"
Me![ACCTLAB] = _
Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs
End Select
End If

End Sub

ElseIf Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTLAB] = 3052
Me![ACCTLAB] = 0

Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--

 

[Guest]

Ummm... I guess I got a little happy with the find and replace tool. This
is the way the code is supposed to look. It appears to be working correctly
now. Thank you so much for all your help.

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

hi Jay,

Are you saying that my idea is wrong, or that I am going about it the wrong
way?

Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--

 

[Guest]

Ah, once again my ignorance is on display! Having fixed (?) the other issues
with this code, are you now suggesting that I change the event to the on
change event for the cboKEYNUM combo box? I notice, that while the code as
it stands does do what I intend, but I need to requery the form when I make
changes or the changes will not be visible. Will this cause the results to
be more responsive? I have zero experience in opening and editing recordsets
it VBA. Allen Browne has demonstrated the wisdom and convenience of using
this method, but I don't have any reference material to work from. So,
barring that option, I will try to move the code to the On Change event and
see what happens.
--
Why are you asking me? I dont know what Im doing!

Jaybird

hi Jay,

I have done as you suggested, renaming my text boxes on the form. I was
unaware of my confusion concerning the names of the fields vs the names of
the textboxes. If I understand correctly, cboKEYNUM would refer to the value
of the combobox on the form, where Me![KEYNUM] would refer to the value of
the field being stored in the underlying table?

Yes, that's correct.

In any case, I have renamed
my form text boxes. I have also attempted to incorporate George's suggestion
of correcting my If, Then, Else statements. However, I am still on the wrong
track, I think. Here is my revised code:

Private Sub Form_Current()

This event is only called when a record is made the current one. It is
not called when you change the value of a combo box, as you mentioned in
your first mail.

So the best way here, is to calculate the values in the forms record source.

Otherwise, if you like to change the values as a reaction on the combo
box change, you must use the event of this control:

Private Sub cboYourCombo_Change

If Not IsNull(cboYourCombo.Value) Then
Select Case cboYourCombo.Value
Case Is = "SURCHGE"
Me![ACCTLAB] = 0
Case Is = "LINEITEM"
Me![ACCTLAB] = _
Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs
End Select
End If

End Sub

ElseIf Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTLAB] = 3052
Me![ACCTLAB] = 0

Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--

 

[Guest]

Brilliant!! I did as you suggested and placed a DoCmd.Requery command at the
end and it works perfectly! Very responsive. I appreciate everything you've
done, Stefan.
--
Why are you asking me? I dont know what Im doing!

Jaybird

hi Jay,

I have done as you suggested, renaming my text boxes on the form. I was
unaware of my confusion concerning the names of the fields vs the names of
the textboxes. If I understand correctly, cboKEYNUM would refer to the value
of the combobox on the form, where Me![KEYNUM] would refer to the value of
the field being stored in the underlying table?

Yes, that's correct.

In any case, I have renamed
my form text boxes. I have also attempted to incorporate George's suggestion
of correcting my If, Then, Else statements. However, I am still on the wrong
track, I think. Here is my revised code:

Private Sub Form_Current()

This event is only called when a record is made the current one. It is
not called when you change the value of a combo box, as you mentioned in
your first mail.

So the best way here, is to calculate the values in the forms record source.

Otherwise, if you like to change the values as a reaction on the combo
box change, you must use the event of this control:

Private Sub cboYourCombo_Change

If Not IsNull(cboYourCombo.Value) Then
Select Case cboYourCombo.Value
Case Is = "SURCHGE"
Me![ACCTLAB] = 0
Case Is = "LINEITEM"
Me![ACCTLAB] = _
Forms.frmInvoice.sbfInvoice.Form.txtTtlChrgs
End Select
End If

End Sub

ElseIf Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTLAB] = 3052
Me![ACCTLAB] = 0

Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi,

Ah, once again my ignorance is on display!

Don't called it ignorance. Everybody has to start with only little
knowledge and wisdom.

Having fixed (?) the other issues
with this code, are you now suggesting that I change the event to the on
change event for the cboKEYNUM combo box?

Yes, I haven't realized before my last post, so shame upon me too.

I notice, that while the code as
it stands does do what I intend, but I need to requery the form when I make
changes or the changes will not be visible. Will this cause the results to
be more responsive?

Yes, this will be the result case.

I will try to move the code to the On Change event and
see what happens.

Do this.


mfG
--> stefan <--

 

[BruceM]

Note the Select Case function that Stefan illustrated. It would be easier
to work with, I think. If you are going to use ElseIf I believe you need to
have a plain If first (I used Not IsNull, as Stefan did with Select Case, to
simplify the coding):

If Not IsNull(Me![KEYNUM]) Then
If Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "CERT" Then
etc.
End If
End If

You don't need the line:
Else
' do nothing
although if you prefer to have a placeholder you can keep it.

Also note that the syntax for referring to a subform control is:
Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs

For more information on the subject of bang(!) vs. dot:
http://doc.advisor.com/doc/05352
A Google search will turn up additional information, I expect.

Ummm... I guess I got a little happy with the find and replace tool.
This
is the way the code is supposed to look. It appears to be working
correctly
now. Thank you so much for all your help.

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

hi Jay,

Are you saying that my idea is wrong, or that I am going about it the
wrong
way?

Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--

 

[John W. Vinson]

I will try to move the code to the On Change event and
see what happens.

I'd suggest the AfterUpdate event instead - Change fires at *every keystroke*,
Change only after a row has been selected.

John W. Vinson [MVP]

 

[Guest]

Bruce,

Thank you for your comments. The Select Case method confuses me most of the
time, although I'm not entirely immune using the if/then method . The
code now works as I intend, so I am reluctant to change it. I'm aware of
many of my shortcomings, but still have to find ways around them. Often, my
understanding of the way things are supposed to work is incomplete.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Note the Select Case function that Stefan illustrated. It would be easier
to work with, I think. If you are going to use ElseIf I believe you need to
have a plain If first (I used Not IsNull, as Stefan did with Select Case, to
simplify the coding):

If Not IsNull(Me![KEYNUM]) Then
If Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "CERT" Then
etc.
End If
End If

You don't need the line:
Else
' do nothing
although if you prefer to have a placeholder you can keep it.

Also note that the syntax for referring to a subform control is:
Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs

For more information on the subject of bang(!) vs. dot:
http://doc.advisor.com/doc/05352
A Google search will turn up additional information, I expect.

Ummm... I guess I got a little happy with the find and replace tool.
This
is the way the code is supposed to look. It appears to be working
correctly
now. Thank you so much for all your help.

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

hi Jay,

Jaybird wrote:
Are you saying that my idea is wrong, or that I am going about it the
wrong
way?
Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--

 

[Guest]

Thanks for posting, John! The sequence and (con)sequence of events has me
confused. I can't seem to find a resource to set me straight.
--
Why are you asking me? I dont know what Im doing!

Jaybird

 

[BruceM]

As I said, you can use the ElseIf construction. I tend to find it
cumbersome for more than a few options.

Select Case would go like this:

If Not IsNull(Me![KEYNUM]) Then
Select Case Me.KEYNUM
Case = "SURCHGE"
Me.ACCTLAB = "SC"
Me.ACCTPRE = 3052
Me.LINETOTL = 0
Case = "CERT"
Me.ACCTLAB = "AL"
Me.ACCTPRE = 3052
Me.LINETOTL = 10
Case = "LINEITEM"
Me.ACCTLAB = "AL"
Me.ACCTPRE = 3052
Me.LINETOTL = Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs
End Select
End If

As opposed to:
If Not IsNull(Me![KEYNUM]) Then
If 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"
Me.ACCTLAB = "AL"
Me.ACCTPRE = 3052
Me.LINETOTL = Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs
End If
End If

I just find the first easier to work with. In this instance there is little
difference, but if you come across code where a series of If or ElseIf
statements are proving unwieldy, take another look at Select Case. No big
deal either way.

Bruce,

Thank you for your comments. The Select Case method confuses me most of
the
time, although I'm not entirely immune using the if/then method . The
code now works as I intend, so I am reluctant to change it. I'm aware of
many of my shortcomings, but still have to find ways around them. Often,
my
understanding of the way things are supposed to work is incomplete.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Note the Select Case function that Stefan illustrated. It would be
easier
to work with, I think. If you are going to use ElseIf I believe you need
to
have a plain If first (I used Not IsNull, as Stefan did with Select Case,
to
simplify the coding):

If Not IsNull(Me![KEYNUM]) Then
If Me![KEYNUM] = "SURCHGE" Then
Me![ACCTLAB] = "SC"
Me![ACCTPRE] = 3052
Me![LINETOTL] = 0
ElseIf Me![KEYNUM] = "CERT" Then
etc.
End If
End If

You don't need the line:
Else
' do nothing
although if you prefer to have a placeholder you can keep it.

Also note that the syntax for referring to a subform control is:
Forms!frmInvoice!sbfInvoice.Form!txtTtlChrgs

For more information on the subject of bang(!) vs. dot:
http://doc.advisor.com/doc/05352
A Google search will turn up additional information, I expect.

Ummm... I guess I got a little happy with the find and replace tool.
This
is the way the code is supposed to look. It appears to be working
correctly
now. Thank you so much for all your help.

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


:

hi Jay,

Jaybird wrote:
Are you saying that my idea is wrong, or that I am going about it
the
wrong
way?
Both in some kind of way.

Rename your controls first, so that the have a different name than the
field behind them, e.g. txtKeynum for the field KEYNUM. Then you can
assign in the on current event with Me![KEYNUM] a value to the field.
You cannot assign it to the textbox.

http://www.mvps.org/access/forms/frm0024.htm
http://www.mvps.org/access/forms/frm0047.htm
http://www.databasedev.co.uk/conditional_formatting.html


mfG
--> stefan <--