En passant Valeur du Paramètre de Requête Access à l'aide de vba

Notre exigence -

Nous avons peu de fournisseurs et nous voulons produire un rapport mensuel des achats de chaque fournisseur, au cours du mois et de l'exportation vers excel. Je pense que la solution est de créer un paramètre de requête où je peux passer la valeur de l'identificateur de fournisseur, le Mois et l'Année. La liste des fournisseurs qui ne cesse de changer et est stockée dans une table séparée.
Donc, en gros, je devrais être capable de lire séquentiellement le fournisseur ID de la table et de passer en paramètre à ma requête pour générer le rapport du fournisseur.

Le plus proche des solutions que j'ai trouvé pour mon obligation (qui sont de nature similaire) -

L'exportation de jeu d'enregistrements dans une feuille de calcul

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/filtering-a-query-used-by-docmdtransferspreadsheet/06d8a16c-cece-4f03-89dc-89d240436693

Pourquoi je pense qu'il pourrait être une meilleure solution -

Dans la solution proposée, nous créons plusieurs requêtes et la suppression de ces. Sur le plan conceptuel, je pense que cela doit être un moyen de créer un paramètre de la requête et de l'utilisation de la boucle do while de façon séquentielle de transmettre la valeur du paramètre (DeptName dans l'exemple ci-dessus) à la requête et d'exporter les résultats vers excel.

Je devrais être capable de réaliser ceci, si je peux utiliser vba pour passer une valeur à un paramètre de la requête. Et c'est ce que je n'ai pas encore été en mesure de comprendre.

Mise à jour le 24-Fév -

Voici le code que j'ai écrit -

Private Sub Monthly_Supplier_Sales_Report_Click()
Dim strDirectoryPath As String
Dim DateFolderName As String
DateFolderName = Format$((DateSerial(year(Date), month(Date), 1) - 1), "YYYY MM")
strDirectoryPath = "C:\dropbox\Accounting\Sales Reports\" & DateFolderName
If Dir(strDirectoryPath, vbDirectory) = "" Then MkDir strDirectoryPath

Dim Filename As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim DesignerCode As String
Dim month1 As String
Dim year1 As Integer
Dim Query1 As DAO.QueryDef
Dim query2 As DAO.QueryDef
Dim rsDesigner As DAO.Recordset

Set rsDesigner = CurrentDb.OpenRecordset("Designer Details Master")

Do While Not rsDesigner.EOF
DesignerCode = rsDesigner![Designer Code]
month1 = "Jan" 'right now hardcoded, will call this programatically
year1 = 2014   'right now hardcoded, will call this programatically

strSQL1 = "SELECT * FROM [Sales Report Generation Data] WHERE [designer code] = '" & DesignerCode & "' AND [Shipping Month]= '" & month1 & "' AND [Shipping Year]=" & year1

strSQL2 = "SELECT * FROM [Sales Report Generation - Monthwise Inventory Snapshot] WHERE [designer code] = '" & DesignerCode & "' AND [Snapshot Month]= '" & month1 & "' AND [Snapshot Year]= " & year1

Set Query1 = CurrentDb.CreateQueryDef(Name:="TempSalesQuery", SQLText:=strSQL1)
Set query2 = CurrentDb.CreateQueryDef(Name:="TempInventoryQuery", SQLText:=strSQL2)

Filename = strDirectoryPath & "\" & DesignerCode & Format$(Now(), " yyyy mm") & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempSalesQuery", Filename, False, "Sales Report"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempInventoryQuery", Filename, False, "Inventory"

CurrentDb.QueryDefs.Delete "TempSalesQuery"
CurrentDb.QueryDefs.Delete "TempInventoryQuery"

rsDesigner.MoveNext
Loop

End Sub

Au lieu de cela, La logique, que je veux appliquer est -

Do While Not 
assign Value to Parameter 1 = rsDesigner![Designer Code]
assign Value to Parameter 2 = Month1
assign Value to Parameter 3 = Year1
Run the two Parameter queries, for which about three parameters are the input value and export to excel in respective sheets.
Loop

