Search in a subform 4

[ruthcali]

hello,

i have 2 tables: one called tblProject and one called tblBuildings.

The tblProject has a primary key called projectID and other fields such as IssuedDate, Status, etc.

The tblBuildings has 2 fields: projectId and BuildName.

There is a one to many relationship with the 2 tables based on ProjectID.

In my form, i have the fields from tblProject and i have the subform listing all the BuildNames for each tblProject.

My question is, if i click on the BuildNames field in the subform and then click on the Binoculars (Find) to search for another building, it doesn't search the entire tblProject.

For example, in my form, i have 300 records. i'm looking at the record for the projectID called Proj123 which is record 1 of 300.

That projectId contains 3 buildings: Bldg1, Bldg2 and Bldg3.

Bldg3 is used quite often, so I want to see the other projects that have gone to Bldg3. So, i click inside the subform, then click the binoculars and type Bldg3 as my search term. but Access returns no matches.

Can i search a field in the subform and have the main table display the results?

Thanks

 

[grtammi]

I'm not too sure if you can go backwards with the search ... the reason Access isn't coming up with any records in the subform is because it's pulling data for only the current projectID that you're on ...

You could, however, create work around for this problem by utilising a list box and either an input box or dialog form, your choice.

If this sounds like it may be of assistance to you, feel free to leave a post and I'll get back to you.

Greg



Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[willir]

ruthcali

Greg (grtammi) has made a good point about going the "wrong way" in a one-to-many relationship, and I agree on his solution.

Except I prefer to use an unbound combo box(es) on the main form to accomplish this. For the row source, define your query to suite your needs. You can even edit the relationship between tblProject and tblBuilding to retrieve just the matches, all of the projects (based on your descriuption, going the other way, all of the buildings does not make sense with your design.) Then add your me.filter = "xxx" and me.filteron = true to the after the update event for the combo box, or alternative query method. Hint: It may be useful to use DISTINCTROW or DISTINCT in the SELECT clause depending on your needs.

So you can have one unbound combo box to search tblProject to select specific projects. Another combo box that selects the building and uses a join between the tables.

This leads me to my next comment...
I keep thinking there may be something wrong with your design. Between buildings and projects, I keep seeing many-to-many, but you have commented that you have a one-to-many -- one project includes many buildings. You have your Project table which looks fine. But the Building table bothers me. I may be wrong, but it seems that this table ties a project to the building or buildings and it is not a table that defines the building. If so, you may run into data entry errors down the road - eg. BLDG 25, Building 26, Shed 26, etc.

Which suggests to me that you have to type the name of the building everytime. You may want to create a building table -- you will be able to better track projects by building.

Bear with me -- since a project can include many buildings, and presumably, a building can have many projects, your "transaction" table will need to include the primary keys for both project and building.

In review...
tblProject: PK = projectID
tblBuilding: PK = buildingID or building (unique name)
tblProjectProfile: PK = projectID + buildingID (or building name)

I talk too much.
Good luck
Richard

Good luck.
Richard

 

[grtammi]

Good point Richard about being able to use a combo box as well - but it all comes down to SQL in the end.

I prefer the list box approach because I can show the user more than one record (if it exists) that meets their search criteria ... but I normally try to have a lot of room on my forms. The combo box solution will work exceptionally well if you have limited space on your form ... just an extra click for the user after that.



Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[willir]

Greg

You are right about the list box allowing the selection of more than one variable. A great tidbit for things such as on-line surveys -- "choose all that apply". I just have a bias to combo boxes and the smaller footprint. As long as the tool does the job is the main thing. In re-reading my comments, I can see how I may have come across wrong.

I will give you a star for pointing out that list boxes can be used to capture / depict more than one selection.

Take care
Richard

 

[ruthcali]

Greg and Richard,
Thank both of you so much for writing. Please let me apologize for not writing back sooner. i unexpectally didn't have access to a computer all week. Please forgive me.

