INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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!

RE: key matchin field error

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 




RE: key matchin field error

(OP)
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.

RE: key matchin field error

(OP)
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

RE: key matchin field error

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.

RE: key matchin field error

(OP)
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. :)
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close