DoCmd.OpenForm where condition is null

[skyline666]

Hi,

I am using Access 2003 project, with SQL Server 2005. I have a form, which has a command button that opens up another form called riskregister (which has a subform in called risk). I have this code that does this:

Private Sub cmbOpenRiskRegister_Click()

Dim stLinkCriteria As String

    stLinkCriteria = "[forms]![riskregister]![risk].form.[DateClosed] =  null"
    
    DoCmd.OpenForm "RiskRegister", acNormal, , stLinkCriteria, , acNormal
    Me.Visible = False

End Sub

Without the stLinkCriteria bit, it works. What I want to do is open up the form as normal, but only open where the column DateClosed is Null (so all records with something in arent displayed).

Many thanks in advance,

Andrew

 

[Remou]

The criteria refer to a field in the underlying table, for example:

"DateClosed Is Null"

However, it seems that you wish to only include records where the underlying table of a subform has a null date. If this is the case, things get a bit more complicated.

 

[skyline666]

Hi Remou,

I tried using "DateClosed Is Null" before but it didn't work as the DateClosed field is in the subform Risk, whereas the OpenForm command is opening the form RiskRegister.

Yes I want to only show records where the dateclosed has no records in the subform. I have made a stored procedure that works, but don't know how to use it in the open form statement. Here is the stored procedure I have:

alter PROCEDURE [dbo].[usp_DateClosed]
@ProjectID int,
@CategoryID int

AS
set nocount on

SELECT *
FROM Risk
WHERE DateClosed is null and ProjectID = @ProjectID AND CategoryID = @CategoryID

set nocount off

Is there anyway to use the stored procedure in the openform command? If not how would I do it the other way?

Thanks,

Andrew

 

[Remou]

You need to set the RecordSource of the main form, I think. In Jet, it would go something like:

[tt]Select * From MainTable Left Join Risk On MainTable.ProjectID=Risk.ProjectID Where Risk.DateClosed Is Null[/tt]

You can set the RecordSource of a form programmatically.

 

[skyline666]

Ok i'll try this. Working on something else aswell at the moment so will get back to you.