Richard,
You made a good point about the set up of my tables and you are right, they are not set up optimally.

There can only be one project and that project uses multiple buildings. But those buildings are also used by other projects. So your way of creating a third table would be optimal. But, i can't modify the tables since i import these tables from another database using ODBC.

But i'm impressed with your observation!

Greg and Richard,
Your suggestions of combo or list boxes are good. i guess i was trying to use the built in Find function to make it easy.

but the combo/list box idea suggests that the user has to search for the buildings he wants to see. Example: Use the list or combo box to scroll down and find Build5, then show me all the records that go to Build5.

i was thinking of having something on the form where, if the user is already looking at the record for Project33 which goes to Build5, that the user could click a button and see all the other Projects that go to Build5 without having to search for it using a combo or list box.

Then a more advanced function would be if there are 2 or more buildings on the form, to show all projects that go to those same buildings.

Thanks!

 

[willir]

ruthcali

I achieve what you are tyring to do with a command button or buttons in a continuous form. The button opens another form using the link criteria to point to the specific record. (The wizard does a great job for this)

Example: You select a project, and the continuous form displays all the buildings. Click on button next to the project to open up a form depicting the detail on the project. Click on the button next to the building and a form opens up for the building showing all the projects for the building.

Hint: Access creates a huge button with a picture or text. I shrink the button to the height of the field, and make it square, and then use either a text character "?", or a small icon.

Richard

 

[grtammi]

Richard:

Good point on using the wizard ... give ya a star!

Now, since I'm programming biased ... I shall offer a SQL solution using my now infamous list box

The pseudo code for this is as follows:

When the button (that you're going to put on your form) is clicked:

1) Check what building(s) are associated with the project.
2) Build appropriate SQL statement using following:
SELECT all buildings from the Building Table Where the project ID is equivalent to the current project
3) Set the recordsource of this listbox to the SQL statement and requery it.

So, using your syntax, a button called btnGo, and a list box called lstBuilding, the code is as follows:

Private Sub btnGo_OnClick()

   ' declare variables
   Dim sSQL As String   ' SQL variable
   Dim lngID As Long    ' used to retrieve current ProjectID
   
   ' end declarations - code start

   ' retrieve Project ID

   lngID = Me!ProjectID   ' or the name of your ProjectID text box

   ' set up SQL statement

   sSQL = "SELECT BuildName FROM tblBuildings WHERE ProjectID=" & lngID

   ' set list box source and requery

   With lstBuilding
      .RowSourceType = "Table/Query"
      .RowSource = sSQL
      .Requery
   End With

   ' clear variables to release memory
   sSQL = ""

End Sub

Hope this helps ... I'm still pondering the multiple buildings question.

Greg


Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[ruthcali]

Thanks to both of you for writing and for your good advice. you both get stars!

I have a single form displaying data about the project such as the project name, dates, remarks, etc. The form also contains a subform that displays the building info. (Usually a project has 2 or 3 buildings).

So i don't need to pull the building data for each project since the subform does that automatically.

I wanted a pop up search form where the user could choose 2 methods to search:
--find info for all projects going to a building. Ex: on the form is a project that goes to bldg2, bldg3 and bldg4. The user wants to see everything that has ever gone to bldg4.

--find info for all projects going to all buildings. Using the example from above: the user wants to see only projects that have gone to ALL 3 of the buildings: bldg2,bldg3 and bldg4.

Thanks!

 

[willir]

ruthcali

If the previous methods is not what you are looking for, and you mentioned "pop-up", perhaps this may help. Although I have not used this approach before, it should work...

On your main form, you have a hidden text field that stores the "where" clause.

The popup window is activated (command button); the user chooses their query. This query in the form of a where clause to stored in the hidden field on the main form. When the focus returns back to the main form, the where clause is activated using the "filteron = " your where clause and "filteron = true"

Richard