How to create a form with parameters? (Using VB Microsoft Access)

[sorina]

I basically need a form like this:

Start date: _____
End Date: _______
Trigger: ________

Whereby the start date, end date and trigger are the parameters to be keyed in by the end user before the report is to be generated (only for those particular dates and trigger value)

So far this is my code in creating the form. Unfortunately it's wrong.

WRONG code for form:::

Private Sub Command2_Click()
    QueryDate = [Forms]![Bond Price Exception 1]![start_date As Date]
    QueryDate = [Forms]![Bond Price Exception 1]![end_date As Date]
    PctChange = [Forms]![Bond Proce Exception 1]![trigger As Single]
    
    Call BondPriceException([start_date As Date], [end_date As Date], [trigger As Single])
End Sub

This is my VB code for the module:::

Sub BondPriceException()
On Error Resume Next
    Dim sqltext As String
    Dim start_date As Date
    Dim end_date As Date
    Dim trigger As Single
    
    start_date = "9/17/2010"
    end_date = "9/24/2010"
    trigger = "0.02"

    sqltext = "SELECT DISTINCT [Bond Price].Date, [Bond Price].Instrument, [Bond Price].[MTM Price], [Bond Price].[Current Rate] INTO TempException1 " & _
                "FROM [Bond Price] WHERE [Bond Price].Date=#" & start_date & "#"
    DoCmd.RunSQL sqltext
    
    sqltext = "SELECT DISTINCT [Bond Price].Date, [Bond Price].Instrument, [Bond Price].[MTM Price], [Bond Price].[Current Rate] INTO TempException2 " & _
                "FROM [Bond Price] WHERE [Bond Price].Date=#" & end_date & "#"
    DoCmd.RunSQL sqltext
    
    sqltext = "SELECT tp.Date, tp.Instrument, tp.[Current Price], tp.[Previous Price], b.Typology, bt.[Trade ID], bt.[Trade Date], bt.[Deal Price], bt.Portfolio, btn.[Nominal Quantity] " & _
                "INTO ExceptionRpt FROM " & _
                "(((SELECT t2.Date, t1.Instrument, t1.[MTM Price] AS [Current Price], t2.[MTM Price] AS [Previous Price] FROM TempException2 t2 " & _
                "INNER JOIN TempException1 t1 ON (ABS(t1.[MTM Price]/t2.[MTM Price]-1)>" & trigger & ") AND (t2.Instrument=t1.Instrument)) tp " & _
                "INNER JOIN [Bond] b ON b.Instrument = tp.Instrument) " & _
                "INNER JOIN [Bond Trade] bt ON bt.Instrument = b.Instrument) " & _
                "INNER JOIN [Bond Trade Nominal] btn ON btn.Reference = bt.Reference AND btn.Date = tp.Date "
    DoCmd.RunSQL sqltext
    
    sqltext = "drop table TempException1"
    DoCmd.RunSQL sqltext
    
    sqltext = "drop table TempException2"
    DoCmd.RunSQL sqltext
End Sub

[Delerna]

it should be more like this I think

Private Sub Command2_Click()
   dim StartDate  As Date,EndDate  As Date,PctChange As Single
   StartDate = [Forms]![Bond Price Exception 1].[start_date]
   EndDate  = [Forms]![Bond Price Exception 1].[end_date]
   PctChange = [Forms]![Bond Proce Exception 1].[trigger]

   Call BondPriceException(StartDate, EndDate,PctChange)
End Sub

[sorina]

thanks but when i clicked run nothing happened.

[NeoPa]

That may well be, but the problem you asked about nevertheless has that fix.

If your other code doesn't work then that's maybe something you need to ask for help with separately. As far as this question goes, about tying in the parameters from the form, Delerna's response is perfect and works.