Calculation in Form? or Query? to populate Table 2

[bcgeu]

I have a query created from my tables and a form created from the query. As the user completes the form (one of the field being "Rec'dDate&quot I want the "Deadline" field to be completed as (Rec'dDate + 30). I can create this calculation in the form with no problem but it doesn't reflect back to the table. I can't get the calulation to work in the query which is where I think it really should be. But then I am so confused I don't know where anything is or should be. Can anyone help a real novice?

 

[RickSpr]

You don't want the calculation in the query. The query runs before the data is displayed, so it can't access data entered into the form by the user. Also, a calculated field in a query isn't updatable; even though you may have named it the same as a column in the table, the query column isn't connected to the table at all. That's why your table doesn't get updated.

What you need to do is to include the Deadline field from the table in the query as an ordinary field. You need code behind the form to set its value when you're about to write a record to the table. The code goes in the form's BeforeUpdate event procedure, and it's quite simple:
Deadline = DateAdd("d", 30, RecdDate)


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[bcgeu]

Thanks Rick, that helped a lot. I was getting advice to put it in the query and I was much happier with it in the form except that it wasn't populating the table.

I followed your instructions and it worked well except the date only showed on my report. I was using expression builder on BeforeUpdate in the Deadline field. I found, thanks to your getting my mind working, that if I put the statement in a Code Builder, On Exit, in the RecdDate field that it populated the Deadline field on the form so the user could see it was taken care of and it also worked in the original table and the report.

Thanks for your help.

Louise

 

[RickSpr]

Well, I had said to put it in the form's BeforeUpdate event, not the Deadline control's, because I didn't realize you wanted it to appear on the form.

Putting it in the Exit event procedure will work most of the time, but it's better if you put it in the AfterUpdate event procedure. The problem with using Exit is that it won't get calculated until the cursor moves out of the RecdDate field. If you type in a RecdDate and then, without tabbing out of that control, save the record or move to another record or close the form, the Exit event occurs after the record is saved, so the Deadline will not get set in the saved record.

The control's AfterUpdate event always occurs before the record is saved, so it's safer to do data changes there. The Exit event should be reserved for making changes to the appearance of the control, such as changing the background color to highlight the field that has the focus.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[bcgeu]

Thanks Rick. You are right, I hadn't read it properly. I would kill to know Access the way you do! But in the meantime I will plod along, grateful that you are there when we need you.

Thanks again!

 

[RickSpr]

Thank you for the laurel wreath--though there's still lots of Access I don't know. I've never yet put together a project using ADO, for instance. You may be teaching me something some day.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[ramsenisaac]

Hi, Rick,

I think I'm having a similar problem. If you have time, please read my post at thread700-672281.

Thank you.
Sincerely,
Ramsen Isaac

 

[JerryDennison]

Actually, you shouldn't store a calculated value in your table. No attribute should be dependent on any other (non-key) attribute. You should, in fact, simply include the field as a calculated one in the form or report's underlying recordset. As soon as the field the calculation is based on is entered the calculated value will display.

"Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[ramsenisaac]

Jerry,

I have a few calculated fields in my form, and the calculations are working correctly in the form. However, I am trying to merge a lot of fields from the MS Access table into an MS Word document, and a handful are calculated fields.... Any suggestions?

Sincerely,
Ramsen

 

[JerryDennison]

Yes, use a query for the source of your merge data. This can work from either the Access side or the Word side. From the Word side you would use the Merge Document Wizard, select the DB and query for your source. From the Access side there are several ways to do this. My favorite is to create a Word Template and then create custom properties in the template (one property for each field you wish to merge). Once you've created the properties they will show up in your Fields... list and can be added anywhere in the document. You can then use the following code (original by Helen Fedema, modified by yours truly) to populate each property. This works very well and gives the user a new document each time (in case they want to save it for posterity).

Private Sub cmdMemorandum_Click()
'Written by Helen Feddema 4-22-98
'Last modified 8-2-2000

On Error GoTo ErrorHandler

' Dim appWord As Word.Application ****modified to use the object class instead of application class
Dim appWord As Object
' Dim docs As Word.Documents ****modified to use the object class instead of Word document class
Dim docs As Object
Dim strLetter As String
Dim strTemplateDir As String
Dim prps As Object
Dim strDate As String


Set appWord = GetObject(, "Word.Application&quot
strDate = CStr(Date)

strTemplateDir = "f:\warpgms\warfiles\"
strLetter = strTemplateDir & "Memorandum.dot"

Set docs = appWord.Documents
docs.Add strLetter

Set prps = appWord.ActiveDocument.CustomDocumentProperties

With prps
.Item("PropertyName1&quot.Value = Me.FieldName1
.Item("PropertyName2&quot.Value = Me.FieldName2
etc......
End With

With appWord
.Visible = True
.Activate
.Selection.WholeStory
.Selection.Fields.Update
.Selection.MoveDown
'.Selection.MoveDown Unit:=wdLine, Count:=1
End With

ErrorHandlerExit:
Exit Sub

ErrorHandler:
If Err = 429 Then
'Word is not running; open Word with CreateObject
Set appWord = CreateObject("Word.Application&quot
Resume Next
Else
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End If

End Sub

"Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien