Open form based on criteria 1

[skyline666]

Hi,

A user selects a project from a combo box on a form, and then clicks a command button to open up a different form which shows all risks for that project. The form is called RiskRegister, and has a subform called Risk (which shows all the risks). In the table Risk, there is a field called Status, and what I would like to do is open up the form/subform and display all risks where Status equals Open. I tried this code with no success:

Private Sub cmbOpenRiskRegister_Click()
On Error GoTo Err_cmbOpenRiskRegister_Click

Dim stLinkCriteria As String

    stLinkCriteria = Forms!RiskRegister!Risk.Form.Status = "Open"

    DoCmd.OpenForm "RiskRegister", acNormal, , stLinkCriteria, , acNormal
    Me.Visible = False

Exit_cmbOpenRiskRegister_Click:
    Exit Sub

Err_cmbOpenRiskRegister_Click:
    MsgBox Err.Description
    Resume Exit_cmbOpenRiskRegister_Click
    
End Sub

The error I get is "Cant find the form RiskRegister referred in the VB code". If I take out the stLinkCriteria it works fine albeit showing all risks.

Many thanks in advance,

Andrew

 

[jordanking]

what happens if you change

Forms!RiskRegister!Risk.Form.Status = "Open"

to

Forms!RiskRegister!Risk.Status = "Open"


is the subform based on a table or a query? If it is a query, you change change that dynamically. Does the subform take every open risk, or only the ones associated with he project in the main form.



.....
I'd rather be surfing

 

[skyline666]

I get the same error when I change it. The subform is based on the table Risk, and is linked to a text box called ProjectID on the main form (and also CategoryID). The txtProject text box stores which Project the user has chosen. As for CategoryID, the main form is based on the table Category, and again which ever Category the user selects (which is done on the main form itself, there are next/prev category buttons), it is stored in txtCategory. There is a Category field in Risk aswell.

 

[skyline666]

I tried doing this aswell just now, but get another error saying Incorrect syntax near 'RiskRegister':

Dim stStatus As String

stStatus = "Open"
stLinkCriteria = "Forms!RiskRegister!Risk.Form.Status = '" & stStatus & "'"
DoCmd.OpenForm "RiskRegister", acNormal, , stLinkCriteria, , acNormal

 

[MakeItSo]

You reference a subform on the form you are about to open, but which of course is not open yet.
No go.

Besides that you have a double =
Do you wish to use the entire string on the right hand side as criterion? then you need to enclose it in quotes.

Rather use the data source of the form as criterion, not the form.

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell

 

[jordanking]

is status the name of the field or the control,

if it is the name of the column in the underlying table try replacing

Forms!RiskRegister!Risk.Form.Status = "Open"

with

Status = "Open"

In theory, the criteria string is supposed to be a valid sql statement, a WHERE clause, without the WHERE word.



.....
I'd rather be surfing

 

[skyline666]

How would I use the data source to for the criteria that I need MakeItSo?

Also, I thought I did enclose the string on the RHS in quotes:

stLinkCriteria = [red]"[/red]Forms!RiskRegister!Risk.Form.Status = '" & stStatus & "'[red]"[/red]

Is that code not correct?

Andrew

 

[jordanking]

sorry should be

stLinkCriteria = "Status = 'Open'"


.....
I'd rather be surfing

 

[skyline666]

Status is the name of a field in the table Risk. I tried this, but get an Invalid column name 'Status', and I think this is because it is trying to find the column Status in the main form RiskRegister, whereas it is in the subform Risk.

Dim stLinkCriteria As String
Dim stStatus As String

    stStatus = "Open"
    
    stLinkCriteria = "Status = '" & stStatus & "'"

    DoCmd.OpenForm "RiskRegister", acNormal, , stLinkCriteria, , acNormal

Andrew

 

[skyline666]

I tried it with the change with no luck still.

 

[jordanking]

sorry, i see now. You are right. You cannot set the criteria for a sub form in the open call of the parent form.

If this form logic will never change, ie, you will always want "status = open", I suggest changing the recordsource of the subform to a query. It will still work the same.

If you want to be able to change the status value and have the subform records change, you can reference a control in the query criteria and then use a form event to requery the subform after the status value control changes.

or try this
i have not tested it though,

Dim stLinkCriteria As String
Dim stStatus As String

    stStatus = "Open"
    
    stLinkCriteria = "Status = '" & stStatus & "'"

    DoCmd.OpenForm "RiskRegister", acNormal
    Me.Risk.Form.Filter = strLinkCriteria
    Me.Rist.Form.FilterOn = True

.....
I'd rather be surfing

 

[jordanking]

spelling error
Me.Rist.Form.FilterOn = True
to
Me.RisK.Form.FilterOn = True

.....
I'd rather be surfing

 

[skyline666]

awesome thanks jordanking that worked, have a star lol

ps. i'd rather be surfing aswell, or at least go karting or something other than work lol

 

[jordanking]

sweet, glad I could help

.....
I'd rather be surfing