asked on

pop up form on double click

hello,

i need help in figuring out how to load a form with a double click.
When tthe user double clicks the row. This will popup a new form, displaying the order number, client, item number and quantity in a “header”.

i.e

order: order value
client: client value
item: item value

code inside a class builds the order details row:

strSql = ""
strSql = "proc_GetOrderDetails '" & m_strOrderNumber & "', '" & m_strClient & "', " & m_lngHighJumpToAgressoJobId
Set rstSelect = m_cnnAgresso.Execute(strSql)

' Loop through each detail record and add it to the history array.
If Not rstSelect Is Nothing Then
If Not rstSelect.BOF Or Not rstSelect.EOF Then
Do While Not rstSelect.EOF
dblOrdered = CDbl(rstSelect![rev_val])
dblReserved = CDbl(rstSelect![reserved_val])
dblShipped = CDbl(rstSelect![vow_val])
If m_strOrderType = "SO" Or m_strOrderType = "NW" Then
' This is a sales order.
' Add another row to the array
intRecordCount = intRecordCount + 1
ReDim Preserve p_vntSalesOrderDetails(8, intRecordCount)
p_vntSalesOrderDetails(1, intRecordCount) = IIf(rstSelect![Level] = 1, rstSelect![line_no], "")
p_vntSalesOrderDetails(2, intRecordCount) = IIf(rstSelect![Level] = 1, Trim(rstSelect![article]), "")
p_vntSalesOrderDetails(3, intRecordCount) = IIf(rstSelect![Level] = 1, rstSelect![art_descr], Trim(rstSelect![physical_article]) + " - " + rstSelect![art_descr])
p_vntSalesOrderDetails(4, intRecordCount) = rstSelect![rev_val]
p_vntSalesOrderDetails(5, intRecordCount) = IIf(rstSelect![Level] = 1, "", rstSelect![reserved_val])
p_vntSalesOrderDetails(6, intRecordCount) = IIf(rstSelect![vow_val] = -1, "NOT SHIPPED", rstSelect![vow_val])
p_vntSalesOrderDetails(7, intRecordCount) = rstSelect![Status]
p_vntSalesOrderDetails(8, intRecordCount) = IIf(rstSelect![Level] = 1, "", rstSelect![dispatch_date])

and code inside the control loads the array:

' Populate the sales order details fields.
vfgSalesOrderDetails.Clear
vfgSalesOrderDetails.Rows = 1
blnReturn = p_objSalesOrder.GetSalesOrderDetailsArray(vntSalesOrderDetails)
If blnReturn Then
' Successfully got the sales order details array.

' Set column headings based on order type.
If p_objSalesOrder.strOrderType = "SO" Or p_objSalesOrder.strOrderType = "NW" Then

' Set up the titles for the sales order details.
vfgSalesOrderDetails.FormatString = "Ln|Product |Description |Ordered|Released|Dispatched|Status |Dispatch Date"
ElseIf p_objSalesOrder.strOrderType = "RO" Then
' Set up the titles for the return details.
vfgSalesOrderDetails.FormatString = "Ln|Product |Description |Returned|Repair|Replace|Dispatched|Condition |Action |Status |Dispatch Date"
End If
' Load the grid with the array of sales order details.
vfgSalesOrderDetails.LoadArray (vntSalesOrderDetails) <<< this loads the grid with the array.
Else
' Error occurred while getting the sales order details array.
End If

I don't know where to put the double click function, and how i can capture the values for the order number, client, item, line number and quantity (an example on only how to cpature one would help me a lot) and pass them to the pop up form when the row is double clicked.

any help on this is greatly appreciated.

thank you..

itortu

ASKER

i am using vb 6

itortu

ASKER

i was thinking putting a call to a double click sub after this line:

p_vntSalesOrderDetails(8, intRecordCount) = IIf(rstSelect![Level] = 1, "", rstSelect![dispatch_date])

Call vntSalesOrderDetails_DblClick

and inside this sub have code to pop up the form.

does this makes sense?

itortu

ASKER

may be it is better to do it after the array is loaded?

vfgSalesOrderDetails.LoadArray (vntSalesOrderDetails)

the array contains 8 elements or grid columns.

could someone shoe me how to create a double click function that pops up a form. even an empty form.

