Querying a Subform

[jonillson]

Hi,

I have a form, card_file, with an embedded subform, card_access_point_file.

I would like to create a combobox on the main form to query the card_file on values in a field on the card_access_point _file. Specifically, I would like the combobox to query the card_access_point_file.accesspoint_entry field.

I have tried to do this as follows, where SAPE is the name of the cbo box, but I'm doing something--possibly many things--wrong.

Any help you can provide is greatly appreciated.

John

Private Sub SAPE_Button_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "card_file"
stLinkCriteria = "Forms!card_file!card_access_point_file.Form.acces spoint_entry LIKE" & "'*'&" & "'" & Forms!card_file!SAPE & "'" & "&'*'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_SAPE_Button_Click:
Exit Sub

Err_SAPE_Button_Click:
MsgBox Err.Description
Resume Exit_SAPE_Button_Click

End Sub

[weekend00]

I am not sure the other part of syntax of form's filter, but the follow part need to be fix:
Original:
stLinkCriteria = "Forms!card_file!card_access_point_file.Form.a cces spoint_entry LIKE" & "'*'&" & "'" & Forms!card_file!SAPE & "'" & "&'*'"
Revised:
stLinkCriteria = "Forms!card_file!card_access_point_file.Form.a cces spoint_entry LIKE" & "'*" & Forms!card_file!SAPE & "*'"

[jonillson]

I am not sure the other part of syntax of form's filter, but the follow part need to be fix:
Original:
stLinkCriteria = "Forms!card_file!card_access_point_file.Form.a cces spoint_entry LIKE" & "'*'&" & "'" & Forms!card_file!SAPE & "'" & "&'*'"
Revised:
stLinkCriteria = "Forms!card_file!card_access_point_file.Form.a cces spoint_entry LIKE" & "'*" & Forms!card_file!SAPE & "*'"

i have corrected this, but i'm still not having any luck.

[weekend00]

So I don't think you can set a filter this way.

are you trying to filter the sub form by the value of SAPE? if you are, you need to do follow things:
1 set the "control source" of SAPE to a field of the source table of the main form;
2 set up the link of main form and subform.

Oops! No, you the link's relative can not be "like".
you need to filter the subform by SAPE_afterUpdate event.

in the after update event of SAPE, use following codes(not tested):

private sub SAPE_afterUpdate()
     card_access_point_file.filter = "access_point_entry LIKE '" & SAPE & "'"
     card_access_point_file.filterOn = true
end sub

[jonillson]

Perhaps I can provide some more information.

There are three tables involved:

card_file
access_point_file
card_access_point_file

The card_file contains three fields:

cf_ID
cf_heading
cf_entry

The access_point_file contains a few more than three, but here are the relevant fields:

apf_ID
apf_entry

The card_access_point_file is an intermediary table designed to handle the many-to-many relationships between the card_file and the access_point_file. It has the following fields:

capf_ID
cf_ID
apf_ID

The database is designed to serve as a small catalog of paper index cards. An index cards look as follows:

Heading: Clubs

Entry: G. Morton Wolfe, president. Courier Express. Jan. 13, 1945, p.16; Jan.26 sec.4 p.9.

I am using the the card_access_point_file to handle the multiple access points associated with each card. In the example above, the access points might be something like:

1. Wolfe, G. Morton
2. Clubs
3. Courier Express

On my main form, card_file, I would like to query access points and retrieve all entries in the card_file that are related to the access point queried. So, e.g., I'd like to query "Wolfe, G. Morton" and retrieve all entries in the card_file table that have this access point. I would also like the other access points to display.

Perhaps this is not possible, but it seems like pretty basic functionality.

Thanks in advance for your help.

John

[weekend00]

would please describe what is showing in the main form, the subform and in SAPE when the form open?

Then, how will you query an access point? type then name in text box or select from SAPE or subform? and then what do you want to show in the main form and sub form and SAPE?

[jonillson]

I've attached a screen.

The second cbo box, which is presently not labeled, is the one I'm working on. It has this row source:

SELECT access_point_file.accesspoint_entry FROM access_point_file ORDER BY access_point_file.accesspoint_entry;

Which generates a list of access points currently in use in the DB.

(Note: I simplified my description of the tables/names involved in my description above. Here are the actual names:

There are three tables involved:

card_file
access_point_file
card_access_point_file

The card_file contains three fields:

cf_ID (Key)
card_heading
card_entry

The access_point_file contains a few more than three, but here are the relevant fields:

accesspoint_entry (Key)

The card_access_point_file is an intermediary table designed to handle the many-to-many relationships between the card_file and the access_point_file. It has the following fields:

capf_ID (Key)
cf_ID
accesspoint_entry

)

[weekend00]

I am lost on what you are try to do. you mentioned you want to query an access_point, then what do you want to show in the main form and sum form.

do you want to query like: select an access point in the not_labled_cbo_box, then show all card information in the sub form and show cards in SAPE?

[jonillson]

I am lost on what you are try to do. you mentioned you want to query an access_point, then what do you want to show in the main form and sum form.

do you want to query like: select an access point in the not_labled_cbo_box, then show all card information in the sub form and show cards in SAPE?

SAPE is the name of the unlabeled cbo_box on the main form. I want to select a value from that box and retrieve all records in the card_file table (main form) that have that associated access_point heading. I want everything you see displayed to be displayed.

E.g., In this screen, I have selected "architects" from the list. I want to be able to click the find button and retrieve all of the records in the card_file that have this access point, "architects" in the subform.[IMG][/IMG]

[weekend00]

I am sorry but I can't see any picture because of the policy in my computer.

So, you can put script in the after_update event of the cbo box or behind the "find" button as you said.

try following code in the event:
card_access_point_file.filter = "accesspoint_entry LIKE '" & SAPE & "'"
card_access_point_file.filteron =true