Juste que je n'ai pas encore été en mesure de comprendre - comment atteindre cet objectif.

Pas sûr de savoir comment beaucoup de temps vous êtes ou si vous êtes à l'aise avec les étapes décrites dans les liens que vous avez posté, mais voici ma suggestion. (1) créer un formulaire simple avec (a) une zone de liste pour les 12 mois; (b) zone de liste pour les prochaines années; (c) listbox/zone de liste déroulante pour les fournisseurs ** omettre si vous souhaitez exécuter le rapport pour TOUS les fournisseurs; (d) bouton de commande pour la présentation du rapport; (e) ajouter le code VBA dans l'événement clic du bouton pour sélectionner des valeurs dans listbox/zone de liste déroulante pour créer un filtre pour le jeu d'enregistrements, puis ouvrir le rapport en passant le filtre. Si vous êtes intéressé par cette démarche, je peux ajouter plus de détails sur la manière de l'accomplir.
J'ai été capable d'écrire le code comme par la solution donnée dans les deux liens et d'obtenir le résultat souhaité. Mais juste que je me sens il devrait y avoir une meilleure solution. Même dans ce que vous suggérez, je me demande pourquoi devrions-nous à créer le formulaire pour récupérer la valeur, parce que mon plan est de les utiliser pour s'exécuter sur tous les 4 / 5e de mois pour aller chercher les rapports pour le mois précédent pour tous les fournisseurs. Donc, idéalement, nous devrions être en mesure de définir la valeur de Mois, l'Année et le fournisseur à l'aide du code vba.
Bien sûr, vous pouvez l'exécuter automatiquement -- j'ai juste pensé que vous souhaitez peut-être la possibilité d'exécuter des rapports à tout moment vous avez voulu, et pour un fournisseur sélectionné, si vous avez seulement besoin pour cela. La requête, comme indiqué dans les liens que vous avez joint, ne doivent pas être supprimés, et vous pouvez utiliser une requête pour "Transférerfeuillecalcul" (au moins dans Office 2010 et au-dessus). Vous devriez avoir votre requête acceptent des paramètres (ou Fonctions) pour la date et le fournisseur (sauf si vous voulez tous les fournisseurs), puis définissez les paramètres dans VBA.
L'édition de la question ci-dessus - d'inclure le code que j'ai utilisé que par les deux solution donnée.

OriginalL'auteur Rohit Bansal | 2014-02-23

0

