opening a form with 2 different filters

[dixxy]

I am trying to open a form based on 2 different criteria.

I have a form [frmJobs] with a button to open [frmNewPO]. This form is an intermediate between jobs and actual purchase orders, so taht 1 job can have multiple Purchase Orders. This form will list all PO for that particular job. This form has a JobNumber field (bound to JobNumber), a list box taht shows all the po's for that job.

I would like this [frmNewPO] form to act in 2 different cases. First has described above, and second I would like this form to be used outside of jobs, so the user can create Purchase Orders for 'Stock'.

If the form is opened from the Jobs form, it is linked to the proper Job Number. If the form is opened on it's own then it has to goto 'Stock'. This is were i have trouble making this happened.

This is the code that opens the form form the jobs form:

Private Sub cmdPO_Click()
On Error GoTo Err_cmdPO_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmNewPO"
    
    stLinkCriteria = "[JobNumber]=" & "'" & Me![JobNumber] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

Exit_cmdPO_Click:
    Exit Sub

Err_cmdPO_Click:
    MsgBox Err.Description
    Resume Exit_cmdPO_Click
    
End Sub

This is the code i have so far on the open event of the frmNewPO:

Private Sub Form_Open(Cancel As Integer)

If IsFormOpen("frmJobs") Then
    Me.[txtJobNumber].DefaultValue = "[Forms]![frmJobs]![JobNumber]"
        MsgBox "jobs opened"  'debug

Else
    MsgBox "jobs not opened"  'debug
    Me.[txtJobNumber].DefaultValue = "stock"
'        Me.Filter("jobnumber") = "Stock"
End If

End Sub

after the else statment, should say 'goto record Stock', but i don't know how to acheive this.

Can someone shed some light on this matter?


Thanks,

Sylvain

 

[PHV]

Perhaps this ?
Me.Filter = "jobnumber='Stock'"
Me.FilterOn = True

Hope This Helps, PH.
FAQ219-2884 or FAQ181-2886

 

[dixxy]

thanks for the fast response. But now i get a 'Enter Parameter Value' message and it does not filter. here is the new code:

Private Sub Form_Open(Cancel As Integer)

If IsFormOpen("frmJobs") Then
    Me.[txtJobNumber].DefaultValue = "[Forms]![frmJobs]![JobNumber]"
        MsgBox "jobs opened"  'debug

Else
    MsgBox "jobs not opened"  'debug
'    Me.[txtJobNumber].DefaultValue = "stock"
'        Me.Filter("jobnumber") = "Stock"
Me.Filter = "txtJobNumber='Stock'"
Me.FilterOn = True
End If

End Sub

Thanks,

Sylvain

 

[dixxy]

got it...i thought i had to put the field name...

works now..

thanks

Thanks,

Sylvain

 

[PHV]

Why [!]txt[/!]JobNumber ?

 

[dixxy]

one more question if i may.

now the list box on this form is based on a SQL statment like this:

SELECT tblPurchaseOrders.PurchaseOrderNumber, tblPurchaseOrders.JobNumber
FROM tblPurchaseOrders
WHERE (((tblPurchaseOrders.JobNumber)=[Forms]![frmJobs]![JobNumber]));

if the Jobs form is not opened then i get promt for 'Parameter value' because of the where in the SQL.

How can i make this SQL look at the jobnumber if the Jobs form is opened, and filter itself based on the fileter from the form if Jobs foem is not opened?

Thanks,

Sylvain

 

[dixxy]

the txt is the prefix for the filed name. I thought your expression had to be the field name.

Thanks,

Sylvain

 

[PHV]

You're confusing field and control.
Did my suggestion work ?

 

[dixxy]

yes it did thank you very much.

Now i am on the second problem, filtering the List box. (as idicated above)

Thanks,

Sylvain

 

[PHV]

In the Load event procedure of the form:

Dim stJob As String
If IsFormOpen("frmJobs") Then
  stJob = [Forms]![frmJobs]![JobNumber]
Else
  stJob = "Stock"
End If
Me![[i]name of listbox[/i]].RowSource = "SELECT PurchaseOrderNumber, JobNumber" _
 & " FROM tblPurchaseOrders WHERE JobNumber='" & stJob & "'"

Hope This Helps, PH.
FAQ219-2884 or FAQ181-2886

 

[dixxy]

WOW....PHV YOU ARE THE MAN!!!!!....this works like a charm.

Thank you very much for your help, and QUICK reply's.

U are GOOOODDDDD!!!

Thanks,

Sylvain