COMPARING RECORDSET TO WRITE TO TABLE

[Angelique]

My form for "Overdue Clients" is coming along really well, thanks to those who assisted with my "no record" problem in the subform.

Now! having said that I have had a problem which I spent all weekend trying to resolve without much success. I need to have the form record the date printed into the main table when the user selects the client then hits the "process" button.

I am trying to compare the ClientID in the temporary tblClientSelect table with the TrainingDetails table with the added criteria that the date in the qryOverDue is the same and if so, add today's date to the OverDueLetter field.

To get it to check the two dates by looping through each recordset but it doesn't like:

If tblClientSelect.ClientID = TrainingDetails.ClientID AND
TrainingDetails.[Date of Training] = " & Me![lstClient].Column(2)"

Any suggestions with the syntax - I can post the code if necessary.

Angelique

 

[tcalbaz]

Maybe try something similar to this:

'-------------------------------
if Not IsNull(DLOOKUP("ID","TrainingDetails","[ClientID]='" & tblCLientSelect.ClientID & "' AND [Date of Training] = #" & Me![lstClient].Column(2) & "#" )) Then

[OverDueLetter]=format(now,"dd/mm/yyyy&quot

Endif
'-------------------------------

Hope it helps,

Ted

 

[Angelique]

Ted,

The DSum, DCount, DLookup functions are something I am just learning when to use. I never even considered DLookup so I will give it a go and let the forum if it works.

Angelique

 

[Angelique]

tcalbaz & others

I tried the syntax and received an error "424 Object Required". I must admit I have trouble writing the code, it took a lot of tweaking with the "'.

More help!


Angelique