Change value in txtbox dependent on another, but still allow manual edit.

[Singh400]

I need my form – frmFinance – to automatically select between two different values depending on what value is in another txt box. However, sometimes this value needs to be manually edited (in very rare cases, say less than 5% of the time).

frmFinance is based on a qry; qryFinance. This pulls together the relevant fields from tblBookings and tblFinance.

If the Language field has BSL in it, the default value would be 70. Anything other than BSL in the Language field and the value would be 50.

I tried using an IIF statement, and this worked fine, until I discovered I could not edit the value when I need to change it, because it was bound to the IIF statement.

This was the IIF statement:

=IIf([Language]="BSL",70,50)

The above code was entered as the Control Source for txtSUCharge on frmFinance.

I then did some googling and it looked like DLookUp would do what I want:

=DLookUp("[tblFinance].[SUCharge]","[tblFinance]","[tblBookings].[Language]")

I also tried:

=DLookUp("[tblFinance].[SUCharge]","[tblFinance]","[tblBookings].[Language]='BSL'")

Again, this was inputted into the same place as the IIF statement. However, the behaviour was the same. It would not let me manually change the value of txtSUCharge if need be.

Any ideas? I did try to do the IIF in VBA, but that didn’t work either.

 

[missinglinq]

As in real estate, location is everything! This will assign the proper value to txtSUCharge, while still allowing manual editing of it:

Private Sub Language_AfterUpdate()
  Me.txtSUCharge = IIf([Language] = "BSL", 70, 50)
End Sub

 

[Singh400]

Oh god. I feel like a right plonker now. Thanks dude

 

[Singh400]

It doesn't work.

Does it have anything to do with the fact that whatever is in [Language] isn't inputted by the end user on the frmFinance. It's pulled from tblBookings. The value (language field) is inputted by the end user on the frmBookings.

So whenever frmFinance is opened, the [Language] field already has a value in it. Plus the field is locked, so no changes can be made on this form to the data.

 

[missinglinq]

Yes it is! When a field is filled in thru code, the AfterUpdate event doesn't fire!

As I said before, location is everything! Try it in the Form_Current event, only doing it if this is a new record:

Private Sub Form_Current()
If Me.NewRecord Then
 Me.txtSUCharge = IIf([Language] = "BSL", 70, 50)
End If
End Sub

 

[Singh400]

Yeah that's what I thought. I did try putting it in OnCurrent, but then it assumes I am filling in a record, and won't let me move to the next record without me filling in the specified PK. That only happens when I click =>| or =>*.

Is this more hassle than it's worth? It's not for me, it's for the bloody end users. They can't remember to manually change the 50s to 70s where required so I've been asked if I can include this.

 

[alfi]

in the attached file i build two text boxes where the input of one will effect the result on the other. i also created a button that emulates an automatic change.

 

[missinglinq]

then it assumes I am filling in a record, and won't let me move to the next record without me filling in the specified PK.

I'm sorry, I'm don't understand what you're saying. The code I gave you will on execute if you move to a new, blank record. Why else would you be doing this if you weren't "filling in a record?" If you start to create a new record and change your mind, back out by hitting <Esc> twice.

 

[Singh400]

I'm sorry, I'm don't understand what you're saying. The code I gave you will on execute if you move to a new, blank record. Why else would you be doing this if you weren't "filling in a record?" If you start to create a new record and change your mind, back out by hitting <Esc> twice.

Yeah I didn't explain myself very well did i? Ok here it goes.

tblBookings and tblFinance are linked in relationship manager.

frmBookings is directly based off tblBookings. Where as frmFinance is based on qryFinance which pulls together selected fields from both tblBookings & tblFinance. The end result is frmFinance.

Ok so. frmBooking is used to fill in location, start time, date of appt, etc etc. Now when you open the same record in frmFinance some of the fields from frmBookings are displayed and are already filled in (such as txtLanguage etc etc). What isn't filled in is the financial side of that particular booking (txtSUCharge and etc). Now what happens is because you have the ability to add a new booking in either frmFinance or frmBookings.

The code:

Private Sub Form_Current()
If Me.NewRecord Then
 Me.txtSUCharge = IIf([txtLanguage] = "BSL", 70, 50)
End If
End Sub

Fires everytime I move to a new record. Which as it should be. However, when I move to end of the current record set, and move to a "blank" record (not filled in through frmBookings). The code thinks I am making a new booking (instead of completing an existing record, which is what frmFinance is for). So I get trapped in a loop of having to fill in the PK.

I've partially fixed this problem, by disabling the ability to add new records via frmFinance. But frmFinance/the code above still isn't able to correctly change values according to what is in txtLanguage.

If I change the above code to:

Private Sub Form_Current()
 Me.txtSUCharge = IIf([txtLanguage] = "BSL", 70, 50)
End Sub

It does work, however, as you would expect it overwrites the values every time.

I suspect "If Me.NewRecord Then" doesn't work because I've now disabled the ability to add new records via frmFinance.

in the attached file i build two text boxes where the input of one will effect the result on the other. i also created a button that emulates an automatic change.

Thank you alfi. I will take a look

 

[Singh400]

Ahhhh, just had a brainwave. Saw a post of John's and it inspired me. I've got it working using this...

Private Sub Form_Current()
If IsNull(Me.txtSUCharge) Then
    Me.txtSUCharge = IIf([txtLanguage] = "BSL", 70, 50)
End If
End Sub

If anyone has a better way, please let me know!

 

[missinglinq]

"However, when I move to end of the current record set, and move to a "blank" record (not filled in through frmBookings). The code thinks I am making a new booking"

Why are you moving to a "blank" record? Blank records are new records. If the problem is that when you fill in the last textbox on a record you hit tab and it goes to a new record, the answer to that is to goto the form's Properties - Other and set the Cycle Property to Current Record

 

[Singh400]

"However, when I move to end of the current record set, and move to a "blank" record (not filled in through frmBookings). The code thinks I am making a new booking"

Why are you moving to a "blank" record? Blank records are new records. If the problem is that when you fill in the last textbox on a record you hit tab and it goes to a new record, the answer to that is to goto the form's Properties - Other and set the Cycle Property to Current Record

I don't. It's the End User I have to think like. Alot of them use the scroll wheel to naivgate records and/or PgDown/PgUp and/or the new record symbol (=>*) as a "sign" of saving/completing edit of a record. I have now disabled the creation of new records in frmFinance. The Cycle property is already set Current Record (as is on all my frms). Cheers for your input

Just wondering, was the above code ok? Nothing wrong with? Nothing that could cause unforseen hassle?