search main form using the values in the subform

[haritbhasin]

Hi,

I am trying to fetch the values in the main form using the values that are selected in the subform using the combo boxes. In the subform there are three combo boxes. I am already able to search anything in the main form using a free text box field.

What exactly i want to achieve is:-

1). I want to have three combo boxes on the expertlist page and i want to filter the records using the 3 drop downs and once the data is filtered then use the free text box field and and again filter out the records.

I am attaching the database file as an attachment. Please help me with this

 

[boblarson]

You seem to have things backwards.

You don't limit the main form based on the subform. You limit the subform based on what is in the MAIN form. I can't open the database at work because I don't have 2010 here. But that is basically the thing. Typically the main form is set to the table which has the one side of the one-to-many relationship and the subform has the table which stores the many side of the one-to-many and they are linked by the Master/Child properties on the subform control (control on the parent form which houses/displays the subform on the parent form).

 

[KiEESH]

I need to do the opposite: Have a main form search an editable subform (with table as source).

I have code that uses a form/subform combo to do a a search & view, respectively.
However, I need to view & edit after the search is performed.
As such, I'd prefer to have a table be the subform's record source, but I'm not sure how to alter the code to search the table.

I ultimately need to update the table using partial/whole user search input.

I'd consider an update query, if that's easier, but still unsure of how to incorporate.

Code in question highlighted.

Any ideas would be appreciated.


Option Compare Database
Option Explicit

Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me!cmbZONE = ""
Me!txtBLDG_NUM = ""
Me!cmbCOMPASS_PT = ""
Me!txtSTREET = ""
Me!cmbATERY = ""


End Sub

Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub
Private Sub btnSearch_Click()

' Update the record source
Me!frmsub_EditBldg.Form.RecordSource = "SELECT * FROM qry_AREA_GROWTH2 " & BuildFilter

' Requery the subform
Me!frmsub_EditBldg.Form.Requery


End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter


' Check for ZONE
If Me!cmbZONE > "" Then
varWhere = varWhere & "[ZONE] = " & Me.cmbZONE & " AND "
End If

' Check for LIKE BLDG_NUM
If Me!txtBLDG_NUM > "" Then
varWhere = varWhere & "[BLDG_NUM] LIKE """ & Me.txtBLDG_NUM & "*"" AND "
End If

' Check for COMPASS_PT
If Me!cmbCOMPASS_PT > "" Then
varWhere = varWhere & "[COMPASS_PT] = " & Me.cmbCOMPASS_PT & " AND "
End If

' Check for LIKE STREET
If Me!txtSTREET > "" Then
varWhere = varWhere & "[STREET] LIKE """ & Me.txtSTREET & "*"" AND "
End If

' Check for ATERY
If Me!cmbATERY > "" Then
varWhere = varWhere & "[ATERY] = " & Me.cmbATERY & " AND "
End If


' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

End Function

 

[jdraw]

A couple of thoughts after reading your post.

-It's probably better - for getting feedback/assistance - if you start a new topic. The one you attached to is from Oct 2012.

- It isn't clear to me WHAT you are trying to do. A few lines in plain English to help the reader understand you (level of experience), your database (purpose/structure), the business issue (problem/opportunity/situation to be resolved), importance (urgent issue production is stopped/(casual) I've often wondered how this works/(need to know) there's a big change happening and I'm trying to prepare..

-We only know what you tell us - we can't guess what exactly you meant to say. So tell us in simple terms, WHAT you are trying to do/need help with.


-I notice you do this

Dim intIndex As Integer

but you don't seem to use intIndex. Did you write the code?
Can you tell us about this line?

-A technique used by many programmers is to Dim a string variable to hold your SQL. As you build the SQL, you can do a Debug.Print of that string variable without atempting to execute the SQL and getting some error. With Debug.Print of the variable you'll get a "print" in the immediate window of that SQL string. You can often spot syntax errors.

Anyway just a few comments for consideration. Good luck with your project.

 

[KiEESH]

Sorry. I tried posting a new thread, but couldn't find out how to do it.

Thanks for replying.

VBA Experience: Low

DB Purpose: To provide a main form & subform where users can search for and update records in a table, respectively.

It is important to mitigate user input error. Users, first, search for certain fields in the table to find the record that they need to edit. Partial search input is allowed. Then, a subform, added as a control to the main form, produces the editable result(s) of the main form search.

Business Issue: Used code (with permission) provided by online user. However, his code references a query as a record source for his subform, whereas we would prefer to refer to an editable table. Otherwise, the code will work perfectly for what we need. Not sure how to set this up.

Importance: Urgent

Also, I stripped out the references to the additional unused variables.

 

[jdraw]

Do you have a data model (tables and relationships)?
Why is it form/subform?

What subject matter is involved?

Some sample data would help along with some description of what you're looking for and under what conditions?
Seems the search is to find records to be edited. How are records edited currently?
Why urgent now?

I did some stuff a couple of years ago on partial searches that may be useful to you.
see this thread exchange
http://www.accessforums.net/queries/true-keyword-search-19909/index4.html

 

[jdraw]

Here's a link that may be useful.
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

 

[KiEESH]

Thanks for that example.
Your suggestion solves my search & find issue...

But not my (table) edit issue.

I need to search, find, then edit the source table, but in form view.

As far as I know, I can't edit query data.

How can the code in the example be manipulated to search and point to a table?

 

[KiEESH]

To build on the example, I'm imagining some type of OnClick event where I click on the row I want, but a form opens up with the matching data with my table as a record source (instead of the query).

 

[John Big Booty]

Have a look at the List Box's Double Click event. I think you'll find that that does what you are looking for.

 

[KiEESH]

Your DB is awsome. Can't get over how cool it is.

I'd like to have users search one or more fields at once with a space added in between. For example:

Bldg
or
Bldg_CompassPt
or
Bldg_CompassPt_Street
or
Bldg_CompassPt_Street_Artery


I tried adding side by side criteria for each field, cascading down and over, as directed, but adding one more field each time.

No luck in search, even without spaces.

Am I doing something wrong?

(P.S. Thanks for your patience with me!)




WHERE (((AREA_GROWTH2.ZONE) Like "*" & [Forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((AREA_GROWTH2.WR) Like "*" & [Forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((AREA_GROWTH2.BLDG_NUM) Like "*" & [Forms]![FRM_SearchMulti]![SrchText] & "*")) OR (((AREA_GROWTH2.BLDG_NUM) Like "*" & [Forms]![FRM_SearchMulti]![SrchText] & "*") AND ((AREA_GROWTH2.COMPASS_PT) Like "*" & [Forms]![FRM_SearchMulti]![SrchText] & "*"));

 

[John Big Booty]

That sample is not amenable to searching multiple fields with multiple text criteria. It will only search multiple fields with a single string.

 

[KiEESH]

Figured it out and got it working perfectly with the multi field & multi text criteria.

Thanks.

 

[jdraw]

Do you want to tell us/show us what you did to resolve the issue?
It would be good if you could show us all the code or a sample database showing the approach.

Glad you have it working.

 

[KiEESH]

I simply made new fields in query design that concatenated a several fields in my query.

I was then able to run searches with or without spaces.

Cheers all.