I am the only one user and master & child keys are linked as below:
This is main form coding:
Code:
Dim HasError As Boolean
Private Sub Form_Open(Cancel As Integer)
Me.RecordSource = "SELECT * FROM Bill WHERE BillId = " & gblKeyId
If gblNewKeyId Then
Me.lblHeader.Caption = "Add Bill"
Else
Me.lblHeader.Caption = "Edit Bill"
End If
If Me.BillStatus = "Active" Then
Me.BillStatus.BackColor = gblActiveColor
Me.btnExit.Caption = "Save and Exit"
Me.btnSave.Enabled = True
Me.IssueDate.Locked = False
Me.VendNo.Locked = False
Me.VendRefNo.Locked = False
Me.LicenseNo.Locked = False
Me.CurCode.Locked = False
Me.frmBillItem.Locked = False
Else
Me.BillStatus.BackColor = gblVoidColor
Me.btnExit.Caption = "Exit"
Me.btnSave.Enabled = False
Me.IssueDate.Locked = True
Me.VendNo.Locked = True
Me.VendRefNo.Locked = True
Me.LicenseNo.Locked = True
Me.CurCode.Locked = True
Me.frmBillItem.Locked = True
End If
Me.IssueDate.SetFocus
End Sub
Private Sub ExitForm()
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm "frmBillList"
End Sub
Private Sub btnVendNo_Click()
Dim PreviousListForm As Boolean
PreviousListForm = gblOpenListForm
gblOpenListForm = False
DoCmd.OpenForm "frmVendorList", , , , , acDialog, 5
Me.VendId = Val(Forms!frmVendorList.Tag)
DoCmd.Close acForm, "frmVendorList"
gblOpenListForm = PreviousListForm
If Me.VendId > 0 Then
Call OpenDatabase("select * from Vendor where VendId=" & Me.VendId)
Me.VendNo = gblRst!VendNo
Me.VendName = gblRst!VendName
Call CloseDatabase
Me.VendRefNo.SetFocus
End If
End Sub
Private Sub CheckError()
HasError = False
If Nz(Me.VendNo) = "" Then
Call ErrorBox(Me.lblVendNo.Caption & " is missing. Please enter again.")
Me.VendNo.SetFocus
HasError = True
Exit Sub
End If
If Nz(Me.VendRefNo) = "" Then
Call ErrorBox(Me.lblVendRefNo.Caption & " is missing. Please enter again.")
Me.VendRefNo.SetFocus
HasError = True
Exit Sub
End If
End Sub
Private Sub SaveRecord()
DoCmd.RunSQL ("update logfile set totalamt=" & Me.TotalAmt & " where billid=" & Me.BillId)
Call NoticeBox(Me.lblBillNo.Caption & " " & Me.BillNo & " has been saved successfully.")
End Sub
Private Sub btnSave_Click()
Dim DocNo As String
Call CheckError
If HasError Then
Exit Sub
End If
Call SaveRecord
DocNo = NewDocumentNo("Bill")
DoCmd.RunCommand acCmdRecordsGoToNew
Me.BillNo = DocNo
Me.IssueDate = Now()
Me.BillStatus = "Active"
Me.LastUser = "JOE"
Me.CurCode = "HKD"
Me.Refresh
gblKeyId = Me.BillId
Call Form_Open(0)
End Sub
Private Sub btnExit_Click()
If Me.btnExit.Caption = "Save and Exit" Then
Call CheckError
If HasError Then
Exit Sub
End If
Call SaveRecord
End If
Call ExitForm
End Sub
This is subform coding:
Code:
Private Sub btnFind_Click()
Dim PreviousListForm As Boolean
PreviousListForm = gblOpenListForm
gblOpenListForm = False
DoCmd.OpenForm "frmProductList", , , , , acDialog, 5
Me.ProdId = Val(Forms!frmProductList.Tag)
DoCmd.Close acForm, "frmProductList"
gblOpenListForm = PreviousListForm
If Me.ProdId > 0 Then
Call OpenDatabase("select * from Product where ProdId=" & Me.ProdId)
Me.ProdNo = gblRst!ProdNo
Call CloseDatabase
Me.Price.SetFocus
End If
End Sub
Private Sub btnRemove_Click()
If ConfirmBox("Do you want to DELETE " & Me.lblProdNo.Caption & " " & Me.ProdNo & " ?") Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End Sub
Private Sub Price_AfterUpdate()
Me.Amt = Nz(Me.Price, 0) * Nz(Me.Qty, 0)
End Sub
Private Sub Qty_AfterUpdate()
Me.Amt = Nz(Me.Price, 0) * Nz(Me.Qty, 0)
End Sub
Private Sub ProdNo_AfterUpdate()
Me.ProdId = Me.ProdNo.Column(3)
Me.Price.SetFocus
End Sub