Looping

Private Sub btnGetProfile_Click()
	Me.btnCount.Enabled = False
	Me.btnCountScore.Enabled = False
	Me.txtCameoCode.Text = ""
	Me.TxtCreditBand.Text = ""
	Me.txtYearsOfResidency.Text = ""
	Me.txtFamily.Text = ""
	Me.txtTPS.Text = ""
	Me.txtDead.Text = ""
	Me.txtYearofBirth.Text = ""
	Me.txtMortgageName = ""
	Me.txtToolsName = ""
	Me.txtPostcode.Text = ""
	Me.txtScore = ""
 
	If Not IsNumeric(Me.txtID) Then
		MsgBox "Please enter only numbers", vbCritical
		Exit Sub
	End If
 
	Dim dbMortgage As New ADODB.Connection
	Dim dbTools As New ADODB.Connection
	Dim rsMortgage As New ADODB.Recordset
	Dim rsTools As New ADODB.Recordset
	Dim myArea As String
 
	dbMortgage.ConnectionString = strMortgage
	dbMortgage.Open
 
	rsMortgage.Source = "SELECT [post code],[telephone],[lastname] from [contacts] WHERE [ID]=" & CLng(Me.txtID)
 
	rsMortgage.Open , dbMortgage, adOpenStatic
 
	If rsMortgage.EOF Then
		MsgBox "Can not find reference number " & Me.txtID, vbCritical
		Exit Sub
	End If
 
	rsMortgage.MoveFirst
	If IsNull(rsMortgage.Fields("Post Code")) Or rsMortgage.Fields("Post code") = "" Then
		MsgBox "There is no post code entered for that record", vbCritical
		Exit Sub
	End If
 
	myArea = Left$(rsMortgage.Fields("Post Code"), 2)
 
	If IsNumeric(Right$(myArea, 1)) Then
		myArea = Left$(myArea, 1)
	End If
 
	dbTools.ConnectionString = strTools
	dbTools.Open
	rsTools.Source = "SELECT [" & myArea & "].* FROM [" & myArea & "] WHERE [telephone]='" & rsMortgage.Fields("Telephone") & "'"
	rsTools.Open , dbTools, adOpenKeyset
 
	If rsTools.EOF Then
		MsgBox "No matching record", vbCritical
		Exit Sub
	End If
 
	Me.txtPostcode.Text = myArea
	Me.txtCameoCode.Text = NZ(rsTools.Fields("Cameo Code").Value)
	Me.TxtCreditBand.Text = NZ(rsTools.Fields("Credit Band").Value)
	Me.txtYearsOfResidency.Text = NZ(rsTools.Fields("Years of residency").Value)
	Me.txtFamily.Text = NZ(rsTools.Fields("Family").Value)
	Me.txtTPS.Text = NZ(rsTools.Fields("tps").Value)
	Me.txtDead.Text = NZ(rsTools.Fields("dead").Value)
	Me.txtYearofBirth.Text = NZ(rsTools.Fields("Year of Birth"))
	Me.txtMortgageName.Text = NZ(rsMortgage.Fields("lastname"))
	Me.txtToolsName.Text = NZ(rsTools.Fields("lastname"))
	Me.btnCount.Enabled = True
	Me.btnCountScore.Enabled = True
	Me.txtScore = NZ(rsTools.Fields("score"))
End Sub