Results 1 to 9 of 9
  1. #1
    jb08 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    5

    MS Access Forms data saved to MySQL Error?

    My company has implemented a new database system, I converted our old MDB file to a new MySQL Server.

    The Issue:



    On the form where we track work orders and status information we have a subform for notes, there is a relationship between the main forms table and the notes table, I linked the work order numbers together. My problem is when I create more than one new note access is defaulting to the last used Work Order number, anyone have any ideas?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You should be able to use Access' Referential Integrity rules to create new records in a subform that has a Main/Child link. I do not use intrinsic referential integrity features. I use VBA to enforce constraints and would apply the Main form's PK value to the new record created by the subform. Maybe a before update event in the subform object's VBA module and some code like

    Me.ForeignKey = Forms!MainFormName.PK

  3. #3
    jb08 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    5
    Thank you for the suggestions, I tried coding in an PK update and I even tried adding a Foreign Key to MySQL, to no avail, but I did get more information!

    Click image for larger version. 

Name:	Capture.PNG 
Views:	7 
Size:	6.2 KB 
ID:	16203
    Ok so this is what the sub-form looks like (typically hiding the wo# column), so my issue is for some reason the wo# field is defaulting to another Work Order each time and I can't see a rhyme or reason.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I will assume wo# is the FK and not the PK for your Notes table.

    Something seems to be populating the FK in your notes table. If you can not UPDATE the field via VBA then you will need to first locate the intrinsic rule that is populating the WO field.

    me![wo#] = -771008360

  5. #5
    jb08 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    5
    Yes that is correct, the PK is labeled as WorkOrderID and the FK is the wo# on the notes table

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by jb08 View Post
    Yes that is correct, the PK is labeled as WorkOrderID and the FK is the wo# on the notes table
    Something seems to be populating the FK in your notes table. If you can not UPDATE the field via VBA then you will need to first locate the intrinsic rule that is populating the WO field.

    Try assigning a literal value to the field using VBA in the Subform's Before Update Event.
    me![wo#] = -77222222

  7. #7
    jb08 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    5
    So I attempted to assign a literal value to the wo# cell like you suggested, and I used the following code subbing both before update and after update:

    Private Sub DID_AfterUpdate(Cancel As Integer)
    Me![wo#] = -77222222
    End If
    End Sub

    Click image for larger version. 

Name:	Capture.PNG 
Views:	5 
Size:	10.2 KB 
ID:	16217
    As soon as I hit save record the wo# was changed to -771008360, so I am beginning to wonder if this is a MySQL Issue?

    This was my original code:
    Private Sub DID_BeforeUpdate(Cancel As Integer)
    If IsNull(Form_WO_Notes!DID) Then
    Form_WO_Notes!DID = Form_order_status_entry_tab_form![DID]
    End If
    End Sub

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    If the following is the actual code then it is not firing. This subprocedure will cause an exception. There is a missing If for the End If.
    Private Sub DID_AfterUpdate(Cancel As Integer)
    Me![wo#] = -77222222
    End If
    End Sub

    Also, the record below the row with -71008360 has a value that seems to be similar to an Autonumber. The last row has an asterisk that would indicate this record has not been saved yet. There must be something telling the wo# field to populate with a default value. Intrinsic feature or otherwise, I believe this is the culprit.

  9. #9
    jb08 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2014
    Posts
    5
    So then the correct coding would be to remember the "End If"?

    Private Sub DID_AfterUpdate(Cancel As Integer)
    Me![wo#] = -77222222
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-16-2013, 11:41 AM
  2. MySQL Syntax Error from Query String in VBA
    By raynman1972 in forum Programming
    Replies: 2
    Last Post: 09-17-2012, 05:59 PM
  3. Replies: 1
    Last Post: 12-21-2011, 02:11 PM
  4. Replies: 6
    Last Post: 07-21-2010, 11:47 AM
  5. Access data page connection to MySQL
    By dkperez in forum Access
    Replies: 1
    Last Post: 09-29-2009, 06:02 AM

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