Full Version: Opening Form (daily Quick Look)
UtterAccess Forums > Microsoft® Access > Access Forms
xfaith
Not sure how to do this but I would like to have I think something like a switchboard (cant find any def answer on this).
want it to have an opening screen where I can options to input data
Add Ship Ride
Add Ship Install
Add A Certification
Add Volunteer Work
But additionally I would like an area on this opening screen to show a quick look of information (That is always up to date):
IE
Total Ship Rides: XXX
Total Ship Installs: XXX
Total Certifications: XXX
Total Volunteer Work: XXX
Thanks
blobbles78
Your "Quick look" totals I would do through code. Something like:
!--c1-->
CODE
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT ShippingID FROM Shipping")
    If rs.EOF <> True And rs.BOF <> True Then
        rs.MoveLast
        Me.lblCaptionControlName.Caption = rs.RecordCount
    End If
    
    Set rs = Nothing

And repeat for any that you need.
blobbles78
Oh yeah, I would put that code on the "On Load" event of the form. You could have a button on the form called "Update Quicklook totals" which would link to the same code to update the totals.
omething like:
CODE
Private Sub Form_Load()
    UpdateQuickLooks
End Sub
Private Sub ButtonName_Click()
    UpdateQuickLooks
End Sub
Private Sub UpdateQuickLooks()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT ShippingID FROM Shipping")
    If rs.EOF <> True And rs.BOF <> True Then
        rs.MoveLast
        Me.lblCaptionControlName.Caption = rs.RecordCount
    End If
    
    Set rs = Nothing
End Sub
blobbles78
Actually, change it to this sorry!
!--c1-->
CODE
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT COUNT(ShippingID) FROM Shipping")
    If rs.EOF <> True And rs.BOF <> True Then
        Me.lblCaptionControlName.Caption = rs(1)
    End If
    
    Set rs = Nothing

That will save you returning a whole recordset of data! You could add a WHERE statement in your SQL if you want particular records...
blobbles78
One more correction, change from rs(1) to rs(0). Having a bad day sorry!
xfaith
Thanks
will give it a try in the morning.
GroverParkGeorge
The recordset approach would certainly work.
However, I might be inclined to go with a slightly different approach. You have FOUR categories to keep updated. Inasmuch as we can't see the tables where these values are stored it's not clear what calculations you really need, but we can definitely guess at what they might look like.
I am sort of a low-tech guy whenever possible. So I would just create four queries that count the items you want to count.
SELECT Count(*) AS ShipRideCount FROM tblShipRides
SELECT Count(*) As ShipInstallCount FROM tblShipInstalls
SELECT Count(*) As CertificationCount FROM tblCertifications
SELECT Sum(VolunteerWorkHours) As TotalVolunteerWork FROM tblVolunteerWork
In the four Text boxes on the form where you display these values, put an expression like this for the control's ControlSource
=NZ(DLookup("ShipRideCount", "qryShipRideCount"),0)
=NZ(DLookup("ShipInstallCount", "qryShipInstallCount"),0)
=NZ(DLookup("CertificationCount", "qryCertificationCount"),0)
=NZ(DLookup("TotalVolunteerWork", "qryTotalVolunteerWork"),0)
You could either use a manual process--the click event on a command button--or a timer event to requery these four controls by calling this sub:
CODE
Private Sub UpdateCounts()
Me.txtShipInstallCount.Requery
Me.txtCertificationCount.Requery
Me.txtTotalVolunteerWork.Requery
Me.txtShipRideCount.Requery
End Sub
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.