Customization and Integration

[trodman]

We don't own the SDK so I'm trying to accomplish this with Customizer.  I want to add a PO lookup to the "Enter Transfer Orders" screen.  It works on the "View / Edit Vouchers" screen, but I'm not able to apply the same logic to the "Enter Transfer Orders" screen.

I have the following code in place on the "View / Edit Vouchers" screen and it's working fine:

Sub Form_Activate

Dim ctl
Dim moClass

Set ctl = Form.Controls.Add("EntryLookupControls.TextLookup", "lkuPO", Form.Controls("Frame2"))

ctl.Visible = True
ctl.Top = 0
ctl.Left = 0

Set moClass = Form.Controls("calBatchPostDate").Parent.moClass
Set ctl.Framework = moClass.moFramework
Set ctl.SysDB = moClass.moAppDB

ctl.LookupID = "PurchaseOrder"
ctl.LookupMode = 1

ctl.ParentIDColumn = "TranNo"    
ctl.ParentKeyColumn = "POKey"
ctl.ParentTable = "tpoPurchOrder"

End Sub

I tried using the same code on the "Enter Transfer Orders" screen, except I changed "calBatchPostDate" to "calOrderDate".

I get the following error.  Is there only one field per screen that can access the moClass property?

[trodman]

Any takers?

[LouDavis]

Well Kudo's first of all of even getting a lookup to work on a form in Customizer.  I don't think anyone has done that before.  That being said, I don't know if anyone here can provide much assistance with this.  It may be a question for Sage to answer.  The only thing I might suggest is try setting the .AppDB as well for the lookup as this is something that needs to be done when working with this control in VB 6 to see if that does anything.. 

[LouDavis]

Also I would add based on the error message, that particlar control doesn't have a propety called parent.  Maybe another control on the form has something that will get moClass for you.

[jmiles]

I developed a solution for this a while ago, but it requires that you add your own textbox and button. (You don't use the TextLookup control.) A plain textbox and button will work nicely.

Hopefully I'm not breaking any rules by posting this.

In the new button's "click" event handler, add the following code:

Dim lku
Dim lkuVal
Dim retVal
Dim val

Set lku = Form.moForm.Controls.Add("LookupViewControl.LookupView", "lkuCZLookup")
With lku
	Set .AppDatabase = Session.AppDatabase
	Set .Session = Form.moForm.oClass.moSysSession
	Set .FrameWork = Form.moForm.oClass.moFramework
	.LookupID = "Customer"
	.LookupMode = 1
	.RestrictClause = "CompanyID = '" & Replace(Session.CompanyID, "'", "''") & "'"
	
	retVal = .OpenLookupForm("", "", False, "CustID")
	MsgBox .ReturnColumnValues.Item(1)
End With
Set lku = Nothing

This code calls the "Customer" lookup, and it simply displays the selection in a message box. You can make the following changes:

• Change the LookupID property to "PurchaseOrder".
• Change the return column (currently "CustID") in the OpenLookupForm function call. I'm guessing you'll want "TranNo" as the return column.
• Change the MsgBox call to put the value returned by the lookup into your new textbox.

[jmiles]

I forgot to review the code before I posted it. You don't need to declare the "lkuVal" or "val" variables. They aren't used anywhere in the code.

[trodman]

Thanks Jason. This is great!

Unfortunately, I don't think it will work for me since it doesn't prevent someone from typing anything they want into the text field. I need to validate if they use the lookup or if they type in the PO number manually.

[trodman]

On second thought, maybe this will work.  I'll get complaints from the users who don't like being forced to use the mouse during data entry, but at least I will ensure the field is validated.  Two questions though:

1. Can I change the button to display an image so I can use the same magnifying glass as the other lookups?

2. Can I prevent this error from occurring when using the button more than once during a screen session?

[JohnHanrahan]

You know you could validate it on the Form_Save event right?  If they type something bogus you can have it fail save and pop up a message.  Lou posted some code that does that somewhere here.  If you can't find it I will post some.

[jmiles]

Tim:

In response to your questions, here are some answers:

1. Your button is a Customizer button, and I'm not aware of a way to use an image on it instead of text. When I've used this method in the past, I've just set the button text to three periods ("...") to indicate that something will happen when you click the button. Alternatively, you could put the code in a toolbar button. This allows for a limited selection of images instead of using text on the button face, and it will also let you assign a key combination to the button. This will help your keyboard-centric users.
2. You found a bug in my code. I was testing the code with the Form_Load event, which only creates the lookup control once. This code also checks to see whether the user chose an item or cancelled out of the lookup. Here is the revised code:

Dim lku
Dim retVal

Set lku = Form.moForm.Controls.Add("LookupViewControl.LookupView", "lkuCZLookup")
With lku
	Set .AppDatabase = Session.AppDatabase
	Set .Session = Form.moForm.oClass.moSysSession
	Set .FrameWork = Form.moForm.oClass.moFramework
	.LookupID = "Customer"
	.LookupMode = 1
	.RestrictClause = "CompanyID = '" & Replace(Session.CompanyID, "'", "''") & "'"
	
	retVal = .OpenLookupForm("", "", False, "CustID")
	If .ReturnColumnValues.Count > 0 Then
		MsgBox "User selected item " & .ReturnColumnValues.Item(1)
	Else
		MsgBox "User did not select an item."
	End If
End With
Form.moForm.Controls.Remove(lku)
Set lku = Nothing

Regarding validation, the SOTAMaskedEdit control that Customizer provides does have some events you can trap. You could put your validation logic into the Change, KeyPress, LostFocus, or Validate event. If you want the lookup window to appear when the user tabs to the textbox, you could put the code above into the textbox's GotFocus event. Additionally, pressing the space bar while the focus is on a button "clicks" the button. If you set your tab order correctly, your keyboard-centric users could just do that.