Help please

[Dano60]

Hello,

Not a programmer but know access a bit. Need help, have to had another option like the underlined option below. where dog is selected in field pet from a field in another form. That field is in tbl_Produit. (Dog and pet are just as example)

Thanks very much.

Private Sub cmd_FicheProd_Click()

Dim sqlString, DateTerminaisonCalc As String
Dim rs, rs2, rs3 As Recordset

sqlString = "SELECT tbl_Commande.DateSignature, tbl_Tache.DateFinReel, tbl_Commande_Produit.ProduitID, tbl_Tache.TypeTacheID, tbl_Tache.MachineID, tbl_Commande.ID_Commande " & _
"FROM (tbl_Commande_Produit INNER 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.DateSignature) Is Null) AND ((tbl_Commande_Produit.ProduitID)=" & NoProduit & ") AND ((tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID)=34)) OR (((tbl_Tache.DateFinReel) Is Null) AND ((tbl_Commande_Produit.ProduitID)=" & NoProduit & ") AND ((tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID)=34));"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If rs.RecordCount = 0 Then
sqlString = "SELECT tbl_Commande.DateExpedition, tbl_Commande_Produit.ProduitID, tbl_Commande.DateTerminaison " & _
"FROM tbl_Commande_Produit INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande " & _
"WHERE (((tbl_Commande_Produit.ProduitID)=" & NoProduit & "));"
Set rs2 = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)

If rs2.RecordCount > 0 Then
If Nz(rs2.Fields("[DateExpedition]"), 0) <> 0 Then
If Nz(rs2.Fields("[DateTerminaison]"), 0) = 0 Then
tempDate1 = CalcDateTerminaison(NoProduit)
If tempDate1 = 0 Then
tempDate2 = "0"
Else
tempDate2 = "#" & Month(tempDate1) & "-" & Day(tempDate1) & "-" & Year(tempDate1) & "#"
End If


sqlString = "UPDATE tbl_Commande_Produit INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande SET tbl_Commande.DateTerminaison = " & tempDate2 & " " & _
"WHERE (((tbl_Commande_Produit.ProduitID)=" & NoProduit & "));"
CurrentDb.Execute sqlString, dbFailOnError
End If
sqlString = "UPDATE tbl_Produit SET tbl_Produit.ImpressionFicheProd = 1 " & _
"WHERE (((tbl_Produit.ID_Produit)=" & NoProduit & "));"
CurrentDb.Execute sqlString, dbFailOnError

DoCmd.OpenReport "rpt_FicheProductionAupel", acViewPreview, , "[tbl_Produit].[ID_Produit]=" & NoProduit, , "Produit"
DoCmd.RunCommand (acCmdZoom100)
Else
MsgBox "Il n'y a pas de date d'expédition!", vbCritical, "Date d'expédition"
End If
End If
rs2.Close
Else
MsgBox "Certaine(s) date(s) nécessaire à la création de la fiche de production sont manquantes!", vbCritical, "Date manquante"
End If

rs.Close

[June7]

Please post lengthy code within CODE tags to retain indentation and readability.

Don't understand what you want. You want another option to do what?

[orange]

Dano,

This does not work the way you think

Dim sqlString, DateTerminaisonCalc As String
Dim rs, rs2, rs3 As Recordset

IN Access vba you must explicitly declare the variable type. If you do not, variables will default to Variant type.

You can do this

Dim sqlString As String, DateTerminaisonCalc As String
Dim rs As Recordset, rs2 As Recordset, rs3 As Recordset

or this (or some combination)

Dim sqlString As String 
Dim DateTerminaisonCalc As String
Dim rs As Recordset 
Dim rs2 As Recordset 
Dim rs3 As Recordset

It is often suggested to be explicit with the recordset. eg Dim rs As DAO.Recordset, rs2 As DAO.Recordset, rs3 As DAO.Recordset

I suggest you provide a description of what you are trying to do with an example(s) rather than have readers review your code and make an educated guess as to your requirement. If you prefer you can write your description in French and have Google translate (or other) convert to English. It is best to have a clear description, make a model and test the model using pencil and paper rather than jumping into physical Access.

Good luck.

[Dano60]

Thanks for the reply, I'm French and have almost no experience in code.

I have a report that is call rpt_FicheProductionAupel.

When I press see rpt_FicheProductionAupel button from frm_DetailProduit_Scolaire_E that report come up. Now, on that report there a field with the colour of that product. What I want to do is to prevent the report to come up if it contain the colour "to be determined" is the field colour. Need to keep the option to be determined as colour, it's useful when the client is not decided on the colour.

I hope it's ok?

[Dano60]

Thanks for helping!!!!!

[June7]

Is this a report with a single record that has Colour field? That colour field is viewed on the form?

Maybe don't execute code if field value is "to be determined".

If Me.Colour = "to be determined" Then
MsgBox "Colour to be determined, report canceled."
Else
.... continue with your original code to open report.

If that field is not viewed on form and it is not available in any of the recordsets in the code, perhaps do a DLookup() or DCount() test, something like:

If DCount("*", "tablename", "ID=" & Me.ID & " AND Colour='to be determined'") > 0 Then
MsgBox ...

Or will this report have multiple records with Colour and some will be 'to be determined'?

[orange]

In the report's record source, do not select records where colour = "to be determined"

[Dano60]

Yes, that colour field is on the form.

I still want to alert the employee that the colour is "to be determined" and need to be update. vbCritical message, I think.

[Dano60]

Yes, this a report with a single record that has a Colour field.
Yes, that colour field is viewed on the form.

Question, what is the Me in: If Me.colour?

[June7]

Me. is alias for the object the code is behind. Instead of typing out the complete form or report reference as in Forms!myFormNameHere, just use Me.

[orange]

I suggest you provide a description of what you are trying to do with an example(s) rather than have readers review your code and make an educated guess as to your requirement. If you prefer you can write your description in French and have Google translate (or other) convert to English. It is best to have a clear description, make a model and test the model using pencil and paper rather than jumping into physical Access.

Provide sample data of what you have, and another of what you want/expect.

Good luck.

[Dano60]

I've tried to put a print screen of the form and report on here does not seem to be able to!!!!

Wasted 1 hour writing up that explaination!!!!!