Repeat code on after Update

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following code in one of my fields:

Private Sub InvDesc_AfterUpdate()
If InvDesc.Value = "Deliveries" Then
AcctCode.Value = "10025"
End If

End Sub

This works fine except i need to know if there is a way to repeat the same
effect since i have about 10 descriptions with each having an acct code. How
can this be done?
 
Use a Select Case structure:

Private Sub InvDesc_AfterUpdate()
Select Case Me.InvDesc

Case "Deliveries"
Me.AcctCode = "10025"
Case "SomethingElse"
Me.AcctCode = "99999"
Case Else
'-- We've missed something
End Select

End Sub
 
I am using the following code in one of my fields:

Private Sub InvDesc_AfterUpdate()
If InvDesc.Value = "Deliveries" Then
AcctCode.Value = "10025"
End If

End Sub

This works fine except i need to know if there is a way to repeat the same
effect since i have about 10 descriptions with each having an acct code. How
can this be done?
Click to expand...

Ummm... sounds like a possible data normalization problem. If InvDesc and
AcctCode each imply the other, I wonder why - or if - you need both fields in
your table?

What are these fields? How are they related? Do you have a table of AcctCodes,
and does it contain an InvDesc field?

You can *do* this with a SELECT CASE block:

Private Sub InvDesc_AfterUpdate()
Select Case InvDesc ' you don't need the Value property, it's the default
Case "Deliveries"
AcctCode = "10025"
Case "Receiving"
AcctCode = "10020"
Case "Annoying the Hired Help"
AcctCode = "99999"
Case Else
AcctCode = Null
End Select
End Sub

but this is just furthering what seems to be a problem with the table design,
will be hard to maintain if your account codes change, and is inefficient!

John W. Vinson [MVP]
 
Thanks John,

There is a table for Account Codes and only one acct code can be applied to
each invoice.Each invoice can only have one description. I tried your code
but the acct code field is not automatically updated when an associated
decription is selected from the drop down list. The orginal code i used works
but i do need it multiple times.Could it be the event procedure?
 
I checked again and it worked great, typo on named field. Mnay thanks for the
support John.
 
Thanks John,

There is a table for Account Codes and only one acct code can be applied to
each invoice.Each invoice can only have one description.
Click to expand...

Then, in my opinion, you should NOT store the description in this table. It's
redundant. You can always look it up using a Combo Box (bound to the account
code but showing the description), or with a Query joining the two tables.
I tried your code
but the acct code field is not automatically updated when an associated
decription is selected from the drop down list. The orginal code i used works
but i do need it multiple times.Could it be the event procedure?
Click to expand...

Yes... but I can't see the event procedure from here. Care to post it?

John W. Vinson [MVP]
 
Thanks John, I would like to have a Combo Box bound to the account code but
showing the description as you have described to avoid redundancy but i am new
to this and need a little direction. Could you find the time to give me an example
using the information provided so far? I have three tables, one for invoices, one
for customers and one for accounts.
Click to expand...

Since I have no idea which of the fields you posted is in which table, all I
can really say is that you can have a Combo Box bound to an ID field; the
combo would be based on a Query containing the ID and the description; and you
can use the Bound Column and ColumnWidths properties of the combo so that the
first nonzero width column is the description. This combo will then store the
ID and display the description.

John W. Vinson [MVP]
 
Back
Top