OpenForm arguments problem

[Stuart Jack via AccessMonster.com]

Hi

I am having a problem with OpenForm arguments, basically I want to dbl click
on a listbox 'frmAuditTrail' and use the value in Column(2) of the listbox
for the OpenForm argument, in order to filter the form to the ID of the
patient I have dbl clicked on.

frmPatient is the form I want to open, filtered to the correct patient, I am
using the following code to do this in the dbl click event of frmAuditTrail --


Private Sub lstView2_DblClick(Cancel As Integer)

Dim strValue As String

strValue = Me.lstView2.Column(2)

DoCmd.OpenForm "frmPatient", , , "[PtID] =" & strValue

End Sub

The form opens but contains no records, in other words seems to be filtered
on nothing, on checking the code in debugger window I can see that the
correct PtID is being picked up by strValue, but for some reason is not being
passed through.

Any ideas anyone?

Thanks

Stuart

 

[Rick Brandt]

Hi

I am having a problem with OpenForm arguments, basically I want to
dbl click on a listbox 'frmAuditTrail' and use the value in Column(2)
of the listbox for the OpenForm argument, in order to filter the form
to the ID of the patient I have dbl clicked on.

First get the terminology correct. One uses the WhereCondition argument to
filter the form being opened not the OpenArgs argument. The OpenArgs
argument is just for passing some text to the form being opened so that it
can use it in some code. By itself the OpenArgs argument does nothing upon
opening the form.

frmPatient is the form I want to open, filtered to the correct
patient, I am using the following code to do this in the dbl click
event of frmAuditTrail --

Private Sub lstView2_DblClick(Cancel As Integer)

Dim strValue As String

strValue = Me.lstView2.Column(2)

DoCmd.OpenForm "frmPatient", , , "[PtID] =" & strValue

End Sub

The form opens but contains no records, in other words seems to be
filtered on nothing, on checking the code in debugger window I can
see that the correct PtID is being picked up by strValue, but for
some reason is not being passed through.

Any ideas anyone?

That would suggest that there are zero records satisfying the WhereCondition
argument you are passing. You do know that Column(2) is actually the third
column of your ListBox right? That is one common mistake. I would put a
break on the DoCmd.line and then hover the mouse over the strValue when the
code break occurs. That will show you the value contained in the variable
so you can confirm what you are getting.

Also, your code is correct for a numeric value of PtID, but not for a Text
value. Is your PtID field numeric? If not then you need to put quotes
around it.

 

[Stuart Jack via AccessMonster.com]

Hi Rick

Thanks for reply.

I do want the form to be filtered to the patient selected in the the listbox,
so I guess I need to use the WHERE argument. I understand that the Column
property of the listbox starts at 0 so as it is the 3rd column that contains
the ID I am using Coulumn(2). I have checked the code using a breack and it
is picking up the correct field alright and the correct ID number that I dbl
click on, it is not though passing it through to the form to filter against.

I did make a typo on my earlier post in that initially I was trying to work
this on PtID which was the wrong field, it is actually a field called [CHI]
which is a text field, as such I am using the following format:

DoCmd.OpenForm "frmPatient", , "[CHI] ='" & strValue & "'"

This does not cause any errors and opens the form at the first record, not at
the correct record, have I got the position of the WHERE wrong or something
else??

many thanks

Stuart

Hi

I am having a problem with OpenForm arguments, basically I want to
dbl click on a listbox 'frmAuditTrail' and use the value in Column(2)
of the listbox for the OpenForm argument, in order to filter the form
to the ID of the patient I have dbl clicked on.

First get the terminology correct. One uses the WhereCondition argument to
filter the form being opened not the OpenArgs argument. The OpenArgs
argument is just for passing some text to the form being opened so that it
can use it in some code. By itself the OpenArgs argument does nothing upon
opening the form.

frmPatient is the form I want to open, filtered to the correct
patient, I am using the following code to do this in the dbl click

[quoted text clipped - 16 lines]

Any ideas anyone?

That would suggest that there are zero records satisfying the WhereCondition
argument you are passing. You do know that Column(2) is actually the third
column of your ListBox right? That is one common mistake. I would put a
break on the DoCmd.line and then hover the mouse over the strValue when the
code break occurs. That will show you the value contained in the variable
so you can confirm what you are getting.

Also, your code is correct for a numeric value of PtID, but not for a Text
value. Is your PtID field numeric? If not then you need to put quotes
around it.

 

[Rick Brandt]

Hi Rick

Thanks for reply.

I do want the form to be filtered to the patient selected in the the
listbox, so I guess I need to use the WHERE argument. I understand
that the Column property of the listbox starts at 0 so as it is the
3rd column that contains the ID I am using Coulumn(2). I have checked
the code using a breack and it is picking up the correct field
alright and the correct ID number that I dbl click on, it is not
though passing it through to the form to filter against.

I did make a typo on my earlier post in that initially I was trying
to work this on PtID which was the wrong field, it is actually a
field called [CHI] which is a text field, as such I am using the
following format:

DoCmd.OpenForm "frmPatient", , "[CHI] ='" & strValue & "'"

This does not cause any errors and opens the form at the first
record, not at the correct record, have I got the position of the
WHERE wrong or something else??

Yeah, you're missing a comma (should be three). As you have it now you are
using the Filter argument which I must admit that I have NEVER used, but
which I believe would normally be the name of a table or query to use as a
filter.

If you just add a third comma you should be good to go.

 

[Stuart Jack via AccessMonster.com]

Hi Rick

Thanks,

I have it working now.

Much appreciated.

Stuart

Hi Rick

[quoted text clipped - 18 lines]

record, not at the correct record, have I got the position of the
WHERE wrong or something else??

Yeah, you're missing a comma (should be three). As you have it now you are
using the Filter argument which I must admit that I have NEVER used, but
which I believe would normally be the name of a table or query to use as a
filter.

If you just add a third comma you should be good to go.