Clients - same family name 1

[santastefano]

My application has conventional client files, First Name, Family Name
In addition it has students held in a separate table. I need to avoid the unlikely event of two brothers attending the same session.
qryCheckClient works and returns required information.
qryClientID updates the student table.

Dim rsCheckClient As DAO.Recordset
    Dim db As DAO.Recordset
    Set db = CurrentDb
    Set rsCheckClient = db.OpenRecordset("qryCheckClient", dbopendynaset)
    rs.Open "CheckClient"
        If Not rsCheckClient.BOF And Not rsCheckClient.EOF Then
        DoCmd.OpenQuery "qryClientID"
        Else
        Reply = MsgBox("There is no record of this student", 64, "School Run")
        DoCmd.CancelEvent
        DoCmd.GoToControl "LastName"
        Me.FirstName = ""
        Me.LastName = ""
        End If
    rsCheckClient.Close 
    Set rsCheckClient = Nothing 
    Set db = Nothing

Returns error message Type mismatch
DAO is selected in type libraries.
Also tried a list box that retrieves First Name, Last Name and ID Number together with text boxes (=[ListBoxName].[Column],0) but the ListBox always selects the first example found.
Any help gratefully received.

 

[Golom]

Got a few strange things here

Dim db As DAO.Recordset  [red]<-- db is a DAO.Database ... Not Recordset[/red]
Set db = CurrentDb
Set rsCheckClient = db.OpenRecordset("qryCheckClient", dbopendynaset)
rs.Open "CheckClient"  [red]<-- ADO syntax ... DAO recordsets don't have an "Open" method.
... and what is "rs" anyway? I don't see it used anywhere.[/red]

 

[santastefano]

First thanks for your careful analysis.
The code is taken from The Developers Handbook and, in fact, works elsewhere in the application however...
My problem is to identify if a query has any data and if not return an error message.
Any suggestions for this?
Regards and thanks

 

[randy700]

My first suggestion would be to fix the errors pointed out by Golom! Or, at least, provide additional information to explain the questions he asked, such as "what is rs?"


Randy

 

[PHV]

What about this ?
Dim rsCheckClient As DAO.Recordset
Dim db As DAO.[!]Database[/!]
Set db = CurrentDb
Set rsCheckClient = db.OpenRecordset("qryCheckClient", dbopendynaset)
[!]'rs.Open "CheckClient"[/!]
If Not rsCheckClient.BOF And Not rsCheckClient.EOF Then
DoCmd.OpenQuery "qryClientID"
Else
Reply = MsgBox("There is no record of this student", 64, "School Run")
DoCmd.CancelEvent
DoCmd.GoToControl "LastName"
Me.FirstName = ""
Me.LastName = ""
End If
rsCheckClient.Close
Set rsCheckClient = Nothing
Set db = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[santastefano]

Thanks to all.
PHV - returns too few parameters, expected 2
Randy - I am not capable of answering the questions raised, I took the code from Access 2000 Developers Handbook and it works elsewhere in the same application.
Forgetting about this code the problem I need to solve is to identify if a select query contains data.
Would it be better to start a new thread with this specific question?
Thanks in advance

 

[PHV]

too few parameters, expected 2
Seems that qryCheckClient is a parametized query !

identify if a select query contains data
You may consider the DLookUp or DCount functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[santastefano]

Thanks
I tried to implement Count without success (probably my syntax)
The DLookup function works OK
Just by-the-way the too few parameters comes at the beginning of the code

Set rsCheckClient

The query uses data from a user form. It is impossible for there to be more than one line of data in the query and it works in isolation. I have a working solution now but if you have time could you explain what that problem might be.

 

[Golom]

The Too few parameters message from a query usually indicates (as PHV said) that the SQL for the query contains parameters and you haven't set values for them.

For example, if qryClientID was

Select * From ClientTable
Where ClientID = '" & [Enter ClientID] & "'"

and you just tried to open it with

DoCmd.OpenQuery "qryClientID"

You would get "too few parameters, expected 1" because you have not supplied a value for [Enter ClientID]. You would need

Dim qry As DAO.QueryDef
Set qry = CurrentDB.QueryDefs("qryClientID")
qry("[Enter ClientID]") = "12345"

Set rs = qry.Openrecordset

 

[TheAceMan1]

How are ya santastefano . . .

If your gonna nail this you need to [blue]post the SQL[/blue] of [blue]qryCheckClient[/blue] . . .

See Ya! . . . . . .