Parameter Passing with VBA and Access

[mikem1260]

There are some places in my project where I need to pass parameters to Access queries that feed Access reports, and don't want the user to have to punch in data.

1. I want to feed data from the VBA form to a snapshot that is to be E-mailed. I have the primary key field, called ID, on the form, and this would be used to choose the record. My Code:

Private Sub cmdMail_Click()
On Error GoTo Err_cmdMail_Click

    Dim stDocName As String

    stDocName = "tblMainData"
    DoCmd.SendObject acReport, stDocName, acFormatSNP, "<email adress>", , , "New Question"
 
Exit_cmdMail_Click:
    Exit Sub

Err_cmdMail_Click:
    MsgBox (Str(Err.Number) + " " + Err.Description)
    Resume Exit_cmdMail_Click
    
End Sub

2. I want the user to be able choose a date range for some other reports. I have the date parameters built into the queries. What I'd like to do is put a couple of date fields onto a form and automatically pass these values to the query parameters.

3. I want the users to be able to pull up records that they or another specific user have entered. I tried to use a multi-column combo box, but couldn't get it to recognize my choice. I looked for a grid to populate, but couldn't find one for which I wouldn't have to buy a license. I tried populating a similar form with nav buttons, this worked, but the manager wants something more intuitive. This would be keyed off the user's name.

Thanks in advance for your assistance.

 

[PHV]

Have your queries reading the parameters from the form(s).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mikem1260]

There's the crux of my problem: How does one do that? I'm a little rusty at Access and VBA.

Mike

The light at the end of the tunnel is only a train 85% of the time.

 

[PHV]

The basic way, in the criteria cell of a date field in the query grid:
Between [Forms]![name of open parameter mainform]![name of start date control] And [Forms]![name of open parameter mainform]![name of end date control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886