Results 1 to 9 of 9

Thread: RecordCount

  1. #1
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3

    Unanswered: RecordCount

    I have an unbound form open with one command button. When I click the button I want to open a recordset (a query with 1 criteria) and check if it contains any records. If so I want to open one form, if not I want to open a different form. Being a novice (and grey haired) I cannot make this work. I attach the SQL of the Query and my code attached to the command button on the open form, which returns a "type mismatch" error message. Can anyone help me please? Thanks in advance.
    SQL thus:-
    SELECT Vehicles.VehicleID, Vehicles.VeMake, Vehicles.VeModel, Vehicles.VeRegNo, Vehicles.VeMOTDate
    FROM Vehicles
    WHERE (((Vehicles.VeMOTDate) Between Date() And DateAdd("d",+14,Date())));

    Code thus:-
    Private Sub CmdButOK_Click()

    On Error GoTo CmdButOK_Err
    Dim dbs As Database
    Dim rst As Recordset
    'Open a recordset object in the current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryMOTsDue")

    If rst.RecordCount = 0 Then
    DoCmd.OpenForm ("frmMainMenu")

    Else
    DoCmd.OpenForm acForm, ("frmMOTsDue")

    End If

    rst.Close
    Set dbs = Nothing

    CmdButOK_Exit:
    Exit Sub

    CmdButOK_Err:
    MsgBox Error$
    Resume CmdButOK_Exit

    End Sub

  2. #2
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    Try this >>
    eg. Between Now() And Now()+14
    Last edited by ingineu; 07-09-03 at 22:35.

  3. #3
    Join Date
    Jun 2003
    Location
    Canada
    Posts
    88
    You may have to change your database line to:
    Dim dbs as DAO.database ... as I seem to recall this error message appearing when I switched to Access 2000.
    Last edited by ingineu; 07-10-03 at 02:14.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and make sure that the DAO 3.6 library is referenced (alt-F11 - tools -references)

    izy

  5. #5
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3
    Query works fine on either type of expression (Date & DateAdd or Now & Now+14). Have changed database line to DAO.Database and ensured Reference is valid. Still get type mismatch. I am using Access 2000. Help!!!

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try:

    Dim dbs As DAO.Database 'i know you've done this already
    Dim rst As DAO.Recordset 'but do this as well
    'Open a recordset object in the current database.
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryMOTsDue")
    rst.movelast 'not critical when checking for 0 records, but good discipline
    If rst.RecordCount = 0 Then
    DoCmd.OpenForm "frmMainMenu" 'you don't need the parenthesis
    Else
    DoCmd.OpenForm "frmMOTsDue" 'what was that acform doing there?
    End If

    CmdButOK_Exit:
    set rst = nothing
    Set dbs = Nothing
    Exit Sub

    CmdButOK_Err:
    MsgBox Error$
    Resume CmdButOK_Exit

    assuming this still doesn't work for you, we need to know which line generates the type mismatch - simplest is some experiments with a break point - moving it progessively down through the code till you find the guilty line.

    izy

  7. #7
    Join Date
    Dec 2002
    Posts
    60
    Another method:

    Private Sub CmdButOK_Click()

    ...

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryMOTsDue")

    If rst.BOF and rst.EOF then
    'No records returned
    Else
    'Records returned
    EndIf

    ...

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You shouldn't need to open a recordset for this. Just use the dcount() function.

    if DCount ("[Vehicle_ID]", "[Vehicles]", "[VeMOTDate] between Now() and Now() + 14") then
    DoCmd.OpenForm ("frmMainMenu")
    else
    DoCmd.OpenForm acForm, ("frmMOTsDue")


    blindman

  9. #9
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3
    What can I say blindman - you are a star! My final coding (slightly modified) is :-
    Private Sub CmdButOK_Click()

    On Error GoTo CmdButOK_Err

    DoCmd.Close acForm, Me.Name

    If DCount("[VehicleID]", "[qryMOTsDue]", "[VeMOTDate] between Now() and Now() + 14") Then
    DoCmd.OpenForm ("frmMOTsDue")
    Else
    DoCmd.OpenForm ("frmMainMenu")
    End If

    CmdButOK_Exit:
    Exit Sub

    CmdButOK_Err:
    MsgBox Error$
    Resume CmdButOK_Exit

    End Sub

    The first line after on error is an addition but the above works perfectly.
    Thank you to all who contributed. I am very grateful.
    JAWN

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •