Default Calculated Date Value

[HMEpartsmanager]

Hello, I have had this little 'nuisance' in my database since 2005.

I have a form for a purchase order and a subform to hold the items being purchased. I put this database into service 06/16/2005, and that was the day of the first purchase order. The form's default value for ETA is current date + 14 days. I then change the ETA field later when I know what it is for sure. In this case, the first purchase order had a ETA delivery date of """06/24/2005""".

In the subform, I have the ETA for each part default to the ETA from the purchase order. I can then change each individual ETA line items if necessary.

The subform's default value for ETA is;

=[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]

This works pretty much as it is supposed to work. Every new line item in the subform automatically fills in with the purchase order's default date that was calculated.

And now here is where it gets 'interesting'.

On many occasions when items were added to a purchase order, the default value for the line item gets entered by default of 06/24/2005. Yep, the very first purchase order's ETA date that was calculated. I have never figured out why. But today, I found a way to duplicate this calculating and now need help on how to eradicate it and have the subform grab the correct date.

If I put the purchase order FORM into Datasheet view, then scroll down to a purchase order I want to work with, right click to open that purchase order, it will open the purchase order and subform for the line items, and there it is. The subform has somehow grabbed the ETA date for the next available line item from the very first purchas order ever written. It should have grabbed the ETA date from The Current FORM I have open.

Can you tell from my default value for the subform's code;

=[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]

... why it would grab the ETA from the the first purchase order, and not use the ETA field from the 'CURRENT' purchase order I am working on and have open?

I would appreciate a little help on this one. It is one of those little peskies that don't do a lot of harm in the application, but is very annoying.



Thank you for reading,

Tim

[RuralGuy]

Have you set a default value for that field at the Table level?

[HMEpartsmanager]

Have you set a default value for that field at the Table level?

No, there is no default value set for the Table level on the ETA for the line item. The only default value set for the line item ETA is in the subform.

Tim

[RuralGuy]

Is that the ControlSource property of that control that you have set to "=[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]"?

[HMEpartsmanager]

Is that the ControlSource property of that control that you have set to "=[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]"?

No. It is a TextBox to enter the ETA date.

On the subform, the TextBox is as follows:

Name: ordlineEta
Control Source: ordlineEta
Format: mm/dd/yyyy
Default Value: =[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]

The idea is that the next line ready in the subform to order something, will grab the ETA date from the Purchase Order (FORM) that is sitting above it and open. I have the Form/Subform linked together so when you open a purchase order (form), the line items (subform) displays underneath it. I can't remember how I linked them back in the day, but all is normal for everything except this date issue.

Could it be that I may have to change my Default Value line a bit? Maybe since I never opened a form, instead I opened this form from the Datasheet View, it does not register a default Form data.

However, if I go to the next record in this FORM, the form will replenish with the next purchase order and then populate with the correct ETA from the Form to the subform. It seems this odd date grab is only duplicated by going from Datasheet View to Form View the first time. Any advance forward or backward of the form data to a new record, refreshes the subform and all is well. It only happens at the open of the Form.

Thank you for taking the time to analyze this.

Tim

[RuralGuy]

I would set the value in the Current event of the SubForm IF there was no existing value AND Me.NewRecord is true instead of depending on the DefaultValue to update it in time.

[HMEpartsmanager]

I would set the value in the Current event of the SubForm IF there was no existing value AND Me.NewRecord is true instead of depending on the DefaultValue to update it in time.

Gone yesterday, busy today. I will try this tomorrow and see if that would do the trick.

I think I understand what you are stating above. Right now when I open up a Purchase Order (FORM), the Line Items (SubForm) next line entry item will already have it's ETA date set by the Default calculated value. Thus going to the next Record (Purchase Order Number), the next line item for the subform on the next record will have a new default value.

To duplicate this, I would use the On Current event in the SubForm to make that calculation instead of the Default Value field calculation of the SubForm. Is this correct?

