Storing data from a specific field when clicking on another

[David57]

Hello there!
I’m coping with this problem I haven’t been able to solve so far.
I have a subform, say sbfrm1. What I need is that whenever in sbfrm1 I click
any record within a specific field, say field2, Access could read the numerical value shown inside a string in the same record but in another field, say field1 and store this value in a variable called Var1.
For example. If I click the value shown in field2 record ID7 while field1 recordID 7 shows the string “ABC 125”I need the numerical value 125 to be stored in Var1.
I suppose an onclick event could be used but I haven’t the faintest idea on how I can get what I need.
Thank you in advance for any useful suggestions.

[NeoPa]

That sounds straightforward enough David, however your description doesn't make very clear what data you want from [Field1] if it isn't exactly in the format you've illustrated. Is it always in position #5 that the numeric data starts? Will there ever be non-numeric data after the numeric data starts? For now I'll assume there will always be the first space before the numeric data.

You also don't describe what Var1 is or what you expect do do with it. For simplicity here I'll assume it's a variable you've defined elsewhere in the code and has a scope that makes it accessible from the form's module.

Private Sub Field2_Click()
    Var1 = Val(Split(Me.Field1.Value, " ")(1))
End Sub

This code would go in the Form's associated module. If you paste it into the module then [Field2]'s OnClick property will be set up for you automatically.

Good luck with your project :-)

[David57]

Hello NeoPa,
Thank you very much for replying.
ABC 125 was just an example.
The data I need to retrieve from Field1 is always the numeric one located at the end of a string of 3 possible types: FHP 177 or Z/FHP 125 or RCC/FHP 35 (the numbers can vary but the strings are the real one).
As you can see there always 1 space before the numeric data and no non-numeric data after.
The result I'm trying to obtain, NeoPa, is changing the font color and size of all records (usually just a few) showing in Field1 the numeric value stored in Var1.
David

[David57]

Hello NeoPa,
Wonder if adding a new field, say FieldNo, for the numeric value only, could make things any easier.
It still remains, however, the problem of changing the size and color of the records containing in Field1 the value stored in Var1, becouse this is the final result I am aiming to.
David

[David57]

Hello NeoPa,
In the last line of my previous post please read:
"containing in FieldNo the value stored in Var1"
Sorry for the mistake.
David

[NeoPa]

That sounds like quite a different question David. One gives you a value from a Control after you click on another Control, the other changes Control properties based on the same value, but not triggered by clicking.

I suggest you give up on this one as the question's been asked and answered, and ask a separate one which more closely matches what you want. Other than the basic logic of extracting the number from the string the answer will be very different from this one.

[David57]

Hello NeoPa,
Thank you for your concerning in my problem.
Let us suppose I add one more field, say FieldNo, for numeric data only, then I can summarize the problem as follows (please refer to my first post).
Whenever I click any record within Field2, I want the value shown in the same record but in FieldNo
to be stored in Var1 in order to change the font size and color of any record in sbfrm1 that in fieldNo shows the value stored in Var1.
As a matter of fact the above actions are triggered by clicking any record within Field2.
I don’t know if this problem can be solved. Creating Var1 and adding FieldNo are just my idea for trying out a possible solution but maybe this is not the best choice. Sorry for my poor English.
David

[NeoPa]

Hi David.

I expect your English is the problem with your not understanding my last post very clearly. I will say it again for you, but more clearly.

Your new question needs to be posted in a completely separate thread. That means you need to start a new thread and ask your question fully (so include all the details) and clearly. You are welcome to post in link to this current thread from your new one, but this one isn't very easy to follow so I would suggest starting from scratch completely may well be a better option for you.

This current thread will then stay as it is with an answer to the question you originally asked (even though it wasn't the question you intended to ask).

I'll look out for your new thread.

[David57]

Hi NeoPa,
I fully understand the point, now. I'll ask a different question in a new tread.
Thank you for helping.
David

[NeoPa]

Always glad to help David :-)