key matchin field error
key matchin field error
(OP)
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!
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!
Talk To Other Members
RE: key matchin field error
CODE -->
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 SubRE: key matchin field error
RE: key matchin field error
RE: key matchin field error
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.
RE: key matchin field error
Thanks again for all the shared expertise! Much much appreciated. :)