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:

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

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:

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

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:

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.

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:

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

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:

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:


The Macform code:


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:

Thank you for your help.