Change event reading info from database

I open up a database and read in the first record, but when I try to search
by a field the data will not change. After debugging, the query string holds
the info but the when it is time to change, the text box change event does
not take place. It only stores the current record.

Private Sub cmdSerial_Click()
Dim searchSerial As String

Dim rsSearch As ADODB.Recordset
Dim searchQuery As String

searchSerial = InputBox("Enter Serial Number to Search")
Set rsSearch = New ADODB.Recordset
searchQuery = "SELECT * FROM SerialNumber Where CBSerialNumber ='" & _
Trim(searchSerial) & "'"
rsSearch.Open searchQuery, cnSerialNumber, adOpenStatic, adLockPessimistic
If rsSearch.RecordCount > 0 Then
MsgBox "Serial Number Not Found"
Else
If rsSearch.BOF = True Or rsSearch.EOF = True Then
Exit Sub
End If
txtSerial = rsSerialNumber!CBSerialNumber
txtType = rsSerialNumber!New_Upgrade
txtMotherboard = rsSerialNumber!Motherboard
txtHarddrive = rsSerialNumber!HardDrive
txtCd = rsSerialNumber!CD_ROM
txtCdrw = rsSerialNumber!CDRW_DVDRW
txtFloppy = rsSerialNumber!Floppy
txtNetworkCard = rsSerialNumber!NetworkCard
txtHardware = rsSerialNumber!OtherHardware
txtSpeakers = rsSerialNumber!Speakers
txtOS = rsSerialNumber!OperatingSystem
txtProduct = rsSerialNumber!ProductKey
txtNetworkType = rsSerialNumber!TypeNetwork
txtUser = rsSerialNumber!UserName
txtPassword = rsSerialNumber!Password
End If

End Sub

Just a quick comment:

this code looks VERY suspect:

rsSearch.Open searchQuery, cnSerialNumber, adOpenStatic, adLockPessimistic
If rsSearch.RecordCount > 0 Then
MsgBox "Serial Number Not Found"
Else
If rsSearch.BOF = True Or rsSearch.EOF = True Then
Exit Sub
End If
txtSerial = rsSerialNumber!CBSerialNumber

You are testing the RecordCount (which is generally NOT a good idea, unless
you have a CLIENT-SIDE cursor. The RecordCount property is NOT accurate until
the Recordset has been FULLY populated, and that USUALLY requires a recordset.MoveLast),
and if it is Greater than 0 (which would indicate that you have FOUND AT
LEAST ONE RECORD), you display a MsgBox saying the the Serial Number WAS
NOT found???? (you found at least 1 record, but the serial number was not
found? This makes NO Sense)

Arthur Wood


"Tommy" <lavon326@surfsouth.com> wrote:
>
>I open up a database and read in the first record, but when I try to search
>by a field the data will not change. After debugging, the query string holds
>the info but the when it is time to change, the text box change event does
>not take place. It only stores the current record.
>
>Private Sub cmdSerial_Click()
>Dim searchSerial As String
>
>Dim rsSearch As ADODB.Recordset
>Dim searchQuery As String
>
>searchSerial = InputBox("Enter Serial Number to Search")
>Set rsSearch = New ADODB.Recordset
>searchQuery = "SELECT * FROM SerialNumber Where CBSerialNumber ='" & _
> Trim(searchSerial) & "'"
>rsSearch.Open searchQuery, cnSerialNumber, adOpenStatic, adLockPessimistic
> If rsSearch.RecordCount > 0 Then
> MsgBox "Serial Number Not Found"
> Else
> If rsSearch.BOF = True Or rsSearch.EOF = True Then
> Exit Sub
> End If
>txtSerial = rsSerialNumber!CBSerialNumber
>txtType = rsSerialNumber!New_Upgrade
>txtMotherboard = rsSerialNumber!Motherboard
>txtHarddrive = rsSerialNumber!HardDrive
>txtCd = rsSerialNumber!CD_ROM
>txtCdrw = rsSerialNumber!CDRW_DVDRW
>txtFloppy = rsSerialNumber!Floppy
>txtNetworkCard = rsSerialNumber!NetworkCard
>txtHardware = rsSerialNumber!OtherHardware
>txtSpeakers = rsSerialNumber!Speakers
>txtOS = rsSerialNumber!OperatingSystem
>txtProduct = rsSerialNumber!ProductKey
>txtNetworkType = rsSerialNumber!TypeNetwork
>txtUser = rsSerialNumber!UserName
>txtPassword = rsSerialNumber!Password
>End If
>
>End Sub
>
>