Help with select from where clause

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TESA0_4 - 29 Mar 2008 15:42 GMT

Hi,
I have a parent form and subform. The parent form displays details for a
'process'. Each process has a unique ID called QRID. The subform displays the
'tasks' that make up the process. The user is required to list the 'tasks'
and give each task a number that indicates the sequence in which the tasks
are done.
I have created a Function (plagerised from another dicsusion thread) that
enables the user to re-sort and re-number the task records so that they are
displayed in sequence and so they can insert or delete tasks. The Function
allows the tasks to be number with an integer increment of 1 (or more to
allow for insertion of tasks).
However, using the following code I get an error "Too few parameters. Expect
1".
Variable Q was included to prove to myself that the Function is actually
reading the QRID from the txtQRID field on the Parent form. If I amend the
Function so that the WHERE clause reads, say, [QRID] = 6 the Function works
fine for the process where QRID = 6.
I hope someone can help with some simple advice.

Public Function Renumber() As Integer
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim I As Double
Dim Q As Long
I = 0
Q = Me.Parent.txtQRID.Value
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("select [StepNo] from
QryRiskAssessTasks where [QRID] = Me.Parent.txtQRID.Value")
While Not MyRec.EOF
MyRec.Edit
I = I + Me.IncValue.Value
MyRec!StepNo = I
MyRec.Update
MyRec.MoveNext
Wend
End Function

Reply to this Message

Allen Browne - 29 Mar 2008 16:01 GMT

Concatenate the value from the parent form into the string:
"select [StepNo] from QryRiskAssessTasks where [QRID] = " &
Me.Parent.txtQRID.Value

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi,
> I have a parent form and subform. The parent form displays details for a
[quoted text clipped - 36 lines]
> Wend
> End Function

Reply to this Message

TESA0_4 - 29 Mar 2008 23:53 GMT

Thanks Allen! You're a gem! Blowed if I understand the syntax of
concatenation requirements in code.

> Concatenate the value from the parent form into the string:
> "select [StepNo] from QryRiskAssessTasks where [QRID] = " &
[quoted text clipped - 40 lines]
> > Wend
> > End Function

Reply to this Message

Allen Browne - 30 Mar 2008 04:32 GMT

Agreed: it's not particluarly obvious.

When you use an expression such as:
[Forms].[Form1].[Text99]
in a query, the query calls a thing called the Expression Service (ES) which
tries to make sense of it. If the ES finds Form1 open, and find a text box
named Text99 on it, the ES passes back the value to the query. If the query
doesn't get a value returned, it pops up a parameter box for you to enter a
value.

The ES is not available when you OpenRecordset() in code, e.g. it cannot
make sense of this:
Dim rs As DAO.Recordset
Dim strSql As String
strSql "SELECT * FROM Table1 WHERE ID = [Forms].[Form1].[Text99]"
Set rs = dbEngine(0)(0).OpenRecordset(strSql) 'Error!!!
What happens is that it complains about the "parameter."
However, you can build the string so the number goes into the string:
strSql "SELECT * FROM Table1 WHERE ID = " & [Forms].[Form1].[Text99]
This creates a string that looks like this if the text box contains 88:
SELECT * FROM Table1 WHERE ID = 88
and the OpenRecordset can make sense of that.

In your particular case, you tried to use a string containing:
"... where [QRID] = Me.Parent.txtQRID.Value"
so it works when you use:
"... where [QRID] = " & Me.Parent.txtQRID.Value

When you put a literal value into the query string, you do need to use
delimiters around it: quotes around text, or # around date values. So
there's a bit more to learn about how to put quotes inside quotes:
http://allenbrowne.com/casu-17.html

Hope that helps.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks Allen! You're a gem! Blowed if I understand the syntax of
> concatenation requirements in code.
[quoted text clipped - 52 lines]
>> > Wend
>> > End Function

Reply to this Message