Jumat, 31 Maret 2017

[belajar-access] File - Tata Tertib dan Aturan.txt

 


Tata tertib dan peraturan
Mailing List: belajar-access@yahoogroups.com

1. Mailing list ini membahas mengenai pemrograman Microsoft Access.
2. Tidak diperkenankan mem-posting topik yang tidak ada kaitannya sama sekali dengan pemrograman MS Access, peluang kerja atau tawaran kerja sama dengan keahlian di bidang MS Access, atau pengajaran/kursus MS Acces. Pelanggaran terhadap aturan ini akan di-ban dari keanggotaan milis ini.
3. Mohon berdiskusi dengan baik, dengan semangat membangun, demi kemajuan kita bersama. Hindarilah perbantahan (flame) yang bisa menjadi pertengkaran yang tidak perlu.
4 Hindari reply permintaan one-liner seperti 'saya minta juga dong', 'saya setuju', dan lain-lain yang tidak perlu.
5. Sedapat mungkin memberikan data-data yang lengkap dalam mengajukan suatu masalah untuk memudahkan rekan-rekan sesama member mengidentifikasi dan mencarikan solusi, termasuk memberikan subject yang sesuai dengan isi email, tidak dengan kata-kata seperti "tologing dong", "pusing...", "ada yang bisa bantu..", dll.

Moderator

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (121)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

SPAM IS PROHIBITED

.

__,_._,___

[AccessDevelopers] File - Monthly_Notices.txt

 


Monthly notices:

Hi Kids!:

Don't forget to check out our "Links" section at the website for helpful sites. Also take a peek at books that others have found worthwhile in our books database under the 'Database' link of the main AccessDevelopers page. Feel free to add any books or links that you have found useful.

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (153)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


Please zip all files prior to uploading to Files section.

.

__,_._,___

Re: [MS_AccessPros] Calculating total of all previous transactions

 

Sigurd-


Without knowing the structure of your tables, I would suggest that you could use a DSum in the query to get the total of all previous months.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Mar 31, 2017, at 10:10 PM, Sigurd Andersen sigurd@solbakkn.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have a set of queries that take records with employee hours worked,
calculate how much their time is charged for a client (using a separate
table of pay rates that have different rates for each employee for each
fiscal year), then totals all the payments for each month for each
project to which hours are charged.

Each project has a maximum amount that can be charged - so if there are
hours worked that would take the total (from the inception of the
project) over this amount, the project is not charged for anything over
the project maximum.

So I want to calculate, for each project's monthly total, the sum of all
the previous month's charges so I'll know whether that month went over
the maximum or not.

I have this calculation working in a monthly Invoice report (using, in a
footer, =Sum() of a value in the detail section and the starting and
ending dates for the invoice period). How can I do the equivalent in a
query that includes all projects for all the months in which work was done?

Each row of the desired query would have the total chargeable for a
given month and as a separate value the total chargeable for ALL
previous months, something like this:
Project ID, total for Jan 2017, total for all months before Jan 2017
Project ID, total for Feb 2017, total for all months before Feb 2017
Project ID, total for Mar 2017, total for all months before Mar 2017

I'd be grateful for any suggestions as to how to approach this.

--
Sigurd Andersen
St. Johnsbury, VT 05819



------------------------------------
Posted by: Sigurd Andersen <sigurd@solbakkn.com>
------------------------------------


------------------------------------

Yahoo Groups Links

<*> To visit your group on the web, go to:
   http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
   Individual Email | Traditional

<*> To change settings online go to:
   http://groups.yahoo.com/group/MS_Access_Professionals/join
   (Yahoo! ID required)

<*> To change settings via email:
   MS_Access_Professionals-digest@yahoogroups.com
   MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
   MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
   https://info.yahoo.com/legal/us/yahoo/utos/terms/


__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

[MS_AccessPros] Calculating total of all previous transactions

 

I have a set of queries that take records with employee hours worked,
calculate how much their time is charged for a client (using a separate
table of pay rates that have different rates for each employee for each
fiscal year), then totals all the payments for each month for each
project to which hours are charged.

Each project has a maximum amount that can be charged - so if there are
hours worked that would take the total (from the inception of the
project) over this amount, the project is not charged for anything over
the project maximum.

So I want to calculate, for each project's monthly total, the sum of all
the previous month's charges so I'll know whether that month went over
the maximum or not.

I have this calculation working in a monthly Invoice report (using, in a
footer, =Sum() of a value in the detail section and the starting and
ending dates for the invoice period). How can I do the equivalent in a
query that includes all projects for all the months in which work was done?

