SubForm problem

[mdmackillop]

I’m trying to create a searchable database of Questions and Answers. By opening the Search form, I type the search term (e.g. “detail”) in the textbox, select Q, A or B (both). And the button unhides a subform appropriate to that query.
Very clumsy I know, and it doesn’t work! The forms work separately, but not as a subform, I get all data showing.
Any suggestions to correct this, or a better methodology welcome.

[mdmackillop]

Moved on a bit. Just one subform and query. How do I get the subform results to change when I change the query paramater or type?

[SoftwareMatt]

The subform query should target the main forms parameters and then you need to requery the subform every time you change a parameter or click the run query button. At the moment the subform's query is hardwritten to find everything like *detail*

[SoftwareMatt]

In the attached file i have amended the query so it targets the main form's search box. I have also added code on the AfterUpdate of the search textbox that requeries the subform.

From this you should be able to work the rest out.

[hansup]

Make the main form unbound.

Use a single subform.

Then your command button can apply a filter to the subform.

Sample attached.

[mdmackillop]

Hi Hansup.
Thanks for that. I've not programmed much in Access and that many years ago, and I never got into Filters.
I've tweaked things a bit to get this version

JD,
I'll have a look at your fix tomorrow

Thanks both.

[hansup]

You've made a lot of progress since that first version!

I want to mention a few points.

I changed the field names index to Id and Project to Project_url because index and project are reserved words. (Google "Allen Browne reserved words".) Also changed another field to Q_num to avoid the space in the previous name.

Seems to me Q_num might be better as numeric rather than text data type. I left it as text in case you had a real need for it as text. But that in turn led me to the somewhat hackish "Val(Q_num) AS Q_num_as_number" in the base query. If possible, change Q_num to Number:Single, index it, and revise the query and form to match.

I removed 2 references: ADO; and OLE Automation. Neither was used by your code, and the project compiled fine without them. My rule of thumb is to operate with the fewest references possible. I typically have only 3: VBA; Access Object Library; and DAO.

Suggest you consider naming the bound data controls differently than the row source fields to which they are bound. For example, txtId for a text box bound to a field named Id.

Since Id is an AutoNumber, I wouldn't usually display it to the user. If you need a text box for it, you can hide it from the user (Visible = No). Or if you do want them to see it, they should have no business trying to change it, so set properties Enabled = No and/or Locked = Yes on that text box.

I like your decision to move the Filter creation statements to a separate DoSearch procedure. However, I think you may be calling that more often than necessary.

I originally did this in Form_load:

Me.qryData1.Form.Filter = "Id = " & DLookup("Id", "qryFirstQuestion")

My intention was to limit the rows in the subform before the user did any searching. However, in hindsight, I don't think that was worthwhile.

Filters are an easy way to handle this situation, where the data table is local and not huge. However, if the data table were actually a link to a Jet table on a network share, or a link to a table on a server database, and especially if that table were huge ... think I would abandon the filter approach. In that situation, write the subform's rowsource SQL to pull one or only a few rows when the form first loads. Thereafter, re-write the subform SQL to match the user's search selection criteria. The point is to limit the number of rows you need to pull from the remote data source.

[mdmackillop]

In the attached file i have amended the query so it targets the main form's search box. I have also added code on the AfterUpdate of the search textbox that requeries the subform.

From this you should be able to work the rest out.

I'm getting mixed results.

I'm not so keen on the textbox change event and would prefer the Frame value to show the data. I've tried using your corrected Subform code, but I'm not getting the new data. The underlying data is changing (I've added some recordcount code to check), but the refresh still fails.



Option Explicit
Option Compare Database

Private Sub Form_Load()
Me.Text2 = "Policy"
End Sub

Private Sub Frame4_AfterUpdate()
Dim qdf As DAO.QueryDef
Dim Ast1 As String, Ast2 As String
Dim SQL As String
Dim intX As Long, intY As Long
Dim strSearch As String


Ast1 = """*"
Ast2 = "*"""
strSearch = Ast1 & [Forms]![Search]![Text2] & Ast2
intX = DCount("*", "qry")

Select Case Frame4.Value
Case 1
SQL = "SELECT Data.Question, Data.Answer, Data.Project " & _
"FROM Data " & _
"WHERE (((Data.Question) Like " & strSearch & "));"
Case 2
SQL = "SELECT Data.Question, Data.Answer, Data.Project " & _
"FROM Data " & _
"WHERE (((Data.Answer) Like " & strSearch & "));"
Case 3
SQL = "SELECT Data.Question, Data.Answer, Data.Project " & _
"FROM Data " & _
"WHERE (((Data.Answer) Like " & strSearch & ")) " & _
"OR (((Data.Question) Like " & strSearch & "));"
End Select
Set qdf = CurrentDb.QueryDefs("qry")
qdf.SQL = SQL

intY = DCount("*", "qry")
MsgBox intX & " - " & intY

Me.subSearch.Form.Requery
Me.Refresh
Set qdf = Nothing

End Sub