Set default value of a field based upon another field (1 Viewer)

[HillTJ]

Hi, seems simple but i am building a quoting database that has a reference price in a field, but i need to have the ability to save the actual price i enter which may be different to the reference price. I set the default = to the reference but obviously every time i pass through the record it reverts to the reference price. I guess i need an if statement to see whether the actual price field is null, then if it is then copy over the reference value. It could then be edited to achieve the price i need. Apreciate any assistance.

 

[June7]

DefaultValue property only triggers for a new record, therefore it would not change the entered value when revisiting record. If it is changing then you are doing something else. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Gasman]

Not if it is the default value.?, that only affects new records.

 

[gemma-the-husky]

you would need to do this within a form, in code.

When you enter a record, at that point read the value of the reference you want to use for the reference. You could do this either in the current event (if you know it at that point), or at the time you read the reference value, if later.

 

[HillTJ]

Guys, understood. So I'll need an if statement to stop the receiving field from being overwritten when i re enter the form right?. For example, test if the receiving is null, if so then copy the reference cell, otherwise skip the copy as there is a value in that field already?. Will this work if triggered by on entry to the recipient field? Sorry, if my question is simple & my skills are minimal, but I'm learning.

 

[Minty]

I would simply check for a new record in the forms current event, something like

If Me.NewRecord Then
Set your default value here.
End If

This will only fire on a new record.

Edit - sorry I see Dave already suggested this.

 

[theDBguy]

Hi HillTJ. How exactly are you setting the default value? As was already mentioned, it shouldn’t overwrite existing values and there’s no need to check for new record because the default value automatically only applies to new records. Can you show us your code please? Thanks.

 

[HillTJ]

Hi dbguy, don't have code at this stage. I have a table with 2 fields on a form. Field "initialprice" gets its value from a lookup, but the user may wish to vary that price. So the "Actualprice" field is initially populated from "initialprice" field & this values referred to in calcs but could be adjusted by the user. That is, you can go with the initial price or make a change that is saved. Just need to get my head around the appropriate method. Cheers.

 

[theDBguy]

Hi dbguy, don't have code at this stage. I have a table with 2 fields on a form. Field "initialprice" gets its value from a lookup, but the user may wish to vary that price. So the "Actualprice" field is initially populated from "initialprice" field & this values referred to in calcs but could be adjusted by the user. That is, you can go with the initial price or make a change that is saved. Just need to get my head around the appropriate method. Cheers.

Hi. If what you said above is all that's happening, I don't see from there where the issue of the value the user entered is getting overwritten when they view previous records. Didn't you say that was an issue? Maybe I misunderstood.

 

[HillTJ]

Dbguy, I set the default of actual price = initial price & expected it to populate actual price upon a new record. Seems simple, but upon reflection i'm not really sure whether i tried this on a new record, rather i think i may have scrolled through existing records where i now understand the default event is not triggered. Should this approach work?

 

[theDBguy]

Dbguy, I set the default of actual price = initial price & expected it to populate actual price upon a new record. Seems simple, but upon reflection i'm not really sure whether i tried this on a new record, rather i think i may have scrolled through existing records where i now understand the default event is not triggered. Should this approach work?

Right, this is the normal approach to set static default values. If you want to use a dynamic default value, then you'll need to use some code.

 

[June7]

And where will the initial price come from? If it is not available when new record is initiated then the DefaultValue property accomplishes nothing. Do this test. Set the DefaultValue of a textbox with a static parameter, such as for a date field, use Date() as default. Now look at the form and you will see the current date showing on the new record row even before record is initiated. That field will automatically populate when value is entered into any other control which initiates record.

So in AfterUpdate event of whatever control is used to select/enter reference price, run code to set value of actual price.

 

[gemma-the-husky]

I can see the default price might be available based on the customer/product, and until you pick the product, you don't have a default price. Maybe you need to enter a quantity to get a special discount/quantity break.

Now how you mange the default (looked up) price, and how you permit the price to be modified is a different issue. Maybe order entry clerks can modify the default price within set parameters.

 

[HillTJ]

So, my experiment is thus. I've a lookup field for selecting the initial value, upon exit of that field code checks whether actual value field (the receiving field)is 0 (default). If 0 then the initial value is copied across to the actual value. Works but if I change the combo selection, clearly the code does not recopy the revised value across as the actual value contains a value. So not right & needs a bit of refinement. Ideas?

 

[theDBguy]

So, my experiment is thus. I've a lookup field for selecting the initial value, upon exit of that field code checks whether actual value field (the receiving field)is 0 (default). If 0 then the initial value is copied across to the actual value. Works but if I change the combo selection, clearly the code does not recopy the revised value across as the actual value contains a value. So not right & needs a bit of refinement. Ideas?

Hi. Earlier, I asked for your code, and you said you didn’t have any. But now, are you saying you have code? If so, can you post it please? Thanks.

 

[HillTJ]

I wrote this this morning as a test,
Private sub combo27_exit(cancel as integer)
If (quoted_freight) = 0 then (quoted_freight) = (freight_rate)
End Sub

All field names are inside square brackets. Not the curved ones as shown.

 

[theDBguy]

I wrote this this morning as a test,
Private sub combo27_exit(cancel as integer)
If (quoted_freight) = 0 then (quoted_freight) = (freight_rate)
End Sub

All field names are inside square brackets. Not the curved ones as shown.

Hi. Thanks, but that’s not how you set default values. If you use the default value property, then you don’t need to use any code at all.

 

[HillTJ]

theDBguy, I tried some code but since deleted. It was triggered by the on exit event of the combobox. It checked whether the receiving fieldwas 0 or not with an if statement, then if 0 went on to copy the lookup to the value to the receiving cell. This worked, but if you changed the lookup, as the receiving field already contained a value it would not copy again.

 

[theDBguy]

theDBguy, I tried some code but since deleted. It was triggered by the on exit event of the combobox. It checked whether the receiving fieldwas 0 or not with an if statement, then if 0 went on to copy the lookup to the value to the receiving cell. This worked, but if you changed the lookup, as the receiving field already contained a value it would not copy again.

Hi. I am not sure what you're trying to do with your code, but all I'm saying is this is not the way to set a default value. So, if your original intent was to simply assign a default value, as indicated by your topic title, then we just need to clarify that.

 

[HillTJ]

TheDBguy, go easy on me I'm just a keen newby. What i wish to do is lookup a value, then be able to keep this or amend it and save it in another field. This is for freight cost, where may be a requirement to use a different value to that in the lookup in this instance. If not, the value is prefilled. I thought i'd be able to simply set the default value of the receiving field equal to that of the lookup ie default value = (combolookup) but square brackets.