VB SQL Statement Help

  • Thread starter Thread starter Daveo
  • Start date Start date
D

Daveo

Hi there,

Can anyone tell me what is wrong with this bit of code.? (I hope it's
pasted OK). I get an error message saying Run-time error '3061'"Too few
parameters. Expected 1"


strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = " &
Me.[assetType].Value).Fields(0)

Ive double checked all the field names etc.

What confuses me is that I have the following bit of code in the
preceding line which works with no problems:

strAssetCat = CurrentDb.OpenRecordset("SELECT
LU_assetCategories.category" & _
" FROM LU_assetCategories WHERE LU_assetCategories.assetCategoryID =" &
Me.assetCat).Fields(0)

It's exactly the same but seems to work when the other one doesn't!.

Thanks - David
 
What's the data type of the field assetTypeID in table LU_assetTypes? If
it's text, you need to enclose the value you're passing in quotes:

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = " & Chr$(34) & _
Me.[assetType] & Chr$(34)).Fields(0)

or

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = '" & _
Me.[assetType] & "'").Fields(0)

Exagerated for clarity, that second one is

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = ' " & _
Me.[assetType] & " ' ").Fields(0)
 
just to let you know, Doug, a duplicate post was posted at 4:50 pm, which i
picked up. turns out he had misspelled the table name in the WHERE clause;
he's fixed it.
tina :)


Douglas J. Steele said:
What's the data type of the field assetTypeID in table LU_assetTypes? If
it's text, you need to enclose the value you're passing in quotes:

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = " & Chr$(34) & _
Me.[assetType] & Chr$(34)).Fields(0)

or

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = '" & _
Me.[assetType] & "'").Fields(0)

Exagerated for clarity, that second one is

strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = ' " & _
Me.[assetType] & " ' ").Fields(0)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Daveo said:
Hi there,

Can anyone tell me what is wrong with this bit of code.? (I hope it's
pasted OK). I get an error message saying Run-time error '3061'"Too few
parameters. Expected 1"


strAssetType = CurrentDb.OpenRecordset("SELECT LU_assetTypes.type" & _
" FROM LU_assetTypes WHERE LU_assetType.assetTypeID = " &
Me.[assetType].Value).Fields(0)

Ive double checked all the field names etc.

What confuses me is that I have the following bit of code in the
preceding line which works with no problems:

strAssetCat = CurrentDb.OpenRecordset("SELECT
LU_assetCategories.category" & _
" FROM LU_assetCategories WHERE LU_assetCategories.assetCategoryID =" &
Me.assetCat).Fields(0)

It's exactly the same but seems to work when the other one doesn't!.

Thanks - David
Click to expand...
Click to expand...
 
Back
Top