mah8473

I'm assuming here that you aren't wanting to specify a particular column that fires the double click event, but rather you can double click anywhere in the grid and want to open the new form to display drilled down details based on the clicked rows OrderNo

Propably the easiest way is to add a Property to your new form (OrderNo) which you can populate before loading the form with your double click on the grid

'======================
'new form code
Dim g_OrderNo as integer 'Private declaration of the form property
Public Property Get OrderNo() As Integer
OrderNo = g_OrderNo
End Property
'Public declaration of the form property
Public Property Let OrderNo(ByVal vData As Integer)
g_OrderNo = CInt(vData)
End Property

'in the forms load event write some code to retrive the order details based on the parsed Property for OrderNo
EG: call PopulateOrder(OrderNo) 'this routine retrieves the order and poulates the fields on the new form based on the OrderNo Property

'============
'grid double click code
Private Sub vfgSalesOrderDetails_DoubleClick()
On Error Resume Next
With vfgSalesOrderDetails
If .MouseRow <> 0 Then 'not the grid header row
myNewForm.Orderno = .TextMatrix(.MouseRow,0) 'read the OrderNo from the clicked row (I'm assuming this is the forms 1st column, hence the 0)
myNewForm.Show vbModal 'open your form modally to force the user to do something even if is just to view the Order details in more detail and then close.......by opening modally it ensures you don't have popup forms floating around in the background
End If
End With
End Sub

Hope this helps
Good Luck

mah8473

oops when stepping through the code you may experience mixed results as .MouseRow will actually follow your mouse when you are code view..... so your probably better off using .Row....sorry about that

mah8473

raaaaaa clearly I'm a little slow this morning.....In addition when you unload the new form, ensure you set the OrderNo property = 0 so it doesn't retain the value of the property in memory
EG:
OrderNo = 0
Unload me

itortu

ASKER

hi again!

i have a question related to my original question.

i am filling one column of a grid with serial numbers that are part of an order.
one order can have several serial numbers.

when i load the form i place the value of the serial number like this:

Private Sub Form_Load()
grdSerialNumber = m_strSerialNumber
End Sub

but this only places the first serial number that founds and does not loops through all.

how can i populate the column with all serial numbers

something like:

0001
0002
0003
0004
and so on...

thank you

mah8473

If my suggestion was a solution to your initial problem then you should accept it as an answer and post another question as although it may be related it is actually a new question all together. That said I'll try to help you out but you might like to refer to the rules of EE for future reference.

what is "m_strSerialNumber" declared as a string? or a string array? and how/where is it being poulated?

mah8473

I'm guessing it's an array

so just iterate through the array...but you'll physically need to write each serial number to the grid rows....your code "grdSerialNumber = m_strSerialNumber" would likely assume you are talking about the .Text property of grdSerialNumber

Assuming m_strSerialNumber is an array do something like
'================================
Private Sub Form_Load()

Dim m_strSerialNumber(3) As String ' I've declared this only for illustration purposes
Dim i As Integer
Dim iRowNo As Integer

m_strSerialNumber(0) = "test" ' I've populated this only for illustration purposes
m_strSerialNumber(1) = "test1" ' I've populated this only for illustration purposes
m_strSerialNumber(2) = "test2" ' I've populated this only for illustration purposes

For i = 0 To UBound(m_strSerialNumber) - 1
With grdSerialNumber
iRowNo = iRowNo + 1 'retain the row count
.Rows = .Rows + 1 ' add a new row to the grid
.TextMatrix(iRowNo, 0) = m_strSerialNumber(i) ' fill grid iRowNo colum 0 (your serial No column) with the items from the array
End With
Next i

End Sub

Hope this helps

mah8473

oops didn't finish that idea in my previous comment

where I said "your code "grdSerialNumber = m_strSerialNumber" would likely assume you are talking about the .Text property of grdSerialNumber"....i forgot to mention the grid.Text Property will only refer to .text of the cell with focus....not the entre textual content of the grid.

itortu

ASKER

it is working now, i just need to know how can i put a header that reads "serial number" inside this logic:

