DblClick issue?

[techsup07]

I am having issues with opening a form with subforms in another way.

Currently it is set to have the user start with the customer form.

When the customer has been located in which a quote will be entered the user would then click on a button to open another form for data entry.

The customer form (Customers) has a subform (QuoteSummary) that lists all quotes if any.
In this subform the user would double-click on the quotenumber to view the form (CustomQuotes with subform CustomQuotesDetails) with the quoted data for making edits, etc ....


Well, i have another form with a list of just the quotenumbers and customer info (list view) so when the user needs to go back to make edits to a quote the user can open this form and search on just the quotenumber instead.

The user needs to open the same form with the quoted data (frmCustomQuotes).

Issue ... this form has this procedure:

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
    
    Me.Requery
    If IsLoaded("Customers") Then
        If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
            DoCmd.GoToControl "QuoteID"
            DoCmd.FindRecord Forms![Customers]![QuoteSummary Subform].Form![QuoteID]
        End If
    
    End If
    
Exit_Form_Activate:
    Exit Sub

Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
    
End Sub

and driven by

Private Sub CustomQuoteNumber_DblClick(Cancel As Integer)
    ViewOrder
End Sub

Private Sub ViewOrder()
On Error GoTo Err_ViewOrder
    DoCmd.OpenForm "CustomQuotes"
    
Exit_ViewOrder:
    Exit Sub

Err_ViewOrder:
    MsgBox Err.Description
    Resume Exit_ViewOrder
End Sub

I can't edit the Dbl-click from the ByQuote form to open the CustomQuotes because of the OnActivate.

Private Sub QuoteID_DblClick(Cancel As Integer)
On Error GoTo Err_DblClick

    Me.Requery
        DoCmd.FindRecord Forms![ByQuote].Form![QuoteID]
    
Exit_DblClick:
    Exit Sub

Err_DblClick:
    MsgBox Err.Description
    Resume Exit_DblClick
End Sub

what do i need to change and how to make the CustomQuotes open from the ByQuote on the Dbl-click Event Procedure?

 

[VBAPrincess]

Do you plan on leaving the CustomQuotes form open all the time? Will the Customers form be open when the user may try to get to CustomQuotes from the ByQuote form? If not, then you can have an If...ElseIf statement to check to see if Customers IsLoaded or if ByQuote IsLoaded.

Actually, my suggestion would be to move the code to the OnOpen event. You can still test for which (calling) form is loaded and locate the desired QuoteID from on the appropriate form.


Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[techsup07]

No forms will be opened (loaded) except the initiating form.

so if the user needs to search by Quote to make edits then the ByQuote form will be the 1st one opened.

the Dbl-click on the QuoteID in the ByQuote form is what we would like the CustomQuotes form to open with the QuoteID info just as it works from the QuoteSummary subform from the Customers form when the QuoteID there is double clicked.

I thought I can use a If, else statement in the OnActivate but nothing that I come up with is working.

What will change by moving this to OnOpen?
and how will this section be modified to do both?

    Me.Requery
    If IsLoaded("Customers") Then
        If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
            DoCmd.GoToControl "QuoteID"
            DoCmd.FindRecord Forms![Customers]![QuoteSummary Subform].Form![QuoteID]
        End If
    
    End If

 

[MajP]

I am not sure if I understand which code is on which form, but I think you could simplify this by using a global variable. This way you can get the value of the QuoteID depending on which form calls your main form.

So in a standard module something like:
public intQuoteID as integer

then in on your form:

DoCmd.GoToControl "QuoteID"
DoCmd.FindRecord intQuoteID

then on the initiating forms you set the intQuoteID prior to calling your main form. Something like:

Private Sub CustomQuoteNumber_DblClick(Cancel As Integer)
intQuoteID = me![QuoteID]
ViewOrder
End Sub

Do something similar for the double click event. If I am calling a form or report from multiple locations, and the form that called it determines the code to run I will use a global variable.

 

[techsup07]

ok, sounds interesting i will try it out and post back.

 

[techsup07]

MajP,
i got the other areas updated but being not very good with Modules ...

not sure how to write.

i did this and am receiving an error (of course)

Function intQuoteID()
Public intQuoteID As Integer
End Function

 

[MajP]

All you want to do is declare a public variable that you can use anywhere in the program so no function is needed. In the VBA window select "insert" "module" and you will create a new standard module for writing code. There are four places to write code in access: form module, report module, standard module, or class module. The form or report module is for writing code that is specific to that form or report. A standard module is for writing code that can be generic to the application. A class module allows you to build objects for object oriented programming.

so create a standard module and then at the top simply write

Option Compare Database
Option Explicit

Public intQuoteID As Integer

Since you make this public and it is in a standard module then it can be used anywhere in the application.

You can go to the access help file and type
Understanding Scope and Visibility
or google "vb variable scope

 

[techsup07]

Thank MajP,
ok, this is what I have

I inserted the code just as a Module.

Option Compare Database
Option Explicit

Public intQuoteID As Integer

then the following updated codes:

On Dbl click on the QuoteSummary subform which is on the Customer form:

Private Sub CustomQuoteNumber_DblClick(Cancel As Integer)
    intQuoteID = Me![QuoteID]
    ViewOrder