Each row of the desired query would have the total chargeable for a
given month and as a separate value the total chargeable for ALL
previous months, something like this:
Project ID, total for Jan 2017, total for all months before Jan 2017
Project ID, total for Feb 2017, total for all months before Feb 2017
Project ID, total for Mar 2017, total for all months before Mar 2017

I'd be grateful for any suggestions as to how to approach this.

--
Sigurd Andersen
St. Johnsbury, VT 05819

__._,_.___

Posted by: Sigurd Andersen <sigurd@solbakkn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Re: [MS_AccessPros] Command button greyed if no entry in related form/table

 

I don't understand.  What is the design of your tables?  Which table is being edited by F_EquipmentSpecs?  Why are you automatically adding rows to other tables when a new row is created in F_EquipmentSpecs?  If there should be no matching data, why create "null" rows?


Also, please include the entire message string when replying.  If you're replying on the web, be sure to click Show message history in the Reply box.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Mar 31, 2017, at 7:21 PM, access_kri@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John,

        Thanks for your guidance. However, I think I have not properly explained what I wanted to accomplish. I am trying to explaining the problem in a better way. 


My main form is a continuous form called F_EquipmentSpecs. On my main form, I have command buttons to open the related tables/forms and enter data. 


When I enter a new data in the main form and create a new equipment record, I update the equipment ID in all the remaining tables to automate the process. 


One of the related form is "F_Equipment History".  Initially, when I create the new equipment, the equipment History table gets new record added with equipment ID number but other fields such as Failure description etc. are blank.


Some equipment have no failures till date and hence the Failure description field in Equipment History table is null but due to my click action on main form, has a single recordset in equipment history table.  What I desire is that when the main form opens and I navigate to different equipment records, if the "Failure description" field is null or blank in Equipment History, the command button on the main "F_Equipment Specification" form to appear greyed and when clicked, it will display message "No Failure History available for this equipment" and when failure description field in related "Equipment History" table is not null or blank, the command button will appear normal. 


I need help to achieve the desired visual indication. Thanks in advance.




__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Re: [MS_AccessPros] Command button greyed if no entry in related form/table

 

John,

        Thanks for your guidance. However, I think I have not properly explained what I wanted to accomplish. I am trying to explaining the problem in a better way.


My main form is a continuous form called F_EquipmentSpecs. On my main form, I have command buttons to open the related tables/forms and enter data.


When I enter a new data in the main form and create a new equipment record, I update the equipment ID in all the remaining tables to automate the process.


One of the related form is "F_Equipment History".  Initially, when I create the new equipment, the equipment History table gets new record added with equipment ID number but other fields such as Failure description etc. are blank.


Some equipment have no failures till date and hence the Failure description field in Equipment History table is null but due to my click action on main form, has a single recordset in equipment history table.  What I desire is that when the main form opens and I navigate to different equipment records, if the "Failure description" field is null or blank in Equipment History, the command button on the main "F_Equipment Specification" form to appear greyed and when clicked, it will display message "No Failure History available for this equipment" and when failure description field in related "Equipment History" table is not null or blank, the command button will appear normal.


I need help to achieve the desired visual indication. Thanks in advance.

__._,_.___

Posted by: access_kri@yahoo.com
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___

Re: [MS_AccessPros] Error 3396

 

David-


The label has scope only within the local procedure, so yes, you can use a label like "DelErr" in multiple procedures.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Mar 31, 2017, at 4:26 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



John
Do statement labels have a scope of only the sub the label is contained within?   Can I use the same "DelErr" label in multiple subs within the same form  module? Or do I have to use unique labels?  Most examples I see have labels that include the sub name rather than the simpler label name.


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

You need to add On Error - something like this:

Private Sub cmdDeleteCorporation_Click()
    On Error GoTo Del_Err
    If Me.lstCorporations.ItemsSelected.Count > 0 Then
        Dim strSQL As String
        Dim strMessageBoxMessage As String
        
        strMessageBoxMessage = "Are you sure you want to delete this Corporation?" & vbNewLine & _
                                "Before you can delete a Corporation, all records associated with the corporation must be deleted." & vbNewLine & _
                                "Permission to do this is limited to certain personnel."
        
        If MsgBox(strMessageBoxMessage, vbQuestion + vbYesNo, "Delete Lots of Data?") = vbYes Then
            strSQL = "DELETE FROM lu_tblCorporations WHERE CorporationID =" & lstCorporations.Column(0)
            CurrentDb.Execute strSQL, dbFailOnError
            Me.lstCorporations.Requery
            Me.lstLocations.Requery
            Me.lstSites.Requery
         End If
     Else
            MsgBox "You must choose a Corporation to delete." _
        , vbInformation, gstrAppTitle
    End If

