Help Please...Tables/Forms and Excel

[OLineCoach]

I am making a simple proggie that keeps track of students signing in late to school and also out from school.

The main point on my form is the DATE and TIME.

I need to be able to do a search for all DATE = xxxx and generate a report that I can email teachers - I thought Excel might be best for this.

Any help or advice? I am stuck on creating form that asks for date, and command to show results.

thx.

 

[tails]

highlight the rows that have the date/time and the student etc then go to the data tab up the top next to tools. go to filter and then select auto filter. A little drop down icon will appear and will show a few certain dates and also show a (custom...) in it. Select that and it will come up with a pop up box. just type in the date or time that you want it to sort to and it will only show that certain date, and the students on that date.

 

[mdmilner]

um

Did you intend to post an Excel question in the Access sub-forums?

In my opinion, this might be done more easily in Access...access requires more user knowledge though.

Try this (if you're brave)

Import your spreadsheet into Access.

Open up a blank form in design mode.
Add a Textbox. Add a button.
In the button's click event add:

Dim dbs As DAO.Database
Dim qdf As QueryDef
Dim strSQL As String

Set dbs = CurrentDb()


strSQL = "SELECT * FROM tblName WHERE dteFld =#"
strSQL = strSQL & Me.textboxname.value & "#"

If ObjectExists("Query", "AName") Then
    DoCmd.DeleteObject acQuery, "AName"
End If
Set qdf = dbs.CreateQueryDef("AName", strSQL)

If you happen to use Outlook at the school you can use something like:
Substitute something appropriate for my strAddr, strCAddr, strSubject, strBody. My actual approach was to add text to avariables and then put the variables into the DoCmd object to keep it from being 30 lines long

DoCmd.SendObject acSendQuery, "", "acFormatRTF", strAddr, _
         strCAddr, "", strSubject, strBody, True, ""

You will also need this to detect the query (you can't create a new query using the same name without deleting the old one)

Function ObjectExists(ByVal strObjectType As String, _
                ByVal strObjectName As String) As Boolean

     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
On Error GoTo HandleErr
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
 
End Function

 

[mdmilner]

lesson 2

Here's a 2nd topic - use this in place of the default Text box in the previous message.

Add this line to the KeyPress event for the text box
Make sure you set the format of the text box to a date format (Short Date works for me) Also set the default value for it to Date()

What should happen is, when you open the form, it defaults in the right format to a date field (MM/DD/YY) and then you can use the + or - keyboard keys to change the date. You can also manually change the date to anything you want.


I believe the Plus/Minus code was a borrow from www.accessweb.com

Call PlusMinus(KeyAscii, "frmChanges", "sfmChanges1")


----  add this as a separate function

Public Function PlusMinus(intKey As Integer, strFormName As String, Optional _
    strSubformName As String = "", Optional strSubSubFormName As String = "") _
    As Integer
'Allows a date or number field on a form or subform to respond to plus/minus keys
'Sample Usages (in the Keypress event):
'   Call PlusMinus(KeyAscii, Me.Name)
'   Call PlusMinus(KeyAscii, Me.Parent.Name, Me.Name)
'   Call PlusMinus(KeyAscii, Me.Parent.Parent.Name, Me.Parent.Name, Me.Name)

On Error GoTo TheHandler
Dim ctl As Control
    
    If strSubformName <> "" Then
        If strSubSubFormName <> "" Then
            Set ctl = Forms(strFormName).Controls(strSubformName).Form.Controls(strSubSubFormName).Form.ActiveControl
        Else
            Set ctl = Forms(strFormName).Controls(strSubformName).Form.ActiveControl
        End If
    Else
        Set ctl = Forms(strFormName).ActiveControl
    End If
    
    ctl = CDate(ctl)
    
    Select Case intKey
        Case Is = 43        'the '+' key
            ctl = ctl + 1
            intKey = 0
        Case Is = 45        'the '-' keys
            ctl = ctl - 1
            intKey = 0
        Case Is = 61        'the '='/'+' key next to Backspace
            ctl = ctl + 1
            intKey = 0
    End Select
           
ExitHandler:
    PlusMinus = intKey
    Set ctl = Nothing
    Exit Function

TheHandler:
    Select Case Err.Number
        Case Is = 94    'Invalid use of null
        Case Is = 13    'Type mismatch
        Case Else
            MsgBox Err.Number & ":  " & Err.Description
            intKey = 0
    End Select
    Resume ExitHandler
End Function

Mike

 

[Norie]

Mike

I hope you don't mind but I really don't like ElseIf's.

Function ObjectExists(ByVal strObjectType As String, _
                ByVal strObjectName As String) As Boolean

     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
      
On Error GoTo HandleErr
     Set db = CurrentDb()
     ObjectExists = False
      
     Select Case strObjectType
     
        Case "Table"
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
          
        Case "Query"
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
          
        Case "Form", "Report", "Module"
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
          
        Case "Macro"
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
          
        Case Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End Select
  
End Function

 

[mdmilner]

naw

I don't mind - the code is actually the sample code from:
http://www.mvps.org/access/modules/mdl0014.htm

I may have made a tiny modification to use it myself but I never saw a reason to write my own...regardless of how simple this particular function actually is.

Mike