No value given for 1 required parameter

[Howlsta]

I need to parameters in the below code the one is passed ok, but i'm trying to give the first parameter StudentIDF using an SQL statement. I assigned the SQL statement to variable StrSQL, but it's not being recognised as a parameter it seems like it's taking it as a string, but if I remove the "" a syntax error occurs. Anyone know what is wrong? The offending code is below.

Rich

Private Sub cmdSelect_Click()

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblStudent.StudentIDF from tblStudent where tblStudent.UBNumber = txtUB"

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from qryPickOption"
.AddNew
!StudentIDF = strSQL
!ModuleIDF = Me.cboOptionalMod
.Update
End With

Me.lboOptions.Requery

End Sub

 

[Drew]

Hi Howlsta,

you only seem to be passing one value in ( tblStudent.UBNumber = txtUB ), if i've gone blind and can't see the second post back

.
As you refer to it as a number you need to change your SQL line to read -
strSQL = "SELECT tblStudent.StudentIDF from tblStudent where tblStudent.UBNumber = " & txtUB

if it's a string instead then you'd need -
strSQL = "SELECT tblStudent.StudentIDF from tblStudent where tblStudent.UBNumber = " & chr(34) & txtUB & chr(34)

HTH

Drew

 

[Howlsta]

Hi Drew,

I changed it but it still says the same thing. I also tried switching qryPickOption with the table name which i figured should work okay too, then I got a type mismatch. UBNumber is a number field and StudentIDF is autonumber so don't see why a problem occurred.
I assumed the second parameter i'm passing is moduleIDF, is that correct. I want studentIDF value selected by SQL to be placed into the table with the moduleIDF.

Rich

 

[Drew]

yup, my bad - i looked at the top sql and failed to look at the rest properly, getting the wrong end of the stick..oops. You need to run the SQL against the database, otherwise it's just a string like any other and won't have retrieved any data. You could either do that the normal way or use
!StudentIDF = DLookup("StudentIDF", "tblStudent", "UBNumber = " & txtUB) and get rid of strSQL all together, probably easier unless speed is a big issue.

!ModuleIDF = Me.cboOptionalMod is working okay yeah?

HTH this time

Drew

[This message has been edited by Drew (edited 08-29-2001).]

 

[Howlsta]

Hi Drew,

Yeah, the other parameter is working fine, in fact they both are now. The DLookup worked OK, I didn't know about that method else I would have used it, cos speed is not really an issue as you mentioned. Now I know for next time as well. Cheers,

Rich