Finding existing records

[Pat Dools]

Hello,

In my clinical research application, I am attempting to have users fill in a
'Patient Info.' form with 8 total fields. The first 3 fields (ID, intials,
site) are stored in one table that can be added to. There are 3 more fields
on the 'Patient Info.' form that are populated by a combo box that looks up
to another table which contains a set of records related to the forms that
are filled out for these patients (Study Phase, Form Name, Form Page Number).
These 2 tables are the 'Patient Info.' form's Control Source. Finally,
there are 2 more fields on the 'Patient Info.' form that have to do with what
Day of Study they are in (Study Day, and Study Day Flag) that are populated
by combo boxes that have Value Lists supply the potential values and are not
bound to a specific table.

Once these fields are populated, the following code is executed behind a
Command Button to open the 'Patient Status' form:

Private Sub CommandGoToPatientStatus_Click()
On Error GoTo Err_CommandGoToPatientStatus_Click

If MsgBox("Click Yes for data entry or No for QC", vbYesNo) = vbYes Then
DoCmd.OpenForm "FCRFStatus", , , , acAdd
'Call SetAutoValues(Forms(Me!SelectForm))
Else
DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"
End If

Exit_CommandGoToPatientStatus_Click:
Exit Sub

Err_CommandGoToPatientStatus_Click:
MsgBox Err.Description
Resume Exit_CommandGoToPatientStatus_Click
End Sub

Then the 'Patient Status' form opens with this code in the form's 'On
Current' Event:

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If
End Sub

Where 'SetAutoValues(Me)' is:

Sub SetAutoValues(frm As Form)
On Error GoTo SetAutoValues_err

' Set Automatic Values in each form in series
' Add as many fields as necessary (make sure each field has the same
name on EVERY form)
With frm
!id = Forms!fEnterPatientInfo!id
!ptin = Forms!fEnterPatientInfo!ptin
!site = Forms!fEnterPatientInfo!site
!studyphase = Forms!fEnterPatientInfo!studyphase
!CRFname = Forms!fEnterPatientInfo!CRFname
!pagenum = Forms!fEnterPatientInfo!pagenum
!cyclenum = Forms!fEnterPatientInfo!CboCycle
!studyday = Forms!fEnterPatientInfo!CboStudyDay
!studydayflag = Forms!fEnterPatientInfo!CboStudyDayFlag


End With

Exit_SetAutoValues:
Exit Sub

SetAutoValues_err:
MsgBox Err.Description
Resume Next

End Sub

I want be able to go to either new records or find existing ones, but when
this set of code executes, it always opens the 'Patient Status' form and
auto-populates the 8 fields from the 'Patient Info.' form as if it were a new
record.

For the table underneath the 'Patient Status' form, 5 of the 8 fields
inherited from the 'Patient Info.' form combine to form that table's Primary
Key. Is this why I cannot be brought to a specific record-- or at least be
brought to the records where there is a match on the 'id' field (see code
from above):

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

Again, ideally, I would like to be brought to the 'Patient Statys' record
that matches the 8 fields I fill out on the 'Patient Info.' record, so the
data entry person can update that record as needed.

Thanks,

 

[Nikos Yannacopoulos]

Pat,

Is this why I cannot be brought to a specific record-- or at least be
brought to the records where there is a match on the 'id' field (see code
from above):

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

This is exactly where the problem is! You have enclosed the id control
reference in the quotes, so Access treats it as part of a text string
instead; as a consequence, it finds no matching record, so the form
opens to a new record, which in turn runs the SetAutoValues sub.

Change to:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

if the id field is numeric, or

DoCmd.OpenForm "FCRFStatus", , , "[id] = '" & _
Forms.fEnterPatientInfo.id & "'"

if id is text.

HTH,
Nikos

 

[Pat Dools]

Hi Nikos,

Unfortunately, it is still seeing the record as 'new'. The 'id' field is a
Number set to Long Integer type. Is there maybe a way to adjust the Form's
'On Current:' Event to make the record be seen as existing?

Private Sub Form_Current()
If Me.NewRecord Then
Call SetAutoValues(Me)
End If
End Sub

When it gets to the line:

If Me.NewRecord Then

it still moves to execute the 'SetAutoValues(Me)' sub.

Thanks for your help.
--
Pat Dools

Pat,

Is this why I cannot be brought to a specific record-- or at least be
brought to the records where there is a match on the 'id' field (see code
from above):

DoCmd.OpenForm "FCRFStatus", , , "[id] = Forms.fEnterPatientInfo.id"

This is exactly where the problem is! You have enclosed the id control
reference in the quotes, so Access treats it as part of a text string
instead; as a consequence, it finds no matching record, so the form
opens to a new record, which in turn runs the SetAutoValues sub.

Change to:

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id

if the id field is numeric, or

DoCmd.OpenForm "FCRFStatus", , , "[id] = '" & _
Forms.fEnterPatientInfo.id & "'"

if id is text.

HTH,
Nikos

 

[Nikos Yannacopoulos]

Pat,

Unfortunately, it is still seeing the record as 'new'.

Either the filter is not correct (so no records are reurned, therefore
the form opens in a new record), or the form's Data Entry property (tab
Data) is set to Yes, or there is a macro or some code behind the form's
Open or Load event which takes it to a new record. The first two are
more likely than the third one. As far as the first one goes, insert a
break point at the DoCmd.OpenReport line, and check that the id control
reference is correct, so it does read the current id value from the form.

The 'id' field is a
Number set to Long Integer type. Is there maybe a way to adjust the Form's
'On Current:' Event to make the record be seen as existing?

This is not the way to go. You need to identify and eliminate whatever's
going wrong out of the possibilities above.

Tip: comment out the filter, and open the form unfiltered; if you still
get no records, then either the form's recordource is wrong, or the Data
Entry setting is set to yes. If the record count is higher but you are
still at a new record, then there is a macro or code that takes you there.

HTH,
Nikos

 

[Pat Dools]

Hi Nikos,

Alright, I'm an idiot. I had that nagging feeling that it was something in
the Form properties, and I probably looked at that particular one about a
hundred times without it clicking that I was only permitting new records with
that form. Thank you!

Do you know what the proper syntax would be if I wanted to filter by another
field in addition to 'id'? I'm trying something like this, but its not
working and I'm not sure if you can filter via multiple fields like this.

DoCmd.OpenForm "FCRFStatus", , , "[id] = " & Forms.fEnterPatientInfo.id &
Forms.fEnterPatientInfo.pagenum

Thanks again!

 

[Nikos Yannacopoulos]

Pat,

Assuming the control on the form is also named pagenum, then it should be:

DoCmd.OpenForm "FCRFStatus",,, "[id] = " & Forms.fEnterPatientInfo.id _
& " And [pagenum] = " & Forms.fEnterPatientInfo.pagenum

This would work if you provided both the id AND pagenum; I usually check
all possible filters and construct the filter expression first, so I
only include those populated; this allows the filtering to work with any
combination of filters. To achieve this, your code could be something like:


Dim strWhere As String
If Not IsNull(Forms.fEnterPatientInfo.id) Then
strWhere = "[id] = " & Forms.fEnterPatientInfo.id"
End If
If Not IsNull(Forms.fEnterPatientInfo.pagenum) Then
If Not IsNull(strWhere) Then strWhere = strWhere & " AND "
strWhere = strWhere _
& "[pagenum] = " & Forms.fEnterPatientInfo.pagenum
End If
DoCmd.OpenForm "FCRFStatus", , , strWhere


HTH,
Nikos

 

[Pat Dools]

Excellent, Nikos. Thank you!

 

[Nikos Yannacopoulos]

You're welcome!