Pop Up / Double Click Event Question

[smille777]

Hi everybody.

I have a form "Customer Quotes" which contains a cboProductID and contains [ProductID] (foreign key from "Products" Table), [Manufacturer].

I have another table "Vendor Quotes" which also has [ProductID] as a FK from "Products Table"

I want to create a Double Click Event in the [ProductID] field that will bring up a Pop Up to "display" the veendor quotes associated with that particular ProductID.

I appreciate any help!

Scott

 

[Darrylles]

Hi,

Create a simple form with this as it's recordsource:

'SELECT * FROM tblVendorQuotes where pk = forms!callingformname!pk;"
(This may be callingformname.pk)- I always get it mixed up.

Name this form: frmVendorQuotes.

Put the following in the double-click event of the calling form text-box:

DoCmd.OpenForm "frmVendorQuotes", , , stLinkCriteria

ATB

Darrylle







Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[smille777]

Thanks Darrylle.

I actually should have mentoned that I have a form "Vendor Quotes by Product" which contains the subform "Vendor Quotes by Product Subform".

ProductID is the Master and Child Link between tblProducts and tblVendorQuotes.

Based on this info, how would you revise?

Thanks!

 

[Darrylles]

Hi,

Why have a form 'popping up' when it's already displaying the data that you want to see (the subform)?

I.E. your subform displays vendor quotes per product already - what's the point in having a pop-up form that also shows the related vendor quotes?

Regards,

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience. darrylles@yahoo.co.uk

 

[smille777]

Darrylle,

As mentioned in the original thread (which maybe I did not explain clearly) The table from which I want to access the "Vendor Quotes by Product" form is Customer Quotes.

The linked field is ProductID.

What I wanted to know, originally, was how to have a pop up, or in this case "Vendor Quotes by Product" pop up with a Double Click Event in the [cboProductID] field of "Customer Quotes" where [ProductID] is =.


If I am Clicking on XYZ Product in Customer Quotes, it will pull XYZ Product record in "Vendor Quotes by Product"

I hope that makes sense. Sorry If I was not clear to begin with and Thanks for any assistance.

Scott

 

[TheAceMan1]

How are ya smille777 . . . . .

Design a query with the following criteria for [blue]ProductID[/blue] ([purple]the query is used as the Record Source for your pop-up![/purple]):

[blue]Forms![[purple][b]Customer Quotes[/b][/purple]]!ProductID[/blue]

Next add the following code to a module in the modules window:

[blue]Function IsOpenFrm(frmName As String) As Boolean
   Dim cp As CurrentProject, Frms As Object
   
   Set cp = CurrentProject()
   Set Frms = cp.AllForms
   
   If Frms.Item(frmName).IsLoaded Then
      If Forms(frmName).CurrentView > 0 Then IsOpenFrm = True
   End If
   
   Set Frms = Nothing
   Set cp = Nothing

End Function[/blue]

Then add the following code to your Double Click event:

[blue]   Dim frmName As String
   
   frmName = "[purple][b]YourPopUpFormName[/b][/purple]"
   
   If IsOpenFrm(frmName) Then
      Forms(frmName).Requery
   Else
      DoCmd.OpenForm frmName, acNormal
   End If[/blue]

There are other techniques, but this one allows you to select alternate [blue]ProductID's[/blue] from different records in [blue]Customer Quotes"[/blue], while the pop-up uis open.

See Ya! . . . . . .

 

[smille777]

Ace Man.....I'm doing good, buddy. I was very happy to see your post there. I am going to get cracking on it right now. I'll get back to you and let you know how it went.

Thanks!!!!!

Scott

 

[smille777]

Ace Man.....

Followed all of the above. When I double click on [ProductID], the form opens, BUT there are no records.

Any Ideas?

Thanks!

 

[TheAceMan1]

smille777 . . . . .

Did you set RecordSource of the popup to the Query?

Make sure the names in purple are correct, particularly [purple]Customer Quotes[/purple].

In either case post the query SQL?

See Ya! . . . . . .

 

[smille777]

Yes, I did. Everything matches up.

Query Name = qryVendor Quotes by Product
Pop UP Form Name = popVendor Quotes by Product AND has a popVendor Quotes by Product Subform

For Double Click Event for [ProductID]in Customer Quotes:

rivate Sub ProductID_DblClick(Cancel As Integer)
Dim frmName As String

frmName = "popVendor Quotes by Product"

If IsOpenFrm(frmName) Then
Forms(frmName).Requery
Else
DoCmd.OpenForm frmName, acNormal
End If

End Sub

But as mentioned, when the form open up, the records are blank.

In case it matters, I have a form "Vendor Quotes by Product" RECORD SOURCE = tblPRODUCTS

and Vendor Quotes by Product Subform" RECORD SOURCE = tblVendor Quotes

They are linked by ProductID.

I have NO IDEA!! Thanks ACE MAN!

 

