No Value ERROR

[Dubya007]

hey i have this code

If Trs.State = adStateClosed Then
    Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble, tblMachines", DBConn, adOpenDynamic, adLockOptimistic
    Call Display(0)
    Trs.MoveFirst

and i get an error that says that
"No Value given for one or more of the parameters"

then debugger points to this line

Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble, tblMachines", DBConn, adOpenDynamic, adLockOptimistic

[techgnome]

You are selecting data from two tables and yet you don't show how the tables are related.

You need a JOIN clause or a WHERE clause that links the two together.

TG

[Dubya007]

ok cool, how do i do that?

Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble, tblMachines JOIN Testble, tblMachines", DBConn, adOpenDynamic, adLockOptimistic

[Ecniv]

See the second top thread for hints on sql statements, joins etc.

Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble, tblMachines", DBConn, adOpenDynamic, adLockOptimistic, adCmdText

Bit in red missing.
Also you should check that both table.field names exist, and are spelt correctly.
The query should run, but would give you a cartesian result (ie everything in the one table against everything in the other table)

Use a left join on at least one field that is the same in both tables, something like the following in the from clause.

Testble left join tblMachines on Testble.IDNum=tblMachines.IDNumID

Vince

[Dubya007]

thnax for the help. this is the code that i used.

Private Sub Form_Load()
Me.MousePointer = 11
If Trs.State = adStateClosed Then
    Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble LEFT JOIN tblMachines on Testble.IDNum=tblMachines.IDNumID", DBConn, adOpenDynamic, adLockOptimistic, adCmdText
    Call display(0)
    Trs.MoveFirst
Else
    Call display(1)
End If
Me.MousePointer = 0
End Sub

i still get the same error. is there something else that i am missing? thnax for your response on both of my threads

[Ecniv]

Originally posted by Dubya007
thnax for the help. this is the code that i used.

Private Sub Form_Load()
Me.MousePointer = 11
If Trs.State = adStateClosed Then
    Trs.Open "SELECT Testble.IDNum, tblMachines.machines_desc FROM Testble LEFT JOIN tblMachines on Testble.IDNum=tblMachines.IDNumID", DBConn, adOpenDynamic, adLockOptimistic, adCmdText
    Call display(0)
    Trs.MoveFirst
Else
    Call display(1)
End If
Me.MousePointer = 0
End Sub

Ok... time to go to debugging basics.. I hope..

Copy n paste the sql statement into Access Sql Builder (or whichever db you are using) to see whether it runs.
If it doesn't run, it should have a more helpful pointer to the problem area (suggests a fieldname is incorrectly spelt, so it can't find it).
Is dbconn open?
Breakpoint the operation and step through to see what variables are holding (look for nothings... and errors created).
Separate out the .open statement like so:

Dim adoCon As ADODB.Connection
    Dim adoRst As ADODB.Recordset
    
    adoCon.Open "Connection string"
    
    adoRst.LockType = adLockOptimistic
    adoRst.CursorType = adOpenStatic
    adoRst.ActiveConnection = adoCon
    adoRst.Source = "Select * from testtble"
    adoRst.Open Source, con, CursorType, LockType, options
    
    adoRst.Close
    adoCon.Close
    Set adoRst = Nothing
    Set adoCon = Nothing

Or something like that (use the intellisense drop downs?)

Other than that, if its small enough, or zipped up, put it up onto a website (yours?) and paste a link in a post, we can look at it and advise better/fix it and send back so you can see how to amend it yourself.


Vince

[Dubya007]

i got it thanx so much for the help, i went into Access made a query exactly how i wanted it and then copied the SQL statements into my program. here is the code that i used if you were wondering

Private Sub Form_Load()
Dim sql As String
Me.MousePointer = 11
sql = "SELECT Testble.IDNum, tblMachines.machine_desc, Testble.NeedACheck," _
       & "tblMachines.NeedsCheck, Testble.Date, tblMachines.deptId, Testble.Supervisor " _
       & "FROM tblMachines INNER JOIN Testble ON tblMachines.id = Testble.IDNum"
If Trs.State = adStateClosed Then
    Trs.Open sql, DBConn, adOpenDynamic, adLockOptimistic, adCmdText
    Trs.MoveFirst
    Call display(0)
Else
   Call display(1)
End If
Me.MousePointer = 0
End Sub