Keep getting the syntax incorrect (1 Viewer)

[Dave 14867]

Hello all,

I need to grab a field in a table "tblMain" field named "Interval" and use it to calculate a new date in a different table "tblPass" field name "NextDate"

I have what I want working on a form and it works perfectly in the If statement below,

' If Me.Term = "Months" Then
' Me.NextDate = DateAdd("m", Me.Interval, Me.Date)
' End If

I need to get the original "Interval" and "Term" from the tblMain table rather than enter it on the form, possibly entering in incorrect data.

While writing this I could also (maybe just as easily and maybe better way) have the Form fields on the subform populated with the data in the "tblMain" table for the record in use. Would that be workable? If I was to use that method, my original "If" statement would still work properly.

Thanks

Dave

 

[Minty]

You could use an update query to pull in the matching records and update the specific record accordingly.

 

[Dave 14867]

Minty,

Could you please explain further? I have a form with a subform. The main form ties to "tblMain" and the subform has other fields that tie to "tblAtion". "tblMain" has constants for the item that never (or very rarely) need to get changed, "tblAtion" contains specific info for each use of the item, such as when does it have to be checked again.

How would I use an update query in this instance?

Thanks

Dave

 

[Minty]

If the data you need is in the related table, then a command button could easily run a query based on the data in the main form and update the current Subform record.

What determines the data in the main form? Do you have a unique ID for the main data?

 

[Dave 14867]

Minty,

Yes, both tables have a unique ID ( I always have unique ID's in all of my tables).

Could I also run a query on the "Form Load"?

Thanks

Dave