Help with Assigning Variables from a SELECT Statement

  • Thread starter Nadine via AccessMonster.com
  • Start date
N

Nadine via AccessMonster.com

Hi!

First of all - I want to say how much this website has helped me to date. I
am a beginner to ACCESS and use this site to help me learn.

I believe I have a problem which is not that complicated to the experts and
was hoping to get some help. Thank you in advance to anyone who replies to
this thread.

I have a form with two text boxes for the user to input values. I want to
use these values to SELECT data from a table.

Here is my attempt and am falling short as to how to proceed:
****************************************************************************************************
- - I decided to go with a recordset but am not sure if this is the best
choice. - -

Dim db As Database
Dim rec As Recordset

Dim SQLString As String


strCertificate = txtCertificate
strCrop = txtCrop

SQLString = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05 " & _
"FROM dat_AllPlans " & _
"WHERE Certificate = " & strCertificate & " AND CROP = " &
strCrop & ""

Set db = CurrentDb()
Set rec = db.OpenRecordset(SQLString)

- - This is where I am lost - -
********************************************************************************************************

How do I assign variables based on what the SELECT statement brings back? I
have six textboxes on the form that I want to display information for
(Contract, PLAN, AIY, Price_05, Area, Cvg_05) based on the Certificate and
Crop information entered by the user.

I hope someone is able to understand my problem!

Thank you!

Nadine
 
M

Marshall Barton

Nadine said:
First of all - I want to say how much this website has helped me to date. I
am a beginner to ACCESS and use this site to help me learn.

I believe I have a problem which is not that complicated to the experts and
was hoping to get some help. Thank you in advance to anyone who replies to
this thread.

I have a form with two text boxes for the user to input values. I want to
use these values to SELECT data from a table.

Here is my attempt and am falling short as to how to proceed:
****************************************************************************************************
- - I decided to go with a recordset but am not sure if this is the best
choice. - -

Dim db As Database
Dim rec As Recordset

Dim SQLString As String


strCertificate = txtCertificate
strCrop = txtCrop

SQLString = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05 " & _
"FROM dat_AllPlans " & _
"WHERE Certificate = " & strCertificate & " AND CROP = " &
strCrop & ""

Set db = CurrentDb()
Set rec = db.OpenRecordset(SQLString)

- - This is where I am lost - -
********************************************************************************************************

How do I assign variables based on what the SELECT statement brings back? I
have six textboxes on the form that I want to display information for
(Contract, PLAN, AIY, Price_05, Area, Cvg_05) based on the Certificate and
Crop information entered by the user.
Click to expand...


Once you have opened a recordset with the desired data, you
can retrieve the values by referring to the recorset's
fields:
Me.txtPlan = rec!Plan

Don't forget to close the recordset when you are done with
it:
rec.Close: Set rec = Nothing
 
N

Nadine via AccessMonster.com

Thank you very much for your response, Marshall. I have added the code to
assign the variables.
**********************************************************************************************************
Dim db As Database
Dim rec As Recordset

Dim SQLString As String

strCertificate = txtCertificate
strCrop = txtCrop

SQLString = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05 " & _
"FROM dat_AllPlans " & _
"WHERE Certificate = " & strCertificate & " AND CROP = " &
strCrop & ""

Set db = CurrentDb()
Set rec = db.OpenRecordset(SQLString, dbOpenDynaset)

Me.txtContract = rec!Contract
Me.txtPlan = rec!Plan
Me.txtAIY = rec!AIY
Me.txtEstablishedPrice = rec!Price_05
Me.txtAreaInsured = rec!Area
Me.txtCoverageLevel = rec!Cvg_05

rec.Close
Set rec = Nothing
****************************************************************************************************************
When I test this, I get the following error.

"Too few parameters. Expected 1."

Any idea what the problem might be? Thanks again.

Nadine
 
M

Marshall Barton

Nadine said:
Thank you very much for your response, Marshall. I have added the code to
assign the variables.
**********************************************************************************************************
Dim db As Database
Dim rec As Recordset

Dim SQLString As String

strCertificate = txtCertificate
strCrop = txtCrop

SQLString = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05 " & _
"FROM dat_AllPlans " & _
"WHERE Certificate = " & strCertificate & " AND CROP = " &
strCrop & ""

Set db = CurrentDb()
Set rec = db.OpenRecordset(SQLString, dbOpenDynaset)

Me.txtContract = rec!Contract
Me.txtPlan = rec!Plan
Me.txtAIY = rec!AIY
Me.txtEstablishedPrice = rec!Price_05
Me.txtAreaInsured = rec!Area
Me.txtCoverageLevel = rec!Cvg_05

