Code problem...please help.

[Ashfaque]

Hi,

Is there any problem with below?

Set rst2 = CurrentDb.OpenRecordset("Select * from T_VehicleMaster where VehicleRegNoEng=" & Me.VehicleRegNoEng)

It producing "Run-time error 13" - Type mismatch.

VehicleRegNoEng filed is a number field.

But rst2 recordset I am opening withing rst1 recordset and closing as well not intrup outer loop.

Example..

Dim db as DAO.Datase
Dim rst1 as DAO.Recordset
Dim rst2 as DAO.Recordset

Set rst1 = CurrentDb.OpenRecordset("Select * from Tble_X where VehicleRegNoEng=" & Me.VehicleRegNoEng)

Me.VehicleRegNoEng=rst1!VehicleRegNoEng
-----
----

Set rst2 = CurrentDb.OpenRecordset("Select * from T_VehicleMaster where VehicleRegNoEng=" & Me.VehicleRegNoEng)

Getting other related data of vehicle on form and then

rst2.close
Set rst2=Nothing

-----
----
---
rst1.close
Set rst1=Nothing

 

[namliam]

Assuming the field is numeric, it should work....

If its text you need to use

Set rst2 = CurrentDb.OpenRecordset("Select * from T_VehicleMaster where VehicleRegNoEng='" & Me.VehicleRegNoEng & "'")

Or preferably....

Set rst2 = CurrentDb.OpenRecordset(" Select * " & _ 
                                   " from T_VehicleMaster " & _
                                   " where VehicleRegNoEng= '" & Me.VehicleRegNoEng & "'")

Another alternative, are you running into eof and/or a possible NULL value?

 

[Ashfaque]

Thanks Mailman ,

1. VehicleRegNoEng filed is a number field.
2. Data is available in table

I tried both way you explained.

Any other idea ?

 

[spikepl]

1. You have only shown a snippet, cutting out the relevant bits: what is the declaration of rst2?

2. Write a descriptive title : Runtime error 13 allows others to know what this is about.. ALL posts are about a "code problem" and all posts are posted to get help, so that info is redundant.

3. The title is important because it allows making a list like the one at the bottom here.: Similar threads. But only if the titles are descriptive is it valuable.If they just say "problem" or "help" they contain no clue as to what's inside.

 

[Ashfaque]

Sir,

Here is the complete code ...

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim Jdb As DAO.Database
Dim rst2 As DAO.Database

Set rst = CurrentDb.OpenRecordset("Select * from T_JobCards where JobCardNumber=" & SearchJobCard)

If rst.BOF = False And rst.EOF = False Then

Me.VehDateIN = rst!VehDateIN
Me.VehicleRegNoEng = rst!VehicleRegNoEng
Me.VehicleRegNoEngTxt = rst!VehicleRegNoEngTxt
Me.VehUserName = rst!VehUserName
Me.JobCardNumber = rst!JobCardNumber
Me.WshopIncharge = rst!WshopIncharge
Me.UserDept = rst!UserDept



Set rst2 = CurrentDb.OpenRecordset("Select * from T_VehicleMaster where VehicleRegNoEng=" & rst!VehicleRegNoEng)


Me.VehBodyModel = rst2!VehBodyModel
Me.VehicleMaker = rst2!VehicleMaker
Me.VehChasisNumber = rst2!VehChasisNumber
Me.VehMajorColor = rst2!VehMajorColor
rst2.Close
Set Jdb = Nothing


Me.VehLastVisitDate = rst!VehLastVisitDate
Me.VehPreviousKM = rst!VehPreviousKM
Me.VehFuelReading = rst!VehFuelReading
Me.VehKeepParts = rst!VehKeepParts
Me.VehConsumableUse = rst!VehConsumableUse
Me.InvAmt = rst!InvAmt
Me.VehRepairInstructions = rst!VehRepairInstructions

Me.VehInsuranceValidity = rst!VehInsuranceValidity
Me.VehRegValidity = rst!VehRegValidity

Else
MsgBox "No Such Job Card", vbInformation, "INAT - Helpline"
VehDateIN.SetFocus
SearchJobCard.SetFocus
Exit Sub
End If

rst.Close
Set rst = Nothing

I am collecting few relative fields from tbl T_VehicleMaster. Everything works out exept the line :

Set rst2 = CurrentDb.OpenRecordset("Select * from T_VehicleMaster where
VehicleRegNoEng=" & rst!VehicleRegNoEng)

Thanks for help.

 

[spikepl]

1. With 437 posts you should know to use code brackets by now: GO advanced> select code >press #

2. Step through your code and check the values, whether they actually contain anything. Follow "To step through code" section of this post: http://www.access-programmers.co.uk/forums/showthread.php?t=149429

 

[namliam]

Why declare DB and JDB when you dont use it?

Please use code tags when posting code on the forum, see my signature for details

In your original post you have
Set rst2=Nothing
Where in your full code you have
Set Jdb = Nothing

I think =Nothing should only be used at the very end of the code, not in the middle if you intend to reuse the rst2 object.

 

[essaytee]

Firstly, I concur with spikepl, use the code tags. I typed the following prior to seeing your extended code snippet above (post #5), very hard to read but again I concur with spikepl, get some debug statements in there and check values.

I think the following is still apt, so I'm posting anyway.


For these situations, you may find it easier to use constants or variables in lieu of actual quotes. Improved readability is the result.

Also, store the SQL code in a variable and use the variable in the OpenRecordset method.

When building the SQL statement, it's easy to throw in a debug.print statement to check the result of the SQL statement.

Dim strSQL as string
Dim Qd as string
Dim Qs as string

Qd = chr(34)      ' same as Qd = """      three double quotes
Qs = chr(44)      ' same as Qs = "'"       two double quotes with a single quote inside

strSQL = "Select * from T_VehicleMaster where VehicleRegNoEng = " & Qd & Me.VehicleRegNoEng & Qd

debug.print strSQL     (or msgbox "strSQL = " & strSQL)

Set rst2 = CurrentDb.OpenRecordset(strSQL)

The code is now easier to read ('Qd' could be replaced with 'quote' your preference). The debug statement will clearly show if you have written the SQL correctly.

Obviously, the code snippet posted is not the full code required, just the pertinent bits.