By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,681 Members | 1,854 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,681 IT Pros & Developers. It's quick & easy.

Reference to current field on a form

P: 2
MS Access 2000-
I have a table with 200 fields named F001 through F200
and a form to collect data for those fields. I need to
execute an event procedure On Exit to do a conversion ie.

Expand|Select|Wrap|Line Numbers
  1. Private Sub F001_Exit(Cancel As Integer)
  2.     If Me![F001].Value = "1" Then
  3.         Me![F001].Value = "A"
  4.     ElseIf Me![F001].Value = "2" Then
  5.         Me![F001].Value = "D"
  6.     ElseIf Me![F001].Value = "3" Then
  7.         Me![F001].Value = "U"
  8.     End If
  9. End Sub
  10.  
Is there a way to reference the current field in this
procedure so that one procedure will work for all
fields (F002, F003, ... F200) so I can copy and paste
the procedure rather than replace the field name in
each of the 200 fields ie. instead of using
Me![F00x].Value is there a reference to the current
field that would work in all the fields?

Any suggestions will be greatly appreciated.
Sep 12 '06 #1
Share this Question
Share on Google+
3 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi,

Type your conversion as a function! Like:

Function my_conversion(my_field)

if my_field=1 then
my_conversion="A"
end if
and so on...

End function

Have a nice day!

:)
Sep 13 '06 #2

P: 2
Thank you very much for the response. Yes, I can accomplish the same thing with either a function or with a 'Sub' code procedure but the only way I can do either is with a reference to the field name. In order to pass the field parameter to the function, I must use the field name as in:
Function my_conversion(FieldA)
To do this for FieldB, I must pass that field name as the parameter ie.
Function my_conversion(FieldB) and so on
This is fine for a few fields but for hundreds of fields, it would be much easier if I could just reference the current field, whatever that field's name may be.

For example, a reference to the current form is:
Me![ProdID] - where Me references whatever form you are currently in
which can be used as a 'shortcut' in place of [Forms]![Products]![ProdID]
and that will work to reference the field ProdID in any current form.

Is there any such 'shortcut' or symbolic reference that means "current field" so that a function or event 'Sub' procedure can be coded referring to whatever field you are currently working with.

Or am I just missing the point entirely. My function
Function CvtNum(F001)
works to return the correct conversion to the field named F001
but to use that same function with the field named F002, I have to
change the parameter to
Function CvtNum(F002)

in the On Exit event line I am using
=CvtNum(F001)
for the field named F001 and it works but if I copy and paste that same line to the On Exit event line for the field named F002 it does not work. If I try to pass a parameter other than the actual field name, it returns an error that the parameter is an invalid field name.

In looking for a reference or term meaning "current field" am I just wishing for something that doesn't exist? Again, any help or ideas will be greatly appreciated. Thanks.
Sep 28 '06 #3

PEB
Expert 100+
P: 1,418
PEB
Hi,

Try

Application.Screen.ActiveDatasheet.Form.Recordset. ActiveControl

This one works for the queries...

For the forms You have to try it! If it works pls tell me!

Thanks

:)
Sep 30 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.