Flexibility Question

[Zbob]

I am checking the order qty at order entry to make sure the orders are in full case packs (12) this is stored in the IMITMIDX user field 4.

I got this to work using "ersrecset" but would like to use ADO.

I have read some previous posts from VBAJock and tried to get it to work.

Thanks

Public Macola As New Connection

Function OpenMacolaConnection()
On Error GoTo OpenMacolaConnection_err
'initialize the object
Set Macola = Nothing

'this value may vary according to the speed of your system

Macola.ConnectionTimeout = 120
Macola.Open "Provider=MSDASQL;DSN=Macola"

OpenMacolaConnection = True

Exit Function
'***********************
OpenMacolaConnection_err:
'***********************

MsgBox Str$(Err) + Error$

OpenMacolaConnection = False

End Function
Private Sub Qty1_GotFocus()

'Call the function in your code by using this line:
Call OpenMacolaConnection
'Subsequent code can then use the open connection to do stuff. In the following example I use it to create a copy of the ARTYPFIL in an Access database:

Dim Ordqty As String
Dim Item As String
Dim CPstmt As String
Dim cpvalue As Double
Dim macrs As Recordset
Ordqty = macForm.Qty.Text
Item = macForm.ItemNo.Text

On Error GoTo PopArTypeFile_Err

Set macrs = New ADODB.Recordset

CPstmt = "SELECT IMITMIDX.USER_FIELD_4 "
CPstmt = CPstmt & "FROM IMITMIDX WHERE IMITMIDX.Item_no = '" & Item & "'"

macrs.Open CPstmt ', Macola, adOpenForwardOnly, adLockReadOnly, adCmdText

