Need help

[Dano]

Hi,

Please help can I run this code from a macro on exit from a field. if not show me how to write it up.


Public Function CalcDateTerminaison(ProduitID As Long) As Date
Dim rs As Recordset
Dim sqlString As String
Dim tempDate, DateExp As Date
Dim DateOK As Boolean
Dim count As Integer

DateOK = False

sqlString = "SELECT IIf(tbl_Commande.DateSignature<Max(tbl_Tache.DateFinReel),Max(tbl_Tache.DateFinReel),DateValue(tbl_Command e.DateSignature))+56 AS DateTerminaisonCalc, tbl_Commande.DateExpedition " & _
"FROM (tbl_Commande_Produit LEFT JOIN tbl_Tache ON tbl_Commande_Produit.ID_CommandeProduit = tbl_Tache.ID_CommandeProduit) INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande " & _
"WHERE (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ((tbl_Tache.TypeTacheID) Is Null Or (tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID) Is Null Or (tbl_Tache.MachineID)=34)) " & _
"GROUP BY tbl_Commande.DateSignature, tbl_Commande.DateExpedition;"

Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If Nz(rs.Fields("[DateTerminaisonCalc]"), 0) <> 0 Then
tempDate = rs.Fields("[DateTerminaisonCalc]")
Else
tempDate = 0
DateOK = True
End If

DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close




Do While DateOK = False
sqlString = "SELECT tbl_DateConge.DateConge " & _
"FROM tbl_DateConge " & _
"WHERE (((tbl_DateConge.DateConge)=#" & tempDate & "#));"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
count = rs.RecordCount
rs.Close
If Weekday(tempDate) = 1 Or Weekday(tempDate) = 7 Then
tempDate = tempDate + 1
ElseIf count > 0 Then
tempDate = tempDate + 1
Else
DateOK = True
End If
Loop

If tempDate > DateExp Then
CalcDateTerminaison = DateExp
Else
CalcDateTerminaison = tempDate
End If


End Function

[jonh]

If you mean, can you run a macro after editing a field in a table or query, then no.
But you can add controls to a form, run macros from the control events and display the form as a datasheet which looks just like a table/query.

[Dano]

This is in a form and I what that if I change the data on a specific fields it will recalculate the public function at the top.

[jonh]

Well I haven't read your code but presumably it's a function that just returns a date based on whatever ID is passed in.

You could create another function that accepts the control to update as well as the ID


Public Function UpdateDate(c As Control, ProduitID)
c = CalcDateTerminaison(ProduitID)
End Function


and in the property sheet for the event you want it to fire from paste

=UpdateDate([ctl1],[ctl2])

where ctl1 is the control you want to update and ctl2 is the ProduitID control.

[Dano]

Ok let's start new.

The code at the top is part of a module that was created by someone else.

It is run when a order is placed, that code generate a termination date according to other dates within the order and that termination date can not be updated. That order is place within a form. What I what to do is being able to change that termination date if one of the other date changes. Like running a macro or whaterever when I change on of the dates.

[jonh]

That's not 'starting new' or telling me something I didn't already know.

Running a macro from the event of a control is basic stuff.

If there's something about the advice I gave above that you don't understand or don't think is right you'll have to explain.

[Dano]

You will have to excuse me. I'm at a beginner level. Did not understand what u r saying. Sorry very much beginner.

From the form the field control at the property sheet, on update run macro but can it be the code on top?

[jonh]

No problem, we all start somewhere.

The code you posted is a function.

CalcDateTerminaison

that takes a paremeter

(ProduitID As Long)

and returns a date

As Date

The code I posted uses your function to set the value of any control you pass into it

Public Function UpdateDate(c As Control, ProduitID)
c = CalcDateTerminaison(ProduitID)
End Function

In your form's design view, click a control and look at the Property Sheet > Event.

That shows all the events you can use to code the control.

If you click the down arrow you'll see [event procedure]. That means that that event has vba code behind it in the form's module.

If you select [event procedure] and click the ... button a new procedure is created in the vba module for that event that you can then enter code into.

Alternatively, instead of setting the event in the property sheet to [event procedure] you can call a function direct which is the method I described above.

I.e. instead of selecting [event procedure] you can simply paste

=UpdateDate([ctl1],[ctl2])

As long as UpdateDate is in a standard module and declared as Public it should work and if both controls are the same (ctl1 and ctl2) you can simply paste that line of text into all of the events you need without editing the form module.

Hope that helps.

[Dano]

OK, let see if I have that right.

I have to paste the public function UpdateDate in the main module where my Public function CalcDateTerminaison is.

In Design view the control where I might potentialy make a change, I paste =UpdateDate([ctl1],[ctl2]) in the event line and that will run the public function CalcDateTerminaison. I can paste that =UpdateDate([ctl1],[ctl2]) in all the control were I may make changes.

On last thing in that SELECT IIF command, I would like no date as DateTerminaisonCalc earlier then June 5th

sqlString = "SELECT IIf(tbl_Commande.DateSignature<Max(tbl_Tache.DateFinReel),Max(tbl_Tache.Dat eFinReel),DateValue(tbl_Commande.DateSignature))+56 AS DateTerminaisonCalc, tbl_Commande.DateExpedition " & _

[jonh]

When debugging queries embedded into code, type ?variablename in the immediate window and paste the output into a new query so that you can run it as a query and test the results.


Then you can use the UI to modify the query and copy the sql changes back into the module.





DateTerminaisonCalc gets it's value from either tbl_Tache.DateFinReel or tbl_Commande.DateSignature, whichever is higher, and then adds 56 days.


To filter the data you'll need to modify the WHERE clause


I can't say for certain what the change should be, you'll have to work it out and test it yourself, but it could be something like this



"WHERE ((tbl_Tache.DateFinReel > #6/5/2015# + 56) AND (tbl_Commande.DateSignature > #6/5/2015# + 56)) AND (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ...

[Dano]

HI Jonh,

Thank you very much for your help.

Did not get how to run the test query.

[jonh]

create > query design > close the 'show table' dialog > SQL > paste sql > Run

[Dano]

Hi Jonh,

I guess the june 5th add-on work but did not return anything. Forgot to say that when before as to return june 5th

Here is the complete codeline



"WHERE (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ((tbl_Tache.TypeTacheID) Is Null Or (tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID) Is Null Or (tbl_Tache.MachineID)=34)) " & _

[jonh]

I can't help with specific sql issues unless you are willing to post up a (test) copy of your database.

[Dano]

Hi Jonh

How can I send you the complete database,

[jonh]

..

[Dano]

Hi Jonh have you received the database?

[jonh]

Hi

I got the file but the tables are just links to another database so not much use.