Simple Searches & forms

[arcticsponge]

Hi All, This has probably been answered elsewhere but I cannot find it. I have 1 query & 3 forms. I am trying to perform user searches of the query, but my SQL is appalling. Basically, the first form acts as a homepage (in that it is always active); it has an unbound textbox (for the search terms) and an option group: The user types in the terms and selects the options: op1 (searches columns 1-3), op2 (column4) op3 (col 5) & op 4 (col 6) The results of the search are to be displayed in an unbound listbox on the second form, of which the user double clicks and the record is opened on the third form. I am stuck.

 

[MarkK]

You haven't asked a question or described a problem.

 

[arcticsponge]

It would help wouldn't it. Basically, I have the forms and the table as described above however, as I am appalling with SQL, I cannot work out the code to take the textbox search string and look it up from the table / query and then display the results in a listbox on the second form which on double-click will display the record on the main form.

 

[GoodNamesTaken]

While I'm not expert it sounds like you might be looking for a subform bound by a field on the main form.

Hope this helps.

 

[MarkK]

As a first step use the query design grid. Get something that works or is close to working since you do know, despite your SQL, the table and field names and so on. Then post back with specific problems you encounter.
It's my observation that targetted and specific questions and requests stand a much greater chance of yielding a useful response. Questions with a larger scope are not easily answered in forum like this.
Cheers,

 

[arcticsponge]

Its not a subform that I am using rather its 3 separate forms - I tend to avoid sub forms like the plague.
Rather than this search form functioning to locate records on the current form, I am using unbound form a (as home) unbound form b (as search results) and bound form c (as the user form (bound to the table).
The purpose is that form c is only used when using the record, the user never remains in the form instead returning to the "home-page" (form a) so I needed the code that would take the string from the unbound textbox on form a, look for the record against a query (not the main table as my previous posts state), and output the results to form b for the user to select the right record on double-click.
A colleague helped me to find the right code but for future purposes, I have posted it here:

Private Sub Command36_Click()
Dim rs As Recordset
Dim IntRecCount As Integer
Me.Text41.SetFocus
If Len(Me.search) > 0 Then
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QryM_RecCount", acViewNormal
    DoCmd.SetWarnings True
 
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_RecCount", dbOpenSnapshot)
 
    IntRecCount = rs.RecordCount
    rs.Close
    Set rs = Nothing
 
    If IntRecCount = 0 Then
        GoTo err_search
    Else
    DoCmd.OpenForm "frm_search"