Open a Form with Matching Data otherwise open blank form

[stupiet]

To anyone who can help,

On the "Purchase Info" form you can type in various info about the purchase. Also, I want to have a button that will open another form called "Purchase Status". On this form I will enter information such as whether it has been paid already.

Now, what I want is when I click on that button it will open the "Purchase status" for that specific purchase. However, if the status has not been entered yet, it should open a new blank "Purchase Status" form.

I've tried doing it by using the following where condition:

Forms![Puchase Status]![Purchase #]=Forms![Purchase Info]![Purchase #]

However, when I use this condition it will open a complete white form with nothing on it when there's no "purchase status" info for that specific purchase. It should open a new blank form so I can enter the purchase status.

Please help me fix this problem. If you are explaining using VB script, please write the whole code as I am not that great in VB.

Let me know if you need more info as well.

Thanks,

Stupiet

 

[a9]

This is a two stage process, assuming you open the second form each time the button is clicked on the first. In this case the button on the first form is named "Details" and opens the second. This example makes use of the "OpenArgs" system variable.

The "onClick" code for the button on the first form is:

Private Sub Details_Click()
On Error GoTo Err_Details_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Salesman Personal Details Form"

stLinkCriteria = "[Payroll_Number]=" & "'" & Me![Payroll_Number] & "'"
Screen.PreviousControl.SetFocus
DoCmd.OpenForm stDocName, acNormal, , , acFormEdit, , Me![Payroll_Number]

Exit_Details_Click:
Exit Sub

Err_Details_Click:
MsgBox Err.Description
Resume Exit_Details_Click

End Sub


In the second Form using the "On Open" event this code either opens the form filtered or in add new record mode.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
If OpenArgs = "" Then
Me.Filter = ""
DoCmd.GoToRecord , , acNewRec
Else
Me.Filter = "Payroll_Number ='" & OpenArgs & "'"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 2, , acMenuVer70
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox "Error Opening Form " & Err.Description, vbCritical + vbOKOnly, "Error"
Resume Exit_Form_Open
End Sub

 

[a9]

You don't need the lines

Dim stLinkCriteria As String



stLinkCriteria = "[Payroll_Number]=" & "'" & Me![Payroll_Number] & "'"

in the first piece of code these were accidently left in from my original code - sorry.

 

[stupiet]

Thanks a lot for your help. It seems to be working!

 

[a9]

I thought of a much more elegant solution on my way home last night. Code below which is run from the starting form. NO on open action required in the destination form.

Private Sub Details_Click()
On Error GoTo Err_Details_Click
Dim MyDb As Database, MyRst As Recordset, Recs As Integer, SQLString As String
Dim stDocName As String, stLinkCriteria As String
Set MyDb = CurrentDb
SQLString = "SELECT Count(*) AS RECORDS FROM tabSalesDetails WHERE (((tabSalesDetails.Payroll_Number)='" & Me![Payroll_Number] & "'));"
Set MyRst = MyDb.OpenRecordset(SQLString, dbOpenDynaset)
MyRst.MoveFirst
Recs = MyRst!records
MyRst.close
Set MyDb = Nothing
stDocName = "Salesman Personal Details Form"
If Recs > 0 Then

stLinkCriteria = "[Payroll_Number]=" & "'" & Me![Payroll_Number] & "'"
Screen.PreviousControl.SetFocus

DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
End If

Exit_Details_Click:
Exit Sub

Err_Details_Click:
MsgBox Err.Description
Resume Exit_Details_Click

End Sub

 

[stupiet]

Thanks again for your help. I appreciate you putting time into this.