Correct Event, please

J

Jaybird

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!
 
S

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.
Click to expand...
This is not possible by design.
Please give me a clue!
Click to expand...
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 <--
 
J

Jaybird

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


Stefan Hoffmann said:
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.
Click to expand...
This is not possible by design.
Please give me a clue!
Click to expand...
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 <--
Click to expand...
 
S

Stefan Hoffmann

hi Jay,
Are you saying that my idea is wrong, or that I am going about it the wrong
way?
Click to expand...
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 <--
 
G

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
Click to expand...

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,



Jaybird said:
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!
Click to expand...
 
J

Jaybird

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


Stefan Hoffmann said:
hi Jay,
Are you saying that my idea is wrong, or that I am going about it the wrong
way?
Click to expand...
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 <--
Click to expand...
 
S

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?
Click to expand...
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:
Click to expand...
Private Sub Form_Current()
Click to expand...
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
Click to expand...
Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--
 
J

Jaybird

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


Stefan Hoffmann said:
hi Jay,
Are you saying that my idea is wrong, or that I am going about it the wrong
way?
Click to expand...
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 <--
Click to expand...
 
J

Jaybird

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


Stefan Hoffmann said:
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?
Click to expand...
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:
Click to expand...
Private Sub Form_Current()
Click to expand...
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
Click to expand...
Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--
Click to expand...
 
J

Jaybird

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


Stefan Hoffmann said:
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?
Click to expand...
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:
Click to expand...
Private Sub Form_Current()
Click to expand...
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
Click to expand...
Each line would overwrite the line before, resulting in a final value of
0 for Me![ACCTLAB]


mfG
--> stefan <--
Click to expand...
 
S

Stefan Hoffmann

hi,
Ah, once again my ignorance is on display!
Click to expand...
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?
Click to expand...
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?
Click to expand...
Yes, this will be the result case.
I will try to move the code to the On Change event and
see what happens.
Click to expand...
Do this.


mfG
--> stefan <--
 
B

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.

Jaybird said:
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


Stefan Hoffmann said:
hi Jay,
Are you saying that my idea is wrong, or that I am going about it the
wrong
way?
Click to expand...
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 <--
Click to expand...
Click to expand...
 
J

John W. Vinson

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

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

John W. Vinson [MVP]
 
J

Jaybird

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


BruceM said:
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.

Jaybird said:
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


Stefan Hoffmann said:
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 <--
Click to expand...
Click to expand...
Click to expand...
 
J

Jaybird

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
 
B

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.

Jaybird said:
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


BruceM said:
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.

Jaybird said:
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 <--
Click to expand...
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

Please Help 2
Saving the value of a Calculated Text Box 3
How can I relate these tables? 3
VBA Coding Help for Beginner 0
Drop down list control value in vba 2
Sub projects added as read only in Master file which are saved on sharepoint 1
NotInList combobox AfterUpdate procedure not working 4
Making a macro only work on certain files 2

Top