Clear fields if no match found...

[TMD]

I have a database that holds service and repair details for PCs, the repair details are held on a repair table that is related by serial number to a device table in access2000. Currently my vb program shows the latest repair job for that device by using the following code:

dtaRepair.Recordset.FindLast ("SerialNumber = '" & txtPCserial.Text & "'&quot

this however, shows the last ever repair job in the access table for any devices that do not have a repair job created against its serial number.

any ideas how i can adapt the code so that the fields are blank if the serial number in the pc textbox - txtPCserial, is not matched to a serialnumber in the repair table.

Thanx in anticipation of your help.

 

[ca8msm]

You would only get the data required by specifying this in you sql statement. For example,

tblDevice
SerialNumber
DeviceName

tblRepair
SerialNumber
RepairName
RepairDate

To get all of the details from the device table (regardless of whether they have had a repair) your sql would look something like:

strSQL = "select tblDevice.SerialNumber, tblDevice.DeviceName, tblRepair.SerialNumber, tblRepair.RepairName, Max(tblRepair.RepairDate) from tblDevice, tblRepair where tblDevice.SerialNumber = tblRepair.SerialNumber(+) group by tblDevice.SerialNumber, tblDevice.DeviceName, tblRepair.SerialNumber, tblRepair.RepairName"

Then if any device didn't have a repair it would simply be left as blank.




----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[TMD]

is there not a method for doing it in vb6, I haven't used sql to write my database just access designview.