Checking if there are records

[svm]

I have two files TBLPROJECTINFO and TBLBIDINFO.

TBLPROJECTINFO is a header File and TBLBIDINFO is a Detail file.

TBLPROJECTINFO has data and TBLBIDNFO may not.

How do you check if there are records in the other file and display a message "No Record Found"

Here is the program I created. I need some help and I'm a beginner in Visual Basic. Appreciate any help you can give me. Thanks.


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 Inner 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, "&quot
frmtblProjectInfo.txtFields(13).Text = Format(myRecordset!ExpDeliveryDate, "&quot
frmtblProjectInfo.txtFields(14).Text = Format(myRecordset!ActSignDate, "&quot
frmtblProjectInfo.txtFields(15).Text = Format(myRecordset!ActDeliveryDate, "&quot
frmtblProjectInfo.txtFields(16).Text = Format(myRecordset!probability, "&quot
frmtblProjectInfo.txtFields(17).Text = myRecordset!Note
'Fill combo boxes

'addData:
dbFieldList "tblsalesman", "salesman_name", 0
frmtblProjectInfo.cboFields(0) = Format(myRecordset![Salesman], "&quot

dbFieldList "[tblcustomer name]", "customername", 1
frmtblProjectInfo.cboFields(1) = Format(myRecordset![CustomerName], "&quot

dbFieldList "tbljob_city", "job_city_name", 2
frmtblProjectInfo.cboFields(2) = Format(myRecordset![Job_City], "&quot

dbFieldList "tblstate", "job_state_name", 3
frmtblProjectInfo.cboFields(3) = Format(myRecordset![JobState], "&quot

dbFieldList "tblproduct", "product", 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, "&quot

dbFieldList "tbl_precast_companies", "[Company Name]", 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], "&quot

dbFieldList "tblReason", "Reason", 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], "&quot

dbFieldList "tbljob_status", "JobStatus", 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], "&quot
'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 <> &quot;&quot; Then
txtFields(0).Enabled = False
CmdPrjAdd.Enabled = False
cmdPrjSave.Enabled = False

dbFieldList &quot;tblsalesman&quot;, &quot;salesman_name&quot;, 0
frmtblProjectInfo.cboFields(0) = myRecordset!Salesman

dbFieldList &quot;[tblcustomer name]&quot;, &quot;customername&quot;, 1
frmtblProjectInfo.cboFields(1) = myRecordset!CustomerName

dbFieldList &quot;tbljob_city&quot;, &quot;job_city_name&quot;, 2
frmtblProjectInfo.cboFields(2) = myRecordset![Job_City]

dbFieldList &quot;tblstate&quot;, &quot;job_state_name&quot;, 3
frmtblProjectInfo.cboFields(3) = myRecordset!JobState

dbFieldList &quot;tblproduct&quot;, &quot;product&quot;, 4
frmtblProjectInfo.cboFields(4) = Format(myRecordset!Product, &quot;&quot

dbFieldList &quot;tbl_precast_companies&quot;, &quot;[Company Name]&quot;, 5
frmtblProjectInfo.cboFields(5) = Format(myRecordset![LostJobTo], &quot;&quot

dbFieldList &quot;tblReason&quot;, &quot;Reason&quot;, 6
frmtblProjectInfo.cboFields(6) = Format(myRecordset![Reason], &quot;&quot

dbFieldList &quot;tbljob_status&quot;, &quot;JobStatus&quot;, 7
frmtblProjectInfo.cboFields(7) = Format(myRecordset![Job_Status], &quot;&quot
'Default info for project info add

' frmtblProjectInfo.cboFields(0).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(1).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(2).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(3).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(4).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(5).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(6).Text = &quot;&quot;
' frmtblProjectInfo.cboFields(7).Text = &quot;Pending&quot;


End If
If fNo = &quot;&quot; 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 &quot;The connection could not be made.&quot;
GoTo FindErr
Exit Sub
End If

On Error GoTo FindErr

If BidAdd = False Then


strSQL = &quot;Select * From tblBidInfo &quot;


BidRecordset.MoveLast



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, &quot;&quot
'txtbidflds(1).Text = BidRecordset!BidDate, &quot;&quot
'txtbidflds(2).Text = Format(BidRecordset!SquareFeet, &quot;&quot
'txtbidflds(3).Text = Format(BidRecordset!TotalCost, &quot;&quot
'txtbidflds(4).Text = Format(BidRecordset!Cost_SquareFeet, &quot;&quot
'txtbidflds(5).Text = Format(BidRecordset!Contribution, &quot;&quot
'txtbidflds(6).Text = Format(BidRecordset!Contribution_SqFeet, &quot;&quot
'txtbidflds(7).Text = Format(BidRecordset!SalesPrice, &quot;&quot
'txtbidflds(8).Text = Format(BidRecordset!SalesPrice_SqFeet, &quot;&quot
'txtbidflds(9).Text = Format(BidRecordset!Actual_Bid_Amount, &quot;&quot
'txtbidflds(10).Text = Format(BidRecordset!SelectedBid, &quot;&quot
'txtbidflds(11).Text = Format(BidRecordset!ContributionPcnt, &quot;&quot
'txtbidflds(12).Text = Format(BidRecordset!DateApproved, &quot;&quot
'txtbidflds(13).Text = Format(BidRecordset!Adjustment_Notes, &quot;&quot
'txtbidflds(14).Text = Format(BidRecordset!Time, &quot;&quot
'txtbidflds(15).Text = Format(BidRecordset!TimeChecking, &quot;&quot

dbBidList &quot;tblBase_Alt&quot;, &quot;Base_Alt#&quot;, 0
frmtblProjectInfo.cbobid(0) = BidRecordset!Base_Alt

dbBidList &quot;tblYear&quot;, &quot;Year&quot;, 1
frmtblProjectInfo.cbobid(1) = BidRecordset!Bid_Year

dbBidList &quot;tblRebid#&quot;, &quot;Rebid_No&quot;, 2
frmtblProjectInfo.cbobid(2) = Format(BidRecordset![Rebid#], &quot;&quot

dbBidList &quot;tblPlant_Info&quot;, &quot;Plant_Location&quot;, 3
frmtblProjectInfo.cbobid(3) = Format(BidRecordset!Plant, &quot;&quot

dbBidList &quot;tblEstimator&quot;, &quot;Estimator_Name&quot;, 4
frmtblProjectInfo.cbobid(4) = Format(BidRecordset!Estimator, &quot;&quot

dbBidList &quot;tblComplexity&quot;, &quot;Complexity&quot;, 5
frmtblProjectInfo.cbobid(5) = Format(BidRecordset!Complexity, &quot;&quot

dbBidList &quot;tblAccuracy&quot;, &quot;Accuracy&quot;, 6
frmtblProjectInfo.cbobid(6) = Format(BidRecordset!Accuracy, &quot;&quot

dbBidList &quot;tblEstimator&quot;, &quot;Estimator_Name&quot;, 7
frmtblProjectInfo.cbobid(7) = Format(BidRecordset!CheckedBy, &quot;&quot

End If

FindErr:

'MsgBox &quot;No Record Found&quot;

End Sub

 

[StewartGW]

You can check to see if the record is at the end of the file:

If Not MyRecordSet.EOF Then
'Do something
Else
Msgbox &quot;No records found&quot;
end if

 

[svm]

My problem is trying to check if the key exists in another file otherwise it should give me a message &quot;No record found&quot;.