Done:
    Exit Sub

Del_Err:
    ' Try to analyze the error
    Select Case Err
        ' Cancel, invalid property, or field not found (Setting Dirty = False)
        Case errCancel, errCancel2, errPropNotFound, errInvalidPropSetting, errCantFindField
            Resume Save_Exit
        Case errDuplicate  ' Duplicate row - custom error message
            MsgBox "You're trying to add a record that already exists.  " & _
                "Enter a new Customer Name or click Cancel.", vbCritical, gstrAppTitle
        Case errInvalid, errInputMask
            ' Invalid data - custom error and log
            MsgBox "You have entered an invalid value. ", vbCritical, gstrAppTitle
            ErrorLog Me.Name & "_Save", Err, Error
        ' Field validation, Table validation, Custom Validation, End of Search, Spelling Check
        Case errValidation, errTableValidate, errCustomValidate, errSearchEnd, errSpellCheck
            ' Display the error
            ' All validation rules in the tables have custom error messages.
            MsgBox Error, vbCritical, gstrAppTitle
        Case errCascadeDelete 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue
        Case errCascadeDelete2 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue
        Case Else
            ' Dunno - log and let error display
            ' Save the error code values because ErrorLog may get additional errors
            lngErr = Err
            strError = Error
            ErrorLog Me.Name & "_Delete", lngErr, strError
            MsgBox "Error attempting to delete: " & lngErr & " " & strError & Chr$(13) & Chr$(10) & "Try again or click Cancel to close without saving.", vbExclamation, gstrAppTitle
    End Select
    Resume Done
End Sub


John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Mar 30, 2017, at 3:00 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:



I tried to reply via email but that seemingly didn't work.  If this shows up twice, I apologize.

You said, "It shouldn't do that if you have an error trap set".  That is probably the key, I doubt that I have the error trap set at all since I don't really know how to do that.  I am just realizing that I have added your Form_Error code to the form error event and I added your SAVE function code (which I called Function SaveRecord(). In my cmdSaveAndClose sub I call the SaveRecord function but in my cmdDelete buttons I have done nothing to call SaveRecord().  Instead, I have 

        CurrentDb.execute strSQL, dbFailOnError

It is the dbFail on error that is throwing the system error code.


How do I correct this so that it calls your SaveIt function and provides my custom error message?


I have the following code in the cmdDelete sub:



Private Sub cmdDeleteCorporation_Click()
    If Me.lstCorporations.ItemsSelected.Count > 0 Then
        Dim strSQL As String
        Dim strMessageBoxMessage As String
        
        strMessageBoxMessage = "Are you sure you want to delete this Corporation?" & vbNewLine & _
                                "Before you can delete a Corporation, all records associated with the corporation must be deleted." & vbNewLine & _
                                "Permission to do this is limited to certain personnel."
        
        If MsgBox(strMessageBoxMessage, vbQuestion + vbYesNo, "Delete Lots of Data?") = vbYes Then
            strSQL = "DELETE FROM lu_tblCorporations WHERE CorporationID =" & lstCorporations.Column(0)
            CurrentDb.Execute strSQL, dbFailOnError
            Me.lstCorporations.Requery
            Me.lstLocations.Requery
            Me.lstSites.Requery
         End If
     Else
            MsgBox "You must choose a Corporation to delete." _
        , vbInformation, gstrAppTitle
    End If
End Sub
 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

Where does the code stop?  It shouldn't do that if you have an error trap set.  Have you looked for entries in the ErrorLog table?

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)




On Mar 28, 2017, at 3:57 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Yes, the ELSE is:

Case Else
            ' Undefined error - log and let error display
            ErrorLog Me.Name & "_Error", DataErr, AccessError(DataErr)
            Response = acDataErrDisplay

What I see is the system error message 3200 instead and the code stops.
 


---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

Graham's extra info helps, but it doesn't explain why you're not seeing anything trapped in your error trap code.  You should be at least getting the generic message the code generates if it gets no match on the code.  You still have the Else MsgBox code in there, right?

John Viescas, author
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications

On Mar 28, 2017, at 05:05, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Graham, thank you for that explanation as it explains why I am now getting Error 3200 where I was getting 3396.  The reason is because I had gone back to my tables and noticed the inconsistency just as you described, and changed those tables such that Cascade Delete was not allowed on any of them.  I did not realize that is why my error number changed, but thanks to your explanation I now understand why.