If IsNumeric(("IMITMIDX.USER_Field_4&quot) Then

cpvalue = ("IMITMIDX.USER_FIELD_4&quot
MsgBox "" & cpvalue
If Ordqty Mod cpvalue = 0 Then
Else
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Order Qty not a full case, Would you like to fix qty?, Case Pack= " & cpvalue
Style = vbYesNo + vbDefaultButton1 ' Define buttons.
Title = "Casepack Verification" ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then ' User chose Yes.
macForm.Qty.SetFocus

Set macImitmidx = Nothing

PopArTypeFile_Err:
MsgBox Str$(Err) + " " + Error$
PopARTypeFile = False
End If
End If
End If

End Sub

 

[vbajock]

Are you getting some kind of error?

This statement:

macrs.Open CPstmt ', Macola, adOpenForwardOnly, adLockReadOnly, adCmdText

is wrong, due to single quote after CPsmt
should be
macrs.Open CPstmt , Macola, adOpenForwardOnly, adLockReadOnly, adCmdText

This statement:
On Error GoTo PopArTypeFile_Err
should be
On Error GoTo Qty1_GotFocus_Err

and this
PopArTypeFile_Err:
should be changed to
Qty1_GotFocus_Err:

this statement
PopARTypeFile = False
should be removed. It is from the original code, which was a function, which returns a value. Yours is a sub routine (Private Sub Qty1_GotFocus) which does not return a value, so don't.

Also the form of your code is wrong. Please study how I have corrected it. I would use a much simpler method, but you should try to make this work because you'll learn some great basic ADO:

Private Sub Qty1_GotFocus()


Dim Ordqty As String
Dim Item As String
Dim CPstmt As String
Dim cpvalue As Double
Dim macrs As Recordset
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
On Error GoTo Qty1_GotFocus_Err


Ordqty = macForm.Qty.Text
Item = macForm.ItemNo.Text
Style = vbYesNo + vbDefaultButton1 ' Define buttons.
Title = "Casepack Verification" ' Define title.

Response = MsgBox(Msg, Style, Title, Help, Ctxt)


Call OpenMacolaConnection


Set macrs = New ADODB.Recordset

CPstmt = "SELECT IMITMIDX.USER_FIELD_4 "
CPstmt = CPstmt & " FROM IMITMIDX WHERE IMITMIDX.Item_no = '" & Item & "'"

macrs.Open CPstmt , Macola, adOpenForwardOnly, adLockReadOnly, adCmdText


If IsNumeric(("IMITMIDX.USER_Field_4&quot) Then

cpvalue = ("IMITMIDX.USER_FIELD_4&quot
MsgBox "" & cpvalue

If Ordqty Mod cpvalue = 0 Then

Else

Msg = "Order Qty not a full case, Would you like to fix qty?, Case Pack= " & cpvalue



If Response = vbYes Then ' User chose Yes.
macForm.Qty.SetFocus
End If

End If


End If

Goto Qty1_GotFocus_Exit


'****************
Qty1_GotFocus_Err:
'****************
MsgBox Str$(Err) + " " + Error$
'put a control break
'or the command STOP followed by
'RESUME here
'when you debug it. It will stop
'on the error, use the F

resume Qty1_GotFocus_Exit

'*****************
Qty1_GotFocus_Exit:
'*****************
macImitmidx.close
Set macImitmidx = Nothing
Exit Sub

End Sub

I don't have time to run this so you might get some typos

 

[vbajock]

Ok, heres a typo:
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
should go after
Msg = "Order Qty not a full case, Would you like to fix qty?, Case Pack= " & cpvalue

 

[Zbob]

I keep getting "11 Division by 0" error

It appears my recordset is returing a value not numeric, Do I need to convet it.

Is there a way to display my recordset?

 

[chadt]

one more correction to the first post in the tread..

Dim dOrdqty As Double
Dim sItem As String
Dim sCPstmt As String
Dim dCpvalue As Double
Dim rMacrs As Recordset

Naming conventions are always a good thing.

Just messin with you.....we all started someplace.

 

[Zbob]

My problem seems to be that I am using pervasive not Microsoft SQL.

I have tried the following, but I keep getting
"-2147467259 Unable to connect to data source : Macola"

Macola.ConnectionTimeout = 120
Macola.Open "Provider=PervasiveOLEDB;Data Source=Macola"

Thanks

 

[vbajock]

Have you set up an ODBC connection to Macola using the ODBC manager on the client machine ? You can get the docs on how to do this from your VAR or Exact's web site.

 

[Zbob]

Yes, we use the ODBC for access and crystal regularly.

I changed the Provider to the following,

Macola.ConnectionTimeout = 120
Macola.Open "Provider=PervasiveOLEDB;Data Source=M:\macola70\data"

I'm not sure how to test the connection, It does nothing because the rs is not numberic. When I display the result I only get "IMITMIDX.USER_FIELD_4"

This seems to be my problem

If IsNumeric(("IMITMIDX.USER_Field_4&quot) Then

cpvalue = ("IMITMIDX.USER_FIELD_4&quot

When i used ersrecset I had to add the recordset.

If IsNumeric(macrs("IMITMIDX.USER_Field_4&quot) Then

cpvalue = macrs("IMITMIDX.USER_FIELD_4&quot

I appreciate your help.

 

[Zbob]

When I use the getstring method, I can get it to work, But I would like to set the User_Field_4 to a variable, to display in the message box. When I use the getstring it is gone.

set macrs = New ADODB.Recordset

CPstmt = "SELECT IMITMIDX_sql.User_Def_Fld_4 "
CPstmt = CPstmt & "FROM IMITMIDX_sql WHERE IMITMIDX_sql.Item_no = '" & Item & "'"

macrs.Open CPstmt, Macola, adOpenForwardOnly, adLockReadOnly, adCmdText

If Ordqty Mod macrs.GetString = 0 Then

Also I need to skip if null or blank.

 

[vbajock]

In this code your reference to the data field is incorrect:
If IsNumeric(("IMITMIDX.USER_Field_4&quot) Then

cpvalue = ("IMITMIDX.USER_FIELD_4&quot

to reference the recordset correctly should be:

If IsNumeric( macrs!USER_Field_4) Then

cpvalue = macrs!USER_FIELD_4


cpvalue = macrs("USER_FIELD_4&quot is also valid.



Some rules:

If the field name has a space or other unusual character in it use
macrs![USER_FIELD_4]

If you want to use a variable for a field name use

macrs(variablename)

Test for nulls using the isnull() function e.g.

If isnull(macrs!User_Def_Fld_4) then
do something
else
do something else
endif
What version of PSql are you using?

 

[Zbob]

Thanks that worked perfect.

We are using Perasive 2000 SP4

I used the following connection, I tried many things to fix, I'm just learning.

Macola.ConnectionTimeout = 120
Macola.Open "Provider=MSDASQL;DSN=Macola"

Question for you. I have been looking at taking some VB classes at a Community college, most of the classes are in Visual Basic.net. Is there a big difference between .net and 6?

Thanks Again.

Bob

 

[chadt]

'//Question for you. I have been looking at taking some VB classes at a Community college, most of the classes are in Visual Basic.net. Is there a big difference between .net and 6?
'//

There is a huge difference....consider it a completely new language (OOP). If your going the .NET route I would suggest C# over VB.NET.

You are already seeing C# on other platforms besides M$.

go mono go.

Actually.....go perl go. and go PHP go. and go python go.

 

[vbajock]

.Net and XML are the wave of the future if you are going to be an application programmer who wants to work for multiple clients in a manufacturing environment. If so, go that route. If you are just trying to get ahead where you work or are planning to work there for a while with Macola, go for VB 6 as an introduction to programming because you will get lots of practice in just being a programmer and you might get a raise. Always keep up a program of staying up with the new stuff on the side, or you will get stale and die on the vine.

Get familiar with Access or Crystal Reports as well as VB 6 in a Macola environment because these programs can get you some nice looking output that you can call from your Flex programs - there is no report generator built in to Macola.

 

[chadt]

"VB 6 as an introduction to programming "


VB 6 is programming? lol...just kidding...i use it everyday too....kinda depressing but I do.

 

[vbajock]

To use the connection function I supplied,

Macola.ConnectionTimeout = 120
Macola.Open "Provider=MSDASQL;DSN=Macola"

you have to set up a System DSN in the ODBC Administrator on the machine. Macola has the docs. If you are doing OK with the

Macola.Open "Provider=PervasiveOLEDB;Data Source=Macola"

that is the native Pervasive OLE DB object. I don't think Macola supports it and I have never used it, but you can get all kinds of documentation for it on Pervasive's web site

http://www.pervasive.com.

Either one will support ADO.

 

[vbajock]

Well, I should have said, "take VB 6, a psuedo-coded bloated pig that masquerades as a programming language, but if you are a real man who wants to eat Ram chips covered in heat sink grease for breakfast, take C, C++ and C# in that order"

 

[chadt]

"take VB 6, a psuedo-coded bloated pig that masquerades as a programming language"

Well Said.