Adding another option OnActivate

[angelandgreg]

I have this ...

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


I would like to edit this line:
DoCmd.FindRecord Forms![Customers]![QuoteSummary Subform].Form![QuoteID]


so that from another form it'll open the quote form when the QuoteID is doubled-clicked. how do I add this or something like this to the above:
Forms![Customers]![ByQuoteform]

Basically this ByQuote form will be a list of all the quotes and the user can locate the quote number (QuoteID) and double-click to view the quote.

 

[adalger]

It sounds like what you want is to put the code to open the form into the On Dbl Click event of the QuoteID field.

TMTOWDI - it's not just for Perl any more

 

[angelandgreg]

I'm sorry for any confusion, that is where it is.

Everything works fine ... i just want to also be able to open the same form from another form with the Double-click on the QuoteID field.

The current code works only if it's from Forms![Customers]![QuoteSummary Subform]

as it is in
[code[
DoCmd.FindRecord Forms![Customers]![QuoteSummary Subform].Form![QuoteID]
[/code]


I also need the quote form to open with the related data of the QuoteID to open when double-clicked from my ByQuote form.

hope that clears up what i'm after.

The quote form has the OnActivate code and is triggered by the double-click on the QuoteID from the QuoteSummary subform only.

 

[adalger]

Then you need to put the code into the Dbl Click event of the field on the ByQuote form.

TMTOWDI - it's not just for Perl any more

 

[angelandgreg]

Again, yes, that is there BUT because of the OnActivate to FindRecord from QuoteSummary subform,

all i get is the error re: quotesummary subform because it doesn't exit from the ByQuote form.


hope that made sense.


The quote form is to be opened from both of the other forms.
The one from the Customers works fine.

...
    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]

The ByQuote form is a separate form completely and does not have or linked to the Customers form. Nor does it hae the QuoteSummary subform.

This is to find a customer to enter a new quote but also lists the exiting quotes to view if wanted through teh QuoteSummary subform.


The customer info may not always be known but the user will know the quote number so I created a form that lists all the quotes so they can just locate the QuoteID to view the quote.


Viewing the quote is what has the code OnActivate.

hence the error I am receiving and why I am seeking help to modify it to also accommodate to open the quote info form from the ByQuote form when also double-clicking on QuoteID.

 

[adalger]

Check out DoCmd.OpenForm; it should do what you want. You'll just have to pass it the appropriate query to use as a record source.

TMTOWDI - it's not just for Perl any more

 

[angelandgreg]

i am now utterly confused??

 

[angelandgreg]

can anyone please help me with this?

 

[Remou]

Are you saying that you already have the form with cutomer and quotes open and that the user then opens another form, ByQuotes, which shows a list of quotes? If so, I think that what you want to do is add code to the double-click or click event something like this:

If IsLoaded("Customers") Then
'This is a guess, change CustomerID to the proper name for
'the unique customer key on each form. 'Me' is the OrderBy 
'form, where this code is running.
Forms![Customers].Recordset.FindFirst "CustomerID=" & Me.CustomerID
   If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
      Forms![Customers]![QuoteSummary Subform].Form.Recordset.FindFirst "QuoteID=" & Me.[QuoteID]
   End If
End If

Or there abouts.

 

[angelandgreg]

no, completely separate form so the user can view a quote by the quoteid from the ByQuotes form which lists the active quotes.

if they start from the customer form they have to know and look up the customer to locate the quote. this is too time consuming.

they will know the quoteid before knowing the customer at times (and vice versa, but that's taken care of with the initial Customer form with the QuoteSummary subform). if the customer has any quotes, it will list them in the QuoteSummary subform.

the user needs the ability to look-up by a quoteid, hence the ByQuote form.


BUT because I have the OnActivate event on the quote form that display the details of the actual quote, i am not sure how to make it open from just the ByQuote form.

hope that made more sense?


thank you!

 

[Remou]

Presumably the quote is related to a customer number in the ByQuote form? If so, all is well, if not, we need to look at the way the ByQuote form is set up.

 

[angelandgreg]

yes,

here's the mod on the ByQuote form

Option Compare Database

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

Private Sub QuoteID_DblClick(Cancel As Integer)
    ViewOrder
End Sub

here's the ByQuote form data:

SELECT CustomQuoteHeader.QuoteDate, CustomQuoteHeader.CustomerID, Customers.CompanyName, [FirstName] & " " & [LastName] AS Employee, CustomQuoteHeader.QuoteID FROM Customers INNER JOIN (Employees INNER JOIN CustomQuoteHeader ON Employees.EmployeeID=CustomQuoteHeader.EmployeeID) ON Customers.CustomerNo=CustomQuoteHeader.CustomerID ORDER BY CustomQuoteHeader.QuoteID DESC;

 

[angelandgreg]

here's what I get when I double-click on the QuoteID field from the ByQuote form

Forms!Customers!QuoteSummary subform.Form!QuoteID

it prompts twice then it does open the CustomQuotes form but it is empty

 

[Remou]

So, let's just alter the code above:

'Open the customer form with just the current customer
DoCmd.Openform "Customers",,,"CustomerID=" & Me.CustomerID
'Find the order
   If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
      Forms![Customers]![QuoteSummary Subform].Form.Recordset.FindFirst "QuoteID=" & Me.[QuoteID]
   End If
End If

This would run in a click event from the ByOrders form. Make sure you comment out the Activate code on the Customer form, or use OpenArgs to stop it running.

 

[angelandgreg]

did not work. same message and another about an invalid.

here's the code in it's entirety.

Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
  '  Me.Requery
    'Open the customer form with just the current customer
    DoCmd.OpenForm "Customers", , , "CustomerID=" & Me.CustomerID
    'Find the order
    If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0 Then
      Forms![Customers]![QuoteSummary Subform].Form.Recordset.FindFirst "QuoteID=" & Me.[QuoteID]
    End If
    
Exit_Form_Activate:
    Exit Sub

Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
End Sub

 

[Remou]

I think you missed some of my post. I suggested using the Click event of the OrderBy form and commenting out the On Activate code on the Customer form.

 

[angelandgreg]

well, how will that work, i am confused.
i'm not seeing how what's been provided will do both things depending which form the double-click on the QuoteID will open the CustomQuote form (quote data)

the ViewOrders opens th CustomQuote from with the OnActivate which is the quote information. This form as the OnActivate to open it with data IF it fits under the original code.

BUT I also need it to open with the quote data just from the ByQuote form without the Customer form opened at all.


I'm looking for where the OnActivate code can do either option depending which form is opened to open the quote (CustomQuote form) with the related quote information.

 

[Remou]

The idea was to check that the code I suggested was working in the way you wanted, then to add in the On Activate code with an OpenArgument. Have you tried my suggestion?

 

[angelandgreg]

Hi Remou,
guess my issue is I don't understand what you mean with the "OpenArgument" on the OnActivate code. :-(


I modified the code, that's all.

 

[Remou]

Ok, I think I see the problem. The code you posted is not for the form you want to open, you have three forms:

Customer
CustomQuote
QuoteBy

Not a Customer form with a Quote subform, which is what I gathered from the code you posted, in particular:
[tt]If Forms![Customers]![QuoteSummary Subform].Form.RecordsetClone.RecordCount > 0[/tt]

If this is true, then there is this, I can't say I like the idea as there is too much room for error. It would be more conventional to control the CustomQuote form from the calling form:
* in a similar manner to the first snippet I suggested
* using OpenArgs
* using the Where argument of OpenForm

That being said, here it is:

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

    If IsLoaded("OrderBy") Then
        DoCmd.GoToControl "QuoteID"
        DoCmd.FindRecord Forms![OrderBy]![QuoteID]
    End If

Exit_Form_Activate:
    Exit Sub

Err_Form_Activate:
    MsgBox Err.Description
    Resume Exit_Form_Activate
End Sub

If it is not the case, please explain the set up.