×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
thread639-1707832: A way to limit bin creation...

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,

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

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: www.gainfocus.biz/exceladdin.html

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

(OP)
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

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

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: www.gainfocus.biz/exceladdin.html

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

(OP)

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

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

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: www.gainfocus.biz/exceladdin.html

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

(OP)
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.

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

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: www.gainfocus.biz/exceladdin.html

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

(OP)
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

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

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: www.gainfocus.biz/exceladdin.html

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

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:

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:

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: www.gainfocus.biz/exceladdin.html

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

(OP)
Thank you for your help.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close