VBA, Problem with SELECT statement

Problem with SELECT statement

Author	Message
Michae #1 / 5	Problem with SELECT statement I have some code in the onclick event that looks like this: _____________________________________________________ Set db = CurrentDb Set rsREGIONS = db.OpenRecordset("SELECT * " & _ "FROM [Regions]" & _ "WHERE [Region]=Me!Region") Set valRegionDistributionCode = rsREGIONS![Region Distribution Code] ______________________________________________________ [Regions] is a table with a field "Region" and a field "Region Distribution Code". Me!Region refers to a combo box on the current form. When I click the command button that executes this code, I get an error saying Run Time Error 3061 Too Few Parameters. Expected 1. I'm trying to get the [Region] field on the current form to match up with the [Region] field in a table. Any thoughts?
Mon, 07 Nov 2005 23:29:20 GMT

Michae #2 / 5	Problem with SELECT statement I have run accross this before and I think, if I remember correctly you need to change the WHERE portion of your code to WHERE [Region]= " & Me!Region This should do the trick Michael Quote: >-----Original Message----- >I have some code in the onclick event that looks like >this: >_____________________________________________________ >Set db = CurrentDb >Set rsREGIONS = db.OpenRecordset("SELECT * " & _ >"FROM [Regions]" & _ >"WHERE [Region]=Me!Region") >Set valRegionDistributionCode = rsREGIONS![Region >Distribution Code] >______________________________________________________ >[Regions] is a table with a field "Region" and a >field "Region Distribution Code". >Me!Region refers to a combo box on the current form. >When I click the command button that executes this code, I >get an error saying Run Time Error 3061 Too Few >Parameters. Expected 1. >I'm trying to get the [Region] field on the current form >to match up with the [Region] field in a table. Any >thoughts? >.
Tue, 08 Nov 2005 00:22:12 GMT

Marshall Barto #3 / 5	Problem with SELECT statement Quote: >I have some code in the onclick event that looks like >this: >_____________________________________________________ >Set db = CurrentDb >Set rsREGIONS = db.OpenRecordset("SELECT * " & _ >"FROM [Regions]" & _ >"WHERE [Region]=Me!Region") >Set valRegionDistributionCode = rsREGIONS![Region >Distribution Code] >______________________________________________________ >[Regions] is a table with a field "Region" and a >field "Region Distribution Code". >Me!Region refers to a combo box on the current form. >When I click the command button that executes this code, I >get an error saying Run Time Error 3061 Too Few >Parameters. Expected 1. >I'm trying to get the [Region] field on the current form >to match up with the [Region] field in a table. Any >thoughts? You have to place the value of the combo in the query, not a reference to it (the VBA enviornment can't deal with it the way the QBE or a Form/Report can). The query you posted is also missing a space before the Where clause. Try this: "SELECT * " & _ "FROM [Regions] " & _ "WHERE [Region]= " & Me!Region -- Marsh MVP [MS Access]
Tue, 08 Nov 2005 00:42:17 GMT

nem-te #4 / 5	Problem with SELECT statement Set db = CurrentDb Quote: >>Set rsREGIONS = db.OpenRecordset("SELECT * " & _ >>"FROM [Regions]" & _ >>"WHERE [Region]=" & [Me]![Region]) If you want to improve the reliability, change it to Set rsREGIONS = db.OpenRecordset("SELECT * " & _ "FROM [Regions]" & _ "WHERE [Region]=" & iif(isnull([Me]![Region]),0,[me]! [region]) This will insert a value of zero if the combobox is empty (otherwise, the SQL statement will fail and cause an error)
Tue, 08 Nov 2005 21:09:20 GMT

Michael #5 / 5	Problem with SELECT statement Thanks everyone. I will try these and see if I can get them to work! Quote: >-----Original Message----- >Set db = CurrentDb >>>Set rsREGIONS = db.OpenRecordset("SELECT * " & _ >>>"FROM [Regions]" & _ >>>"WHERE [Region]=" & [Me]![Region]) >If you want to improve the reliability, change it to >Set rsREGIONS = db.OpenRecordset("SELECT * " & _ >"FROM [Regions]" & _ >"WHERE [Region]=" & iif(isnull([Me]![Region]),0,[me]! >[region]) >This will insert a value of zero if the combobox is empty >(otherwise, the SQL statement will fail and cause an error) >.
Fri, 18 Nov 2005 11:09:10 GMT

Page 1 of 1

[ 5 post ]