changing text box value

[BAzZ]

I have a lookup combo box which fills in text box1 and text box2, I have then set text box1 for if it recieves a specific value, it will fill in another text box(text box3). This all works, but occasionally, text box3 will require a different value, if I type in another value, going from one record to another, changes it back again.

 

[Wicklund]

What is the [Control Source] set to for your three text boxes????

 

[BAzZ]

The control source for each text box is set to its corresponding field in the table eg Land Trust is set to strLandTrust, Region strRegion and Roads to use strRoadsToUse.

Land Trust and Region are autofilled with the combo box strCommunity.

my problem is when Land Trust gets a specific value it should fill in a specific string of text(which works), but occasionally the wording needs to be changed manually, but as soon as you leave the record and go back to it, the changes get over-written with the auto fill text

 

[Wicklund]

BAzZ,

When is you code that auto fills the textbox3 triggered to run? Is it when you exit textbox2, or do you have it set elsewhere as well?

If I am understanding you correctly, then the [Event Procedure] should be set "On Exit" of textbox2.

 

[BAzZ]

The code is on textbox2 After Update and on Form Current the code is along these lines:

'Select Case Me.TextBox2

'Case "Specific value"
'Forms!frmTransitPermitApplication!textbox3.Value = "Specific string of text."

this works but when I have to change the "Specific string of text", leaving the record and going back to it changes it back to "Specific string of text." Losing the changes I have made.

I have also tried code along these lines:

If Me.textbox2.Value = "Petermann Land Trust" Then
Me.textbox3.Value = "Specific string of text."
End If

but this does the same thing.

 

[ritchieroo]

If you want any alteration to Box3 to stick permanently, the simplest thing you can do is check whether it is empty before filling it (only fill if empty).

Alternatively you could try using the BeforeUpdate event.

 

[Wicklund]

I agree with ritchieroo, provided that you won't be changing the value in textbox2, which would subsequently be changing the value of textbox3.

Having the code in the "on Form Current" event is causing the textbox3 to be automatically updated when you change records, even after you manually set it.

You can:
1: Only have the event run "After Update".
2: Add an "override" box (yes/no) check box that when checked, textbox3 will not be overridden with textbox2.

Either one should work

 

[BAzZ]

when I take the code out of Form_Current, nothing changes with textbox3, what ever is in textbox3 stays there, if I start a new record, nothing auto fills into it.

 

[Wicklund]

BAzZ,

I appoligize, I miss understood that what you wanted. However, by having the code in Form_Current, it will overwrite anything that you manually enter in textbox3.

What are the scenarios when you want textbox 3 to be filled. By your last response, I am assuming that one is when you add a new record. Is the other when you select a value in your combo box (depending on the value that is put in textbox1)?

 

[BAzZ]

My form is an application form to enter in to communities, so I have a drop down box to select the community(textbox1) to enter, this then autopopulates the LandTrust (textbox2) field and the Region (textbox3) field, that the community resides in. I then have another textbox (textbox4) which lists the roads that are to be used to enter the community, the end-users want textbox4 to fill itself in, when ONE specific LandTrust (textbox2), as the this is the most common community and set of roads. For the other land trusts the end-users will manulally fill in the other roads.
BUT(!)... The one that autofills needs to be able to be manually changed aswell (TO the community, changed to FROM the community) And hopefully from my previous posts you can read my problem.

Thanks for your help, its most appreciated.

 

[Wicklund]

BAzZ,

In that case, I would leave the code where you have it, but modify it to only update textbox4 when the condition on textbox2 is met.

If [LandTrust] = " ???? " Then
[textbox4] = " ????"
endif

Try something similar to this...

Good Luck,

 

[BAzZ]

If I have the code in the AfterUpdate of textbox2, but it doesn't fill in textbox4.

If I put the code also in Form_Current, the text goes into textbox4. but as you know when I change something and then go to the record it changes back because of the form_current.

 

[Rich]

Since you only want the text box to fill when entering new records check for that condition first.
If Me.NewRecord Then
your code etc

 

[Wicklund]

BAzZ,

What I meant was to keep the code in the Form_Current as well as the After_Update. Then use the IF statement to determine if you actually want to code to run and update Textbox4 (If [Land Trust] = "Your Condition").

 

[BAzZ]

I hope I'm not annoying you too much!
But...the code I have been using is an IF statement
IF me.strLandTrust = "Specific Land Trust" THEN
textbox4 = "specific string of text

, and if I have it in both the after_update and the form_current it wont let me manually change the text in textbox4 as next time I view the record it reverts back to the string in the code.
If I just have the code in After_Update it isn't updating textbox4 with anything(but I can manually put in a string of text)

I know we're going in circles here, so I apologise, I think I'm confusing myself.

BAzZ

 

[Wicklund]

BAzZ,

Try this, just fill in your own information where applicable.

If Me.Combo0 = "Your Choice" Then
Me.Text3 = "Your Result"
Else
Me.Text3 = Me.Text3
End If

You should be able to manual update Text3, and it will only be overridden when the Combo0 condition is met. I tried it in the Form_Current & After_Update and it worked fine.

Good Luck,