Hi there,
Could you explain what isn't working? What is the code currently doing for you after you type an MRN in? Are there any errors?
Just for clarification, is the MRN field bound or unbound?
If you're getting the duplicate key error its because access is trying to save your current record with the newly entered MRN you are trying to search by which already exists.
The logic behind the code I wrote was as follows:
a) check to see if the value exists
b) if it exists undo changes to the current form
c) move to the form with the entered value
Hi Jared,
When I type in an MRN and hit enter, the focus changes to the next control, but nothing happens. I receive no error messages. The MRN is bound to a table where it is the primary key and no duplicate entries are allowed.
I tried using DLookup, with an unbound textbox with a query as its control source. That worked to auto populate the controls in the parent form, but then I couldn't add data to or even move to any of the subforms (which also auto populated). When I did so, I received the "The changes you requested to the table were not successful because they would creat duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." This is my code:
Private Sub txtfindMRN_AfterUpdate()
MRN = DLookup("MRN", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
FirstName = DLookup("FirstName", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
MI = DLookup("MI", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
LastName = DLookup("LastName", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
mdfname = DLookup("mdfname", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
mdlname = DLookup("mdlname", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
MediCal = DLookup("MediCal", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
EDCdate = DLookup("EDCdate", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
DeliverAtVMC = DLookup("DeliverAtVMC", "SweetSuccessDataQuery", "MRN = '" & Me.txtfindMRN & "'")
AdmitDate = DLookup("AdmitDate", "SweetSuccessData", "MRN = '" & Me.txtfindMRN & "'")
End Sub
I'm sure it's archaic and inefficient, but unfortunately, it was the only way I could get the form to autopopulate at all. Any advice on this would help greatly!
Your logic makes sense to me, but how do I get the computer to allow me to enter in new records and edit existing ones?
Thanks.
Linda