Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: Problem with my code

    Here is the code:

    Private Sub Command12_Click()

    Me.SetFocus

    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    Dim rs As Recordset

    Set db = CurrentDb

    Dim myHall As String
    myHall = Me.Hall.Value

    Dim myHireDate As String
    Me.DateForHire.SetFocus
    myHireDate = Me.DateForHire.Text

    Dim myHireTime As String
    Me.Time.SetFocus
    myHireTime = Me.Time.Text

    strSQL = "SELECT tblHallBooking.Hall, tblHallBooking.[DateForHire], tblHallBooking.Time"
    strSQL = strSQL & " FROM tblHallBooking WHERE tblHallBooking.HallID " & myHall & " AND tblHallBooking.DateForHire " & myHireDate & " AND tblHallBooking.Time " & myHireTime & ""

    Set qdf = db.CreateQueryDef

    Set rs = qdf.OpenRecordset

    rs.MoveLast

    If rs.Index > 0 Then

    MsgBox "A booking has already been made with this criteria! Please enter new criteria for a booking."

    End If
    End Sub

    The debug highlights ---> Set rs = qdf.OpenRecordset

    And says 'Object invalid or no longer set'

    Whats wrong with this?

  2. #2
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I don't know much about DAO, but looked up createquerydef in the help files

    Isn't it supposed to look something like this?

    Set qdf = db.CreateQueryDef("",strSql)

    or

    Set qdf = db.CreateQueryDef("NewStoredQueryName", strSql)

    And I'm sure I've seen samples where there where paranthesis on the openrecordset thingie

    Set rs = qdf.OpenRecordset()

    Oh - and in stead of setting focus to the controls and using the .Text property, just address the controls with the .Value property (or no property, cause .Value is the default property)

    In stead of

    Me.DateForHire.SetFocus
    myHireDate = Me.DateForHire.Text

    do

    myHireDate = Me.DateForHire.Value

    I think you're going to get problems with some of the rest of the stuff too. I don't konw whether .Index is a property of the DAO recordset object, and issuing a .MoveLast without testing whether there are records might bomb.
    Roy-Vidar

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Here is a quick clean up on the code. Didn't debug though so may not work. The openrecordset method needs to have the recordset type , dbOpenDynaset or , dbOpenReadonly, OR , dbOpenSnapshot

    Check the VB Help for specifics on DAO parameters.
    Code:
    ' change the name of the command button to soemthing meaningful
    Private Sub cmdSetHireDt_Click()
    
    ' no need to set focus your already there
    'Me.SetFocus
    
    Dim db As Database
    Dim qdf As QueryDef
    Dim strSQL As String
    
     ' DAO ir ADO???? Better make it DAO
    Dim rs As DAO.Recordset
    ' Lets clean thi sup put all dim statements at top
    Dim myHall As String
    Dim myHireDate As String
    Dim myHireTime As String
    
    
    Set db = CurrentDb
    
    ' What happens if I'm null, I go crash/debug
    If not isNull(Me.Hall)
    myHall = Me.Hall
    End if
    
    ' YadaYada
    If Not isNull(Me.DateForHire)
    myHireDate = Me.DateForHire
    End If
    
    If not isNull(Me.Time)
    myHireTime = Me.Time
    End IF
    
    ' Concatenate the SQL, make sure we put (= ' VAR ') quote marks around txt variables.
    strSQL = "SELECT [Hall], [DateForHire], [Time]" & _
      "  FROM tblHallBooking WHERE [HallID] = '" & myHall & "' AND " & _     
      "   [DateForHire] ='" & myHireDate & " AND [Time] = '" & myHireTime & "'"
    
    Set qdf = db.CreateQueryDef
    Set rs = qdf.OpenRecordset(STRsql, dbOpenDynaset)
    rs.MoveLast
    
    ' use rs.recordcount, thats better
    'If rs.Index > 0 Then
    If rs.Recordcount > 0 then
    
    MsgBox "A booking has already been made with this criteria! Please enter new criteria for a booking."
    
    End If
    End Sub
    Last edited by savbill; 02-22-05 at 22:40.
    ~

    Bill

Posting Permissions

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