Open Form Macro

[Samantha]

So, I have a datasheet form and in the double click event I have built this macro. I want to be able to double click the JobNumber and the detailed form open set to the specified job number.
The double click event opens the Enter Parameter Value Window, once I type in the number it goes correctly.

This is what I have so far, any help will be greatly appreciated!

Form Name= frmProposalDetails
Where Condition= [Forms]![frmProposalLog]![JobNumber]=[Forms]![frmProposalsDetails]![JobNumber]

Thanks,
Samantha

 

[boblarson]

The first part of your where condition should be the FIELD not the form.

so

Where Condition= [JobNumber]=[Forms]![frmProposalsDetails]![JobNumber]

And if JobNumber is a text field you probably will need:

Where Condition= [JobNumber] = Chr(34) & [Forms]![frmProposalsDetails]![JobNumber] & Chr(34)

 

[Samantha]

Hey Bob,

Thanks for your reply. The field is numerical and I have corrected the code although now I am receiving another error message. It is telling me invalid use of null on this line of code that is in the Sub Form_Current()

'If revision is checked make subform visible
Me.tblRevisionsSubform.Visible = Me.chk_Revision

 

[boblarson]

Hey Bob,

Thanks for your reply. The field is numerical and I have corrected the code although now I am receiving another error message. It is telling me invalid use of null on this line of code that is in the Sub Form_Current()

'If revision is checked make subform visible
Me.tblRevisionsSubform.Visible = Me.chk_Revision

Your check box is probably not set to be false by default (it can either be true, false, or null and when I have that issue I usually make them false as a rule unless they already have a value. But you can simply use:

Me.tblRevisionsSubform.Visible = Nz(Me.chk_Revision,0)

 

[Samantha]

Okay, now back to the initial problem. I changed the where condition to read [JobNumber]=[Forms]![frmProposalDetails]![JobNumber] it opens the form and it filters to a blank record instead. JobNumber is actually a text field (which I don't remember why I did that) I added in the &Chr(34) does not change the outcome.

 

[boblarson]

Make sure your form that you are opening does not have the DATA ENTRY property set to YES. It should be NO.

 

[Samantha]

Yes it is set to No.

 

[boblarson]

Quick question - can we move from a macro to VBA? It is easier to specify some things.

So for your macro if we use VBA instead it shoiuld go like this:

DoCmd.OpenForm "frmProposalDetails", acNormal, , "[JobNumber]=" & Chr(34) & Me!JobNumber & Chr(34)

 

[Samantha]

Beautiful, Works Perfect!

Thank you so much!

 

[Samantha]

Bob, I have another little glitch with this. The form that I am opening also has a navigation subform with several tabs. Prior to changing this code it worked. The subforms are "JobComments" which relates to the base table by JobNumber then there are others like "Billing" which relates to the base table by ServiceAddress. On each of the subforms under properties/filter I have it set to (ServiceAddress=tblProposals.ServiceAddress).

So on my datasheet form the double-click event opens the frmProposalDetails to a filtered view based on the job number. However, it does not continue to filter the navigation subform based on that value. When I remove the main filter then click on the tabs of the subform it filters the subform as it should??
Any Ideas

 

[boblarson]

Bob, I have another little glitch with this. The form that I am opening also has a navigation subform with several tabs. Prior to changing this code it worked. The subforms are "JobComments" which relates to the base table by JobNumber then there are others like "Billing" which relates to the base table by ServiceAddress. On each of the subforms under properties/filter I have it set to (ServiceAddress=tblProposals.ServiceAddress).

So on my datasheet form the double-click event opens the frmProposalDetails to a filtered view based on the job number. However, it does not continue to filter the navigation subform based on that value. When I remove the main filter then click on the tabs of the subform it filters the subform as it should??
Any Ideas

I'm afraid I'd have to take a look at it directly to understand what is going on. You can try uploading a copy if you first run COMPACT AND REPAIR and then zip it by right clicking and selecting SEND TO > COMPRESSED FOLDER. If it is a split database, be sure to include the backend database too (but don't include real data - use fake data).

 

[Samantha]

Okay it took a bit to get it down to size. The file is attached. I have labeled the main navigation frmNavigation, when it opens the first tab is my datasheet form then clicking the 2nd tab Detail it filters the subform from there but if I double click it the subform does not filter.
Than you for all your help!

 

[boblarson]

I'll have to wait until I get home (about 6 hours from now) as I don't have Access 2010 here at work, but do at home.

 

[boblarson]

Okay, I've looked and I hope I got what you wanted. So, what I have is if you click on the job number or select the entire record on the proposal form and then click the Proposal Details form it will be at the record you selected. I couldn't get the double click to go directly.

So here's what I changed:

1. Added a module (named basGlobals) with
Public strJobNumber As String

2. Changed the frmProposal code to

Private Sub Form_Current()
strJobNumber = Nz(Me.JobNumber, 0)
End Sub



Private Sub JobNumber_Click()
    strJobNumber = Me.JobNumber
End Sub

3. Changed the frmNavigaton code to:

Private Sub NavigationButton13_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.NavigationSubform.Form.RecordsetClone
    
    rs.FindFirst "[JobNumber]=" & Chr(34) & strJobNumber & Chr(34)
    If rs.NoMatch Then
        MsgBox "No Match"
    Else
        Me.NavigationSubform.Form.Bookmark = rs.Bookmark
    End If
    
    rs.Close
    Set rs = Nothing
End Sub

See attached revision of your file.

 

[Samantha]

Bob, that is actually much better how you revamped it. Thank you so much for your time on this.

Samantha