newbee needs help

[tw]

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

 

[Ken Snell [MVP]]

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.

 

[tw]

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

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>

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

 

[Ken Snell [MVP]]

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>

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

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>

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

 

[tw]

[au-fk client id] is a number field

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>

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

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>

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

 

[Ken Snell [MVP]]

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>

[au-fk client id] is a number field

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>

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

:

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>

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

 

[Ken Snell [MVP]]

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>

[au-fk client id] is a number field

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>

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

:

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>

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

 

[tw]

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)

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>

[au-fk client id] is a number field

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>

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

:

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>

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

 

[tw]

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

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)

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[tw]

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?

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)

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[Ken Snell [MVP]]

Do you have a reference set to the DAO library in your database? If not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?

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)

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[tw]

I went to Tools | References -"Microsoft DAO 3.6 Object Library" was not
checked. I checked it and tried again. I got the same message.

Do you have a reference set to the DAO library in your database? If not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?

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)


:

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[Ken Snell [MVP]]

You left the ADO library checked? Then you must disambiguate the Dim
statement for rs:

Dim rs As DAO.Recordset


--

Ken Snell
<MS ACCESS MVP>

I went to Tools | References -"Microsoft DAO 3.6 Object Library" was not
checked. I checked it and tried again. I got the same message.

Do you have a reference set to the DAO library in your database? If not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?



:

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)


:

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[tw]

thanks, that worked. Is there anything anywhere that explains these things.
I never had to deal with this kind of set up programming in FP.

You left the ADO library checked? Then you must disambiguate the Dim
statement for rs:

Dim rs As DAO.Recordset


--

Ken Snell
<MS ACCESS MVP>

I went to Tools | References -"Microsoft DAO 3.6 Object Library" was not
checked. I checked it and tried again. I got the same message.

Do you have a reference set to the DAO library in your database? If not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?



:

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)


:

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[Ken Snell [MVP]]

Hmmm... well, I think the best place would be a book on ACCESS... they
usually discuss this in reasonable detail. Otherwise, browse these
newsgroups and you'll find lots of posts regarding it.

--

Ken Snell
<MS ACCESS MVP>

thanks, that worked. Is there anything anywhere that explains these
things.
I never had to deal with this kind of set up programming in FP.

You left the ADO library checked? Then you must disambiguate the Dim
statement for rs:

Dim rs As DAO.Recordset


--

Ken Snell
<MS ACCESS MVP>

I went to Tools | References -"Microsoft DAO 3.6 Object Library" was not
checked. I checked it and tried again. I got the same message.

:

Do you have a reference set to the DAO library in your database? If
not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?



:

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)


:

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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

 

[tw]

thanks

Hmmm... well, I think the best place would be a book on ACCESS... they
usually discuss this in reasonable detail. Otherwise, browse these
newsgroups and you'll find lots of posts regarding it.

--

Ken Snell
<MS ACCESS MVP>

thanks, that worked. Is there anything anywhere that explains these
things.
I never had to deal with this kind of set up programming in FP.

You left the ADO library checked? Then you must disambiguate the Dim
statement for rs:

Dim rs As DAO.Recordset


--

Ken Snell
<MS ACCESS MVP>

I went to Tools | References -"Microsoft DAO 3.6 Object Library" was not
checked. I checked it and tried again. I got the same message.

:

Do you have a reference set to the DAO library in your database? If
not,
then your code does not know what CurrentDb is.

Go to Tools | References and select the DAO library.

--

Ken Snell
<MS ACCESS MVP>

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?



:

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)


:

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>

[au-fk client id] is a number field

:

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>

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

:

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>

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