Ici est une solution. Notez que j'ai créé Fonctions que les deux requêtes à utiliser.
Il suffit de créer votre deux requêtes et de les enregistrer (voir l'exemple de SQL ci-dessous), ajouter votre code de choisir les dates et tout devrait bien se passer.
```
Option Compare Database
Option Explicit

Dim fvShipMonth     As String
Dim fvShipYear      As Integer
Dim fvDesignerCode  As String

Public Function fShipMonth() As String
    fShipMonth = fvShipMonth
End Function

Public Function fShipYear() As Integer
    fShipYear = fvShipYear
End Function

Public Function fDesignerCode() As String
    fDesignerCode = fvDesignerCode
End Function

Private Sub Monthly_Supplier_Sales_Report_Click()
Dim Filename    As String
Dim strSQL1     As String
Dim strSQL2     As String
Dim DesignerCode As String
Dim month1      As String
Dim year1       As Integer
Dim rsDesigner  As DAO.Recordset

'SAMPLE SQL
'SELECT * FROM [Sales Report Generation Data] " & _
'WHERE [designer code] = '" & fDesignerCode() & "' AND [Shipping Month]= '" & fShipMonth() & "' AND [Shipping Year]=" & fShipYear()

    fvShipMonth = "Jan"
    fvShipYear = 2014
    Set rsDesigner = CurrentDb.OpenRecordset("Designer Details Master")
    Do While Not rsDesigner.EOF
        fvDesignerCode = rsDesigner![Designer Code]
        Filename = strDirectoryPath & "\" & DesignerCode & Format$(Now(), " yyyy mm") & ".xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "<your Query 1>", Filename, False, "Sales Report"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "<your Query 2>", Filename, False, "Inventory"
        rsDesigner.MoveNext
    Loop
    rsDesigner.Close
    Set rsDesigner = Nothing
End Sub
```
Bonjour Merci pour votre solution. Mais il ne semble pas fonctionner pour moi. En fait, il a fallu le temps pour moi de comprendre le concept de la fonction publique, etc. Mais les fonctions semblent être de retour pas de valeur. J'ai inséré un msgbox et vérifié que les fonctions sont de retour vide/zero. Quelle pourrait être la raison? Suis-je en train de faire quelque chose de mal?
Je ne sais pas comment vous avez mis en place ma suggestion - avez-vous copié TOUT le code? Où est votre msgbox - à l'intérieur de la "Sous Monthly_Supplier_Sales_Report_Click'? NOTEZ que j'ai édité mon code 22 heures parce que j'ai oublié le () en SQL après les noms de fonction. Également être prudent car les noms de variable sont similaires à la fonction des noms!! Voici comment tester à l'AIDE de LA ci-DESSUS: (1) placer le msgbox suivante juste après que vous avez défini fvShipMonth & fvShipYear: MsgBox "Mo:" & fvShipMonth & vbTab & "Yr:" & fvShipYear & vbTab & "fMo:" & fShipMonth() & vbTab & "l'ex république yougoslave de:" & fShipYear()
1. Copié la fonction publique que dans le module 2. modifié requêtes de paramètre pour définir la valeur de trois paramètres égale à fshipmonth() et ainsi de suite, 3. copié collé votre code, 4. mis à jour les noms des requêtes dans le transferspreadsheat méthode. Cela a créé les fichiers excel, mais tous étaient vides. Donc, créé une boîte de message à l'intérieur de la boucle MsgBox (fDesignerCode() & ", " & fShipMonth() & ", " & fShipYear()) cette retourné , ,0
J'ai juste couru votre msgbox et eu |, Jan, 2014| cela signifie que vous avez quelque chose de mal réglé. Veuillez annuler MANUELLEMENT les valeurs avant la msgbox avec deux lignes: fvShipMonth = "Jan" et fvShipYear = 2014 Puis voir ce que vos msgbox produit.
Toujours Pas de chance! Je pense qu'il ya quelque chose de mal avec la façon dont la fonction est mis en place, qui je suis ne pas être en mesure de comprendre. Mais de toute façon, j'aime bien la solution que vous avez fourni et j'ai appris quelques nouvelles choses. Merci beaucoup!

OriginalL'auteur Wayne G. Dunn

-1

Ouvrez votre base de données
Créer un NOUVEAU module
Collez le code suivant
Placez votre curseur à l'intérieur de la Fonction TEST_MY_CODE()
Appuyez Sur La Touche F5

Coller les résultats à partir de la fenêtre de votre réponse.

Option Compare Database
Option Explicit

Dim fvShipMonth     As String
Dim fvShipYear      As Integer
Dim fvDesignerCode  As String

Public Function fShipMonth() As String
    fShipMonth = fvShipMonth
End Function

Public Function fShipYear() As Integer
    fShipYear = fvShipYear
End Function

Public Function fDesignerCode() As String
    fDesignerCode = fvDesignerCode
End Function

Function TEST_MY_CODE()
    My_Click_EVENT      ' Test the code I provided
End Function

Private Sub My_Click_EVENT()
Dim month1      As String
Dim year1       As Integer

fvShipMonth = "Jan"
fvShipYear = 2014
Debug.Print "**** START TEST ****"
Debug.Print "fvShipMonth = " & fvShipMonth
Debug.Print "fvShipYear = " & fvShipYear
Debug.Print "fShipMonth() = " & fShipMonth()
Debug.Print "fShipYear() = " & fShipYear()
Debug.Print "**** END TEST ****"

End Sub

OriginalL'auteur Wayne G. Dunn

Vous devez vous connecter pour publier un commentaire.