I will have to review all of my tables against your advice, but I think earlier today I pretty much did just what you suggested.  I know I made all of my many to many tables cascade delete.  I made most other relationships to not allow cascade delete. 

However, I know I left my transaction type tables cascade delete.  These are tables where I have something like a report header table and a report details table.  I made these cascade delete.  In your work would you not do that?




---In MS_Access_Professionals@yahoogroups.com, <graham@...> wrote :

Hi David

I believe that error 3396 occurs only in the following unusual situation:

TableA is related to TableB with cascade deletes
TableB is related to TableC WITHOUT cascade deletes
You attempt to delete a record from TableA, where related child AND grandchild records exist in TableB and TableC respectively.

Normally, if it is appropriate to have cascading deletes from parent to child, then it would also be appropriate between child and grandchild.

 

Personally, I commonly use cascading deletes on junction tables which usually contain no data other than the representation of a many-to-many relationship.  Otherwise I would use them very rarely, and never (that I can think of) in the three-table scenario described above.

 

Best wishes,

Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] 
Sent: Tuesday, 28 March 2017 12:20
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3396

 
 

Yes, I am only seeing only the system message.  I am getting one of two errors - either 3200 or 3396.  I have attempted to trap both of them in the same manner you did with the other errors, but to no avail.  I added both to the globals module and added both to the CASE statement in the FORM ERROR and in the SAVE function.

 

Neither the 3200 nor the 3396 errors are being trapped.  I set error 3396 to errCascadeDelete and error 3200 to errCascadeDelete2.  I added them to the CASE statement as with your other cases.

 

Case errCascadeDelete 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue
        Case errCascadeDelete2 'attempting to delete a Parent record with children where cascade delete is not allowed
            MsgBox "You are attempting to delete a record which has related 'child' records.  " & _
                "You must first delete each 'child' record before this record may be deleted.", vbCritical, gstrAppTitle
                Response = acDataErrContinue

 

The error trapping code works fine for the case of duplicate record errors, so I think the sub and function are in general correct.



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

 

Form_Error or error trapping code in your Save code should see that error.  Are you only seeing the system message?  Maybe it's being triggered with a different error code.

 

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

(Paris, France)

 
 
 

On Mar 27, 2017, at 6:35 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

 



Thanks John, I will take the time to understand your code and use it as I am sure you have good reason to do it this way. I also never use cascade update because all my primary keys are autonumber.  However, I did think I should have some of my tables set for cascade delete.  You chose to never to allow that?  I can see where that is the safest choice, but sometimes would be very inconvenient.

 

I did try to add the error 3396 error handling to your form error code and to your error function code.  My custom message and error handling did not appear though.  Is this because this is a class of error which would not be caught by either of these two methods?



---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

 

I generally disallow Cascade Update and Cascade Delete.  I avoid that error in the Contacts app because a) all Primary Keys are AutoNumber, so cascade update will never happen, and b) my code checks before allowing a delete and issues a custom message if you would get a cascade error.  For example, here's the code in frmCompanies:

 

Private Sub Form_Delete(Cancel As Integer)

Dim db As DAO.Database, qd As DAO.QueryDef, rst As DAO.Recordset

Dim varRelate As Variant

    ' Check for related child rows

    ' Get a pointer to this database

    Set db = CurrentDb

    ' Open the test query

    Set qd = db.QueryDefs("qryCheckRelateCompany")

    ' Set the company parameter

    qd!CompanyNo = Me.CompanyID

    ' Open a recordset on the related rows

    Set rst = qd.OpenRecordset()

    ' If we got rows, then can't delete

    If Not rst.EOF Then

        varRelate = Null

        ' Loop to build the informative error message

        rst.MoveFirst

        Do Until rst.EOF

            ' Grab all the table names

            varRelate = (varRelate + ", ") & rst!TableName

            rst.MoveNext

        Loop

        MsgBox "You cannot delete this Company because you have related rows in " & _

            varRelate & ".  Delete these records first, and then delete the Company.", _

            vbOKOnly + vbCritical, gstrAppTitle

        ' close all objects

        rst.Close

        qd.Close

        Set rst = Nothing

        Set qd = Nothing

        Set db = Nothing

        ' Cancel the delete

        Cancel = True

        Exit Sub

    End If

    ' No related rows - clean up objects

    rs

(Message over 64 KB, truncated) 




__._,_.___

Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (17)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.


.

__,_._,___