[TheAceMan1]

smille777 . . . . .

First, never name objects in Access with spaces, if you do, you have to use brackets[blue][][/blue] to encase them. Always turns out to be a pain. For instance

Forms![blue]Customer Quotes[/blue] . . . . no good.
Forms![blue][Customer Quotes][/blue] . . . . OK.

If ya have to have spaces, use the underscore to keep it one word, like:

Forms![blue]popVendor_Quotes_by_Product [/blue]

Get out of the habit, or I promise, [blue]it will have you speaking another language![/blue]

OK, onward & upward . . . .

You did'nt post the SQL of the Query. Open the query in design view and in the top-left toolbar button, select SQL. Copy & paste it in your next post . . . .

See Ya! . . . . . .

 

[smille777]

Ace Man...

You would not believe how much and how little I have learned in the past 6 weeks. I have actually learned about the naming of objexts and using "_" for spaces and brackets and have toyed with the idea of going back through EVERYTHING and renaming.

Oh Man! Would you suggest I do this?

Anyway, the SQL =

SELECT [Vendor Quotes].VendorQuoteID, [Vendor Quotes].Date, [Vendor Quotes].ProductID, [Vendor Quotes].Manufacturer, [Vendor Quotes].VendorID, [Vendor Quotes].Contact, [Vendor Quotes].QtyQuoted, [Vendor Quotes].UnitPrice, [Vendor Quotes].DateCode, [Vendor Quotes].Stock, [Vendor Quotes].Notes
FROM [Vendor Quotes]
WHERE ((([Vendor Quotes].ProductID)=[Forms]![Customer Quotes]![ProductID]));


Scott

 

[TheAceMan1]

[blue]Oh Man! Would you suggest I do this?[/blue]

Well . . . better now then when you have a great many more to change! . . . . ya think . . . . Its not so bad, just make sure you backup the database so you can keep working.

Also, keep names lengths small, makes for much easier reading & troubleshooting. For instance:

popVendor_Quotes_by_Product or [purple]popVenQuoPro[/purple]
Customer Quotes or [purple]CustQuo[/purple]
Manufacturer or [purple]Manuf[/purple]
Vendor Quotes or [purple]VenQuo[/purple]

OK . . . . now

The query looks fine. The problem is apparently the [blue]RecordSource[/blue] of the suborm is set to the query, while the subform is also linked to the maniform. This creates a conflict, the mainform is setting a certain ProductID, while the query is setting a non-matching ProductID in the subform, or

[blue]The subForm query wants to set one thing, the link from main form wants to set another.[/blue]

What you need to do is base the main popup forms [blue]RecordSource[/blue] on a query, and move the criteria from the subforms query to the main popup forms query.

See Ya! . . . . . .

 

[smille777]

Ok you lost me there.


Main form Record Source IS based off the query
and

What I have now for the subform=

Source Object: popVendor Quotes by Product Subform
Link Child Fields: ProductID
Link Master Fields: ProductID

I created this pop up with the form wizzard. Do I need to create the subform in Design View in order to allow for a Record Source?

Thanks again!

 

[smille777]

Scratch that last statement..............I am tired.

OK, I set the subform Record Source to the Query.

What code do I use for the criteria in the subform?

Scott

 

[TheAceMan1]

[blue]What you need to do is base the main popup forms RecordSource on a query, and [purple]move the criteria[/purple] from the subforms query to the main popup forms query.[/blue]

See Ya! . . . . . .

 

[smille777]

Ace Man.....

I think I will have to sleep this one off and start again tomorrow.

I admit, and as you can probably alraedy tell, i am still naive with most of this. And I am a bit confused now because I don't presently have a criteria for [ProductID] in the subform Query, so I don't quite know what you mean by "move" the criteria.

Also, I just noticed (if it matters) that the [ProductID] field is ACTUALLY located in "Customer Quote Details Subform".

Right now I have

popVendor Quotes by Product:

Record Source= qryVendor Quotes by Product with [ProductID]

Criteria = [Forms]![Customer Quotes]![ProductID]

For popVendor Quotes by Product Subform:

Record Source= SELECT [qryVendor Quotes by Product].VendorQuoteID, [qryVendor Quotes by Product].VendorQuoteID, [qryVendor Quotes by Product].Date, [qryVendor Quotes by Product].ProductID, [qryVendor Quotes by Product].Manufacturer, [qryVendor Quotes by Product].VendorID, [qryVendor Quotes by Product].Contact, [qryVendor Quotes by Product].QtyQuoted, [qryVendor Quotes by Product].UnitPrice, [qryVendor Quotes by Product].DateCode, [qryVendor Quotes by Product].Stock, [qryVendor Quotes by Product].Notes FROM [qryVendor Quotes by Product];


Oh Well, You are a patient ACE, man! Maybe it will make sense to me in the morning.

Thanks brother!