Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Help to debug my program
- Thread starter svm
- Start date
- Thread starter
- #3
I am trying to access two files one is tblprojectinfo and the other is tblbidinfo. When you open the screen it goes to a listview which displays the records and some fields in the tblprojectinfo. When you select the F_Number ( the key to both files) it goes to next screen which allows you to switch tab back and forth : tab1 (Project) and tab2 (Bid Info). My problem is when I select an F_Number in listview and it is found in tblprojectinfo, record will display but if I switch tab to view record in Bid Info and no record is found it should give me a message "No Record found in Bid Info "
This is my whole program code :
Option Explicit
'Declare database objects
Private myConnection As New ADODB.Connection
Private BidConnection As New ADODB.Connection
Private myRecordset As New ADODB.Recordset
Private BidRecordset As New ADODB.Recordset
Private connectStr As String
Private cnt As Long
Private strSQL As String
Private strQuery As String
Private Sub cmdFirst_Click()
strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
' BidRecordset.MoveFirst
' BidRecordset.MoveLast
Debug.Print BidRecordset.RecordCount
BidRecordset.MoveFirst
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
'dbBidList "tblBase_Alt", "Base_Alt#", 0
'frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
'dbBidList "tblYear", "Year", 1
'cbobid(1).AddItem BidRecordset!Bid_Year
'dbBidList "tblRebid#", "Rebid_No", 2
'cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
'dbBidList "tblPlant_Info", "Plant_Location", 3
'cbobid(3).AddItem Format(BidRecordset!Plant, ""
'dbBidList "tblEstimator", "Estimator_Name", 4
'frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
'dbBidList "tblComplexity", "Complexity", 5
'cbobid(5).AddItem BidRecordset!Complexity
'dbBidList "tblAccuracy", "Accuracy", 6
'cbobid(6).AddItem BidRecordset!Accuracy
'dbBidList "tblEstimator", "Estimator_Name", 7
'cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdLst_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the last record
BidRecordset.MoveLast
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdNext_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the next record
BidRecordset.MoveNext
'Check for an EOF condition and correct if necessary
If BidRecordset.EOF Then
Call SetMsg("Already at last record", vbBlue)
BidRecordset.MovePrevious
End If
'Load the form fields
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdPrv_Click()
BidRecordset.MovePrevious
'Check for an BOF condition and correct if necessary
If BidRecordset.BOF Then
Call SetMsg("Already at first record", vbBlue)
BidRecordset.MoveNext
End If
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub Form_Load()
Dim itmx As ListItem
Dim strAdd As String
OnTop1.MakeTopMost (frmtblProjectInfo.hwnd)
tabno = SSTab1.Tab
SSTab1.TabEnabled(1) = False
cnt = 0
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
'__________________________________________________________________________
'My connection and Recordset
If prjAdd = False Then
'strSQL = "SELECT * FROM tblProjectInfo where F_Number = '" & fNo & "'"
strSQL = "Select * From tblProjectInfo Left Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number where tblProjectInfo.F_Number = '" & fNo & "'"
Else
strSQL = "SELECT * FROM tblProjectInfo"
End If
myConnection.Open connectStr
If myConnection.State = adStateOpen Then
myRecordset.CursorType = adOpenKeyset
myRecordset.LockType = adLockOptimistic
myRecordset.Open strSQL, myConnection
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
myRecordset.MoveFirst
If prjAdd = False Then
frmtblProjectInfo.txtFields(0).Text = myRecordset!F_Number
frmtblProjectInfo.txtFields(1).Text = myRecordset!JobNumber
frmtblProjectInfo.txtFields(2).Text = myRecordset!ProjectName
frmtblProjectInfo.txtFields(12).Text = Format(myRecordset!ExpSignDate, ""
frmtblProjectInfo.txtFields(13).Text = Format(myRecordset!ExpDeliveryDate, ""
frmtblProjectInfo.txtFields(14).Text = Format(myRecordset!ActSignDate, ""
frmtblProjectInfo.txtFields(15).Text = Format(myRecordset!ActDeliveryDate, ""
frmtblProjectInfo.txtFields(16).Text = Format(myRecordset!probability, ""
frmtblProjectInfo.txtFields(17).Text = myRecordset!Note
'Fill combo boxes
'addData:
dbFieldList "tblsalesman", "salesman_name", 0
frmtblProjectInfo.cboFields(0) = Format(myRecordset![Salesman], ""
dbFieldList "[tblcustomer name]", "customername", 1
frmtblProjectInfo.cboFields(1) = Format(myRecordset![CustomerName], ""
dbFieldList "tbljob_city", "job_city_name", 2
frmtblProjectInfo.cboFields(2) = Format(myRecordset![Job_City], ""
dbFieldList "tblstate", "job_state_name", 3
frmtblProjectInfo.cboFields(3) = Format(myRecordset![JobState], ""
dbFieldList "tblproduct", "product", 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, ""
dbFieldList "tbl_precast_companies", "[Company Name]", 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], ""
dbFieldList "tblReason", "Reason", 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], ""
dbFieldList "tbljob_status", "JobStatus", 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], ""
'Default info for project info add
End If
If prjAdd = True Then
frmtblProjectInfo.cboFields(0).Text = ""
frmtblProjectInfo.cboFields(1).Text = ""
frmtblProjectInfo.cboFields(2).Text = ""
frmtblProjectInfo.cboFields(3).Text = ""
frmtblProjectInfo.cboFields(4).Text = ""
frmtblProjectInfo.cboFields(5).Text = ""
frmtblProjectInfo.cboFields(6).Text = ""
frmtblProjectInfo.cboFields(7).Text = "Pending"
End If
myDataErr:
If fNo <> "" Then
txtFields(0).Enabled = False
CmdPrjAdd.Enabled = False
cmdPrjSave.Enabled = False
dbFieldList "tblsalesman", "salesman_name", 0
frmtblProjectInfo.cboFields(0) = myRecordset!Salesman
dbFieldList "[tblcustomer name]", "customername", 1
frmtblProjectInfo.cboFields(1) = myRecordset!CustomerName
dbFieldList "tbljob_city", "job_city_name", 2
frmtblProjectInfo.cboFields(2) = myRecordset![Job_City]
dbFieldList "tblstate", "job_state_name", 3
frmtblProjectInfo.cboFields(3) = myRecordset!JobState
dbFieldList "tblproduct", "product", 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, ""
dbFieldList "tbl_precast_companies", "[Company Name]", 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], ""
dbFieldList "tblReason", "Reason", 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], ""
dbFieldList "tbljob_status", "JobStatus", 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], ""
'Default info for project info add
' frmtblProjectInfo.cboFields(0).Text = ""
' frmtblProjectInfo.cboFields(1).Text = ""
' frmtblProjectInfo.cboFields(2).Text = ""
' frmtblProjectInfo.cboFields(3).Text = ""
' frmtblProjectInfo.cboFields(4).Text = ""
' frmtblProjectInfo.cboFields(5).Text = ""
' frmtblProjectInfo.cboFields(6).Text = ""
' frmtblProjectInfo.cboFields(7).Text = "Pending"
End If
If fNo = "" Then
txtFields(0).Enabled = True
cmdPrjSave.Enabled = False
CmdPrjAdd.Enabled = True
End If
BidAdd = False
SSTab1.TabEnabled(1) = True
'SSTab1.Tab = 1
'If BidRecordset.Open = 1 Then BidRecordset.Close
If BidConnection.State = 1 Then BidConnection.Close
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo FindErr
Exit Sub
End If
On Error GoTo FindErr
If BidAdd = False Then
strQuery = "Select * From [tblBidInfo] where tblBidInfo.F_Number = tblProjectInfo.F_Number"
BidRecordset.MoveFirst
txtbidflds(0).Text = BidRecordset!F_Number
txtbidflds(1).Text = BidRecordset!BidDate
txtbidflds(2).Text = BidRecordset!SquareFeet
txtbidflds(3).Text = BidRecordset!TotalCost
txtbidflds(4).Text = BidRecordset!Cost_SquareFeet
txtbidflds(5).Text = BidRecordset!Contribution
txtbidflds(6).Text = BidRecordset!Contribution_SqFeet
txtbidflds(7).Text = BidRecordset!SalesPrice
txtbidflds(8).Text = BidRecordset!SalesPrice_SqFeet
txtbidflds(9).Text = BidRecordset!Actual_Bid_Amount
txtbidflds(10).Text = BidRecordset!SelectedBid
txtbidflds(11).Text = BidRecordset!ContributionPcnt
txtbidflds(12).Text = BidRecordset!DateApproved
txtbidflds(13).Text = BidRecordset!Adjustment_Notes
txtbidflds(14).Text = BidRecordset!Time
txtbidflds(15).Text = BidRecordset!TimeChecking
'txtbidflds(0).Text = Format(BidRecordset!F_Number, ""
'txtbidflds(1).Text = BidRecordset!BidDate, ""
'txtbidflds(2).Text = Format(BidRecordset!SquareFeet, ""
'txtbidflds(3).Text = Format(BidRecordset!TotalCost, ""
'txtbidflds(4).Text = Format(BidRecordset!Cost_SquareFeet, ""
'txtbidflds(5).Text = Format(BidRecordset!Contribution, ""
'txtbidflds(6).Text = Format(BidRecordset!Contribution_SqFeet, ""
'txtbidflds(7).Text = Format(BidRecordset!SalesPrice, ""
'txtbidflds(8).Text = Format(BidRecordset!SalesPrice_SqFeet, ""
'txtbidflds(9).Text = Format(BidRecordset!Actual_Bid_Amount, ""
'txtbidflds(10).Text = Format(BidRecordset!SelectedBid, ""
'txtbidflds(11).Text = Format(BidRecordset!ContributionPcnt, ""
'txtbidflds(12).Text = Format(BidRecordset!DateApproved, ""
'txtbidflds(13).Text = Format(BidRecordset!Adjustment_Notes, ""
'txtbidflds(14).Text = Format(BidRecordset!Time, ""
'txtbidflds(15).Text = Format(BidRecordset!TimeChecking, ""
dbBidList "tblBase_Alt", "Base_Alt#", 0
frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
frmtblProjectInfo.cbobid(1) = BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
frmtblProjectInfo.cbobid(2) = Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
frmtblProjectInfo.cbobid(3) = Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
frmtblProjectInfo.cbobid(5) = Format(BidRecordset!Complexity, ""
dbBidList "tblAccuracy", "Accuracy", 6
frmtblProjectInfo.cbobid(6) = Format(BidRecordset!Accuracy, ""
dbBidList "tblEstimator", "Estimator_Name", 7
frmtblProjectInfo.cbobid(7) = Format(BidRecordset!CheckedBy, ""
End If
FindErr:
'MsgBox "No Record Found"
End Sub
Private Sub cboFields_change(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub cboFields_Click(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub cboBid_change(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
Private Sub cboBid_Click(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
Private Sub CmdCancel_Click()
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtFields
clrFld.Text = ""
Next
For Each clrCbo In cboFields
clrCbo.Text = ""
Next
End Sub
Private Sub CmdBidCancel_Click()
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtbidflds
clrFld.Text = ""
Next
For Each clrCbo In cbobid
clrCbo.Text = ""
Next
End Sub
'*******************************************************************************
'Process Project Info Screen
'*******************************************************************************
Private Sub cmdPrjAdd_Click()
On Error GoTo UpdateErr
BidAdd = True
With myRecordset
.AddNew
!F_Number = txtFields(0).Text
!JobNumber = txtFields(1).Text
!ProjectName = txtFields(2).Text
!CustomerName = cboFields(1).Text
!Salesman = cboFields(0).Text
!Job_City = cboFields(2).Text
!JobState = cboFields(3).Text
!Product = cboFields(4).Text
!manufacturerep = ""
!ExpSignDate = Format(txtFields(12).Text, "mm/dd/yyyy"
!ExpDeliveryDate = txtFields(13).Text
!ActSignDate = Format(txtFields(14).Text, "mm/dd/yyyy"
!ActDeliveryDate = txtFields(15).Text
!probability = txtFields(16).Text
!Job_Status = cboFields(7).Text
!LostJobTo = cboFields(5).Text
!Datelost = Format(txtFields(9).Text, "mm/dd/yyyy"
!Reason = cboFields(6).Text
!Notes = txtFields(17).Text
.Update
End With
If BidAdd = False Then
strSQL = "Select * From tblBidInfo on where tblBidInfo.F_Number = tblProjectInfo.F_Number "
Else
strSQL = "SELECT * FROM tblBidInfo"
End If
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo UpdateErr
Exit Sub
End If
On Error GoTo UpdateErr
BidRecordset.MoveLast
If BidAdd = False Then
txtbidflds(0).Text = BidRecordset!F_Number
End If
'Fill combo boxes
dbBidList "tblBase_Alt", "Base_Alt#", 0
cbobid(0).AddItem BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
cbobid(1).AddItem BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
cbobid(3).AddItem Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
cbobid(4).AddItem Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
cbobid(5).AddItem BidRecordset!Complexity
dbBidList "tblAccuracy", "Accuracy", 6
cbobid(6).AddItem BidRecordset!Accuracy
dbBidList "tblEstimator", "Estimator_Name", 7
cbobid(7).AddItem BidRecordset!CheckedBy
'Default info for project info add
If BidAdd = True Then
cbobid(0).Text = ""
cbobid(1).Text = ""
cbobid(2).Text = ""
cbobid(3).Text = ""
cbobid(4).Text = ""
cbobid(5).Text = ""
cbobid(6).Text = ""
cbobid(7).Text = ""
End If
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
txtbidflds(0).Text = txtFields(0).Text
CmdBidUpd.Enabled = False
CmdBidDel.Enabled = False
cmdNext.Enabled = False
CmdPrv.Enabled = False
cmdFirst.Enabled = False
CmdLst.Enabled = False
strSQL = "SELECT * FROM tblBidInfo"
Exit Sub
UpdateErr:
'Unload Me
End Sub
Private Sub CmdBidAdd_Click()
With BidRecordset
.AddNew
!F_Number = txtbidflds(0).Text
!BidDate = Format(txtbidflds(1).Text, "mm/dd/yyyy"
!Bid_Year = cbobid(1).Text
!Base_Alt = cbobid(0).Text
![Rebid#] = cbobid(2).Text
!Plant = cbobid(3).Text
!SquareFeet = txtbidflds(2).Text
!TotalCost = txtbidflds(3).Text
!Cost_SquareFeet = txtbidflds(4).Text
![TempPcnt] = 0#
!Contribution = txtbidflds(5)
!Contribution_SqFeet = txtbidflds(6).Text
!SalesPrice = txtbidflds(7).Text
!SalesPrice_SqFeet = txtbidflds(8).Text
![Actual_Bid_Amount] = txtbidflds(9).Text
!SelectedBid = Format(txtbidflds(10), ""
!ContributionPcnt = txtbidflds(11)
!DateApproved = Format(txtbidflds(12).Text, "mm/dd/yyyy"
!Adjustment_Notes = txtbidflds(13).Text
!Estimator = cbobid(4).Text
!Complexity = cbobid(5).Text
!Time = txtbidflds(14).Text
!Accuracy = cbobid(6).Text
!CheckedBy = cbobid(7).Text
!TimeChecking = txtbidflds(15).Text
.Update
If Err Then Debug.Print Err.Description
End With
CmdBidAdd.Enabled = True
CmdPrjAdd.Enabled = False
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtbidflds
clrFld.Text = ""
Next
For Each clrCbo In cbobid
clrCbo.Text = ""
Next
SSTab1.TabEnabled(0) = True
SSTab1.Tab = 0
'strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
CmdBidUpd.Enabled = False
CmdBidDel.Enabled = False
cmdNext.Enabled = False
CmdPrv.Enabled = False
cmdFirst.Enabled = False
CmdLst.Enabled = False
Exit Sub
MsgBox "Added Record " & txtFields(0).Text
UpdateErr:
End Sub
Private Sub cmdPrjSave_Click()
On Error GoTo SaveErr
With myRecordset
!F_Number = txtFields(0).Text
!JobNumber = txtFields(1).Text
!ProjectName = txtFields(2).Text
!CustomerName = cboFields(1).Text
!Salesman = cboFields(0).Text
!Job_City = cboFields(2).Text
!JobState = cboFields(3).Text
!Product = cboFields(4).Text
!manufacturerep = ""
!ExpSignDate = Format(txtFields(12).Text, "mm/dd/yyyy"
!ExpDeliveryDate = txtFields(13).Text
!ActSignDate = Format(txtFields(14).Text, "mm/dd/yyyy"
!ActDeliveryDate = txtFields(15).Text
!probability = txtFields(16).Text
!Job_Status = cboFields(7).Text
!LostJobTo = cboFields(5).Text
'!Datelost = txtFields(9).Text
!Datelost = Format(txtFields(9).Text, "mm/dd/yyyy"
!Reason = cboFields(6).Text
!Notes = txtFields(17).Text
.Update
End With
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
CmdBidAdd.Enabled = False
CmdBidDel.Enabled = False
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo SaveErr
Exit Sub
End If
On Error GoTo SaveErr
BidRecordset.MoveLast
'strSQL = "Select * From tblBidInfo where tblBidInfo.F_Number = '" & fNo & "'"
strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
txtbidflds(0).Text = BidRecordset!F_Number
txtbidflds(1).Text = BidRecordset!BidDate
txtbidflds(2).Text = BidRecordset!SquareFeet
txtbidflds(3).Text = BidRecordset!TotalCost
txtbidflds(4).Text = BidRecordset!Cost_SquareFeet
txtbidflds(5).Text = BidRecordset!Contribution
txtbidflds(6).Text = BidRecordset!Contribution_SqFeet
txtbidflds(7).Text = BidRecordset!SalesPrice
txtbidflds(8).Text = BidRecordset!SalesPrice_SqFeet
txtbidflds(9).Text = BidRecordset!Actual_Bid_Amount
txtbidflds(10).Text = BidRecordset!SelectedBid
txtbidflds(11).Text = BidRecordset!ContributionPcnt
txtbidflds(12).Text = BidRecordset!DateApproved
txtbidflds(13).Text = BidRecordset!Adjustment_Notes
txtbidflds(14).Text = BidRecordset!Time
txtbidflds(15).Text = BidRecordset!TimeChecking
dbBidList "tblBase_Alt", "Base_Alt#", 0
cbobid(0).AddItem BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
cbobid(1).AddItem BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
cbobid(3).AddItem Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
cbobid(4).AddItem Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
cbobid(5).AddItem BidRecordset!Complexity
dbBidList "tblAccuracy", "Accuracy", 6
cbobid(6).AddItem BidRecordset!Accuracy
dbBidList "tblEstimator", "Checked By", 7
cbobid(7).AddItem BidRecordset!CheckedBy
Exit Sub
SaveErr:
MsgBox "Updated Record " '& txtFields(0).Text
End Sub
Private Sub cmdBidUpd_Click()
On Error GoTo UpdErr
With BidRecordset
!F_Number = txtbidflds(0).Text
!BidDate = Format(txtbidflds(1).Text, "mm/dd/yyyy"
!Bid_Year = cbobid(1).Text
!Base_Alt = cbobid(0).Text
![Rebid#] = cbobid(2).Text
!Plant = cbobid(3).Text
!SquareFeet = txtbidflds(2).Text
!TotalCost = txtbidflds(3).Text
!Cost_SquareFeet = txtbidflds(4).Text
![TempPcnt] = 0#
!Contribution = txtbidflds(5)
!Contribution_SqFeet = txtbidflds(6).Text
!SalesPrice = txtbidflds(7).Text
!SalesPrice_SqFeet = txtbidflds(8).Text
![Actual_Bid_Amount] = txtbidflds(9).Text
!SelectedBid = Format(txtbidflds(10), ""
!ContributionPcnt = txtbidflds(11)
!DateApproved = Format(txtbidflds(12).Text, "mm/dd/yyyy"
!Adjustment_Notes = txtbidflds(13).Text
!Estimator = cbobid(4).Text
!Complexity = cbobid(5).Text
!Time = txtbidflds(14).Text
!Accuracy = cbobid(6).Text
!CheckedBy = cbobid(7).Text
!TimeChecking = txtbidflds(15).Text
.Update
End With
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
CmdBidAdd.Enabled = False
CmdBidDel.Enabled = False
UpdErr:
MsgBox "Updated Record"
End Sub
Private Sub DisableButtons()
'Disable navigation buttons
cmdMoveFirst.Enabled = False
cmdMovePrevious.Enabled = False
cmdMoveNext.Enabled = False
cmdMoveLast.Enabled = False
End Sub
Private Sub SetMsg(strMessage As String, _
Optional lngFontColor As ColorConstants = vbBlack)
'Set the message
lblMsg.Caption = strMessage
'Set the message font color
lblMsg.ForeColor = lngFontColor
End Sub
Sub dbFieldList(dbTable As String, dbField As String, cnt As Long)
Dim dbConn As New ADODB.Connection
Dim dbRec As New ADODB.Recordset
Dim strSQL As String
Dim connectStr As String
Dim itmx As ListItem
Dim strAdd As String
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
strSQL = "SELECT " & dbField & " FROM " & dbTable
dbConn.Open connectStr
If dbConn.State = adStateOpen Then
dbRec.CursorType = adOpenKeyset
dbRec.LockType = adLockOptimistic
dbRec.Open strSQL, dbConn
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
dbRec.MoveFirst
Do Until dbRec.EOF
cboFields(cnt).AddItem _
Trim(Format(dbRec.Fields.Item(0), "")
dbRec.MoveNext
Loop
myDataErr:
If Not myRecordset Is Nothing Then
If dbRec.State Then
dbRec.Close
End If
Set dbRec = Nothing
End If
If Not dbConn Is Nothing Then
If dbConn.State Then
dbConn.Close
End If
Set dbConn = Nothing
End If
End Sub
Sub dbBidList(dbTable As String, dbField As String, cnt As Long)
Dim dbConn As New ADODB.Connection
Dim dbRec As New ADODB.Recordset
Dim strSQL As String
Dim connectStr As String
Dim itmx As ListItem
Dim strAdd As String
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
strSQL = "SELECT " & dbField & " FROM " & dbTable
dbConn.Open connectStr
If dbConn.State = adStateOpen Then
dbRec.CursorType = adOpenKeyset
dbRec.LockType = adLockPessimistic
dbRec.Open strSQL, dbConn
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
dbRec.MoveFirst
Do Until dbRec.EOF
cbobid(cnt).AddItem _
Trim(Format(dbRec.Fields.Item(0), "")
dbRec.MoveNext
Loop
myDataErr:
If Not myRecordset Is Nothing Then
If dbRec.State Then
dbRec.Close
End If
Set dbRec = Nothing
End If
If Not dbConn Is Nothing Then
If dbConn.State Then
dbConn.Close
End If
Set dbConn = Nothing
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Close and dereference database objects
If Not myRecordset Is Nothing Then
If myRecordset.State Then
myRecordset.Close
End If
Set myRecordset = Nothing
End If
If Not myConnection Is Nothing Then
If myConnection.State Then
myConnection.Close
End If
Set myConnection = Nothing
End If
End Sub
Private Sub CmdExit_Click()
Unload Me
End Sub
Private Sub txtFields_Change(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub txtbidFlds_Change(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
This is my whole program code :
Option Explicit
'Declare database objects
Private myConnection As New ADODB.Connection
Private BidConnection As New ADODB.Connection
Private myRecordset As New ADODB.Recordset
Private BidRecordset As New ADODB.Recordset
Private connectStr As String
Private cnt As Long
Private strSQL As String
Private strQuery As String
Private Sub cmdFirst_Click()
strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
' BidRecordset.MoveFirst
' BidRecordset.MoveLast
Debug.Print BidRecordset.RecordCount
BidRecordset.MoveFirst
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
'dbBidList "tblBase_Alt", "Base_Alt#", 0
'frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
'dbBidList "tblYear", "Year", 1
'cbobid(1).AddItem BidRecordset!Bid_Year
'dbBidList "tblRebid#", "Rebid_No", 2
'cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
'dbBidList "tblPlant_Info", "Plant_Location", 3
'cbobid(3).AddItem Format(BidRecordset!Plant, ""
'dbBidList "tblEstimator", "Estimator_Name", 4
'frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
'dbBidList "tblComplexity", "Complexity", 5
'cbobid(5).AddItem BidRecordset!Complexity
'dbBidList "tblAccuracy", "Accuracy", 6
'cbobid(6).AddItem BidRecordset!Accuracy
'dbBidList "tblEstimator", "Estimator_Name", 7
'cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdLst_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the last record
BidRecordset.MoveLast
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdNext_Click()
'Clear any previous messages
Call SetMsg(Empty)
'Move to the next record
BidRecordset.MoveNext
'Check for an EOF condition and correct if necessary
If BidRecordset.EOF Then
Call SetMsg("Already at last record", vbBlue)
BidRecordset.MovePrevious
End If
'Load the form fields
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub cmdPrv_Click()
BidRecordset.MovePrevious
'Check for an BOF condition and correct if necessary
If BidRecordset.BOF Then
Call SetMsg("Already at first record", vbBlue)
BidRecordset.MoveNext
End If
txtbidflds(0).Text = Format(BidRecordset![F_Number], ""
txtbidflds(1).Text = Format(BidRecordset![BidDate], ""
txtbidflds(2).Text = Format(BidRecordset![SquareFeet], ""
txtbidflds(3).Text = Format(BidRecordset![TotalCost], ""
txtbidflds(4).Text = Format(BidRecordset![Cost_SquareFeet], ""
txtbidflds(5).Text = Format(BidRecordset![Contribution], ""
txtbidflds(6).Text = Format(BidRecordset![Contribution_SqFeet], ""
txtbidflds(7).Text = Format(BidRecordset![SalesPrice], ""
txtbidflds(8).Text = Format(BidRecordset![SalesPrice_SqFeet], ""
txtbidflds(9).Text = Format(BidRecordset![Actual_Bid_Amount], ""
txtbidflds(10).Text = Format(BidRecordset![SelectedBid], ""
txtbidflds(11).Text = Format(BidRecordset![ContributionPcnt], ""
txtbidflds(12).Text = Format(BidRecordset![DateApproved], ""
txtbidflds(13).Text = Format(BidRecordset![Adjustment_Notes], ""
txtbidflds(14).Text = Format(BidRecordset![Time], ""
txtbidflds(15).Text = Format(BidRecordset![TimeChecking], ""
cbobid(0).Text = Format(BidRecordset!Base_Alt, ""
cbobid(1).Text = Format(BidRecordset!Bid_Year, ""
cbobid(2).Text = Format(BidRecordset![Rebid#], ""
cbobid(3).Text = Format(BidRecordset!Plant, ""
cbobid(4).Text = Format(BidRecordset!Estimator, ""
cbobid(5).Text = Format(BidRecordset!Complexity, ""
cbobid(6).Text = Format(BidRecordset!Accuracy, ""
cbobid(7).Text = Format(BidRecordset!CheckedBy, ""
' dbBidList "tblBase_Alt", "Base_Alt#", 0
' frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
' dbBidList "tblYear", "Year", 1
' cbobid(1).AddItem BidRecordset!Bid_Year
' dbBidList "tblRebid#", "Rebid_No", 2
' cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
' dbBidList "tblPlant_Info", "Plant_Location", 3
' cbobid(3).AddItem Format(BidRecordset!Plant, ""
' dbBidList "tblEstimator", "Estimator_Name", 4
' frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
' dbBidList "tblComplexity", "Complexity", 5
' cbobid(5).AddItem BidRecordset!Complexity
' dbBidList "tblAccuracy", "Accuracy", 6
' cbobid(6).AddItem BidRecordset!Accuracy
' dbBidList "tblEstimator", "Estimator_Name", 7
' cbobid(7).AddItem BidRecordset!CheckedBy
End Sub
Private Sub Form_Load()
Dim itmx As ListItem
Dim strAdd As String
OnTop1.MakeTopMost (frmtblProjectInfo.hwnd)
tabno = SSTab1.Tab
SSTab1.TabEnabled(1) = False
cnt = 0
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
'__________________________________________________________________________
'My connection and Recordset
If prjAdd = False Then
'strSQL = "SELECT * FROM tblProjectInfo where F_Number = '" & fNo & "'"
strSQL = "Select * From tblProjectInfo Left Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number where tblProjectInfo.F_Number = '" & fNo & "'"
Else
strSQL = "SELECT * FROM tblProjectInfo"
End If
myConnection.Open connectStr
If myConnection.State = adStateOpen Then
myRecordset.CursorType = adOpenKeyset
myRecordset.LockType = adLockOptimistic
myRecordset.Open strSQL, myConnection
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
myRecordset.MoveFirst
If prjAdd = False Then
frmtblProjectInfo.txtFields(0).Text = myRecordset!F_Number
frmtblProjectInfo.txtFields(1).Text = myRecordset!JobNumber
frmtblProjectInfo.txtFields(2).Text = myRecordset!ProjectName
frmtblProjectInfo.txtFields(12).Text = Format(myRecordset!ExpSignDate, ""
frmtblProjectInfo.txtFields(13).Text = Format(myRecordset!ExpDeliveryDate, ""
frmtblProjectInfo.txtFields(14).Text = Format(myRecordset!ActSignDate, ""
frmtblProjectInfo.txtFields(15).Text = Format(myRecordset!ActDeliveryDate, ""
frmtblProjectInfo.txtFields(16).Text = Format(myRecordset!probability, ""
frmtblProjectInfo.txtFields(17).Text = myRecordset!Note
'Fill combo boxes
'addData:
dbFieldList "tblsalesman", "salesman_name", 0
frmtblProjectInfo.cboFields(0) = Format(myRecordset![Salesman], ""
dbFieldList "[tblcustomer name]", "customername", 1
frmtblProjectInfo.cboFields(1) = Format(myRecordset![CustomerName], ""
dbFieldList "tbljob_city", "job_city_name", 2
frmtblProjectInfo.cboFields(2) = Format(myRecordset![Job_City], ""
dbFieldList "tblstate", "job_state_name", 3
frmtblProjectInfo.cboFields(3) = Format(myRecordset![JobState], ""
dbFieldList "tblproduct", "product", 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, ""
dbFieldList "tbl_precast_companies", "[Company Name]", 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], ""
dbFieldList "tblReason", "Reason", 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], ""
dbFieldList "tbljob_status", "JobStatus", 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], ""
'Default info for project info add
End If
If prjAdd = True Then
frmtblProjectInfo.cboFields(0).Text = ""
frmtblProjectInfo.cboFields(1).Text = ""
frmtblProjectInfo.cboFields(2).Text = ""
frmtblProjectInfo.cboFields(3).Text = ""
frmtblProjectInfo.cboFields(4).Text = ""
frmtblProjectInfo.cboFields(5).Text = ""
frmtblProjectInfo.cboFields(6).Text = ""
frmtblProjectInfo.cboFields(7).Text = "Pending"
End If
myDataErr:
If fNo <> "" Then
txtFields(0).Enabled = False
CmdPrjAdd.Enabled = False
cmdPrjSave.Enabled = False
dbFieldList "tblsalesman", "salesman_name", 0
frmtblProjectInfo.cboFields(0) = myRecordset!Salesman
dbFieldList "[tblcustomer name]", "customername", 1
frmtblProjectInfo.cboFields(1) = myRecordset!CustomerName
dbFieldList "tbljob_city", "job_city_name", 2
frmtblProjectInfo.cboFields(2) = myRecordset![Job_City]
dbFieldList "tblstate", "job_state_name", 3
frmtblProjectInfo.cboFields(3) = myRecordset!JobState
dbFieldList "tblproduct", "product", 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, ""
dbFieldList "tbl_precast_companies", "[Company Name]", 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], ""
dbFieldList "tblReason", "Reason", 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], ""
dbFieldList "tbljob_status", "JobStatus", 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], ""
'Default info for project info add
' frmtblProjectInfo.cboFields(0).Text = ""
' frmtblProjectInfo.cboFields(1).Text = ""
' frmtblProjectInfo.cboFields(2).Text = ""
' frmtblProjectInfo.cboFields(3).Text = ""
' frmtblProjectInfo.cboFields(4).Text = ""
' frmtblProjectInfo.cboFields(5).Text = ""
' frmtblProjectInfo.cboFields(6).Text = ""
' frmtblProjectInfo.cboFields(7).Text = "Pending"
End If
If fNo = "" Then
txtFields(0).Enabled = True
cmdPrjSave.Enabled = False
CmdPrjAdd.Enabled = True
End If
BidAdd = False
SSTab1.TabEnabled(1) = True
'SSTab1.Tab = 1
'If BidRecordset.Open = 1 Then BidRecordset.Close
If BidConnection.State = 1 Then BidConnection.Close
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo FindErr
Exit Sub
End If
On Error GoTo FindErr
If BidAdd = False Then
strQuery = "Select * From [tblBidInfo] where tblBidInfo.F_Number = tblProjectInfo.F_Number"
BidRecordset.MoveFirst
txtbidflds(0).Text = BidRecordset!F_Number
txtbidflds(1).Text = BidRecordset!BidDate
txtbidflds(2).Text = BidRecordset!SquareFeet
txtbidflds(3).Text = BidRecordset!TotalCost
txtbidflds(4).Text = BidRecordset!Cost_SquareFeet
txtbidflds(5).Text = BidRecordset!Contribution
txtbidflds(6).Text = BidRecordset!Contribution_SqFeet
txtbidflds(7).Text = BidRecordset!SalesPrice
txtbidflds(8).Text = BidRecordset!SalesPrice_SqFeet
txtbidflds(9).Text = BidRecordset!Actual_Bid_Amount
txtbidflds(10).Text = BidRecordset!SelectedBid
txtbidflds(11).Text = BidRecordset!ContributionPcnt
txtbidflds(12).Text = BidRecordset!DateApproved
txtbidflds(13).Text = BidRecordset!Adjustment_Notes
txtbidflds(14).Text = BidRecordset!Time
txtbidflds(15).Text = BidRecordset!TimeChecking
'txtbidflds(0).Text = Format(BidRecordset!F_Number, ""
'txtbidflds(1).Text = BidRecordset!BidDate, ""
'txtbidflds(2).Text = Format(BidRecordset!SquareFeet, ""
'txtbidflds(3).Text = Format(BidRecordset!TotalCost, ""
'txtbidflds(4).Text = Format(BidRecordset!Cost_SquareFeet, ""
'txtbidflds(5).Text = Format(BidRecordset!Contribution, ""
'txtbidflds(6).Text = Format(BidRecordset!Contribution_SqFeet, ""
'txtbidflds(7).Text = Format(BidRecordset!SalesPrice, ""
'txtbidflds(8).Text = Format(BidRecordset!SalesPrice_SqFeet, ""
'txtbidflds(9).Text = Format(BidRecordset!Actual_Bid_Amount, ""
'txtbidflds(10).Text = Format(BidRecordset!SelectedBid, ""
'txtbidflds(11).Text = Format(BidRecordset!ContributionPcnt, ""
'txtbidflds(12).Text = Format(BidRecordset!DateApproved, ""
'txtbidflds(13).Text = Format(BidRecordset!Adjustment_Notes, ""
'txtbidflds(14).Text = Format(BidRecordset!Time, ""
'txtbidflds(15).Text = Format(BidRecordset!TimeChecking, ""
dbBidList "tblBase_Alt", "Base_Alt#", 0
frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
frmtblProjectInfo.cbobid(1) = BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
frmtblProjectInfo.cbobid(2) = Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
frmtblProjectInfo.cbobid(3) = Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
frmtblProjectInfo.cbobid(5) = Format(BidRecordset!Complexity, ""
dbBidList "tblAccuracy", "Accuracy", 6
frmtblProjectInfo.cbobid(6) = Format(BidRecordset!Accuracy, ""
dbBidList "tblEstimator", "Estimator_Name", 7
frmtblProjectInfo.cbobid(7) = Format(BidRecordset!CheckedBy, ""
End If
FindErr:
'MsgBox "No Record Found"
End Sub
Private Sub cboFields_change(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub cboFields_Click(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub cboBid_change(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
Private Sub cboBid_Click(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
Private Sub CmdCancel_Click()
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtFields
clrFld.Text = ""
Next
For Each clrCbo In cboFields
clrCbo.Text = ""
Next
End Sub
Private Sub CmdBidCancel_Click()
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtbidflds
clrFld.Text = ""
Next
For Each clrCbo In cbobid
clrCbo.Text = ""
Next
End Sub
'*******************************************************************************
'Process Project Info Screen
'*******************************************************************************
Private Sub cmdPrjAdd_Click()
On Error GoTo UpdateErr
BidAdd = True
With myRecordset
.AddNew
!F_Number = txtFields(0).Text
!JobNumber = txtFields(1).Text
!ProjectName = txtFields(2).Text
!CustomerName = cboFields(1).Text
!Salesman = cboFields(0).Text
!Job_City = cboFields(2).Text
!JobState = cboFields(3).Text
!Product = cboFields(4).Text
!manufacturerep = ""
!ExpSignDate = Format(txtFields(12).Text, "mm/dd/yyyy"
!ExpDeliveryDate = txtFields(13).Text
!ActSignDate = Format(txtFields(14).Text, "mm/dd/yyyy"
!ActDeliveryDate = txtFields(15).Text
!probability = txtFields(16).Text
!Job_Status = cboFields(7).Text
!LostJobTo = cboFields(5).Text
!Datelost = Format(txtFields(9).Text, "mm/dd/yyyy"
!Reason = cboFields(6).Text
!Notes = txtFields(17).Text
.Update
End With
If BidAdd = False Then
strSQL = "Select * From tblBidInfo on where tblBidInfo.F_Number = tblProjectInfo.F_Number "
Else
strSQL = "SELECT * FROM tblBidInfo"
End If
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo UpdateErr
Exit Sub
End If
On Error GoTo UpdateErr
BidRecordset.MoveLast
If BidAdd = False Then
txtbidflds(0).Text = BidRecordset!F_Number
End If
'Fill combo boxes
dbBidList "tblBase_Alt", "Base_Alt#", 0
cbobid(0).AddItem BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
cbobid(1).AddItem BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
cbobid(3).AddItem Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
cbobid(4).AddItem Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
cbobid(5).AddItem BidRecordset!Complexity
dbBidList "tblAccuracy", "Accuracy", 6
cbobid(6).AddItem BidRecordset!Accuracy
dbBidList "tblEstimator", "Estimator_Name", 7
cbobid(7).AddItem BidRecordset!CheckedBy
'Default info for project info add
If BidAdd = True Then
cbobid(0).Text = ""
cbobid(1).Text = ""
cbobid(2).Text = ""
cbobid(3).Text = ""
cbobid(4).Text = ""
cbobid(5).Text = ""
cbobid(6).Text = ""
cbobid(7).Text = ""
End If
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
txtbidflds(0).Text = txtFields(0).Text
CmdBidUpd.Enabled = False
CmdBidDel.Enabled = False
cmdNext.Enabled = False
CmdPrv.Enabled = False
cmdFirst.Enabled = False
CmdLst.Enabled = False
strSQL = "SELECT * FROM tblBidInfo"
Exit Sub
UpdateErr:
'Unload Me
End Sub
Private Sub CmdBidAdd_Click()
With BidRecordset
.AddNew
!F_Number = txtbidflds(0).Text
!BidDate = Format(txtbidflds(1).Text, "mm/dd/yyyy"
!Bid_Year = cbobid(1).Text
!Base_Alt = cbobid(0).Text
![Rebid#] = cbobid(2).Text
!Plant = cbobid(3).Text
!SquareFeet = txtbidflds(2).Text
!TotalCost = txtbidflds(3).Text
!Cost_SquareFeet = txtbidflds(4).Text
![TempPcnt] = 0#
!Contribution = txtbidflds(5)
!Contribution_SqFeet = txtbidflds(6).Text
!SalesPrice = txtbidflds(7).Text
!SalesPrice_SqFeet = txtbidflds(8).Text
![Actual_Bid_Amount] = txtbidflds(9).Text
!SelectedBid = Format(txtbidflds(10), ""
!ContributionPcnt = txtbidflds(11)
!DateApproved = Format(txtbidflds(12).Text, "mm/dd/yyyy"
!Adjustment_Notes = txtbidflds(13).Text
!Estimator = cbobid(4).Text
!Complexity = cbobid(5).Text
!Time = txtbidflds(14).Text
!Accuracy = cbobid(6).Text
!CheckedBy = cbobid(7).Text
!TimeChecking = txtbidflds(15).Text
.Update
If Err Then Debug.Print Err.Description
End With
CmdBidAdd.Enabled = True
CmdPrjAdd.Enabled = False
Dim clrFld As TextBox
Dim clrCbo As ComboBox
For Each clrFld In txtbidflds
clrFld.Text = ""
Next
For Each clrCbo In cbobid
clrCbo.Text = ""
Next
SSTab1.TabEnabled(0) = True
SSTab1.Tab = 0
'strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
CmdBidUpd.Enabled = False
CmdBidDel.Enabled = False
cmdNext.Enabled = False
CmdPrv.Enabled = False
cmdFirst.Enabled = False
CmdLst.Enabled = False
Exit Sub
MsgBox "Added Record " & txtFields(0).Text
UpdateErr:
End Sub
Private Sub cmdPrjSave_Click()
On Error GoTo SaveErr
With myRecordset
!F_Number = txtFields(0).Text
!JobNumber = txtFields(1).Text
!ProjectName = txtFields(2).Text
!CustomerName = cboFields(1).Text
!Salesman = cboFields(0).Text
!Job_City = cboFields(2).Text
!JobState = cboFields(3).Text
!Product = cboFields(4).Text
!manufacturerep = ""
!ExpSignDate = Format(txtFields(12).Text, "mm/dd/yyyy"
!ExpDeliveryDate = txtFields(13).Text
!ActSignDate = Format(txtFields(14).Text, "mm/dd/yyyy"
!ActDeliveryDate = txtFields(15).Text
!probability = txtFields(16).Text
!Job_Status = cboFields(7).Text
!LostJobTo = cboFields(5).Text
'!Datelost = txtFields(9).Text
!Datelost = Format(txtFields(9).Text, "mm/dd/yyyy"
!Reason = cboFields(6).Text
!Notes = txtFields(17).Text
.Update
End With
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
CmdBidAdd.Enabled = False
CmdBidDel.Enabled = False
BidConnection.Open connectStr
If BidConnection.State = adStateOpen Then
BidRecordset.CursorType = adOpenKeyset
BidRecordset.LockType = adLockOptimistic
BidRecordset.Open strSQL, BidConnection
Else
MsgBox "The connection could not be made."
GoTo SaveErr
Exit Sub
End If
On Error GoTo SaveErr
BidRecordset.MoveLast
'strSQL = "Select * From tblBidInfo where tblBidInfo.F_Number = '" & fNo & "'"
strSQL = "Select * From tblProjectInfo Inner Join tblBidInfo on tblProjectInfo.F_Number = tblBidInfo.F_Number "
txtbidflds(0).Text = BidRecordset!F_Number
txtbidflds(1).Text = BidRecordset!BidDate
txtbidflds(2).Text = BidRecordset!SquareFeet
txtbidflds(3).Text = BidRecordset!TotalCost
txtbidflds(4).Text = BidRecordset!Cost_SquareFeet
txtbidflds(5).Text = BidRecordset!Contribution
txtbidflds(6).Text = BidRecordset!Contribution_SqFeet
txtbidflds(7).Text = BidRecordset!SalesPrice
txtbidflds(8).Text = BidRecordset!SalesPrice_SqFeet
txtbidflds(9).Text = BidRecordset!Actual_Bid_Amount
txtbidflds(10).Text = BidRecordset!SelectedBid
txtbidflds(11).Text = BidRecordset!ContributionPcnt
txtbidflds(12).Text = BidRecordset!DateApproved
txtbidflds(13).Text = BidRecordset!Adjustment_Notes
txtbidflds(14).Text = BidRecordset!Time
txtbidflds(15).Text = BidRecordset!TimeChecking
dbBidList "tblBase_Alt", "Base_Alt#", 0
cbobid(0).AddItem BidRecordset!Base_Alt
dbBidList "tblYear", "Year", 1
cbobid(1).AddItem BidRecordset!Bid_Year
dbBidList "tblRebid#", "Rebid_No", 2
cbobid(2).AddItem Format(BidRecordset![Rebid#], ""
dbBidList "tblPlant_Info", "Plant_Location", 3
cbobid(3).AddItem Format(BidRecordset!Plant, ""
dbBidList "tblEstimator", "Estimator_Name", 4
cbobid(4).AddItem Format(BidRecordset!Estimator, ""
dbBidList "tblComplexity", "Complexity", 5
cbobid(5).AddItem BidRecordset!Complexity
dbBidList "tblAccuracy", "Accuracy", 6
cbobid(6).AddItem BidRecordset!Accuracy
dbBidList "tblEstimator", "Checked By", 7
cbobid(7).AddItem BidRecordset!CheckedBy
Exit Sub
SaveErr:
MsgBox "Updated Record " '& txtFields(0).Text
End Sub
Private Sub cmdBidUpd_Click()
On Error GoTo UpdErr
With BidRecordset
!F_Number = txtbidflds(0).Text
!BidDate = Format(txtbidflds(1).Text, "mm/dd/yyyy"
!Bid_Year = cbobid(1).Text
!Base_Alt = cbobid(0).Text
![Rebid#] = cbobid(2).Text
!Plant = cbobid(3).Text
!SquareFeet = txtbidflds(2).Text
!TotalCost = txtbidflds(3).Text
!Cost_SquareFeet = txtbidflds(4).Text
![TempPcnt] = 0#
!Contribution = txtbidflds(5)
!Contribution_SqFeet = txtbidflds(6).Text
!SalesPrice = txtbidflds(7).Text
!SalesPrice_SqFeet = txtbidflds(8).Text
![Actual_Bid_Amount] = txtbidflds(9).Text
!SelectedBid = Format(txtbidflds(10), ""
!ContributionPcnt = txtbidflds(11)
!DateApproved = Format(txtbidflds(12).Text, "mm/dd/yyyy"
!Adjustment_Notes = txtbidflds(13).Text
!Estimator = cbobid(4).Text
!Complexity = cbobid(5).Text
!Time = txtbidflds(14).Text
!Accuracy = cbobid(6).Text
!CheckedBy = cbobid(7).Text
!TimeChecking = txtbidflds(15).Text
.Update
End With
SSTab1.TabEnabled(1) = True
SSTab1.Tab = 1
CmdBidAdd.Enabled = False
CmdBidDel.Enabled = False
UpdErr:
MsgBox "Updated Record"
End Sub
Private Sub DisableButtons()
'Disable navigation buttons
cmdMoveFirst.Enabled = False
cmdMovePrevious.Enabled = False
cmdMoveNext.Enabled = False
cmdMoveLast.Enabled = False
End Sub
Private Sub SetMsg(strMessage As String, _
Optional lngFontColor As ColorConstants = vbBlack)
'Set the message
lblMsg.Caption = strMessage
'Set the message font color
lblMsg.ForeColor = lngFontColor
End Sub
Sub dbFieldList(dbTable As String, dbField As String, cnt As Long)
Dim dbConn As New ADODB.Connection
Dim dbRec As New ADODB.Recordset
Dim strSQL As String
Dim connectStr As String
Dim itmx As ListItem
Dim strAdd As String
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
strSQL = "SELECT " & dbField & " FROM " & dbTable
dbConn.Open connectStr
If dbConn.State = adStateOpen Then
dbRec.CursorType = adOpenKeyset
dbRec.LockType = adLockOptimistic
dbRec.Open strSQL, dbConn
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
dbRec.MoveFirst
Do Until dbRec.EOF
cboFields(cnt).AddItem _
Trim(Format(dbRec.Fields.Item(0), ""
)dbRec.MoveNext
Loop
myDataErr:
If Not myRecordset Is Nothing Then
If dbRec.State Then
dbRec.Close
End If
Set dbRec = Nothing
End If
If Not dbConn Is Nothing Then
If dbConn.State Then
dbConn.Close
End If
Set dbConn = Nothing
End If
End Sub
Sub dbBidList(dbTable As String, dbField As String, cnt As Long)
Dim dbConn As New ADODB.Connection
Dim dbRec As New ADODB.Recordset
Dim strSQL As String
Dim connectStr As String
Dim itmx As ListItem
Dim strAdd As String
connectStr = "PROVIDER=MSDASQL;dsn=F-Tracking;uid=;pwd=;"
strSQL = "SELECT " & dbField & " FROM " & dbTable
dbConn.Open connectStr
If dbConn.State = adStateOpen Then
dbRec.CursorType = adOpenKeyset
dbRec.LockType = adLockPessimistic
dbRec.Open strSQL, dbConn
Else
MsgBox "The connection could not be made."
GoTo myDataErr
Exit Sub
End If
On Error GoTo myDataErr
'-just to be sure --
dbRec.MoveFirst
Do Until dbRec.EOF
cbobid(cnt).AddItem _
Trim(Format(dbRec.Fields.Item(0), ""
)dbRec.MoveNext
Loop
myDataErr:
If Not myRecordset Is Nothing Then
If dbRec.State Then
dbRec.Close
End If
Set dbRec = Nothing
End If
If Not dbConn Is Nothing Then
If dbConn.State Then
dbConn.Close
End If
Set dbConn = Nothing
End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
'Close and dereference database objects
If Not myRecordset Is Nothing Then
If myRecordset.State Then
myRecordset.Close
End If
Set myRecordset = Nothing
End If
If Not myConnection Is Nothing Then
If myConnection.State Then
myConnection.Close
End If
Set myConnection = Nothing
End If
End Sub
Private Sub CmdExit_Click()
Unload Me
End Sub
Private Sub txtFields_Change(Index As Integer)
cmdPrjSave.Enabled = True
End Sub
Private Sub txtbidFlds_Change(Index As Integer)
CmdBidUpd.Enabled = True
End Sub
The first thing I noticed was that several of your error handler labels don't have an exit sub before them, so they will be executed whether or not an error is raised. I'm also wondering what strQuery is for. You declare it and set it's value but don't use it anywhere else in your code. If you still have problems, step through your code, then post again.
- Status
Similar threads
- Locked
- Question