Help with this SQL code...

[HLiu]

rs.Open "SELECT Reservation from tblProductType where ProductType LIKE '*" & ProductTypeCheck & "*'", CurrentProject.Connection

This line is returning a 'No value given for one or more required parameters' error.

What it should do is select the value of the field 'Reservation' of the record with field ProductType matching the string typed into text box ProductTypeCheck, from the table 'tblProductType'. Any ideas?

Thanks in advance.

 

[HLiu]

I used query builder to get this code:

SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[Product Type]) Like 'Test'))

'Test' is just a dummy product type. And when I transfered it to my original code

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[Product Type]) Like 'Test'))", CurrentProject.Connection
rs.MoveFirst
MyVal = rs.Fields(0).Value
If MyVal = True Then MsgBox ("Ticked") Else MsgBox ("Not Ticked")
rs.Close
Set rs = Nothing

It works perfectly, however when I replace 'Test' with

'*" & ProductTypeCheck & "*'

so the user enter the product type they want I get a runtime 3021 error 'Either BOF or EOF is true...'

 

[Rabbie]

I used query builder to get this code:

SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[Product Type]) Like 'Test'))

'Test' is just a dummy product type. And when I transfered it to my original code

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[Product Type]) Like 'Test'))", CurrentProject.Connection
rs.MoveFirst
MyVal = rs.Fields(0).Value
If MyVal = True Then MsgBox ("Ticked") Else MsgBox ("Not Ticked")
rs.Close
Set rs = Nothing

It works perfectly, however when I replace 'Test' with

'*" & ProductTypeCheck & "*'

so the user enter the product type they want I get a runtime 3021 error 'Either BOF or EOF is true...'

The error suggests that you are not returning any records in this record set.

 

[HLiu]

That's where I'm stuck. It SHOULD be returning a record, because all I've changed is instead of defining the criteria as 'Test' in the code, I'm defining it in the 'ProductTypeCheck' text box.

 

[nIGHTmAYOR]

You can throw all that code in the garbage and use :
If Dlookup("[Reservation]","tblProductType","[ProductType] Like '" & Me![ProductTypeCheck] & "'") = "Ticked" Then
MsgBox "Ticked"
Else
MsgBox "Unticked"
End If

 

[Rabbie]

What you need to do is to check that you are generating the string you are trying to.

TRy using code like this

Dim rs As ADODB.Recordset
Dim strSQL as string
Set rs = New ADODB.Recordset
strSQL = "SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[Product Type]) Like '*" & ProductTypeCheck & "*' 
))", CurrentProject.Connection
msgbox(strSQL)
rs.open(strSQL)
rs.MoveFirst
MyVal = rs.Fields(0).Value
If MyVal = True Then MsgBox ("Ticked") Else MsgBox ("Not Ticked")
rs.Close
Set rs = Nothing

This should display the SQL before you run it.

 

[HLiu]

You can throw all that code in the garbage and use :
If Dlookup("[Reservation]","tblProductType","[ProductType] Like '" & Me![ProductTypeCheck] & "'") = "Ticked" Then
MsgBox "Ticked"
Else
MsgBox "Unticked"
End If

This gave the result 'Ticked' for every product type... even for the ones that weren't ticked.

Btw Reservation is a check box field, so should it read = True not = "Ticked"?

 

[nIGHTmAYOR]

Then replace the check to the if condition by True !

If Dlookup("[Reservation]","tblProductType","[ProductType] Like '" & Me![ProductTypeCheck] & "'") = True Then
MsgBox "Ticked"
Else
MsgBox "Unticked"
End If

 

[HLiu]

I did change it before using the code, I was just wondering whether you actually meant that or just a mistake.

 

[nIGHTmAYOR]

Dlookup function works perfeclty fine but its only draw back is that it returns only one value for answer , so its mainly used in retrieving values guided by their key value in its where condition which is your case .
However if expected to return more than one record Dlookup report only the very first occurance of the record and return its field value , so u can use it based on such givens .

 

[georgedwilkinson]

Dlookup function works perfeclty fine but its only draw back is that it returns only on value for answer

Don't forget that DAnything is outrageously slow. Not a big deal unless you call it iteratively, in which case it could potentially slow all your users down to a crawl and cause locking problems.

 

[HLiu]

---------------------------
Microsoft Office Access
---------------------------
SELECT tblProductType.Reservation FROM tblProductType WHERE (((tblProductType.[ProductType]) Like '*Test*'))
---------------------------
OK
---------------------------

This is the message, when the string in ProductTypeCheck is 'Test'. Still getting that runtime 3021 though...

Edit: I changed the name of the Product Type field so it has no spaces now like you suggested

 

[HLiu]

Oh I think I got it, removing the asterisks has done the trick. Thanks a lot for a everybody's help!

 

[HLiu]

Its a combo box, so.. lookup I guess.

 

[nIGHTmAYOR]

if you are insisting on using this huge code achieving such a minimal task dont forget to use rs.findfirst at least so the curser moves from top row

 

[StuckfromPR]

Try using % as the wildcard instead of the *