Use field value as query (1 Viewer)

[vidus]

Hey! I have a stored procedure on my DB, a query with the following where clause:

WHERE      (Job = @Job)

This asks the user for a job number... but how can I simply use the value of [Job] for the record that is being viewed?

 

[PNGBill]

Stored Procedure ? or just a query?

You can have a stored procedure - Function that uses the control on your form as it's variable.

eg SomeNameFunction(SomeControlName As String, Integer??) As Long, Currency??

In your form you will have this as the data source of a control

=as above but in the parenthesis you would type the control name that holds the value you want.

Another way is to have a command button on your form that when clicked it activated your query or some other task.
In the command button's code, you have a reference to the form control you refer to above.

 

[vidus]

Stored Procedure ? or just a query?

You can have a stored procedure - Function that uses the control on your form as it's variable.

eg SomeNameFunction(SomeControlName As String, Integer??) As Long, Currency??

In your form you will have this as the data source of a control

=as above but in the parenthesis you would type the control name that holds the value you want.

Another way is to have a command button on your form that when clicked it activated your query or some other task.
In the command button's code, you have a reference to the form control you refer to above.

Its a stored procedure. What you said about having the reference to the form control on the button, how would this be done?

We do use a button to activate the store procedure query, which generates an input box.

 

[PNGBill]

Have you looked at the code of your on click event for the command button?

Here is an example:

Private Sub CmdFrmSingleLoanStatement_Click()
On Error GoTo Err_CmdFrmSingleLoanStatement_Click

    Dim strWhere As String
    
    If Me.Dirty Then    'Save any edits.
        Me.Dirty = False
    End If

    If Me.NewRecord Then 'Check there is a record to print
        MsgBox "Select a record to print"
    Else
      [COLOR=SeaGreen]  strWhere = "[LDPK] = " & Me.[LDPK]
        DoCmd.OpenReport "RptStatementNewStyle", acViewPreview, ,[/COLOR] strWhere
    End If

    
Exit_CmdFrmSingleLoanStatement_Click:
    Exit Sub
    
Err_CmdFrmSingleLoanStatement_Click:
    MsgBox Err.Description
    Resume Exit_CmdFrmSingleLoanStatement_Click

End Sub

Green writing creates the link between [LDPK] in the Report about to be previewed and the form control LDPK.

They don't have to be the same name, just be the same data ie CustomerID

I use this link which assisted in the code above.
http://allenbrowne.com/tips.html

 

[vidus]

I just have no idea how to apply this to my situation. Ill just stick with the input box because I dont know how to do this.

 

[PNGBill]

Fair comment but you will be surprised at how soon it does start to become a little easy to use.

Your wizard can do most, if not all of this.

If you use the wizard to create a button to preview a report. Later, in design view, look at the button's properties and under Event, On Click you will see 3 dots. click on these and the Code the wizard created is displayed.

I just replaced this code with the one on the web site and edited the Report name and names for the link and wala!

You can do a lot of simple vba code this way to open forms, reports etc and when in strife you can get good vba support from this forum.

Check the number of users on the different forum sections and you usually see more in the vba section. - actually this may mean more needing help
But just try when you are ready.

 

[vidus]

Fair comment but you will be surprised at how soon it does start to become a little easy to use.

Your wizard can do most, if not all of this.

If you use the wizard to create a button to preview a report. Later, in design view, look at the button's properties and under Event, On Click you will see 3 dots. click on these and the Code the wizard created is displayed.

I just replaced this code with the one on the web site and edited the Report name and names for the link and wala!

You can do a lot of simple vba code this way to open forms, reports etc and when in strife you can get good vba support from this forum.

Check the number of users on the different forum sections and you usually see more in the vba section. - actually this may mean more needing help
But just try when you are ready.

Thanks PNGbill, maybe I will add this in the future.