ako table ba kai duha ang foreign key then ang last column kay quantity...
Code:
Dim blnAscendingSort As Boolean
Dim searchField As Byte
Dim newEdit As Byte
Private Sub CmdAddFlowerID_Click()
FrameFlowerId.Visible = True
Dim rs As New ADODB.Recordset
rs.Open "select ITEM_NO,ITEM_NAME from ITEM ", Conn, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
MSHFlower.ColWidth(0) = 0
Set MSHFlower.DataSource = rs
With MSHFlower
.ColWidth(1) = 1000
.ColAlignmentFixed(1) = 3
.ColAlignment(1) = 3
End With
End If
FrameItemNu.Visible = False
End Sub
Private Sub Command1_Click()
Dim fnt As New StdFont
Dim myFont, Str As String
Dim YPos As Integer
Printer.ScaleMode = vbTwips
myFont = "Arial Rounded MT"
YPos = 600
fnt.Name = myFont: fnt.Size = 24: fnt.Bold = True
Set Printer.Font = fnt
Str = "ITEM STRUCTURE MASTERLIST"
Printer.CurrentY = YPos
Printer.CurrentX = (Printer.ScaleWidth - Printer.TextWidth(Str)) / 2
Printer.Print Str
YPos = Printer.CurrentY + 200
fnt.Name = myFont: fnt.Size = 12: fnt.Bold = False
Set Printer.Font = fnt
Dim rs As New ADODB.Recordset
rs.Open "select * from structure", Conn, adOpenForwardOnly, adLockReadOnly
'SELECT EMPNO,LASTNAME,FIRSTNME,SALARY,DEPTNAME FROM EMPLOYEE LEFT JOIN DEPARTMENT ON DEPTNO=WORKDEPT", db2conn, adOpenForwardOnly, adLockReadOnly
Str = "ITEM NUMBER"
Printer.CurrentY = YPos
Printer.CurrentX = 2400
Printer.Print Str
Str = "ITEM NAME"
Printer.CurrentY = YPos
Printer.CurrentX = 5600
Printer.Print Str
Str = "QUANTITY"
Printer.CurrentY = YPos
Printer.CurrentX = 8600
Printer.Print Str
YPos = Printer.CurrentY
rs.MoveFirst
While Not rs.EOF
Str = rs!STR_ITEMNO 'rs.Fields(0).Value
Printer.CurrentY = YPos
Printer.CurrentX = 2400
Printer.Print Str
Str = rs!STR_FLOWER 'rs.Fields(1).Value
Printer.CurrentY = YPos
Printer.CurrentX = 5600
Printer.Print Str
Str = IIf(Not IsNull(rs!STR_QTY), rs!STR_QTY, "") 'rs.Fields(4).Value
Printer.CurrentY = YPos
Printer.CurrentX = 8600
Printer.Print Str
rs.MoveNext
YPos = Printer.CurrentY
Wend
Printer.EndDoc
End Sub
Private Sub MSHFlower_Click()
With MSHFlower
TXTFLOWER = .TextMatrix(.MouseRow, 1)
End With
FrameFlowerId.Visible = False
End Sub
Private Sub MSHItem_Click()
With MSHItem
Text2 = .TextMatrix(.MouseRow, 1)
End With
FrameItemNu.Visible = False
End Sub
Private Sub Text2_GotFocus()
Text2.BackColor = RGB(162, 162, 208)
End Sub
Private Sub CmdAdditemnu_Click()
TextVIEWITEMNO.Visible = False
FrameItemNu.Visible = True
Dim rs As New ADODB.Recordset
rs.Open "select ITEM_NO,ITEM_NAME from ITEM ", Conn, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
MSHItem.ColWidth(0) = 0
Set MSHItem.DataSource = rs
With MSHItem
.ColWidth(1) = 1000
.ColAlignmentFixed(1) = 3
.ColAlignment(1) = 3
End With
End If
FrameFlowerId.Visible = False
End Sub
Private Sub cmdCancel_Click()
clearTextboxes
MSHStructure.Enabled = True
cmdInsert.Enabled = True
cmdNew.Enabled = True
cmdCancel.Enabled = True
cmdExit.Enabled = True
cmdInsert.Enabled = False
cmdDel.Enabled = False
cmdEdit.Enabled = False
txtSearch.Enabled = False
TextVIEWITEMNO.Visible = False
Frame1.Enabled = False
End Sub
Private Sub cmdDel_Click()
Dim ans As Byte
ans = MsgBox(" Are you sure you want to remove this record?", vbYesNo, "Delete")
If ans = vbYes Then
Dim rs As New ADODB.Recordset
rs.Open "Delete from STRUCTURE where STR_ITEMNO = '" & Text2.Text & "'", Conn, adOpenDynamic, adLockOptimistic
MsgBox "One record removed!"
Unload Me
Form1.Show
End If
txtSearch.Enabled = False
End Sub
Private Sub cmdEdit_Click()
controlTextBoxes (True)
Text2.Enabled = False
controlButtons (False)
cmdInsert.Enabled = True
cmdCancel.Enabled = True
newEdit = 1
TXTFLOWER.SetFocus
txtSearch.Enabled = False
CmdAdditemnu.Enabled = True
CmdAddFlowerID.Enabled = True
End Sub
Private Sub cmdSave_Click()
controlButtons (False)
controlTextBoxes (False)
cmdNew.Enabled = True
cmdSearch.Enabled = True
cmdCancel.Enabled = True
If Text2 = "" Or TXTFLOWER = "" Or TXTQTY = "" Then
MsgBox ("Please fill up empty fields!")
controlTextBoxes (True)
controlButtons (False)
cmdsave.Enabled = True
cmdCancel.Enabled = True
End If
End Sub
Private Sub cmdExit_Click()
End
End Sub
Private Sub cmdnew_Click()
TextVIEWITEMNO.Visible = False
MSHStructure.Enabled = False
Text2.Enabled = False
TXTFLOWER.Enabled = False
TXTQTY.Enabled = True
cmdInsert.Enabled = True
cmdCancel.Enabled = True
txtSearch.Enabled = False
newEdit = 0
CmdAdditemnu.Enabled = True
CmdAddFlowerID.Enabled = True
Frame1.Enabled = True
End Sub
Function controlButtons(flag)
cmdNew.Enabled = flag
cmdInsert.Enabled = flag
cmdEdit.Enabled = flag
cmdDel.Enabled = flag
cmdSearch.Enabled = flag
cmdCancel.Enabled = flag
End Function
Private Sub cmdInsert_Click()
If Text2.Text = "" Then
MsgBox "No ITEM NO. FOUND.", vbOKOnly + vbCritical, "ERROR: Empty field/s. "
Text2.SetFocus
Exit Sub
End If
If TXTFLOWER.Text = "" Then
MsgBox "No FLOWER ID entered.", vbOKOnly + vbCritical, "ERROR: Empty field/s. "
TXTFLOWER.SetFocus
Exit Sub
End If
If TXTQTY.Text = "" Then
MsgBox "No QUANTITYS.", vbOKOnly + vbCritical, "ERROR: Empty field/s. "
TXTQTY.SetFocus
Exit Sub
End If
Dim rs As New ADODB.Recordset
Dim sqlstr As String
If newEdit = 0 Then
'sqlstr = "insert into STRUCTURE values('" & Text2.Text & "', '" & TXTFLOWER.Text & "', " & Val(TXTQTY.Text) & ")"
sqlstr = "Insert Into STRUCTURE(STR_ITEMNO,STR_FLOWER,STR_QTY) values('" & Text2.Text & "', '" & TXTFLOWER.Text & "', " & Val(TXTQTY.Text) & ")"
If CStr(Text2.Text) = CStr(TXTFLOWER.Text) Then
MsgBox ("The Item Number Can't be The Same with Flower ID")
TXTFLOWERFocus
Exit Sub
End If
' If rs.EOF Then
' rs.Close
rs.Open sqlstr, Conn, adOpenDynamic, adLockOptimistic
MsgBox "New strcuture Information Saved", vbInformation + vbOKOnly, "Saved"
Unload Me
Form1.Show
clearTextboxes
Else
sqlstr = "Update STRUCTURE set STR_QTY='" & TXTQTY.Text & "' where STR_ITEMNO='" & Text2.Text & "'"
'Comm=" & Val(txtComm.Text) & " where empno='" & txtEmpno.Text & "' "
rs.Open sqlstr, Conn, adOpenDynamic, adLockOptimistic
MsgBox "Employee's Information Updated", vbInformation + vbOKOnly, "Updated"
clearTextboxes
End If
populateFlexGrid
controlTextBoxes (False)
cmdInsert.Enabled = True
txtSearch.Enabled = False
End Sub
Function populateFlexGrid()
Dim rs As New ADODB.Recordset
Dim sqlstr As String
sqlstr = "SELECT STR_ITEMNO, (SELECT ITEM_NAME FROM ITEM WHERE ITEM_NO = STR_ITEMNO) AS ITEM, (SELECT ITEM_NAME FROM ITEM WHERE ITEM_NO =STR_FLOWER) AS ITEM_PIECE, STR_QTY AS QUANTITY FROM STRUCTURE order by STR_ITEMNO asc"
rs.Open sqlstr, Conn, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then
MSHStructure.FixedCols = 0
Set MSHStructure.Recordset = rs
With MSHStructure
.FormatString = "ITEM NO|ITEM NO|FLOWER ID|QTY."
.ColWidth(0) = 0
.ColWidth(1) = 2500
.ColWidth(2) = 2500
.ColWidth(3) = 1500
.AllowUserResizing = flexResizeColumns
.SelectionMode = flexSelectionByRow
End With
Else
MSHStructure.FixedCols = 0
With MSHStructure
.TextMatrix(1, 0) = ""
.TextMatrix(1, 1) = ""
'.TextMatrix(1, 2) = ""
End With
End If
End Function
Function clearTextboxes()
Text2 = ""
TXTFLOWER = ""
TXTQTY = ""
End Function
Function controlTextBoxes(flag)
Text2.Enabled = flag
TXTFLOWER.Enabled = flag
TXTQTY.Enabled = flag
End Function
Function fillUpITEMNO()
Dim rs As New ADODB.Recordset
Dim sqlstr As String
sqlstr = "SELECT ITEM_NO from ITEM"
rs.Open sqlstr, Conn, adOpenForwardOnly, adLockReadOnly
While Not rs.EOF
Text2.AddItem (rs!ITEM_NO)
rs.MoveNext
Wend
rs.Close
End Function
Private Sub cmdSearch_Click()
txtSearch.Enabled = True
txtSearch.Visible = True
Text2.Enabled = False
TXTFLOWER.Enabled = False
TXTQTY.Enabled = False
MSHStructure.Enabled = False
cmdNew.Enabled = False
cmdInsert.Enabled = False
cmdEdit.Enabled = False
cmdDel.Enabled = False
cmdCancel.Enabled = True
cmdExit.Enabled = True
End Sub
Private Sub Form_Load()
TextVIEWITEMNO.Enabled = False
db2Connect
populateFlexGrid
controlButtons (False)
controlTextBoxes (False)
MSHStructure.Enabled = True
searchField = 0
cmdSearch.Enabled = False
cmdNew.Enabled = True
cmdSearch.Enabled = True
cmdCancel.Enabled = True
Text1.Enabled = False
Text4.Enabled = False
FrameFlowerId.Visible = False
FrameItemNu.Visible = False
CmdAdditemnu.Enabled = False
CmdAddFlowerID.Enabled = False
txtSearch.Visible = False
End Sub
Private Sub MSHStructure_Click()
'TextVIEWITEMNO.Enabled = True
Text2.Text = MSHStructure.TextMatrix(MSHStructure.RowSel, 0)
TextVIEWITEMNO.Text = MSHStructure.TextMatrix(MSHStructure.RowSel, 1)
TXTFLOWER.Text = MSHStructure.TextMatrix(MSHStructure.RowSel, 2)
TXTQTY.Text = MSHStructure.TextMatrix(MSHStructure.RowSel, 3)
controlButtons (True)
cmdNew.Enabled = False
cmdCancel.Enabled = True
cmdInsert.Enabled = False
txtSearch.Enabled = False
End Sub
Private Sub MSHStructure_GotFocus()
MSHStructure.BackColor = RGB(204, 204, 255)
End Sub
Function TXTFLOWERFocus()
TXTFLOWER.BackColor = RGB(162, 162, 208)
End Function
Private Sub TXTQTY_GotFocus()
TXTQTY.BackColor = RGB(162, 162, 208)
End Sub
Private Sub TXTQTY_KeyPress(KeyAscii As Integer)
If (KeyAscii >= vbKey0 And KeyAscii <= vbKey9) Or KeyAscii = vbKeyBack Then
Exit Sub
Else
MsgBox "Only numbers required."
KeyAscii = 0
Beep
End If
End Sub
Private Sub txtSearch_Change()
Dim rs As New ADODB.Recordset
Dim sqlstr As String
sqlstr = "SELECT STR_ITEMNO, (SELECT ITEM_NAME FROM ITEM WHERE ITEM_NO = STR_ITEMNO) AS ITEM, (SELECT ITEM_NAME FROM ITEM WHERE ITEM_NO =STR_FLOWER) AS ITEM_PIECE, STR_QTY AS QUANTITY FROM STRUCTURE where STR_ITEMNO like '" & txtSearch.Text & "%' order by STR_ITEMNO asc"
rs.Open sqlstr, Conn, adOpenForwardOnly, adLockReadOnly
Set MSHStructure.Recordset = rs
rs.Close
cmdCancel.Enabled = True
End Sub
Private Sub txtSearch_GotFocus()
txtSearch.BackColor = RGB(162, 162, 208)
End Sub
guys i really need help jud..
unsaon pag update, if duha ang foreign key?
Reply With Quote
Posting Permissions
