Error in populating a FlexGrid

[heprox]

I have a simple application that is retrieving a recordset from a database and populating a flexgrid with the record set accordingly. The idea is that the user will enter an field "txtItemCode" and hit ENTER, then the record will appear at the bottom of the form in the FlexGrid. Then the focus will return back to txtItemCode and the user can enter another item that will populate more rows into the FlexGrid. Every time I enter an Item however the following code dumps to the error handler?


Private Sub cmdEnter_Click()
On Error GoTo errhandler
Dim strsql As String
Dim inti As Integer
Dim blnflag As Boolean
If txtItemCode.Text = "" Then
MsgBox "Item code cannot be empty, Please Enter Item Code", vbCritical
txtItemCode.SetFocus
Exit Sub
End If
strsql = " select a.sku_num, a.itm_cd, b.vsn, a.size_cd, a.color_des, c.upc_cd" & _
" from gm_sku a, gm_itm b, gm_sku2upc_cd c" & _
" where a.sku_num = c.sku_num" & _
" and a.itm_cd = b.itm_cd" & _
" and b.itm_cd = '" & txtItemCode.Text & "'"
Set rsmain = New ADODB.Recordset
rsmain.Open strsql, cn, adOpenKeyset, adLockReadOnly
If rsmain.EOF Then
MsgBox "UPC data data does not exist for this Item Code, Pleas re-enter", vbCritical
txtItemCode.SetFocus
Exit Sub
Else
blnflag = False
For inti = 1 To MSFItem.Rows - 1
If Left(MSFItem.TextMatrix(inti, 0), 9) = txtItemCode.Text Then
blnflag = True
Exit For
End If
Next
If blnflag = False Then
Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
MSFItem.TextMatrix(inti, 0) = rsmain("a.sku_num")
MSFItem.TextMatrix(inti, 1) = rsmain("a.itm_cd")
MSFItem.TextMatrix(inti, 2) = rsmain("a.vsn")
MSFItem.TextMatrix(inti, 3) = rsmain("a.size_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("a.color_des")
MSFItem.TextMatrix(inti, 5) = rsmain("c.upc_cd")
rsmain.MoveNext
Loop
Else
MsgBox " This Item is already entered", vbInformation
End If
txtItemCode.Text = ""
txtItemCode.SetFocus
Exit Sub
End If
Exit Sub
errhandler:
MsgBox "Errors occured while retreiving the item information" & vbCrLf & "Please Click on Reset Button and Redo the process" & vbCrLf & Err.Number & ":" & Err.Description, vbCritical
Exit Sub
End Sub

 

[johnwm]

1. Rem out the error handling so you can see where the error occurs (enable only when your code works)

2. Are your tables actually called a, b and c? Or should there be aliases stated?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

Essex Steam UK for steam enthusiasts

 

[vladk]

heprox,

Instead of calculating each time the number of rows you can do just this:
MSFlexGrid1.AddItem MyColData1 & vbTab & ... MyColDataN

It will simplify your code.

vladk

 

[vladk]

heprox,

1.Should you reference your fields instead of:

rsmain("a.sku_num")


like this

rsmain("sku_num").Value & vbNullStrring ?

2. There are too many Exit Sub lines. The well written sub does not need them. The last Exit Sub is not needed since the next line is End Sub.

3. Your event does many different things under a single roof. Wrap each task in it's own function with individual error handling:
- Check for the dupes (should be the first task!)
- Call to the function to return recordset from DB
- Set recordset object to accept the recordset returned by the above function
- Populate Grid
- Call to the function that sets focus (if control disabled or invisible, this will cause run time error - set the focus in professional manner: in it's own shared function).

Each bullet is a stand alone function/sub

vladk

 

[vladk]

Check for the valid and unque entries you can put in Validate Event

 

[heprox]

The tables are called something else, the aliases are in the actual SQL statement, could that be the problem? I think that the sub is grouping the actual recordsets retreived?

I just want to see in the FlexGrid every record retreived (there will be mulitple for each query) and have the sub add to the FlexGrid each time a new query is entered. I've written a lot of code to group recordsets together and display them in a grid but this time I just want the whole recordset displayed.

Here is my code for valid entries:

Public Function ValidateCombo(ctrl As Control) As Boolean
Dim inti As Integer
For inti = 0 To ctrl.ListCount - 1
If ctrl.Text = ctrl.List(inti) Then
ValidateCombo = False
Exit Function
End If
Next
ValidateCombo = True
End Function

...I'm not quite sure what mean by using:

MSFlexGrid1.AddItem MyColData1 & vbTab & ... MyColDataN

...are you saying replace the:

Do While Not rsmain.EOF()
If MSFItem.TextMatrix(1, 0) = "" Then
inti = 1
Else
inti = MSFItem.Rows
MSFItem.Rows = MSFItem.Rows + 1
End If
MSFItem.TextMatrix(inti, 0) = rsmain("a.sku_num")
MSFItem.TextMatrix(inti, 1) = rsmain("a.itm_cd")
MSFItem.TextMatrix(inti, 2) = rsmain("a.vsn")
MSFItem.TextMatrix(inti, 3) = rsmain("a.size_cd")
MSFItem.TextMatrix(inti, 4) = rsmain("a.color_des")
MSFItem.TextMatrix(inti, 5) = rsmain("c.upc_cd")
rsmain.MoveNext
Loop

...What would the syntax be?

 

[heprox]

I got it worked out after all, it turned out to be the aliases in my recordset and some recordsets returned a NULL value, so I wrote a little public function to handle any NULL values being returned. Any idea how to make the rows that have NULL values in them display another color in a FlexGrid? Thanks for all your help.

 

[vladk]

>...What would the syntax be?

Do While Not rsmain.EOF()
MSFItem.AddItem rsmain("a.sku_num").Value & vbNullString & vbTab & _
rsmain("a.itm_cd").Value & vbNullString & vbTab & _
rsmain("a.vsn").Value & vbNullString & vbTab & _
rsmain("a.size_cd").Value & vbNullString & vbTab & _
rsmain("a.color_des").Value & vbNullString & vbTab & _
rsmain("c.upc_cd").Value & vbNullString
rsmain.MoveNext
Loop