Relating info in Forms and Subforms

[Danielle17]

On one of my forms there is a subform where the user can add a new entry to an existing record, for example the date....The main form (where the subform is located) and subform are related by the GageNumber-There are 2 tables in my database- one for the basic info for a piece of equipment, This equipment needs to be checked every so often so that is why there is another table...it holds the calibration information for each piece of equipment(there will be more then one calibration entry for each piece of equip.)...On the main form, which contains the 'basic' info for a piece of equipment, there is a text box that contains the Cal.Frequency...this determines how often equipment needs to be checked....So if in the subform (where the user would add a new record to show that the equip. had been calibrated) the new date reads 8/2/01 then depending on the frequency (i.e. 12 months) it will change the other text box on the main form (Cal.Due)...So the NEW date in the Cal.DUe box(also located on the main form) should read 8/2/02....so if the frequency was 6 months then the NEW date in the Cal.Due box would be 2/2/02...and so on with 3 months, 9 months,....I tried to put some code on the ON Change event of the text box in the subform that contains the new date but I can't seem to remember how to write it so that it looks at the new date there, then at the first text box(freq.) in the main form, then calculates the NEW Cal.Due date....
Can anyone help? Sorry about the confusing explanation

 

[Databaseguy]

I built a calibration database also.

The dateCal table has two fields, ItemID and dateCal.As you mentioned, Item ID will be duplicated, but the ItemID plus dateCal are unique.

In lieu of storing a due date (which violates nomalization), I simply use the date add function to calculate the due date.

DateDue = DateAdd("M",Freq,DateCal). Freq comes from the main ItemID table.

Not a specific answer to your question, but I hope it can be of use.

 

[Danielle17]

Thanks for reading my explanation and answering...actually it sort of helps

 

[Ma3x323]

Hey Danielle,
Okay, databaseguy was on the right track. To do your thingy, put it on the ON change event like you did. Then you make the value of the textbox or whatever you made it to do this:
DateDue.value = DateAdd("M", DateCal, number of months).
And the caldue on the main form should = this box.
If it is days then DateAdd("w", DateCal, number of days).

Hope this helps. Or at least makes sense. -Feryl
--> Just a silly software engineer wannabee.

 

[Danielle17]

Okay I got it to change the date but I put it on the LostFocus event so that it doesn't pop-up a "Null" error....the only problem is that I need the CalDue date to stay the same even if the user puts the cursor back into one of the other records showing in the subform....What it does now is when a new record with a new date is entered it changes the CalDue date on the main form but if the user goes back up to one of the 'old' records on the subform the CalDue date is re-calculated and displayed....How can I get the CalDue date to keep the calculated date for the lastest record on the subform???

 

[Ma3x323]

If you mean the lastest record's Caldue = mainform CalDue, then I think there is a way to reference that item.
set a control to
Forms![mainform]![subform name]![field]![the item #]
Item number should be the number of the last item on the particular field.

I am not quite sure about that referencing thing i did. But I think it works. Maybe it is a different way but that is how I remember it.

Hope I kinda helped. -Feryl
--> Just a silly software engineer wannabee.