If Not rstSelect Is Nothing Then
If Not rstSelect.BOF And Not rstSelect.EOF Then
Do While Not rstSelect.EOF
r = r + 1
grdSerialNumber.Rows = r + 1
For c = 0 To rstSelect.RecordCount - 1
strSerialNumber = rstSelect![serial_number]
grdSerialNumber.TextMatrix(totalRecs, 0) = strSerialNumber
Next c
rstSelect.MoveNext
totalRecs = totalRecs + 1
Loop
Else
' Do nothing
'MsgBox "There are no Serial Numbers for this order. (" & Err.Number & " - " & Err.Description & ")", vbOKOnly, "Package Viewer"
End If
End If

ASKER CERTIFIED SOLUTION

mah8473

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mah8473

oops to keep in line with my form constants

.TextMatrix(iRowNo, g_COL_PRODUCT_SERIAL_NO_ID) = adors!SerialNoID
.TextMatrix(iRowNo, g_COL_PRODUCT_SERIAL_NO) = adors!SerialNo

should've read

.TextMatrix(iRowNo, g_COL_SERIAL_NO) = adors!SerialNo
.TextMatrix(iRowNo, g_COL_PRODUCT_CODE) = adors!ProductCode

sorry about that :)

itortu

ASKER

this is my form_load sub:

how can i use your routine to design my grids from my sub?

i would like to be able to control the column width and place a header the reads "Serial Number"

the grid has only one column.

Private Sub Form_Load()

Dim strSql As String
Dim strStmt As String
Dim rstSelect As ADODB.Recordset
Dim rsSelect As ADODB.Recordset
Dim strSerialNumber As String
Dim strAgressoConnection As String
Dim totalRecs As Integer
Dim r As Long
Dim c As Long
Dim i As Integer
Dim iRowNo As Integer

txtOrder.Text = m_strOrderNumber
txtClient.Text = m_strClient

txtQty.Text = m_strQty

strSql = " SELECT serial_number, line_number " & _
" FROM bhuserial_number " & _
" WHERE order_id = '" & m_strOrderNumber & "' " & _
" AND client = '" & m_strClient & "'"

Set rstSelect = m_cnnAgresso.Execute(strSql)

txtLineNo.Text = rstSelect![line_number]

strStmt = " SELECT item_number " & _
" FROM t_order_detail " & _
" WHERE order_number = '" & m_strOrderNumber & "' " & _
" AND client = '" & m_strClient & "'"

Set rsSelect = m_cnnHighJump.Execute(strStmt)

txtItem = rsSelect![item_number]

If Not rstSelect Is Nothing Then
If Not rstSelect.BOF And Not rstSelect.EOF Then
Do While Not rstSelect.EOF
r = r + 1
grdSerialNumber.Rows = r + 1
For i = 0 To rstSelect.RecordCount - 1
strSerialNumber = rstSelect![serial_number]
grdSerialNumber.TextMatrix(totalRecs, 0) = strSerialNumber
Next
rstSelect.MoveNext
totalRecs = totalRecs + 1
Loop
Else
' Do nothing
End If
End If

End Sub

itortu

ASKER

i implemented your routine, but i get only the first row (header) Serial No
and the rows with the serial numbers have disappear.

Private Sub DesignGrid()
On Error Resume Next
With grdSerialNumber
.FormatString = "Serial No" ' add column headings seperated by |
.Rows = 1 ' this is the header row
.Cols = 1 ' set to how many columns you want
.ColWidth(g_COL_SERIAL_NO) = 5000 ' set to whatever width you like
End With
End Sub

If Not rstSelect Is Nothing Then
If Not rstSelect.BOF And Not rstSelect.EOF Then
Do While Not rstSelect.EOF
r = r + 1
grdSerialNumber.Rows = r + 1
For i = 0 To rstSelect.RecordCount - 1
strSerialNumber = rstSelect![serial_number]
grdSerialNumber.TextMatrix(totalRecs, g_COL_SERIAL_NO) = strSerialNumber

Next
rstSelect.MoveNext
totalRecs = totalRecs + 1
Call DesignGrid
Loop
Else
' Do nothing
End If

End If

itortu

ASKER

ok i think it works somehow now

