By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,094 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

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

P: n/a
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:::
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.     QueryDate = [Forms]![Bond Price Exception 1]![start_date As Date]
  3.     QueryDate = [Forms]![Bond Price Exception 1]![end_date As Date]
  4.     PctChange = [Forms]![Bond Proce Exception 1]![trigger As Single]
  5.  
  6.     Call BondPriceException([start_date As Date], [end_date As Date], [trigger As Single])
  7. End Sub
This is my VB code for the module:::

Expand|Select|Wrap|Line Numbers
  1. Sub BondPriceException()
  2. On Error Resume Next
  3.     Dim sqltext As String
  4.     Dim start_date As Date
  5.     Dim end_date As Date
  6.     Dim trigger As Single
  7.  
  8.     start_date = "9/17/2010"
  9.     end_date = "9/24/2010"
  10.     trigger = "0.02"
  11.  
  12.     sqltext = "SELECT DISTINCT [Bond Price].Date, [Bond Price].Instrument, [Bond Price].[MTM Price], [Bond Price].[Current Rate] INTO TempException1 " & _
  13.                 "FROM [Bond Price] WHERE [Bond Price].Date=#" & start_date & "#"
  14.     DoCmd.RunSQL sqltext
  15.  
  16.     sqltext = "SELECT DISTINCT [Bond Price].Date, [Bond Price].Instrument, [Bond Price].[MTM Price], [Bond Price].[Current Rate] INTO TempException2 " & _
  17.                 "FROM [Bond Price] WHERE [Bond Price].Date=#" & end_date & "#"
  18.     DoCmd.RunSQL sqltext
  19.  
  20.     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] " & _
  21.                 "INTO ExceptionRpt FROM " & _
  22.                 "(((SELECT t2.Date, t1.Instrument, t1.[MTM Price] AS [Current Price], t2.[MTM Price] AS [Previous Price] FROM TempException2 t2 " & _
  23.                 "INNER JOIN TempException1 t1 ON (ABS(t1.[MTM Price]/t2.[MTM Price]-1)>" & trigger & ") AND (t2.Instrument=t1.Instrument)) tp " & _
  24.                 "INNER JOIN [Bond] b ON b.Instrument = tp.Instrument) " & _
  25.                 "INNER JOIN [Bond Trade] bt ON bt.Instrument = b.Instrument) " & _
  26.                 "INNER JOIN [Bond Trade Nominal] btn ON btn.Reference = bt.Reference AND btn.Date = tp.Date "
  27.     DoCmd.RunSQL sqltext
  28.  
  29.     sqltext = "drop table TempException1"
  30.     DoCmd.RunSQL sqltext
  31.  
  32.     sqltext = "drop table TempException2"
  33.     DoCmd.RunSQL sqltext
  34. End Sub
Oct 20 '10 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
it should be more like this I think
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.    dim StartDate  As Date,EndDate  As Date,PctChange As Single
  3.    StartDate = [Forms]![Bond Price Exception 1].[start_date]
  4.    EndDate  = [Forms]![Bond Price Exception 1].[end_date]
  5.    PctChange = [Forms]![Bond Proce Exception 1].[trigger]
  6.  
  7.    Call BondPriceException(StartDate, EndDate,PctChange)
  8. End Sub
  9.  
Oct 20 '10 #2

P: n/a
thanks but when i clicked run nothing happened.
Oct 20 '10 #3

NeoPa
Expert Mod 15k+
P: 31,271
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.
Oct 20 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.