ADO Data Access Problem

[Guest_imported]

I have a very basic text book which touched on ADO, but I've had problems developing an app for work which reads data from an Access 2000 DB. I have two ADO controls on my startup form which are linked to the same DB, but separate tables. When entering a "job number" (production app) into a text field on the form and clicking the "Get Record" command button, the txtjobnum control should be updated with the fldjobnum in the database; the Datasource, Datafield, Connection String and Recordsource properties (where applicable) all seem to be set just right for the controls on the form. The txtjobnum text box is not updated properly, however (as is the case with all other controls on the form that are linked to the DB). It seems that when I first start up the app, enter a job number into the text box and hit the enter button ("Get Record" button is set a default button) I get an invalid entry (due to Msgbox). If I actually "click" the "Get Record" button, however, the controls are updated just fine. And if I clear the form after startup, then enter the job number and hit the enter button again, the form is updated just fine!! Aaarrgghhh! I'm not very familiar with ADO and relatively new at VB, but the code appears to be right. I'm convinced that there are ADO/SQL-related methods or something like that which I'm unfamiliar with that are missing from my code, but I haven't been able to find anything on the web or help file which would tip me off. Below is all of the code from the "Get Command" button (for those who have a little time on their hands). If anyone's interested, I'll be MORE than happy to send you the entire project for a better look. Thanks.

Private Sub cmdGet_Click()
Const conBtns = vbOKOnly + vbExclamation + vbDefaultButton1 + vbApplicationModal
Const conMsg = "Invalid entry:" & vbNewLine & "Please enter a valid job number, case number or serial number for search."
Dim strSearch As String, strFldName As String, intRetVal As Integer, textbox As Control, intCount As Integer
Dim inttemp As Integer

intOpenRecord = 1

adoJobProfile.CommandType = adCmdText
adoRepHist.CommandType = adCmdText

intCount = 0
For intCount = 0 To 22
chkPart(intCount).Enabled = True
Next intCount
txtInstr.Enabled = True

'verify that a job number, case number or serial number
' has been entered for record search and assign value to search string
If Trim(txtJobNum.Text) <> &quot;&quot; Then
strFldName = &quot;fldJobNum&quot;
strSearch = Trim(txtJobNum.Text)
Set textbox = txtJobNum
Else
If Trim(txtCaseNum.Text) <> &quot;&quot; Then
strSearch = Trim(txtCaseNum.Text)
Set textbox = txtCaseNum
strFldName = &quot;fldCaseNum&quot;
Else
If Trim(txtSerNum.Text) <> &quot;&quot; Then
strSearch = Trim(txtSerNum.Text)
Set textbox = txtSerNum
strFldName = &quot;fldSerialNum&quot;
Else
intCount = 0
For intCount = 0 To 22
chkPart(intCount).Value = 0
chkPart(intCount).Enabled = False
Next intCount
txtInstr.Enabled = False
intRetVal = MsgBox(conMsg, conBtns, &quot;Invalid Entry&quot
txtJobNum.SetFocus
Exit Sub
End If
End If
End If

adoJobProfile.RecordSource = &quot;select fldJobNum, fldCaseNum, fldSerialNum from tblJobProfile where &quot; & strFldName _
& &quot; = '&quot; & strSearch & &quot;'&quot;

If adoJobProfile.Recordset.Fields(strFldName) <> strSearch Then
intCount = 0
For intCount = 0 To 22
chkPart(intCount).Value = 0
chkPart(intCount).Enabled = False
Next intCount
txtInstr.Enabled = False
intRetVal = MsgBox(conMsg, conBtns, &quot;Invalid Entry&quot
textbox.SelStart = 0
textbox.SelLength = Len(textbox.Text)
txtRepair.Text = &quot;&quot;
Exit Sub

End If

adoRepHist.RecordSource = &quot;select * from tblrepairhist where &quot; & strFldName _
& &quot; = '&quot; & strSearch & &quot;'&quot;

adoJobProfile.Refresh
adoRepHist.Refresh

cmdUpdate.Enabled = True
cmdUpdate.Default = True
cmdGet.Enabled = False



End Sub

 

[SirPaladin]

I have no idea if your specific problem is being caused by this, but I have had code that worked perfectly in Access 97 inexplicably crash and burn when the database was converted to Access 2000. Bill Gates does not understand the term &quot;Backward compatability&quot; [go figure] and has a habit of making you rewrite how you perform even simple tasks each time he has a new release. Good luck killing the gremlins he's created!