key matchin field error 1

uscctech · Nov 24, 2014

Not sure what I did but i am having a really wierd issue which i never had before.

I have a two tables: Customers and Workorders
Customers has a PK of "CustomerId"
Workorders has a PK of "WorkorderID"
I have a relationship from the two tables. CustomerID is related to a field labeled "CustomerID" in the Workorders table and it is a numberic field set to Indexed and Allow Duplicates since there can be multiple workorders tied to one customer.

I have two forms: Workorders by Customer and Workorders.
The Workorders by Cusotomer will search for a customer by their phone number i=and if it finds a match, lists that customer information along with a subform on the form showing all past workorders for this customer. I can then click on any of the past workordes and click a "View" button which will pull up that workodrder based on the CustomerID Match. I can also click "Add" button to start a new workorder.

Thw problem is now when I fill out the form for a new workoder and click Save, I now get the error "The Microsoft Databse Engine cannot find a record in the table 'Customers' with key matching field(s) 'Workorders.CustomerID'"

The customer table has the customer info in it. Obviously the Workorders table does not have the new record in it. For giggles I added an unbound text box on my Workorders form wich does show the correct CustomerID.

Code for the new workorder button:
Private Sub newworkorder_Click()
On Error GoTo Err_newworkorder_Click

Dim stLinkCriteria As String

stLinkCriteria = "[Workorders.customerID]=" & Me![customerID]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "WorkOrders", , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_newworkorder_Click:
Exit Sub

Err_newworkorder_Click:
MsgBox Err.Description
Resume Exit_newworkorder_Click

End Sub

Code for the View button:
Private Sub Workorders_Click()
On Error GoTo Err_Workorders_Click
If IsNull([customerID]) Then
MsgBox "Enter customer information before entering workorder."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "Workorders"
End If

Exit_Workorders_Click:
Exit Sub

Err_Workorders_Click:
MsgBox Err.Description
Resume Exit_Workorders_Click
End Sub

Im lost and hope someone can shed some light.

Thanks in advance everyone!

MajP · Nov 24, 2014

Code:

Private Sub newworkorder_Click()
On Error GoTo Err_newworkorder_Click
Dim stLinkCriteria As String
stLinkCriteria = "[Workorders.customerID]=" & Me![customerID]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "WorkOrders", , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Exit_newworkorder_Click:
Exit Sub
Err_newworkorder_Click:
MsgBox Err.Description
Resume Exit_newworkorder_Click
End Sub

There is nothing in that code that would save the foreign key in the customerID field. You open the work order form filtered to a customerID, but that will not make new records have that customer ID. You are confusing with how a subform works.
So open the form in addnew mode. No reason to return all the existing workorders just to go to a new record. Then you need to populate the customerID field.

Code:

Private Sub newworkorder_Click()
 On Error GoTo Err_newworkorder_Click
 'open in add mode
 DoCmd.OpenForm "WorkOrders", , , , acFormAdd
 'Set the value of the work orders field
 forms("WorkOrders").customerID = Me!customerID
 Exit_newworkorder_Click:
   Exit Sub
 Err_newworkorder_Click:
   MsgBox Err.Description
End Sub

uscctech · Nov 24, 2014

Thank you for the reply. So I replaced my code for yours and I still get the same error, as well as an additional one stating "Application-defined or Object-defined eror" after clicking the Newworkorder button. then afterwards, I still get the same error of which I was originaly getting regarding the matching key not found.

uscctech · Nov 24, 2014

Found it!! It was the Workorders form's property, under Data source. For some reason, it had the workorders.customerID field in there twice. Removed it and all set. Thanks fir thr assist and giving me a point in the right direction

MajP · Nov 25, 2014

One thing to keep in mind. This opens the form in add mode, and sets the value of the first record's customerID. This only does the first record. If you would like to open the form in add mode, but a allow the user to enter multiple work orders for the customer, instead of setting the value of the customer ID field you could instead set the default value of a control bound to the customerID field. That way the first and all subsequent records would default to the selected customer ID.

so instead of
forms("WorkOrders").customerID = Me!customerID
you could do
forms("WorkOrders").customerID.defaultvalue = Me!customerID

If customer ID is not numerice you would have to wrap it like
forms("WorkOrders").customerID = "'" & Me!customerID & "'"

Most likely your design would be to allow only a single workorder update, but that is how you would do it to allow multiple entries for a give customer.

uscctech · Nov 25, 2014

Thanks again for everything. Luckily for me I only need to allow one work order entry each time so once it's entered, you save it and must close the form to add another work order. Excuse I'm using it as a repair-based system.

Thanks again for all the shared expertise! Much much appreciated.

[pre][/pre]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

key matchin field error 1

uscctech

Technical User

MajP

Technical User

uscctech

Technical User

uscctech

Technical User

MajP

Technical User

uscctech

Technical User

Similar threads

Part and Inventory Search

Sponsor