Pass parameter value to SQL string in FLEX

[ddozer]

Ok, I'm very, very, very new to using FLEX in Macola (ES v 9.5.500) and have very little VB experience...

I am doing some testing in OE0101 because I want to add flex to prevent users from entering an order if a customer is over their credit limit (not just put the order on hold like Macola only allows). However, I've run into a stumbling block early on and I'm sure it's something stupid:

I am just trying to test passing the customer number to a query and returning a value from cicmpy just to see if it works. Here is my code:

Private Sub Customer_LoseFocus(AllowLoseFocus As Boolean)
On Error GoTo ERR_HANDLER
Dim myAdo As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Set myAdo = Me.ConnInfpenADOConn
Set myRecordset = myAdo.Execute("Select cmp_name from cicmpy where cmp_code = 1945", Null, -1)
If (Not myRecordset.BOF) Then
myRecordset.MoveFirst
MsgBox myRecordset.Fields(0).Value
myRecordset.Close
End If
myAdo.Close
Set myAdo = Nothing
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
Exit Sub

End Sub

My test customer number is 1945 and this code works. However, if I replace it with:

Private Sub Customer_LoseFocus(AllowLoseFocus As Boolean)
On Error GoTo ERR_HANDLER
Dim myAdo As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Set myAdo = Me.ConnInfpenADOConn
Set myRecordset = myAdo.Execute("Select cmp_name from cicmpy where cmp_code =" & me.Customer, Null, -1)
If (Not myRecordset.BOF) Then
myRecordset.MoveFirst
MsgBox myRecordset.Fields(0).Value
myRecordset.Close
End If
myAdo.Close
Set myAdo = Nothing
Exit Sub
ERR_HANDLER:
MsgBox Err.Description
Exit Sub

End Sub

Then I get an error saying "Object doesn't support this property or method"

Any ideas?

 

[dgillz]

me.<anything> refers to a form, not a field value. Your code does not know what me.customer means. Replace it with customer.text. Also, add the following where clause so you are nly dealing with customers (you could have a vendor, employee, etc. with the same number, in which case your ado recordset will have multiple members).

Where cmp_type = 'C'


Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[ddozer]

Thanks! That worked. If I can dovetail this into one more question then I think I'll have everything I need...

I did some looking on-line trying to find a command to clear all the values from a form and reset the focus to a specific field. Is there a single command that will do this, or is it more complicated that?

My plan is to take the above code and modify it to check a view that I have that indicates if a customer is over tgheir credit limit or not. If they are not over then I am going to let the user continue back to OE0101 from OECRDLMT. If they are over then I am want to pop up a box and when they hit ok have it clear OE0101 and set them back to the order type field.

I've figured out how to link OE0101 into OECRDLMT and got the above code to run when OECRDLMT closes, so I think I can get everything to work except the clearing of OE0101...

Thanks!

 

[dgillz]

I think you'll need to use SendKeys({Escape}) and SendKeys("N") to do this. I am not sure of the exact syntax.

Software Sales, Training, Implementation and Support for Macola, Synergy, and Crystal Reports. Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html