Message about duplicate entries and not being able to edit a record

[Barth Wilsey]

I have 2 tables
tblPatients (key field medrec)
tblQuestions (key field date and medrec)
the relationship is one to many (tblPatients one, tblQuestions many based
upon date of completion)

I am trying to capture input by using a series of forms that ask simple
questions for the patients to complete
I keep getting a message that the data could not be saved because it would
cause duplicate entries in tblQuestions

I have used the following means to try to have the forms work on the same
record (suggested in a previous message)
1) I set a parameter in the query feeding the second form that
would refer to the ID field of the record on the first form to limit the
query
to only that record.
2) I supplied a filter criteria (using the criteria in #1) in the
DoCmd.OpenForm call to filter the form when it opens.
3) I had the second form check the ID field of the record in the
first form as it opens and use the Recordset object of the second form and
do a FindFirst to go to the correct record.

Below is sample code for the third option used in one of my forms:

Public Function WorstPain()
On Error GoTo Err_WorstPain
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblQuestions", dbOpendynset)

rs.MoveFirst
Do Until rs.EOF
With rs
.FindFirst "[MedRec] = """ & Me.txtMedRec & """"
.Edit
!WorstPainPastWeek = Me.optFrame2
.Update
End With
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
Exit_WorstPain:
Exit Function
Err_WorstPain:
MsgBox Err.Description
Resume Exit_WorstPain
End Function

Can you please tell me how to .findfirst for two key fields (date and
medrec)?
Any thoughts as to why the first two methods enumerated above do not work?

thanks in advance, Barth

 

[Guest]

As far as I know, findfirst doesn't allow for two search
criteria. What you could do though is something like
inserting the following

if (![date] = ....) then
.Edit
....
.Update
else
.FindNext
endif

Hopy this helps

Chris

 

[Graham Mandeno]

Hi Barth

To FindFirst on two fields, use the AND operator to concatenate the
criteria:

rs.FindFirst "[MedRec] = """ & Me.txtMedRec _
& """ AND CompletionDate=" & Format(Me.txtDate, "\#mm/dd/yyyy\#")

Note the formatting of the date value - mm/dd/yyyy and enclosed in # signs.

Have you considered using a subform for your tblQuestions records? If you
correctly link a subform to the main form with LinkMasterFields and
LinkChildFields then Access will look after this filtering for you, and will
even fill the link fields with the correct values when you add new records.

Be aware that the code you have below will probably result in an endless
loop, because you are doing a .FindFirst instde the loop which will
continually set the Recordset position back to the first matching record.
Hence you will never get the .EOF condition.