"On click" problem

[Caroline]

Sorry to repost, but I haven’t got a final answer on this one.

I have 2 forms which I want to link using the "on click" event in the
properties.
One form is tabular with all the case numbers, and the other is a detail
of each case (columnar). I want to click a case in the tabular form and get
to the details of that case number.
I have [case_number]=[Forms]![frmBeingClicked]![case_number] in the where
condition of the “on click†macro.
When I click on the case number in the tabular form, it opens the right
“details†form but the form opens blank or with only one record (it has 47
records when I open it independently of the link).

Please help. Thank you,
Caroline

 

[BruceM]

This link may give you some ideas about how to handle the situation I think
you are describing:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

 

[Caroline]

Thanks. This is not exactly what I was looking for. Can you help with the "on
click" macro?
Thanks,
Caroline

This link may give you some ideas about how to handle the situation I think
you are describing:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Sorry to repost, but I haven't got a final answer on this one.

I have 2 forms which I want to link using the "on click" event in the
properties.
One form is tabular with all the case numbers, and the other is a detail
of each case (columnar). I want to click a case in the tabular form and
get
to the details of that case number.
I have [case_number]=[Forms]![frmBeingClicked]![case_number] in the where
condition of the "on click" macro.
When I click on the case number in the tabular form, it opens the right
"details" form but the form opens blank or with only one record (it has 47
records when I open it independently of the link).

Please help. Thank you,
Caroline

 

[BruceM]

You say that "this" is not exactly what you are looking for, but there are
about seven examples on the linked page. Are any of them close? Neither I
nor anybody else can help achieve an undefined end result.

Here is an example of a continuous subform within a continuous subform.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=262

It uses a single line of code in the Current event of the right-hand
(detail) subform. Note that the query for the detail subform uses the
left-hand (Main) subform as the criteria.

I am guessing somewhat as to what you need. You would do better to describe
the end result you seek rather than the method. I do not use macros except
in limited situations, so cannot advise you if there is a specific need to
use a macro rather than VBA. I prefer the flexibiltiy of VBA, so I would
use an Event Procedure rather than a macro.

Thanks. This is not exactly what I was looking for. Can you help with the
"on
click" macro?
Thanks,
Caroline

This link may give you some ideas about how to handle the situation I
think
you are describing:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Sorry to repost, but I haven't got a final answer on this one.

I have 2 forms which I want to link using the "on click" event in the
properties.
One form is tabular with all the case numbers, and the other is a
detail
of each case (columnar). I want to click a case in the tabular form and
get
to the details of that case number.
I have [case_number]=[Forms]![frmBeingClicked]![case_number] in the
where
condition of the "on click" macro.
When I click on the case number in the tabular form, it opens the right
"details" form but the form opens blank or with only one record (it has
47
records when I open it independently of the link).

Please help. Thank you,
Caroline

 

[Caroline]

I would like my "on click" macro to work and not open the form to a blank
record.
I know this should work since I also saw this expression in the Access help:
[case_number]=[Forms]![frmBeingClicked]![case_number] but for some reason it
does not. I just want to know why it's not working for me so that it can be
fixed.
I tested it in a very simple database and got the same result: the form
opens blank instead of opening to the corresponding case number.
Thanks, C

You say that "this" is not exactly what you are looking for, but there are
about seven examples on the linked page. Are any of them close? Neither I
nor anybody else can help achieve an undefined end result.

Here is an example of a continuous subform within a continuous subform.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=262

It uses a single line of code in the Current event of the right-hand
(detail) subform. Note that the query for the detail subform uses the
left-hand (Main) subform as the criteria.

I am guessing somewhat as to what you need. You would do better to describe
the end result you seek rather than the method. I do not use macros except
in limited situations, so cannot advise you if there is a specific need to
use a macro rather than VBA. I prefer the flexibiltiy of VBA, so I would
use an Event Procedure rather than a macro.

