Is the following possible in Access?

[bo8482]

Hi

I've just built my first Access database for a work project and now I have my sights set on something higher!

Before I begin building the dbase though I wanted to know if the following was possible, and a brief indicator of what it would require, i.e. VBA code etc and level of difficulty.

First a brief overview of what I'm trying to achieve. I have about 100 clients who every month need their management accounts manually checked by an admin assistant who reviews their turnover, net worth etc. If they drop below a certain level, then their financial covenant is breached. So I just want a form that is filled in for their turnover etc for that month and then behind the scenes Access can then compare this to the clients standing data to see whether a covenant has been breached. I then want managers to be able to generate a report that details all clients, and which covenants are breached. So thats the dream....!

To that extent, is it possible....

a) To produce a report based on information entered into a form. eg. In a form, simply type in a client number, and then hit a command button that generates a report compiled for that one client from several different tables. I can't see how this is done yet - just a brief pointer in the right direction would be nice so I can then research how its done.

b) If I set user level security, is it possible to have say several fields in a table, but only one that can be edited by a manager (i.e. date, client etc filled in by operator but then approved can only be ticked by a Manager?)

c) Schedule Access to email a report once a month to managers that will list all outstanding data for a particular client for that month?

d) Draw data from a table so that appears in a form once one field has been filled in. I.e. Once client number is typed in, it will show all the standing data for that client from the table client details, and then the rest of the form can be completed which will fill in another table, monthly covenants. I have experimented with dlookup - I'm not sure whether this is the correct route, or whether subforms is a better bet. Any ideas?

Many thanks for any help you can give me, it would be a big help.

Brian

 

[HiTechCoach]

So you are using dbase (click)as a back end?

Which version? Maybe this: dBASE PLUS 2.61.5

 

[bo8482]

Sorry, I should clarify - I'm using MS Access...I just abbreviated database to dbase. Do you know if those requirements are possible in MS Access? Thanks

 

[HiTechCoach]

Sorry, I should clarify - I'm using MS Access...I just abbreviated database to dbase. Do you know if those requirements are possible in MS Access? Thanks

Ahhhh .... I normally see DB used as the abbreviation for database.

Everything that you want to do should be possible. I have done a variation fo all of them.


You should only use a form to edit data in a table.

This example is a custom security model, but it will give you an idea of how to implement security:
Security Demo

Hope this gets you started ...

 

[wiklendt]

Hi Brian,

a) In a form, simply type in a client number, and then hit a command button that generates a report compiled for that one client from several different tables.

yes, this is very possible. read up on dropdown combos (so you can easily select form all the clients in your database, rather than having to painstakingly type them in - saves time and typos), then you'll need a tiny bit of VBA to send that ClientID to the report as a filter. in the past i have used:

Private Sub cmdMassagePreview_Click()
On Error GoTo Err_cmdMassagePreview_Click

    Dim strDocName As String
    Dim strCriteria As String
    Dim strMassageID As String
    Dim Msg As String
    
    If IsNull(cmbSelectPrintSession) Or cmbSelectPrintSession.Value = "" Then
        MsgBox "Please select a date from the Massage drop-down." _
             , vbInformation, "The PED - Select a massage session"
        Exit Sub
    Else
    
    strDocName = "rptHorseMassage"
    strCriteria = "[MassageID]= " & Me!cmbSelectPrintSession
    
    DoCmd.OpenReport strDocName, acPreview, , strCriteria, acDialog

    End If

Exit_cmdMassagePreview_Click:
    Exit Sub

Err_cmdMassagePreview_Click:
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, vbOKOnly, "The PED", Err.HelpFile, Err.HelpContext
    Resume Exit_cmdMassagePreview_Click

End Sub

c) Schedule Access to email a report once a month to managers that will list all outstanding data for a particular client for that month?

sure, you can create a query which filters for clients (which can use the dropdown combo i mentioned in point (a)) and also for the last month... how to do it automatically and to send via email is a little more complicated but i know i've seen people do it here on the forums.

d) Draw data from a table so that appears in a form once one field has been filled in.

here is where you can again utilise that client dropdown (you can, of course, use a listbox instead if you'd like all the clients displayed a little more obviously - i like to attach a search textbox which filters the listbox as you type...). associated with this dropdown you can attach some code which updates the data in your bound form:

Private Sub cboSpecimens_AfterUpdate()
        
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SpecimenID] = " & Str(Nz(Me![cboSpecimens], 1))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

the one client combo box can be used to help you in all these instances that i've addressed.

i'm afraid the other questions are beyond my expertise to answer.

 

[bo8482]

Thanks a lot for your detailed answer!

I have absolutley no VBA skills whatsoever, I think I may have to buy a book that can help me out with that. I'm starting the database today so will be sure to refer to this post lots...

 

[gemma-the-husky]

b) If I set user level security, is it possible to have say several fields in a table, but only one that can be edited by a manager (i.e. date, client etc filled in by operator but then approved can only be ticked by a Manager?)

this is the hard one

generally you permit users to access tables or not - if they can access a given table, then they can access all fields in the table

SO - instead of letting users see the tables, you give them forms to interract with those tables, and now you can give certain users access to certain forms only, and achieve what you want

BUT the users can still get at the the underlying tables, and destroy all this, so now you need a way of preventing a determined user from getting at the tables directly, while still giving them decent levels of functionality

so look at security threads here, but finding the right solution for you is a complex issue.

 

[speakers_86]

b) If I set user level security, is it possible to have say several fields in a table, but only one that can be edited by a manager (i.e. date, client etc filled in by operator but then approved can only be ticked by a Manager?)

I have done this in access 07. 07 does not have ULS, so I had to create my own login form. The on click event of the submit button in the login form has a case select. If the login info is validated, then one of the following will be opened, depending on the users permisions:
frmHome
frmHomeOperator
frmHomeManager
frmHomeUser

All four are the same, except for the amount of sensitive info that is shown.

 

[wiklendt]

Thanks a lot for your detailed answer!

I have absolutley no VBA skills whatsoever, I think I may have to buy a book that can help me out with that. I'm starting the database today so will be sure to refer to this post lots...

a year ago i too had no VBA skills whatsoever. jumping in with both feet is how i did it, but your approach of reading first sounds like a better option (and on top of that i ended up having to re-structure one of my tables and all the coding for it in my first database b/c i had no idea what normalised data was!)

you'll pick up on it quickly, especially with the enthusiams you show

 

[bo8482]

Dear all

Thanks for your input. I've got the fundamentals of my database in place but I think now I have to get down and dirty with VBA. I've ordered a book which should hopefully arrive tomorrow (VBA for Dummies!) so that should help me interpret the useful code posted earlier in this thread and in other parts of this forum. I have no doubt I'll be coming back asking for some more help soon! Thanks again