Limit Creation of Bins in the Inventory Adjustment screen/im receiving screen

[jdbrande]

thread639-1707832

In this thread the person said that you can add code to limit creation of the bins. However there is a lock on the iminvbin database when it is on this screen causing any queries to time out. Do you have a way around it. Or an example of how you got around the issue?

thanks,

 

[dgillz]

Just because another user is in that table should not cause a query to time out (at least in MS SQL). Are you using the Pervasive version? What exactly is happening that makes you say the query times out?

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

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

 

[jdbrande]

I am using Macola ES
When they come to the IMINVENT screen that shows the bin and quantity to be put in that bin.. what I was doing when they tabbed off the bin no field I did a select on iminvbin_sql where the item_no, bin_no, and location existed.. if they didn't exist I deleted from the iminvbin any bins that didn't have the userdefined field of "TEXT". But everytime they did that it would freeze for 2 minutes and then give a sql timeout error

 

[dgillz]

So what froze, the SQL to determine if the bin exists, or the SQL to delete from iminvbin? Can you post your code?

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

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

 

[jdbrande]

The code to determine if the Bins Exist is what freezes.
Function getBins()

On Error GoTo getDescription3_Error

'**********VARIABLES***************************|
Dim rs As New ADODB.Recordset 'Recordset |
Dim strSQL As String 'SQL command |
Dim strItemNo As String ' Item number
Dim strBinNo As String 'Bin Number
Dim strLoc As String ' location
Dim foundrecord As Boolean
Dim cmd As New ADODB.Command 'Command
'**********************************************|
setConnection

getBins = False


'-----Get item number from form------
strItemNo = ItemNo.Text
strLoc = Location.Text
strBinNo = BinNo.Text


'-----Get 3rd description from database-----
strSQL = "SELECT * " & _
"FROM iminvbin_sql " & _
"WHERE item_no='" & strItemNo & "' " & _
"and loc = '" & strLoc & "' " & _
"and bin_no = '" & strBinNo & "' " & _
"and user_def_fld_1 is not null"
cmd.ActiveConnection = strConnection
cmd.CommandText = strSQL
--------------FREEZES ON THE OPEN COMMAND
rs.Open cmd
'-----If item is found, get description3-----
If rs.EOF = False Then

getBins = True

End If

rs.Close 'Close recordset
Set rs = Nothing 'Dispose of recordset



'=====ERROR TRAPPING=====
Resume_No_Errors:
Exit Function

getDescription3_Error:
MsgBox Err.Description, vbCritical, "Bin Master"
GoTo Resume_No_Errors

End Function

 

[dgillz]

Why are you looking at user_def_fld_1? The question is do the bins exist or not correct?

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

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

 

[jdbrande]

After you tab off of bin_no it asked you if you want to create the bin, if the user hits yes it goes to the quantity field. There is where I am calling the above procedure. I put a name in the user defined field 1 to differentiate the bins I created vs the bins that macola automatically makes. This was I know if the user has created a bin and can delete it.

 

[dgillz]

I just did some testing on this and I don't think this is viable. For one, as soon as the user hits YES to create the bin, the records is created in the SQL database. As a result, your code - if you ever got it to run - would ALWAYS find a record.

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

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

 

[jdbrande]

You are correct in everything except that it wouldn't find a user defined field 1. That is how I know that it is an invalid bin, because the "correct" ones had a name in the user field 1 and the ones macola creates do not

 

[dgillz]

OK I'm with you. Are you saying the code to find or not find the bin record is the code that is locking up?

Put a watch on stSQL when you are executing the code. Paste the result into SQL Management Studio in a new query. Does it run? If not can you tell why not?

You code sets a GetBins (a boolean variable?) to True if nothing is found. Then what happens? I'm lost at that point.



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

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

 

[dgillz]

I just got this to work in ES without a hitch. You cannot assign a qty to a bin that has no user_def_fld_1 from this screen.

My code is a bit different than yours. My connection code:

Option Explicit
Public mstSql As String                 'String to hold sql statements
Public conData As New ADODB.Connection  'Connection used to retrieve and test data
Public pstconnection As String

Public Sub ConnectData()
    
    Set conData = Nothing
    Set conData = New ADODB.Connection
    pstconnection = "Provider=sqloledb;Data Source=" & Trim(macForm.ConnInfo.Server) & ";Initial Catalog=" & macForm.ConnInfo.Database & ";Integrated Security=SSPI;"
    conData.Open pstconnection
    
End Sub

The Macform code:

Option Explicit
Private Sub BinNo_LoseFocus(AllowLoseFocus As Boolean)
    Dim stItem As String
    Dim stLoc As String
    Dim stBin As String
    Dim rsData As New ADODB.Recordset
    
    stItem = ItemNo.Text
    stLoc = Location.Text
    stBin = BinNo.Text
    
    mstSql = "SELECT * FROM IMINVBIN_SQL " & _
             "WHERE item_no = '" & stItem & "' and loc ='" & stLoc & "' and " & _
             "bin_no = '" & stBin & "' and user_def_fld_1 IS NOT NULL "

    ConnectData
    
    With rsData
        .Open mstSql, conData, adOpenDynamic, adLockOptimistic
        If .EOF And .BOF Then
            'No valid bins found
            MsgBox "Invalid Bin"
            BinNo.SetFocus
        Else
            'do nothing s this is a valid bin
        End If
        .Close
    End With
    
End Sub

You may or may not already have the proper references. Post back if you have trouble.

This goes beyond what I normally do for free but I wanted to make sure this could be done.

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

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

 

[jdbrande]

Thank you for your help.