Thanks. This is not exactly what I was looking for. Can you help with the
"on
click" macro?
Thanks,
Caroline

This link may give you some ideas about how to handle the situation I
think
you are describing:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Sorry to repost, but I haven't got a final answer on this one.

I have 2 forms which I want to link using the "on click" event in the
properties.
One form is tabular with all the case numbers, and the other is a
detail
of each case (columnar). I want to click a case in the tabular form and
get
to the details of that case number.
I have [case_number]=[Forms]![frmBeingClicked]![case_number] in the
where
condition of the "on click" macro.
When I click on the case number in the tabular form, it opens the right
"details" form but the form opens blank or with only one record (it has
47
records when I open it independently of the link).

Please help. Thank you,
Caroline

 

[BruceM]

Please see Ken's reply, in whihc he addresses the syntax of the Where
condition. I do not know what you are trying to accomplish if none of the
links I sent come close to showing what you are trying to do, so I cannot be
of assistance.

I would like my "on click" macro to work and not open the form to a blank
record.
I know this should work since I also saw this expression in the Access
help:
[case_number]=[Forms]![frmBeingClicked]![case_number] but for some reason
it
does not. I just want to know why it's not working for me so that it can
be
fixed.
I tested it in a very simple database and got the same result: the form
opens blank instead of opening to the corresponding case number.
Thanks, C

You say that "this" is not exactly what you are looking for, but there
are
about seven examples on the linked page. Are any of them close? Neither
I
nor anybody else can help achieve an undefined end result.

Here is an example of a continuous subform within a continuous subform.
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=262

It uses a single line of code in the Current event of the right-hand
(detail) subform. Note that the query for the detail subform uses the
left-hand (Main) subform as the criteria.

I am guessing somewhat as to what you need. You would do better to
describe
the end result you seek rather than the method. I do not use macros
except
in limited situations, so cannot advise you if there is a specific need
to
use a macro rather than VBA. I prefer the flexibiltiy of VBA, so I would
use an Event Procedure rather than a macro.

Thanks. This is not exactly what I was looking for. Can you help with
the
"on
click" macro?
Thanks,
Caroline

:

This link may give you some ideas about how to handle the situation I
think
you are describing:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

Sorry to repost, but I haven't got a final answer on this one.

I have 2 forms which I want to link using the "on click" event in
the
properties.
One form is tabular with all the case numbers, and the other is a
detail
of each case (columnar). I want to click a case in the tabular form
and
get
to the details of that case number.
I have [case_number]=[Forms]![frmBeingClicked]![case_number] in the
where
condition of the "on click" macro.
When I click on the case number in the tabular form, it opens the
right
"details" form but the form opens blank or with only one record (it
has
47
records when I open it independently of the link).

Please help. Thank you,
Caroline

 

[Caroline]

Thanks for taking the time Ken.
I guess I don't need all the records to open, just the one, my bad on this
one.

I did try your expression (text is the data type) but the form still opens
to the wrong record -and incidentally it now opens with all records, ready to
be navigated. It opens to the 1st record of 41... I made sure it was not open
to begin with.

Don't know if it matters, but I'm using Access 2007.
Thanks,
C

Caroline:

The expression for the macro's WhereCondition should be:

"[case_number]= " & [Forms]![frmBeingClicked]![case_number]

The above assumes that case_number is a number data type, not text. If the
latter the expression would be:

