Dmin inconsistent results

[Pete Printer]

Hi

I have a staff form and a training details subform.
The training subform is bound to a junction table that contain staffIID , trainingID, passdate, expDate.

in the after update event of the subform ExpDate i have this code.

Private Sub ExpDate_AfterUpdate() 'saves changes and updates training Enddate
Dim EndDate As Date
Dim StaffNum As Long

   StaffNum = Forms!frmstaff.staffID
   DoCmd.Save
   DoCmd.RefreshRecord

   EndDate = DMin("ExpDate", "JunTblStaffTraining", [staffID] = StaffNum)
  
   'MsgBox EndDate
  
    Forms!frmstaff.stTraineduntill.Value = EndDate
  
End Sub

i have checked StaffID before its sent to the DMin and its correct.
when i use this with the first staff member its fine. if i then select another staff member on the parent form and make a change the previous result is returned.
Its as if the record is stuck

Thanks for looking at this
Best Regards
Pete

 

[Minty]

Your syntax is a little off - have a look at my signature for full examples but try

 EndDate = DMin("ExpDate", "JunTblStaffTraining", "[staffID] = " & StaffNum)

This assumes StaffNum is a number not text.

 

[plog]

I'm surprised that gives any results, much less inconsistent ones. The last argument of a DMin is a string. You don't have a string there really. It should be:

"[staffID] = " & StaffNum

That's assuming [staffID] is actually a numeric field. Also, is ExpDate an actual Date field? In the design view of the table, does it say Date/Time?

Lastly and unrelated, does the 'Jun' in 'JunTblStaffTraining' correspond to the monht June? If so, you've set things up improperly. There should never be a month name in a table name. All data of every month should go into the same table ('TblStaffTraining') and you use queries to get just the data for a timeframe you want.

 

[Pete Printer]

Minty
Thank you for your prompt and helpful reply. i can understand were i went wrong.
Its all working fine thank you.
plog
Thank you
Yes it is a date
JunTblStaffTraining is a junction table

Its great that you guys help so much. i learn so much from this site.
Regards
Pete