rec.Close
Set rec = Nothing
****************************************************************************************************************
When I test this, I get the following error.

"Too few parameters. Expected 1."
Click to expand...


This error means that the query has a name that doesn't mean
anything to Access. The most common cause is that
OpenRecordset, which runs in VBA environment, doesn't
resolve query parameters automatically the way they are
dealt with in the Access environment ,where you are prompted
for the parameter's value.

In your case, where you are not trying to use a parameter,
the message is usually beacuse you used a text string
without surrounding it with qoutes. Your query would be
fine if both the Certificate and CROP fields in the table
were a numeric type. If they are Text fields, you need to
write the query this way:

SQLString = "SELECT . . . _
"WHERE Certificate = """ & strCertificate & _
""" AND CROP = """ & strCrop & """"

The reason for the "extra" quotes is that you need to use
two quotes inside the outer quotes to get one quote in the
result.
 
N

Nadine via AccessMonster.com

Thanks again, Marshall. Made the recommended change but still not working.

The error message that I get is:
"The value you entered isn't valid for this field."

Again this is what my code looks like now....
****************************************************************************************************************
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim SQLString As String

strCertificate = Me.txtCertificate
strCrop = Me.txtCrop

SQLSelect = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05"
SQLFrom = "FROM dat_AllPlans"
SQLWhere = "WHERE Certificate = "" & strCertificate & "" AND CROP = "" &
strCrop & """

SQLString = SQLSelect & " " & SQLFrom & " " & SQLWhere

Set db = CurrentDb()

Set rec = db.OpenRecordset(SQLString)

Me.txtContract = rec!Contract
Me.txtPlan = rec!Plan
Me.txtAIY = rec!AIY
Me.txtEstablishedPrice = rec!Price_05
Me.txtAreaInsured = rec!Area
Me.txtCoverageLevel = rec!Cvg_05

rec.Close
Set rec = Nothing

The CERTIFICATE and CROP values are coming from a textbox on my form. Would
these be considered parameters?
 
N

Nadine via AccessMonster.com

Thanks again, Marshall. Made the recommended change but still not working.

The error message that I get is:
"The value you entered isn't valid for this field."

Again this is what my code looks like now....
****************************************************************************************************************
Dim db As DAO.Database
Dim rec As DAO.Recordset
Dim SQLString As String

strCertificate = Me.txtCertificate
strCrop = Me.txtCrop

SQLSelect = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05"
SQLFrom = "FROM dat_AllPlans"
SQLWhere = "WHERE Certificate = "" & strCertificate & "" AND CROP = "" &
strCrop & """

SQLString = SQLSelect & " " & SQLFrom & " " & SQLWhere

Set db = CurrentDb()

Set rec = db.OpenRecordset(SQLString)

Me.txtContract = rec!Contract
Me.txtPlan = rec!Plan
Me.txtAIY = rec!AIY
Me.txtEstablishedPrice = rec!Price_05
Me.txtAreaInsured = rec!Area
Me.txtCoverageLevel = rec!Cvg_05

rec.Close
Set rec = Nothing

The CERTIFICATE and CROP values are coming from a textbox on my form. Would
these be considered parameters?
 
N

Nadine via AccessMonster.com

OOOOOOOOOOPPPPPPPPPPPSSSSSS!

Somehow I posted that message twice! Sorry about that!

Thanks for your help!

Nadine
 
M

Marshall Barton

Nadine said:
Thanks again, Marshall. Made the recommended change but still not working.

The error message that I get is:
"The value you entered isn't valid for this field."

Again this is what my code looks like now.... [snip]
SQLSelect = "SELECT Contract, PLAN, AIY, Price_05, Area, Cvg_05"
SQLFrom = "FROM dat_AllPlans"
SQLWhere = "WHERE Certificate = "" & strCertificate & "" AND CROP = "" &
strCrop & """ [snip]
The CERTIFICATE and CROP values are coming from a textbox on my form. Would
these be considered parameters?
Click to expand...

Those should not be considered parameters, but without the
correct quoting, Access will think they are. That's why you
got the earlier message.

I asked if the **Fields** in the **table** are Text type
fields, not if they came from a text box control on the
form. Text fields require quotes around the value you're
searching for, while numeriv types do not.

In any case, you did not count the number of quotes
correctly in my earlier post. There are supposed to be
3, 3, 3 and 4 quotes. you seem to have left one out in each
place.
 
N

Nadine via AccessMonster.com

IT WORKED! Thank you so much, Marshall! I appreciate you taking the time to
help me out! I am a beginner and sometimes the littlest think get my tripped
up! You are the MVP in my books!

Nadine
 
Top