"[case_number]= """ & [Forms]![frmBeingClicked]![case_number] & """"

This concatenates the *value* of the control into the expression rather than
referencing the control. Bear in mind that if the other form is already open
it will not move to another record if you click in the frmBeingClicked form
again while on a different record. I'd recommend that you set the WindowMode
to Dialog in the macro to prevent this as this forces you to close the other
form before returning to frmBeingClicked.

However, I'm puzzled by your original post. As you want the other form to
show the details of the current record it will open filtered to just the one
record, which I assume is what you want, not to contain all 47 records. If
you want the second form to open at the current record, but still be able to
navigate to other records in it then you'd have to adopt a different approach.
This would normally be done in code rather than a macro; the code would be
like this:

Dim rst As Object
Dim frm as Form
Dim ctrl As Control

DoCmd.OpenForm "YourSecondForm"

Set frm = Forms("YourSecondForm")
Set rst = frm.Recordset.Clone
Set ctrl = Me.case_number

With rst
.FindFirst "case_number = " & ctrl
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With

Again the above assumes that case_number is a number data type, not text. If
the latter it would be amended as follows:

.FindFirst "case_number = """ & ctrl & """"

If you are unfamiliar with entering code in event procedures, to do this
select the control in form design view and open its properties sheet if its
not already open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.

Ken Sheridan
Stafford, England

Thanks. This is not exactly what I was looking for. Can you help with the "on
click" macro?
Thanks,
Caroline

This link may give you some ideas about how to handle the situation I think
you are describing:

[quoted text clipped - 16 lines]

Please help. Thank you,
Caroline

 

[Caroline]

Thank you both for your help anyways. Can't find the rabbit!!

Caroline:

It sounds like the second form isn't being filtered at all. I'm not really
able to offer any explanation I'm afraid. As far as I can see the second
expression I gave you should work, assuming:

1. Both forms are based on the same table or query.

2. Both include the case_number field in their underlying recordset and the
field contains unique values in that recordset.

3. The case_number field is a text data type.

There will undoubtedly be a rational explanation, and therefore a solution,
but on the basis of the information we currently have, its difficult to see
what it might be. More details about the forms' underlying table or query
might help the dog see the rabbit.

Ken Sheridan
Stafford, England

Thanks for taking the time Ken.
I guess I don't need all the records to open, just the one, my bad on this
one.

I did try your expression (text is the data type) but the form still opens
to the wrong record -and incidentally it now opens with all records, ready to
be navigated. It opens to the 1st record of 41... I made sure it was not open
to begin with.

Don't know if it matters, but I'm using Access 2007.
Thanks,
C

Caroline:

[quoted text clipped - 65 lines]

Please help. Thank you,
Caroline

 

[BruceM]

For future reference it is best to post the table structure, and a
description in real-world terms of what you need to accomplish. For table
structure something like:

tblCase
CaseNumber (primary key)
CaseDate
Other fields specific to the Case

tblCaseDetail
Detail_ID (primary key)
CaseNumber (linking field to tblCase)
Description

Then say which form is based on which table, assuming there are two separate
tables.

The point is that you can see your database, but we cannot. You need to
provide details.

Assuming you have something like the table structure I posted as an example,
one other thing is to be sure CaseNumber is text in both tables.

Thank you both for your help anyways. Can't find the rabbit!!

Caroline:

It sounds like the second form isn't being filtered at all. I'm not
really
able to offer any explanation I'm afraid. As far as I can see the second
expression I gave you should work, assuming:

1. Both forms are based on the same table or query.

2. Both include the case_number field in their underlying recordset and
the
field contains unique values in that recordset.

3. The case_number field is a text data type.

There will undoubtedly be a rational explanation, and therefore a
solution,
but on the basis of the information we currently have, its difficult to
see
what it might be. More details about the forms' underlying table or
query
might help the dog see the rabbit.

Ken Sheridan
Stafford, England

Thanks for taking the time Ken.
I guess I don't need all the records to open, just the one, my bad on
this
one.

I did try your expression (text is the data type) but the form still
opens
to the wrong record -and incidentally it now opens with all records,
ready to
be navigated. It opens to the 1st record of 41... I made sure it was not
open
to begin with.

Don't know if it matters, but I'm using Access 2007.
Thanks,
C

Caroline:

[quoted text clipped - 65 lines]
Please help. Thank you,
Caroline