Page 3 of 3 FirstFirst 123
Results 31 to 40 of 40
  1. #31
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    IT seems like the error self resolved. I adopted the 2nd fix to resolve the issue.

    Quote Originally Posted by chewjekhui View Post
    Thanks! I fixed the errors above.

    1st fix: But I cant fix the Me.ID. They keep giving me back Method or data member not found. Even when I changed it to AuditTrailID, same error appears.

    2nd fix: I changed it to Me!ID but now I get a Runtime error Type 13: type mismatched, Pointing at the line >>> Call AuditChanges("AuditTrailID", "Delete", Me.Name). Changed AuditTrailID to ID and to RecordID also returns the same error. I am lost already

    Private Sub DeleteRecord_Click()


    Dim rst As DAO.Recordset
    Dim strCopy As String, strSQL As String
    Dim answer As Integer


    If IsNull(Me.Client) Then
    MsgBox "No Record to delete"


    Else
    answer = MsgBox("Are you sure you want to delete this record)", vbYesNo + vbCritical + vbDefaultButton2, "Delete Confirmation")
    If answer = vbYes Then
    Call AuditChanges("AuditTrailID", "Delete", Me.Name)
    strCopy = "Insert Into Deletedcustomer select SiteDetails.* from SiteDetails where (SiteDetails.ID = " & Me.ID & ");"


    strSQL = "delete * from SiteDetails where ID= " & Me.ID


    DoCmd.SetWarnings False
    DoCmd.RunSQL strCopy
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True



    DoCmd.GoToRecord , , acNewRec


    End If
    End If
    End Sub

  2. #32
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    But I cant fix the Me.ID.
    When working in forms, I always use the control name, not the bound field name. The field name is "ID", but the control name is "Text196".

    This (circled in red)
    Click image for larger version. 

Name:	Presentation1.jpg 
Views:	17 
Size:	23.9 KB 
ID:	27417
    really concerns me. I think you will have problems later on...

  3. #33
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    When working in forms, I always use the control name, not the bound field name. The field name is "ID", but the control name is "Text196".

    This (circled in red)
    Click image for larger version. 

Name:	Presentation1.jpg 
Views:	17 
Size:	23.9 KB 
ID:	27417
    really concerns me. I think you will have problems later on...
    I put it that way because There would be many clients with many individual sites. I want to keep the list of customers table clean. otherwise it would be messy with client names, sites and countries.

  4. #34
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, but I think you are going pull your hair out later on.

    This is how I would create the tables/relationships. (PK fields are now Autonumber, not Text, FK fields are Long Integer)
    Click image for larger version. 

Name:	relationship.jpg 
Views:	14 
Size:	20.9 KB 
ID:	27429


    And I would have a lot more look up TABLES (example for Unit, UnitType, Service, Engine, Gearbox, ...)

  5. #35
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    OK, but I think you are going pull your hair out later on.

    This is how I would create the tables/relationships. (PK fields are now Autonumber, not Text, FK fields are Long Integer)
    Click image for larger version. 

Name:	relationship.jpg 
Views:	14 
Size:	20.9 KB 
ID:	27429


    And I would have a lot more look up TABLES (example for Unit, UnitType, Service, Engine, Gearbox, ...)
    I was thinking that maybe they might have an problem later as they feed data into the database. Because they would need to remember CustSiteID number. Because when they are feeding the data into Site Details, the sites have names. So actually the build up is from "SiteDetails" >>> "CustSites" >>>> "CustomerList"

    Would it be possible if the reference data for "CustSites" be from the "SiteName" field instead?

  6. #36
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Because they would need to remember CustSiteID number.
    The PK fields are Autonumbers; Autonumbers are NOT intended to be viewed/used by end users of the application.
    PK fields should NEVER be displayed.


    Would it be possible if the reference data for "CustSites" be from the "SiteName" field instead?
    Yes, you could... but WHY?? IMHO, it is better to use autonumbers than text fields. A PK field requires the data for that field to be unique.

    Read this site
    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp
    Pay special attention to this paragraph:Use a Meaningless Primary Key
    Read this site many times.

    Also see
    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Microsoft Access Performance Tips to Speed up Your Access Databases
    http://www.fmsinc.com/microsoftaccess/Performance.html
    Again, pay special attention to this paragraph: Primary Key Should be One Field and Numeric
    (I've probably read these 3 sites more than 50 times)


    Lets say you have a form bound to a query that is based on the table "SiteDetails".
    The field "CustSitesID_FK", a Long Integer, is bound to a combo box on a form.
    The combo box "cboCustSite", has a Row Source set to
    Code:
    SELECT DISTINCT CustSites.CustSiteID_PK, CustSites.SiteName
    FROM CustSites;
    Set the combo box properties:
    Bound Column = 1
    Column count = 2
    Column Widths = 0

    This displays the "SiteName", but stores the "CustSiteID_PK" value! The user does not have to remember numbers!

  7. #37
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Hmmm ok. I try to feed data in first and see how it goes! Thanks for your help!

  8. #38
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My point is that the relationships with tables "ThailandPTTSites" and "MalaysiaEMEPMI" is the wrong way to go.

    You can design the structures/relationships any way you want - it is your dB. But what do you do when you try to add "Brazil - Petrobas"???
    Do you add another table???
    With 2 or more tables , how do you select a site???

    Just trying to point out potential problems....


    Good luck with your project........

  9. #39
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    My point is that the relationships with tables "ThailandPTTSites" and "MalaysiaEMEPMI" is the wrong way to go.

    You can design the structures/relationships any way you want - it is your dB. But what do you do when you try to add "Brazil - Petrobas"???
    Do you add another table???
    With 2 or more tables , how do you select a site???

    Just trying to point out potential problems....


    Good luck with your project........

    no, i decided to follow your method. But I am feeding some data in to see if it is ok.

  10. #40
    chewjekhui is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    22
    Quote Originally Posted by ssanfu View Post
    My point is that the relationships with tables "ThailandPTTSites" and "MalaysiaEMEPMI" is the wrong way to go.

    You can design the structures/relationships any way you want - it is your dB. But what do you do when you try to add "Brazil - Petrobas"???
    Do you add another table???
    With 2 or more tables , how do you select a site???

    Just trying to point out potential problems....


    Good luck with your project........
    Hey just asking why I have issues with the Site Config Report (by Parts) (Previously named as MainPartsDetails form) with the field (Gearbox_model), (Driven_Model) and (Combustor_Type). They keep saying an syntext error but I copied the field heading into the tblMainParts. I even deleted the field and added it back to try but still an issue. I have PMed you the latest copy of the file.

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-01-2015, 02:22 PM
  2. Replies: 1
    Last Post: 09-30-2015, 12:58 PM
  3. Replies: 7
    Last Post: 03-30-2015, 10:04 AM
  4. Replies: 2
    Last Post: 01-06-2014, 04:22 PM
  5. Display table data depending on selected data
    By swavemeisterg in forum Forms
    Replies: 7
    Last Post: 07-30-2013, 03:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums