MySQL backend, primary keys and #Deleted

[nhorton79]

Hopefully someone can shed some light on this issue I am having.
I don’t know whether this is one issue or two…

The gist of it is…
1. When I create a new record in my form it doesn’t automatically show the primary key (JobID)
2. When I try to assign a client to the record, it comes up with #Deleted in a number of fields.

Note: I have included some image attachments showing screenshots (some sensitive info blurred out)

I originally created my database in Access, front-end (FE) and back-end (BE) as one accdb file. Once I had everything pretty much working as I wanted it, I migrated the data across to a MySQL Server BE.

I have created linked tables in the database to the new MySQL tables using an ODBC connector and everything seems like it is working okay-ish.

My issue I am having is on my job form which displays all the relevant info about a job. If I open an existing job from another form it is okay (see pic 5) , code is:

[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Private Sub JobList_DblClick(Cancel As Integer)[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    DoCmd.OpenForm "frmJob", , , "JobID=" & Me.JobList.Column(2)[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]

Note: This is opened from another form (frmJobStatusList) where there is a list of all Jobs (JobList) and shows their current status (Quote, Accepted, Production, Completed…etc). There is both a “Select” button or you can double-click the job in the list. Code is shown for double-clicking in list but using the Select button is the same but assigned to the onclick event of the Select button.

However, when I try to create a new Job by clicking the “New Job” button it doesn’t populate the Primary Key (JobID) anymore in the Job Form (frmJob) - (see pic 1 for old database and pic 2 for MySQL), the code I am using to create a new job is assigned to a button onclick event as:

[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Private Sub btnNewJob_Click()[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    DoCmd.OpenForm "frmJob", , , , acFormAdd[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]

I thought that maybe once I opened the form I could straight away save the record using:

[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]DoCmd.RunCommand acCmdSaveRecord[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]

Which would hopefully then apply and show the Primary key (JobID)
However, as JobClient_FK is a required field for the record, so all I get is:
Runtime error 3101, The Microsoft Office Access database engine cannot find a record in the table ‘tblClient’ with the key matching field(s) ‘JobClient_FK’
This is self-explanatory as a Client hasn’t been assigned upon opening the form, therefore the database cannot save the record.


The normal workflow for this form would then be that once you open the form:

It applies the default JobStatus_FK (which is 1 “Quote”)

The user then selects a Client for the Job (see pic 3 - clicks “Select Company” button which opens a new form frmJobClientSelect, which lists all our clients which they can double-click list or select from list and click button “Select”), the code for the double-click event is:

[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Private Sub lstClients_DblClick(Cancel As Integer)[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3] [/SIZE][/FONT]
 [SIZE=3][FONT=Calibri]Dim iResponse As Integer[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3] [/SIZE][/FONT]
 [SIZE=3][FONT=Calibri]iResponse = MsgBox("Are you sure you want to select this client?", vbYesNo, "Continue")[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3] [/SIZE][/FONT]
 [SIZE=3][FONT=Calibri]If Response = vbYes Then[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    DoCmd.OpenForm "frmJob" ‘open the jobform[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Forms!frmJob.JobClient = Me.txtClientSelect.Value ‘pass the selected client ID to the job form[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Forms!frmJob.cboJobContact.Value = Null ‘clear out any previously selected contact[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Forms!frmJob.Refresh ‘refresh the job form[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    Forms!frmJob.cboJobContact.Requery ‘requery the Contact combobox to show contacts associated with newly selected client[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]    DoCmd.Close acForm, "frmJobClientSelect" ‘close the client select form[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]Else[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3] [/SIZE][/FONT]
 [SIZE=3][FONT=Calibri]End If[/FONT][/SIZE]
 [FONT=Calibri][SIZE=3] [/SIZE][/FONT]
 [SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE]
 [SIZE=3][FONT=Calibri]

Once the client has been selected and it returns to the new form a number of fields are shown as #Deleted (see pic 4)
When I check the actual table the record has been saved, and if I then close the form and reopen the Job it shows the correct JobID and Client etc.


Is there a known issue of this with a MySQL back-end?
The record source is based on a SELECT query that pulls in all fields in tblJob, plus the ClientAccType_FK from tblClient (the FK of clients Account Type) and the AccType from tblAccType (the actual text related to their Account Type) – would having the record source being just the tblJob and doing DLookups for the other two fields make a difference?
Do I need to change my workflow? – maybe have it so that the client is selected first before opening the Job Form..

Help!

 

[Ranman256]

You don't get an auto key until you save the record.
Even if its 1 field, you must save.
But you need enough dAta fields to recall the key.

 

[nhorton79]

Hi Ranman,

Yeah, cheers, I figured that was the case...with it not showing a PK in the JobID field.

Do you have any idea though why when I add the customer to the job I then get #Deleted and it doesn't show the record (even though the record gets created)?

 

[nhorton79]

OK.

When I typed out my problem in my original post, I hit the nail on the head when I mentioned about changing the workflow of my forms....its amazing how you can sometimes solve a problem just by explaining what the problem is!

Originally I had the JobForm open as blank and then ask for the JobClient (selected via another form).

I have now changed the order of those two forms, so when you click "New Job", it opens the a form to select the client prior to opening the Job form.

That way (as JobClient is a required field for the frmJob) I am able to save the record when the form opens. The new code being...

 Private Sub lstClients_DblClick(Cancel As Integer)
  
 Dim Response As Integer
  
 Response = MsgBox("Are you sure you want to select this client?", vbYesNo, "Continue")
  
 If Response = vbYes Then
    DoCmd.OpenForm "frmJob", , , , acFormAdd   'open the job form
    Forms!frmJob.JobClient = Me.txtClientSelect.Value 'pass the selected client ID to the job form
    Forms!frmJob.cboStatus.Value = 1 'set the default job status to Quote
    DoCmd.RunCommand acCmdSaveRecord 'save the record
    DoCmd.Close acForm, "frmPreJobClientSelect" 'close the client select form
    Forms!frmJob.SetFocus 'damn you Access why did you hide my form behind the Dashboard
Else
  
 End If
  
 End Sub

And..yeeehaaa!! It works.

Take that MySQL!!! You thought you could destroy me, by whittling me down with late nights and mental gymnastics..HAHAHA! I will never be beaten!!!!
(Note to MySQL: I'm joking, I know you'll get me back soon...please be gentle...I'm just celebrating small successes where I find them).