For i = 0 To rstSelect.RecordCount - 1
strSerialNumber = rstSelect![serial_number]
grdSerialNumber.FormatString = "Serial No"
grdSerialNumber.ColWidth(g_COL_SERIAL_NO) = 1335
grdSerialNumber.TextMatrix(totalRecs, g_COL_SERIAL_NO) = strSerialNumber

Next

how can the background of the header row be set as grey, to make it different from the rows with the serial numbers?

itortu

ASKER

never mind i did it on the properties window setting a fixed row of 1

thank you.

itortu

ASKER

it iw working, but i was not able to figure out te counter the way you have it.
if you could help me making the logic more congruent that'll be great.

Private m_strSerialNumber As String

strSql = " SELECT serial_number, line_number " & _
" FROM bhuserial_number " & _
" WHERE order_id = '" & m_strOrderNumber & "' " & _
" AND client = '" & m_strClient & "'"

Set rstSelect = m_cnnAgresso.Execute(strSql)

If Not rstSelect Is Nothing Then
If Not rstSelect.BOF And Not rstSelect.EOF Then
Do While Not rstSelect.EOF
r = r + 1
grdSerialNumber.Rows = r + 1
For i = 0 To rstSelect.RecordCount - 1
strSerialNumber = rstSelect![serial_number]
grdSerialNumber.FormatString = "Serial No"
grdSerialNumber.ColWidth(g_COL_SERIAL_NO) = 1335
grdSerialNumber.TextMatrix(totalRecs, g_COL_SERIAL_NO) = strSerialNumber

Next
rstSelect.MoveNext
totalRecs = totalRecs + 1
Loop
Else
' Do nothing
End If

mah8473

to use my DesignGrid routine simply add this "Call DesignGrid()" to your Form_Load....I usually do this before anything else

I just tested you code and my grid fills every line with the same serial No??? Which is what I suggested it would probably do earlier in my comments.

Here's a very simple example of the whole form (using your select statement) which is (kind of) how I would write it and in this example I'm assuming your rstSelect is populated with multiple serial no's, of which you want to display all of them in the grid...

Everyone has their own style of coding so I'm not suggesting this is a better way to do it....but the logic of your grid population certainly looks a little screwy
'----------------------------------
Option Explicit

Dim m_strOrderNumber As Integer
Dim m_strClient As String
'form level Constants for mshFlexGrid Column IDs
Const g_COL_SERIAL_NO = 0
'-----------------------------------------
Private Sub DesignGrid()
On Error Resume Next
With fgdSerialNos
.FormatString = "<Serial No" ' add column headings seperated by |
.Rows = 1 ' this is the header row
.Cols = 1 ' set to how many columns you want
.ColWidth(g_COL_SERIAL_NO) = 1500 ' set to whatever width you like
End With
End Sub
'-----------------------------------------
Private Sub Form_Load()
On Error Resume Next
'I can't see in your code how you've populated these so I'm just adding something random here
m_strOrderNumber = 1
m_strClient = "someClient"

Call DesignGrid 'set up the design of the Grid
Call PopulateGrid 'populated the Grid
End Sub
'-----------------------------------------
Private Function PopulateGrid()
Dim adors As New ADODB.Recordset
Dim iRowNo As Integer
Dim strSQL As String

On Error GoTo Err_PopulateGrid
fgdSerialNos.Rows = 1 'ONLY display the header Row at this point ie; ROW zero

strSQL = " SELECT serial_number, line_number " & _
" FROM bhuserial_number " & _
" WHERE order_id = '" & m_strOrderNumber & "' " & _
" AND client = '" & m_strClient & "'"

Set adors = m_cnnAgresso.Execute(strSQL)

If Not adors.EOF Then
With fgdSerialNos
While Not adors.EOF ' loop through the recordset and add SerialNo's to the grid
iRowNo = iRowNo + 1 ' add 1 to the row counter
.Rows = .Rows + 1 'add a row to the grid
.TextMatrix(iRowNo, g_COL_SERIAL_NO) = adors!SerialNo 'fill the grid
adors.MoveNext 'move to the next record
Wend
End With
End If

Err_PopulateGrid:
If Err.Number <> 0 Then
If adors.State = adStateOpen Then
adors.Close
End If
MsgBox Err.Description, , Err.Source
End If
Set adors = Nothing
End Function
'-----------------------------------------

I hope this helps, good luck