I have a "demographics" form named "DynamicDisplay" which lists patients and their patient numbers. I have a click event set up when the user clicks a patient number, the details form should open to that patient #. My code is as follows:

Public Sub Patient_Num_Click()

DoCmd.OpenForm "DysplasiaTracking", acNormal, Forms![DysplasiaTracking]![Patient_Num] = Me![Patient_Num]

End Sub

The code,"DoCmd.OpenForm "DysplasiaTracking", acNormal" opens the detail form ("DysplasiaTracking") perfectly.

When I add the "Forms![DysplasiaTracking]![Patient_Num] = Me![Patient_Num]" filter, access gives me "run time error 2450 - Access can't find the form 'DysplasiaTracking' referred to in a macro expression or VB code."

Any thoughts?

Thanks,
Mark the newbie

Access is a little funky about taking values in command lines. Also, when the DoCmd line is run, the form that you are setting the parameter to isn't opened yet, so is not visible to Access. I would do the following.

Dim iPatNum As Integer 'I assume, may be string
Dim sLink As String

iPatNum = Me![Patient_Num]
sLink = "[Patient_Num] = " & iPatNum

DoCmd.OpenForm "DysplasiaTracking", acNormal,, sLink

sLink should be in the WHERE clause area, not the filter area, of the DoCmd line.

Did that help?







mmcdonal

Actually, do this line:

iPatNum = Me.Patient_Num

mmcdonal

That did not work. Patient_Num is a string so I changed the variable declaration. The form does open without errors but not to the correct record. This might seem like a silly question but the filter "sLink" does not indicate the field to filter on in the "DysplasiaTracking" form, so could this be why I don't get the patient_Num that I clicked?

Public Sub Patient_Num_Click()

Dim iPatNum As String
Dim sLink As String

iPatNum = Me![Patient_Num]
sLink = iPatNum

DoCmd.OpenForm "DysplasiaTracking", acNormal, , sLink

End Sub

I thought you were filtering by Patient_Num since that is what you were trying to do with the other code.

If the variable is a string, then do this:

Dim sPatNum As String
Dim sLink As String

iPatNum = Me![Patient_Num]
sLink = "[Patient_Num] = '" & iPatNum & "'"


Did that help?



mmcdonal

Here's the current code:
Public Sub Patient_Num_Click()

Dim iPatNum As String
Dim sLink As String

iPatNum = Me![Patient_Num]
sLink = "[Patient_Num] = '" & iPatNum & "'"


DoCmd.OpenForm "DysplasiaTracking", acNormal, , sLink

End Sub

When the "DysplasiaTracking" form opens, there is no record displayed.
When I look at the properties for the form, I see the filter condition reads, [Patient_Num] = '98989898'
Allow Filters = YES

When I filter the form manually and then check the filter, it looks like this...
((Demographics.[Patient #]="333333333"))
So that's how I need to send from the code.
SLink =((Demographics.[Patient #]="333333333"))

So how do I change, sLink = "[Patient_Num] = '" & iPatNum & "'" to
((Demographics.[Patient #]="333333333"))

Thanks. I think we are almost there!

OK. Made some progress. The below code does filter the 2nd form opened but the form opens to a blank record and the bottom of the form indicates that you are looking at record 1 of 1 (filtered). I have to manually click the "remove filter" button and click the "filter by selection" button to make the record appear. There should be a way to do this in code.

Public Sub Patient_Num_Click()

Dim iPatNum As String
Dim sLink As String

iPatNum = Me![Patient_Num]
sLink = "((Demographics.[Patient #] = """ & iPatNum & """))"

OK, I have it now. I added some code for the form load on the 2nd form and this works.......

Private Sub Form_Load()

DoCmd.ShowAllRecords
Me.FilterOn = True

End Sub

Mike, thanks for helping me out on this!

Mark