Update a user defined field from Order entry screen

[nablainc]

My client has certain products that need an export license
when shipped to certain counries. They want a message printed on the Pick Ticket when such a license is required with no user action required.
I have made routines that determine when such a license is required using Flexibility on the Order entry screen. It uses the values of item number and ship-to country. From this screen I want to enter the message into User_def_Fld_5 of the OEORDHDR_SQL table from which it can be entered on the pick ticket.
When I try to update the field directly using code and ADO I get a "record locked" error. Does MACOLA lock the OEORDHDR_SQL table for updating from Flex? My routines can open and read from the table OK, but I can't update, even after trying different cursor and lock options in the code.
My other approach is to add the user defined field to the Screen, but here I am having trouble setting the focus to the text box so that it can be updated. I have read other posts on this forum and have used a global variable and plan to somehow set focus to the new text box to set its value from the VBA code.
I have one work-around: I use the code to enter the order number into another table in an ACCESS database. Then from the Pick Ticket screen I run a Flex routine that uses this table to update the user defined field in OEORDHDR_SQL
before the pick tickets are printed.
I would prefer to have everything done in the order entry process. Any ideas how I can update this field from order entry?

Thanks,

Jerzy

 

[dgillz]

SQL or Pervasive?

I have done something very similar and did not run into record lock issues. However it was on SQL. I am not sure if this is the issue or not, and I will need to look at the code as this has been a long time. I am away from the office and cannot look at it now.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[nablainc]

Hello Don,

It is on Pervasive. The client is updating to SQL within the next month, however. Thanks for reply.

jerzy

 

[dgillz]

I do not have the code,but I have requested a copy from my customer. Give me a day or so and I should have something.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[nablainc]

Thanks for your efforts, Don, in looking into this.
I get "Btrieve error 84" as the record-locking error message.

I continue to work on the other approach: having Flexibility update the value for the user defined field, but I cannot get the code to trigger on the got_focus event. I either get "VBA cannot run set_focus operation" or nothing happens if focus does move to the user defined field.

As always, I'm impressed by how helpful you are to users of this forum.

jerzy

 

[dgillz]

Why don't you post your code? Sometimes you have to set the code on the lose_focus event of the previous field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[nablainc]

Hello Don,

That is what I'm doing: setting a global variable on an event triggered by an earlier field, then setting focus to the user defined field, then on the got-focus event trying to set the user defined field's value to the global value. Similar methods have worked on other Flexibility projects. The code has to run sometime after the item number has been entered on page 2 of the order entry screen. We've tried placing the user defined field either on page two or back on page one. In either case I haven't been able to enter a value into it using VBA.

jerzy

 

[NEmacGuy]

On which event were you trying to update your recordset.

I reviewed some flex projects were a table has been directly updated from the flex code, from a maintenance or trx screen.

Update was sent on following evnet:

Private Sub macForm_Save(AllowSave As Boolean)
strSQL = "update imitmidx_sql set upc_cd= strGtin12"
myRS.Open strSQL
myRS.Close

Only other thought is actual connection used :

Dim MyRS As New ADODB.Recordset

Set myCN = macForm.ConnInfpenADOConn
Set MyRS.ActiveConnection = myCN

 

[nablainc]

Hello NEmacGuy,

I think you might be on to something with your thought about the connection being used: I opened a new connection, rather than using the opened connection being used by macForm. I wasn't sure how to access the connection being used. This could explain the "locked-record" errors.

I had used the save event only to try to update the textbox on the screen, not to run the update query, which I had tried on earlier events.

Tomorrow I shall try out your ideas.

Thank you very much for your suggestions.


jerzy

 

[nablainc]

I tried finding the macform.connifo.openadoconn object but could not: ConnInfo has 4 or 5 properties, but OpenADOConn is not among them. I looked at some of my ADO references and realized that the connection parameter is optional in the openrecordset method: if it is not specified, the active connection will be used. I tried this with the thought that the active connection would be the macform ADO connection. This gave "connection cannot perform operations because it is either closed or invalid" messages.

I tried using the macform_save event as well as other events in the form, but got similar errors.

My client is on MACOLA 7.6.200 now but is upgrading within a month.

 

[abaldwin]

I think there might be a current flex solution for you .


Using ADO
You can build a connection (as you sound familiar with)

You can get the db location and connection information using the macform.conninfo and its sub properties.

Insert a module in the flex project. Create a public variable call blnNeedLicense (or whatever name you want).
Create one more called stOrderNo

On the loose focus event of the item number. Perform the sql query to see if this part needs the license and blnneedlicense = false then set blnneedlicense to true.

Do not set it back to False on the item number.
Set it to false on the order number loose focus and set the stOrderNo = to the order number text here as well.

On the PostSave event. If the blnNeedLicense is true then execute the update statement using the stOrderNo as the where clause.

Viola. Prob solved.

Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[nablainc]

Hello Andy,

The Postsave event does not seem to do anything: the only three events of the macform object that seem to work on my client's installation are the Initform, Save, and Close.
(I test by putting a message box in the event to see if it triggers). Putting the order number in a global variable makes it available after the form closes, at which time it can be inserted into the table----this I infer is what your method is meant to do. I will try the update query on the events that work tomorrow: I think that previously I had taken the order number directly from the form when trying to use the save event to update the table.

Thanks for the input.

jerzy

 

[dgillz]

I agree the post save event does not fire. This is most likely because there is Macola logic at post save that allows for immediate acknowledgements, immediate pick tickets, entry of billing data at OE time, etc.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[nablainc]

I have got the routine to do what I desired (update the user_def_fld_5 when a license is required) by a modification of Andy Baldwin's suggestion. I could not get the postsave event to trigger, but I triggered the update query on the type1(order type) got-focus event.
This was suggested by reading an old post on this forum from 19 May 2004 by pronet74. (You can access the thread by searching on the expression "Tough question regarding MACOLA")

I want again to thank all who replied to my question:
I gained a little insight from every reply.

Thanks again,

Jerzy

 

[MacolaLight]

We are trying to do the same thing. We are on Macola Progression 7.6.300.

Is there no other way to get this done?

We have commandeered the UPS ZONE file under Customer Master records. I was going to use that field to trigger a pop up window to manually add a line item for the license.

Is there a good way to do that?

 

[dgillz]

Flex is the way to go. Why are you using the UPS Zone instead of a user defined field?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[abaldwin]

Why not put the user defined field on the oe screen and fill its value when you get focus on the control? User would have to tab into it but that should be a training issue.


Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[dgillz]

Actually you can change the tab order so the user is FORCED to tab into it. You can also make it a required field, conditionally, with Flex.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?