So I would put in NO Default value for the subform's field for ETA. Then in the On Current event, have it check that ETA is nothing, then calculate what the ETA would be using this Current Form I am working on (have open) and have it placed in that field?

I think I grasp it, but somehow it looks like I would have to requery the subform afterwards to get it to disply on the screen. But whether or not there are any problems with it displaying, but not actually being used.

Right now the ETA can be displayed as preliminary data for the next line item entry. But I may not actually put in any data right now. I think, if memory serves, I have it set up to Save the record only after I have updated everything and some event. So displaying data is not a problem.

I will see what I accomplish tomorrow.

Thank you,

Tim

[RuralGuy]

Using the Current event as I described dirties the record so maybe the default value would be better. Hmmm...

[HMEpartsmanager]

While reviewing this line in the Default Value for the ETA Field on the SubForm;

=[Forms]![v2 - PMS Purchase Orders - Main Form]![ordEta]


... It occurred to me that maybe the problem lies in that when I open up a record in the Form, that it defaults to record #1 for any information collected and/or needed. Maybe what I need to figure out is how to change that line above to Forms!PurchaseOrdres!!!CURRENT!!! and then grabe the ordETA. The line above is not indicating which record to grab when it opens up the main form.

Is there a parameter that can be used with the above to make sure that it grabs the current record data for calculations, instead of grabbing data from the 1st record that this form brings up?

I will go look at alternate calling out of the [Forms]! line.

Tim

[HMEpartsmanager]

RuralGuy, or anyone else following this thread;

When I am in Datasheet View for the Form, I can scroll up and down and all is good. But when I scroll down to any record, highlight a row, then switch to Form View, it looks as though the SetFocus is on the left side of the form being viewed.

Is there any way, through a setting and/or programatically, to set focus on the first field in the record upon the switch from Datasheet View, to Form View?

I have tried the OnOpening or something like that to test, but that doesn't work with the code I put into the event. Either my code, or I still need to figure out how to get focus on this record when switching from Datasheet View, over to Form View. I really think that will be the key to figuring out my problem.


Tim

[RuralGuy]

How are you switching from Datasheet to Form view?

[HMEpartsmanager]

How are you switching from Datasheet to Form view?

Open Form, it defaults to Form View
Right/Click on form to switch to Datasheet View
Scroll down to any of the 6,000 records
Left/Click to highlight a record row
Right/Click on Form to switch to Form View

Now I am in the Form View and the left side of the Form is highlighted Black just like it was when I highlighted it under Datasheet View to choose that record.

And this is where the default values are set, and it looks as if it is grabbing the values for calculations from first record; NOT the record for which I am on right now.

It seems that there must be a way to make the record I have chosen to open, when changing view from Datasheet to Form View; use the now open form to get it's data needed to make the calculations for it's default values, but it is not working out that way.

Like I said, it is a 'minor' glitch, but I would like to solve it. All I have to do is advance the record forward or backwards 1 record while in Form View, and it is fine with the correct calculations.


Tim

[RuralGuy]

The normal method I use for such a situation is to use two forms, one in Datasheet view and one in Form view. It is easy to to use an event in Datasheet view to open the Form view form to the current record. Paul has three such methods in the Form section at the top of his web page: http://www.baldyweb.com/

[HMEpartsmanager]

The normal method I use for such a situation is to use two forms, one in Datasheet view and one in Form view.

In this situation, there is no real need for having 2 forms.

We open the Form in Datasheet View, scroll to the record we want to edit/review, highlight it and switch to From View to work on it. After we are done, we switch back to Datasheet View, scroll to whatever record we want to work on next, and repeat.

Sometimes if we scroll backwards a couple of hundred records and work on a record, then want to go back near the end, we will just press the arrow for end of records on the form and then use the arrows to go backwards until we see the next record we want to work on.

I don't want to open up the record in a new form. I want to keep the current system as it is for convenience.

I will still keep looking for a solution. :-)

Tim

[RuralGuy]

Fair enough.