End Sub

On Dbl Click of the ByQuote form on QuoteID control:

Private Sub QuoteID_DblClick(Cancel As Integer)
    intQuoteID = Me![QuoteID]
    ViewOrder
End Sub

Private Sub ViewOrder()
On Error GoTo Err_ViewOrder
    DoCmd.OpenForm "CustomQuotes"
    
Exit_ViewOrder:
    Exit Sub

Err_ViewOrder:
    MsgBox Err.Description
    Resume Exit_ViewOrder
End Sub

BUT am still getting this parameter prompt from the ByQuote form when double-clicking in the QuoteID control:

Forms!Customers!QuoteSummary subform.Form!QuoteID


What am I doing wrong?

 

[techsup07]

oh yea, and the OnActivate on the CustomQuote:

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
    
    Me.Requery
    If IsLoaded("Customers") Then
        If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
            DoCmd.GoToControl "QuoteID"
            DoCmd.FindRecord intQuoteID 'Forms![Customers]![QuoteSummary Subform].Form![QuoteID]
        End If
    
    End If
    
Exit_Form_Activate:
    Exit Sub

Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
    
End Sub
[code]

I believe it's in this code that is my problem.
I need this to do it's check but because of it my ByQuote receives the parameter error.

 

[MajP]

I may not be following clearly so I hope I am not steering you wrong.
When you double click it it sets the variable and then opens the form customQuote. But if you are being prompted by
Forms!Customers!QuoteSummary subform.Form!QuoteID
Check the recordsource for the customQuote form. Does the query have a criteria in it?

Also not sure about the OnActivate versus onOpen. It is hard to tell without seeing when you want events to happen. The Order is Load-Open-Resize-Activate. Activate takes place when the object has the focus.

Does this code work OK when you enter from the customers form?

Me.Requery
If IsLoaded("Customers") Then
If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "QuoteID"
DoCmd.FindRecord intQuoteID 'Forms![Customers]![QuoteSummary Subform].Form![QuoteID]
End If
End If

But if you are always going to the form from a calling form then you could probably get rid of the above code
and do something like:

Private Sub ViewOrder()
On Error GoTo Err_ViewOrder
DoCmd.OpenForm "CustomQuotes"
DoCmd.GoToControl Forms![CustomQuote]!"QuoteID"
DoCmd.FindRecord intQuoteID

 

[techsup07]

Thanks ... here you go.

these are codes for the ByQuote

Private Sub CustomQuoteNumber_DblClick(Cancel As Integer)
    ViewOrder
End Sub

Private Sub ViewOrder()
On Error GoTo Err_ViewOrder
    DoCmd.OpenForm "CustomQuotes"
    DoCmd.GoToControl Forms![CustomQuotes]![QuoteID]
    DoCmd.FindRecord intQuoteID

Exit_ViewOrder:
    Exit Sub

Err_ViewOrder:
    MsgBox Err.Description
    Resume Exit_ViewOrder
End Sub

Private Sub QuoteID_DblClick(Cancel As Integer)
    ViewOrder
End Sub

Private Sub FindQuoteId_Click()
On Error GoTo Err_FindQuoteId_Click

    Screen.PreviousControl.SetFocus
    DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_FindQuoteId_Click:
    Exit Sub

Err_FindQuoteId_Click:
    MsgBox Err.Description
    Resume Exit_FindQuoteId_Click
    
End Sub

and on the CustomQuotes form:

Private Sub ViewOrder()
On Error GoTo Err_ViewOrder
    DoCmd.OpenForm "CustomQuotes"
    DoCmd.GoToControl Forms![CustomQuotes]![QuoteID]
    DoCmd.FindRecord intQuoteID

Exit_ViewOrder:
    Exit Sub

Err_ViewOrder:
    MsgBox Err.Description
    Resume Exit_ViewOrder
End Sub

IF I have the Customers form which as the QuoteSummary subform, then the ByQuote form Dbl-click opens the CustomQuotes form with the data.
Otherwise I get that parameter prompt.

And am also receiving an error message on the QuoteID number.
"There is no field name "28" (quoteid number) on current record.
and am still receiving the parameter prompt:
Forms!Customers!QuoteSummary subform.Form!QuoteID

 

[VBAPrincess]

Techsup07,

Sorry I didn't answer your question back to me but I forgot to mark the Email notification checkbox.

I know you're pretty far down another path with help from MajP but thought I'd post this just for future thought.

What I meant by using the OnOpen was that if you know only one of the two "calling" forms should be open and supplying a QuoteID, then you simply check to see which one is loaded and then grab the QuoteID from the open (or calling) form.

EXAMPLE:

Dim XGetQuoteID As Integer

If CurrentProject.AllForms("Customers").IsLoaded Then
XGetQuoteID = Forms!Customers!QuoteSummary Subform.Form!QuoteID
ElseIf CurrentProject.AllForms("ByQuote").IsLoaded Then
XGetQuoteID = Forms!ByQuote!QuoteID
Else
'do something to solve problem
End If

After getting the value for XGetQuoteID then all you have to do is use code to locate the correct record. You could use the value to filter your form or go to the correct record in a list. I use this type of option quite often. I usually have it in a global function.

Good luck!


Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!