Continuous form edit data on load

[mrtubby1]

I have a continuous form fed by a simple query. One of the pieces of data displayed is a currency freeShippingLimit. I'd like to have the control bound to the freeShippingLimit field display a value of "Free Freight" when freeShippingLimit = 0.

Currently this is the only way I can think of to do this.

Private Sub Form_Load()
    For i = 1 To Me.Recordset.RecordCount
        Me.Terms = convertCurToText(Me.Recordset!freeShippingLimit)
        Me.Recordset.MoveNext
    Next i
End Sub

*convertCurToText returns a string with either the value of the shipping limit or the "Free Freight" string as required

this however results in the Terms field of every displayed record holding the value of the last processed record and not the proper value. Does anyone know how to fix that issue? Or better yet can someone suggest a more elegant solution to my problem?

 

[boblarson]

Just add the field in the query:

MyNewField: IIf([freeShippingLimit] = 0,"Free Shipping, "")

And then have a control bound to that field in your form.

Or, you can have it as a control source on your form:

=IIf([freeShippingLimit] = 0, "Free Shipping", "")

 

[mrtubby1]

I was dumb for not thinking of that myself

I'd also like to make that control editable, is there a way to do that still? I know that access has no idea what to do with the data provided to it, but I'd like to write an event handler to make it happen.

 

[boblarson]

I was dumb for not thinking of that myself

I'd also like to make that control editable, is there a way to do that still? I know that access has no idea what to do with the data provided to it, but I'd like to write an event handler to make it happen.

No, that control cannot be editable the way you have it now. Why would you store that information (remember - normalization rules) if you have the information to say what it would be?

 

[mrtubby1]

Sorry there was a miss-communication on my part. I'm not looking to have the database store "Free Freight" I would however like to have the the user be able to change the data in that field. For example if it reads "free freight" now the user could put 100 in and it would store that, or they could do the reverse and input either "free freight" or 0 and the database would store 0.

However, I suppose the clarification won't change the fact that the field is not editable. I suppose I'll have to rig some kind of pop up window keyed to a button next to the field.

Thank you