newbee needs help

I have this code in a form on a date field [au from date]. I'm getting an
error message at the "set rs =" statement. The error is that I have too few
parameters. Expected 2. Can anyone tell me what parameters it expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and" & _
"[Au-pk Client id] = Forms![frm clients].[au-fk client id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

One parameter likely is Forms![frm clients].[au-fk client id]. Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client id]

The other parameter may be coming from the lack of a space after the word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " & _
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client id]

If that isn't it, then check the spelling of the two field names in your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm getting an
error message at the "set rs =" statement. The error is that I have too
few
parameters. Expected 2. Can anyone tell me what parameters it expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and" & _
"[Au-pk Client id] = Forms![frm clients].[au-fk client id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

I made the corrections you suggested. I also had a typo in the spelling of
one of the field names. Now my query string looks like this and I'm getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " & _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id]. Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client id]

The other parameter may be coming from the lack of a space after the word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " & _
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client id]

If that isn't it, then check the spelling of the two field names in your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm getting an
error message at the "set rs =" statement. The error is that I have too
few
parameters. Expected 2. Can anyone tell me what parameters it expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and" & _
"[Au-pk Client id] = Forms![frm clients].[au-fk client id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " & _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client
id]

The other parameter may be coming from the lack of a space after the word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client
id]

If that isn't it, then check the spelling of the two field names in your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " & _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client
id]

The other parameter may be coming from the lack of a space after the word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and " &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk client
id]

If that isn't it, then check the spelling of the two field names in your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

Put a breakpoint on the code step just after you set the value of strsql.
Then, when the code pauses, in the Immediate Window, type this expression
and then press Enter:

?strsql

Copy the string exactly as it prints and post it here.. something in this
string is not what you think it is or should be.

--

Ken Snell
MS ACCESS MVP


"tw" wrote in message
...
[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling
of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
& _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

The other parameter may be coming from the lack of a space after the
word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
" &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

If that isn't it, then check the spelling of the two field names in
your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm
getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it
expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client
id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

Also, to go along with my just-sent reply, is it possible for the Me![au-fk
proc id] value to contain a ' character?


--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling
of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
& _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

The other parameter may be coming from the lack of a space after the
word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
" &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

If that isn't it, then check the spelling of the two field names in
your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm
getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it
expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client
id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

I had to make a change to the Forms![frm clients].[AU-FK Client ID] the field
on the form was actually [CL-PK Client ID] but I'm still having problems.

The is the result in the immediate window...

Select * From [CC PCS Authorized Units] Where [Au-fk Proc id] = 'A0080' and
[AU-FK Client ID] = 54

The error I'm getting now is a type mismatch. [AU-FK Proc ID] is text and
[au-fk client id] is number (long)


"Ken Snell [MVP]" wrote:

Also, to go along with my just-sent reply, is it possible for the Me![au-fk
proc id] value to contain a ' character?


--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling
of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
& _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

The other parameter may be coming from the lack of a space after the
word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
" &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

If that isn't it, then check the spelling of the two field names in
your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm
getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it
expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client
id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

there is no chance there is a ' in the value of Me![au-fk proc id] the values
can only come from a list

"tw" wrote:

I had to make a change to the Forms![frm clients].[AU-FK Client ID] the field
on the form was actually [CL-PK Client ID] but I'm still having problems.

The is the result in the immediate window...

Select * From [CC PCS Authorized Units] Where [Au-fk Proc id] = 'A0080' and
[AU-FK Client ID] = 54

The error I'm getting now is a type mismatch. [AU-FK Proc ID] is text and
[au-fk client id] is number (long)


"Ken Snell [MVP]" wrote:

Also, to go along with my just-sent reply, is it possible for the Me![au-fk
proc id] value to contain a ' character?


--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling
of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
& _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

The other parameter may be coming from the lack of a space after the
word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
" &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

If that isn't it, then check the spelling of the two field names in
your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm
getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it
expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client
id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub

I copied the results in the immediate window to the sql query builder window
and it worked just fine. The datatype mismatch seems to be coming from
another part of the statement

Set rs = CurrentDb.OpenRecordset(strsql)

rs is declared as a recordset (dim rs as recordset)
strsql is declared as a string (dim strsql as string)

is CurrentDb correct?



"tw" wrote:

I had to make a change to the Forms![frm clients].[AU-FK Client ID] the field
on the form was actually [CL-PK Client ID] but I'm still having problems.

The is the result in the immediate window...

Select * From [CC PCS Authorized Units] Where [Au-fk Proc id] = 'A0080' and
[AU-FK Client ID] = 54

The error I'm getting now is a type mismatch. [AU-FK Proc ID] is text and
[au-fk client id] is number (long)


"Ken Snell [MVP]" wrote:

Also, to go along with my just-sent reply, is it possible for the Me![au-fk
proc id] value to contain a ' character?


--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
[au-fk client id] is a number field

"Ken Snell [MVP]" wrote:

Is Au-fk Client id field a text field? If yes, delimit the value from the
form with ' characters, just as you did for the Au-fk Proc id field just
above it.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I made the corrections you suggested. I also had a typo in the spelling
of
one of the field names. Now my query string looks like this and I'm
getting
an error message too few parameters expected 1

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and "
& _
"[Au-fk Client id] = " & Forms![frm clients].[AU-FK Client
ID]

thanks for you help

"Ken Snell [MVP]" wrote:

One parameter likely is Forms![frm clients].[au-fk client id].
Concatenate
its value into the SQL string (excerpt shown here)
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

The other parameter may be coming from the lack of a space after the
word
"and" in your string:
strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "' and
" &
_
"[Au-pk Client id] = " & Forms![frm clients]![au-fk
client
id]

If that isn't it, then check the spelling of the two field names in
your
string.

--

Ken Snell
MS ACCESS MVP

"tw" wrote in message
...
I have this code in a form on a date field [au from date]. I'm
getting
an
error message at the "set rs =" statement. The error is that I have
too
few
parameters. Expected 2. Can anyone tell me what parameters it
expects?

Private Sub AU_From_Date_AfterUpdate()
' see if there is a date conflict
' on any other authorizations for same client/procedure
Dim rs As Recordset
Dim strsql As String

strsql = "Select * From [CC PCS Authorized Units] Where " & _
"[Au-fk Proc id] = " & "'" & Me![au-fk proc id] & "'
and" &
_
"[Au-pk Client id] = Forms![frm clients].[au-fk client
id]"

Set rs = CurrentDb.OpenRecordset(strsql)

Do Until rs.EOF
If rs![AU-PK] Me.[AU-PK] Then
If Me.[au from date] = rs![au from date] And Me.[au from
date]
= rs![au to date] Then
MsgBox ("Date conflicts with another authorization
please
check authorization and fix one of them")
Exit Sub
End If
End If
rs.MoveNext
Loop
End Sub