Can not see all the controls in the header section

[Kamakinto]

Good Afternoon Everyone,


I am currently serving as a Peace Corps Volunteer at my host countries Department of Education. The goal is to construct a database encompassing every teacher's information throughout the region. The Form I am currently working on is a search form.

My goal is to create an advanced Teacher search form. The user would search for teacher records based on all the fields in the teacher table (I.e. if the teachers table had first name, last name, date of birth, ID number, address, current school employed,ect. as fields. then the form would allow you to do a search using any combination of those fields and display the results. these results would be able to be turned into a report and printed.

I have made some progress with this, by using the database search designed by Allen Browne (id post the link, but i need 10 posts to do it)

I have it working where the information gathered from the unbound text boxes (First Name and Last Name) are used to build a query that is passed to the filter.

So, the next step would be for me to expand upon this concept by adding the rest of the unbound text boxes/combo boxes in the header section. each control being used to enter search criteria for a different field from my Teachers Table. this would give me about 20 different text boxes/combo boxes in the header section. and have the same 20 fields be going horizontally in the detail section (imagine a datasheet view's layout of records)

plugging in the controls in the header section looks right in the design view, as all the controls are displayed and the records in the details section are visible.

However, when i switch to form view, I am not able to see all of the controls in the header section, and the details section is all but disappeared...there isnt even a way to scroll down to view the rest of the information which was visible when i was in design mode.

I am not sure what i am doing wrong, or if there is some property that is disabled. but in a nutshell i cant view the contents of my form once the controls go down the page to a certain point.

is there maybe an easier way to accomplish this search that i just havnt tried yet? or is my dilemma fixable? (as i am still a beginner to the process). any suggestions would be super helpful, and if I have left a piece of information out PLEASE tell me so i can give you more specifics.

Thanks guys for even reading it to this point lol.

 

[AccessMSSQL]

First you should check your form properties and make sure you have scroll bars set to both - sounds like you have a lot of fields on your form. Do you have a subform in the detail section that displays your records or are you binding your main search form directly to the result query? Check your footer height also - if footer height is too large it may end up hiding your form header fields.

http://www.accessmssql.com

 

[Kamakinto]

got it. went through properties of the form and it is set to both.

yeah it is definitely a lot of fields, it makes me nervous because I am not sure if Access was built to handle such an extensive advanced search.

naw, its a Continuous form (or Multiple items form) so i put the unboound search textboxes in the header, and the text boxes used to display the results in the details section, they are bound to their corresponding table field (ie. the First Name text box's Control Source is the First Name field in the Teachers Table) - this may be an awful approach to displaying 20 fields, but I am honestly at a loss as to another way (and i am almost sure there is a better way). The only reason I am using this method is because it was the way it was outlined in Allen Browne's example here --> http : / / allenbrowne.com/ser-62 . html ( just take away the spaces in the address...im not at 10 posts yet lol)

by looking at that link, it would shed more light on how i have it set up (as it is identical)

 

[AccessMSSQL]

Yeah, I know how it's set up. I have lots of search forms set up similarly. And yes, Access can handle that many search criteria. I have a project that has so many search fields (combo boxes, text boxes, list boxes) that I return the results in a separate form. You might want to try modify it a bit - once users finish entering search criteria, just open a new form bound to the query results. Have a button in the footer of the results screen that returns to the search screen. Also, when I build my query dynamically in the search screen I use a function - ModifyQuery to change the SQL behind the query. You need to add a reference to the DAO object library. Here's the code:

Public Sub ModifyQuery(p_qName As String, p_sql As String)
'this sub updates a query definition for you just pass down query name and sql and update def
Dim l_db As DAO.Database
Dim qdef As DAO.QueryDef
On Error GoTo ModifyQuery_err
 
 
    Set l_db = CurrentDb
 
    Set qdef = l_db.QueryDefs(p_qName)
    qdef.SQL = p_sql
 
    qdef.Close
    Set qdef = Nothing
    Set l_db = Nothing
ModifyQuery_exit:
Exit Sub
ModifyQuery_err:
MsgBox Err.Number & " " & Err.Description
GoTo ModifyQuery_exit:
End Sub

http://www.accessmssql.com/

 

[Kamakinto]

awesome. thank you for responding so quickly, as well as the reassurance that this is very possible to do! I am going to go through the posted code and read up on the functions, and objects so i know exactly whats going on (still new to the VBA coding).
I am trying to get the train of thought right so correct me if I am wrong:

create a form with the unbound search criteria (text boxes, combo boxes, list boxes ect.) and put a "search" button control on it (which should gather the textbox criteria and turn it into a sql string, store that string in a variable (or using your code, once i figure it out) . then open another form for my results)

when the new form opens up, the SQL query should be bound to it (is this posssible to do if the sql string is stored in a variable?) and the new form should have an object to display results.

as well as a button to return to the search (and figure out a button used off this same method to build a report based off the sql query)

let me know if this isnt the logic you were suggesting for the search to work. I Just want to make sure i am researching the right things in order to make the forms function correctly. And I'm thankful you responded, because you seem to have a lot of experience with these type forms.

 

[AccessMSSQL]

You almost got it. I was trying to post some additional code for you yesterday but my session kept timing out. You should have a Results form that is always bound to it's own query - say call it qrySearchResult and frmSearchResult.

In your code that gathers the search criteria - I notice in Allen Browne's code he is setting strWHERE to the forms filter property. Modify that code as follows:

change this:

       'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

to this:

        'Modify the query directly and open the search result form
        ModifyQuery "qrySearchResult", "SELECT * FROM YourTable WHERE " & strWhere
        Docmd.openform "frmSearchResult"

In above, since your frmSearchResult will always be bound to the query "qrySearchResult"
and we are modifying the query SQL Statement directly all you have to do is open the form

 

[AccessMSSQL]

The other thing you should probably do also is before you open the search result form - check to see if the form is already opened. If it is you have to close it before re-opening the form otherwise the form won't refresh.

Use the IsLoaded function to check if the form is opened.

'----------------------------------------------------------------------
' IsLoaded
'
' Returns TRUE if the given form is loaded.
'----------------------------------------------------------------------
Function IsLoaded(FormName)
IsLoaded = (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, FormName) <> 0)
End Function

So if we modified the code I just gave you it would look like this:

 ModifyQuery "qrySearchResult", "SELECT * FROM YourTable WHERE " & strWhere
If Isloaded("frmSearchResult") then
docmd.close "frmSearchResult"
end if
docmd.openform "frmSearchResult"

 

[Kamakinto]

OK! I am with you now! at first I was having trouble understanding the ModifyQuery function. but once you explained the parameters being passed to it via the integration into the Allen Browne code snippet, it all made sense.

I will put the puzzle pieces together and report back as soon as I have efficient electricity (we are experiencing a black out and im running off a backup battery atm)

 

[Kamakinto]

OK. I got the search working ! ( I had to use a split form to get access to get all form fields to show u early...it seems access cant go past 22 inches unless its in a datasheet form)

using the tools you have given me I think im going to do some exploring and see if i can build a more dynamic sql query so that the user will have the ability to select which fields they want in the frmSearchResult. (replace the * with a variable that builds the fields to select based off of which radio buttons are selected in the Search Form.)

printing a report may be a bit more tricky, I think the form results will have to just be exported to Excel or PDF , since I am sure the Report Header length is set to the same limit of 22 inches.

Your guidance, code, and suggestions have helped tremendously!! I greatly appreciate your help. And I want to say thanks you so much.

btw, when my database is all said and done, would you be ok with me uploading a sample database to the message boards? just to give another variation of a search form other users can use?

 

[AccessMSSQL]

Kamakinto...glad my posts were helpful to you and you are using them to build a more robust search. That's awesome! Absolutely please post your results and share with the forum!

http://www.accessmssql.com/

 

[AccessMSSQL]

It sounds like in the search you need to actually use the ID of the School and then in the query result include the teachers table values that correspond with that schoolID? Is that correct?

 

[Kamakinto]

correct. i admit i deleted the post because i decided i didnt want to bother anyone and just kinda figure it out. I got the search form to display the School ID and School Name via a query and adding both columns to the properties. so thats covered. the value returned is the ID, which makes life easier. the next step is to add the school name to the results form, then use the same logic used to build the Where clause to build the fields displayed in the results. long story short, problem solved for now lol