How do I make a single table database searchable with form input and output?

[Scott Deming]

Ok here's the deal. I'm in Afghanistan and attempting to create an Access database for my pilot's to debrief on when they come back from a mission. Included fields would be things like names, mission, time, etc. etc. We currently have an Access database that has been grandfathered down from unit to unit that kind of works. The issue with this database is that it is incredibly messy, unorganized, and half of the original functions of the database don't work anymore. So last week I started making a new database from scratch.

Prior to last week I'd never touched Access in my life. Therein lies my problem.

I've spent countless hours building this new database, learning the ins and outs of Access, and scouring forums trying to find the answers to my problems. As of right now the basic function of the database works. Data can be input to the form I created and is stored on the database back end. That's about the extent of what it can do.

What I need to accomplish is having a form that I can use to search specific fields of the database for date ranges, who was flying that mission, what the mission was etc. and then return those results in a chopped down version of the database that opens on the form. (this works in the current version, and I have the general idea of how to do it, but it's not working) The chopped database that opens needs to have a link that displays the original form with all the appropriate data in the appropriate fields. Like I said, the old database does exaclty this and it works fine... mine doesn't.

More importantly I need to be able to export the data to other programs. Certain data will need to make graphs, other parts of data will create a chopped down version of the database in an Excel doc with the information needed. I don't even know where to begin here.

Ok, here is how my database is set up:
- 1 Table for storing data (tblDebrief)
- 1 Form for inputing data into that table (frmDebrief)
- 10 Tables that correspond to combo boxes on the form for inputing the data (Source:AO, Source:Mission, Source:Team, etc.)
- 4 forms for getting the data to provide the answers I need (frmSearch, frmExport, frmView)
- 1 Switchboard with links to all the Forms (this actually works almost perfectly, and all the forms link back to the switchboard, yay! The only issue with this is that when I set the switchboard to "popup" it is physically impossible for me to go back into the design view of the switchboard)

I realize that's alot to take in Believe me I'll be at this computer waiting for responses and any help at all until I get this damn thing fixed.

The main thing I'm asking for are responses that are in basic English. Yes, I can understand terms like Forms, Tables, Queries, and I know a little bit about VB programming from back in the day, but you start talking in all out technical language and I'm lost. If there are posts anyone knows of that can help with what I'm looking for please post it. I am fully prepared and willing to dig through anything that can be of use. I'm not asking anyone to make this database for me, just please point me in the right direction because right now I am LOST!

[munkee]

Since this seems to be for an epic good cause I dont mind spending quite a bit of time taking a look at this with you.

For starters the search functionality. The best way I have seen for looking at inputting date ranges and multiple criteria is by building a dynamic "where" string. This is essentially using some VBA code to filter your database down "where" certain criteria have been selected.

The best example of this was created by Allen Browne and I will link you outright to his site first and then directly to the code I am talking about.

His site will explain quite a few tricks you will be able to utilise in your database with clear explenations, I was in a similar boat to yourself just 8 months ago and now I feel I know a decent enough amount of code/techniques to get most databases going in the right direction and I used Allen's site religiously:

http://allenbrowne.com/tips.html

Now to the creation of the search:

http://allenbrowne.com/ser-62.html

If you get stuck with any of the search stuff let me know and I will provide a cut down example of his code to get your head around it a bit more (but it is often worth trying to plug away at this yourself.)

The second thing you mentioned was pretty much "being able to view full information of returned search results". In your current database it is likely you perform a search on the main table and you are returned a number of records giving a brief "overview" of all the data that is there. Usually there will be a button beside each of the returned records which you can click to open up a form with the "full" record/information behind it.

If this is what you are talking about, I recently answered a topic where a guy asked something very similar and I posted an example database which you might be able to view just to clarify it is the functionality you are after. The thread is here:

http://bytes.com/topic/access/answers/904495-how-open-detail-form-summary-form

If there is anything else you need to know just post again and to be honest I am sure you will have more specific questions next ;)


Edit:

Sorry for ignoring the exporting results to other programs such as excel etc, I do have some code that will do this but until you have the search form returning results as you want correctly then it might be worth holding fire with the exporting for now.

[Scott Deming]

Munkee,

Thank you so much for the answer. I'm digging around through those sites now and they are definitely helping. For starters, I realized I didn't have any of my other forms linking to the main table, they were all just floating around with nothing to reference to.

I'll post an update tomorrow after I've had a chance to implement all the changes and see where I'm at then.

Thanks again, I really appreciate it!

[munkee]

No problem.

The method Allen uses is to place search options in the forms header.

I use a slightly different setup where I create a subform with recordsource set to query of a table I want to search on. With the form style as "continuous ". I then place text boxes on this subform to house the fields of the underlying table in a more "overview" style, so not all fields are represented with a textbox.

I then add a button beside this setup with the text "view" on it. This will then open up a form when clicked which houses the whole of the record with full information.

My search form contains this subform and I place the search boxes/options etc on this main form and a simple search button.

To illustrate all of this a bit more clearer I have attached a file with some screen shots and basic explenations of why/how.


Continous Subform based on either a table or a query.zip

[Scott Deming]

ok, i'm beating my head against a wall again. sorry it took so long to respond. it got pretty busy there for awhile.

anyways, i've put in all the code. edited to be applicable in my database, checked checked checked and double checked all the spelling on everything. it's only like 2 or 3 pages so it doesn't take too long to check. and i still have nothing.

when i click on the box, there's no error, or click to debug, there's nothing. it's still acting like an empty command button. i tried switching it from the header to the detail section, but that didn't help either.

i hate to be a pest but can you barney style this for me? i don't get what i'm doing wrong.

[dsatino]

Scott, there are numerous users of this site who could get this running fairly shortly if they had it. Probably fairly quickly as well.

You may want to consider making a copy, deleting the records from the copy, inputting some dummy records in the tables, and posting the copy in your post.

I've seen plenty of users here simpy write the code and post it back. Some of them do this as a hobby and are happy to it.

Even if no one undertakes the project as a whole, we'll have all the technical details on hand without you having to explain it and you can post single problems to solve which will lead to incremental, but specific fixes.

Just a thought.

[Scott Deming]

yeah you're right, i just copied all the code and put a bunch of images into a powerpoint. working on explaining what does what. i hadn't thought of dummy records though, thats a really good idea. i should have the file posted on here shortly. thanks.

[munkee]

Hmm probably needs compiling to find the errors and if not there might be a settings issue in your vba editor.. or code might not be pointing in the right directions. Best to upload..im around tonight to take a look at this if others aren't

[Scott Deming]

ok so here it is. i had to take all the classified bits out so it's an empty shell with only three entries that consist of "asdflk" in alot of the boxes.

I'm not really looking for someone to just fix it and go 'here you go'. i'd really like to know what i did wrong so i don't have this issue any more in the future. again, i really appreciate the help. i know this is asking alot and there's not alot i can do to thank you guys properly other than say thank you like a thousand times.

also, i made the powerpoint before i decided to attach the program. so it's kinda useless. but its there. :P

Access Problems.zip

[munkee]

Balls, Im using access 2003 so I cant open your file.

However looking at your setup I can see an issue. It seems to me that you are referencing your main form at the moment in all of your Me. statements. Your search and filter buttons are on your main form but you search needs to filter your subform. So to fix this problem:

Change:

Private Sub form_open(cancel As Integer)
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub

To point to the subform which will be something like me.subfrmblah.f orm.filter = "(False)" etc.

Also your:

        strWhere = Left$(strWhere, lnglen)
        Debug.Print strWhere
        Me.Filter = strWhere
        Me.FilterOn = True

Need to change the me. statements to point to your subform.#

Here is a site which explains the referencing:

http://blogs.techrepublic.com.com/msoffice/?p=188

I think it should be something like Me.subformname. form.filteron = true etc
or maybe me.subformname. filteron = true. Sorry not got a database to test this on at the minute and I always forget the correct convention. If you use me. and intellisense should come up in your vba editor and find the subform control name (the name of the subform box on your page) and then add in the .filter parts it should all come up for you.

All you need to take away from all this ramble is:

Because unlike in Allens original script you are not filtering the main form, you are filtering a subform all of your Me. statements are pointing to the main form and not the subform. So you are trying to filter a form which has no records on it, when you should be filtering the subform which does have records on it.

Your controls to create strWhere are all held on the main form which is fine, but the actual filter strWhere needs to be applied to the subform.

[Scott Deming]

    If Not IsNull(Me.frmsubSearch.Form.District) Then
        strWhere = strWhere & "([district] = """ & Me.frmsubSearch.Form.District & """) AND "
    End If

ok i fixed the specific codes you linked in the top part, but when it comes to each individual control is this what you are talking about? also, i noticed on the page you linked he uses "Me!" instead of "Me.", is that an issue or irrelevant?

[munkee]

For each individual control in this you need to do it based on what the code is saying.

So in your example above:

 If Not IsNull(Me.frmsubSearch.Form.District) Then

This is a if statement to check whether the control on your main form contains anything. If it does contain something then we add on to our strwhere some extra filter information.

If it does not contain anything then "end if" (so dont do anything) and go on to the next statement.

So with that being said your line is referencing the subform when it should be referencing your main form (just Me.district).

The next line in your code is:

strWhere = strWhere & "([district] = """ & Me.frmsubSearch.Form.District & """) AND "

This is basically saying take the strwhere and add on to the strwhere this extra info which is to look at the field [district] in a recordset and look for the specific value supplied from your main form.

In this case you have referenced your subform, which you shouldn't as the main form is what supplies the value you want to go in to the filter. So revert your line:

Me.frmsubSearch.Form.District

To me.district or wherever your textbox value/combo box value is held in your main form.

The basic idea of the Allen Browne filter and the usage with the sub form is to basically do the following:

Use controls/textboxes/comboboxes in your main form to supply values which will go in to your strwhere. So you reference these using me.textbox.valu e or me.combo etc

Strwhere is built through multiple IF statements to see whether there has been anything supplied to be filtered on. Each time an IF statement criteria is met we have the code adding another bit of text to our strwhere string + an "AND" at the end. As we could have multiple criteria met and need an "AND" statement to join them all together.

This strWhere is then at the end of the day check to see if an "AND" statement is left on the end, if it is we just chop that last bit off and produce the final formatted filter strWhere, with all of the options we specified we want to filter/search by.

We then take strwhere and we basically say right now with all the options I selected I want to then apply this to my subform. My subform holds a recordsource which is equal to one of my tables. I want this table to be searched/filtered based on what I asked for.

So we take our strWhere, we reference the subforms filter property using me.subfrmname.f orm.filter and we set it equal to our filter string strwhere as:

me.subfrmname.f orm.filter = strwhere

We then turn the filter on using filteron propery and our table is then filtered down from showing every record to just showing the specific records that match our filter.


Here is some code from one of my projects:

'Date field to find end date for date occured
    If Not IsNull(Me.txtfilterDateEnd) Then   'Less than the next day.
        strwhere = strwhere & "([DteOccur] < " & Format(Me.txtfilterDateEnd + 1, conJetDate) & ") AND "
    End If

 'Listbox to select department responsible passing text value in sql query and not numerical
    If Me.lstfilterDeptResp.ItemsSelected.count > 0 Then
        Dim itm2 As Variant
        Dim strList2 As String
        For Each itm2 In Me.lstfilterDeptResp.ItemsSelected
            If strList2 = "" Then
                strList2 = "([DeptResp] = " & "'" & Me.lstfilterDeptResp.Column(1, itm2) & "'" & ")"
            Else
                strList2 = strList2 & " OR " & "([DeptResp] = " & "'" & Me.lstfilterDeptResp.Column(1, itm2) & "'" & ")"
            End If
        Next
        strwhere = strwhere & "(" & strList2 & ") AND "
    End If

The bracketed statements such as [DeptResp] and [DteOccur] are the names of the fields in my table.

Everything else such as me.lstfilterDep tResp.Column(1, itm2) is just referencing a combobox control on my main form, hence the me.controlname and not delving in to me.subfrm.form. controlname etc.

Once you get this referencing bit right, take a look at the debug.print strwhere line in your vba editor immediate window. Everything will click when you see one of these strwhere lines actually completed.

Here is a debug print of my strwhere just to show the construction:

([DteOccur] >= #01/06/2011#) AND (([DeptRaisedBy] = 'Engineering - Service')) AND (([NCStatus] = 'Initial Notification') OR ([NCStatus] = 'Pending Acceptance') OR ([NCStatus] = 'Action Being Taken') OR ([NCStatus] = 'Awaiting Closure') OR ([NCStatus] = 'Closed'))

As you can see the AND statements just link everything together unless an OR statement is there when I have multiple selections in a listbox.


Edit:

The period . and what is refered to as the bang ! dont make a blind bit of difference in their usage. They are more used to try and make code look a bit cleaner and explain what you are referencing. You by all means can use just .'s or just !'s .. I always use .'s because its just easy for me.

If you did want to follow the true coding style if you referencing a control on a form which will then have a reference to a control on that form below it you would use ! and then .

For example:

me.txtbox1
Me!subfrmform.f orm.txtbox1

It honestly doesn't matter though and it makes no difference to the code, nor does it change how fast things execute.

[mshmyob]

Just subscribing - will look at attached database and give my 2 cents.

cheers,

[Scott Deming]

ok so now that i've fixed that, if i'm understanding what you wrote my code is good to go. the problem is now with the subform table being referenced.

so when i open the search form, the entries to the form should already be displayed. then using the search feature would narrow down the displayed results to what i want to find or filtered for.

i linked the items in the subform directly from the debrief table and they use to function like that. where when i opened the form all of the entries were already there. but i changed something (not sure what, i'll figure it out though) earlier so now when it comes up the table is empty. therefore, when i apply the search function i'm sorting through an empty database. (edit: i checked and when the form is display independantly, it properly reflects the main form. but when the form is brought up as a subform, it is still displaying that there is nothing in it. like in the screen shots i put in the powerpoint, the only thing displayed is "new")

so essentially, once i fix the table and make it display everything intially THEN the search feature should work and display only what i want.

i hope i'm getting warmer on this. i'm not sure how but i can actually understand and comprehend what you wrote. there's hope yet! lmao

[munkee]

Try commenting out the filter=(false) and filteron = true in the form open event.

The false in the filter makes sure no records are shown and the filteron ensures that this false filter is applied. So might be worth removing it.

Then also check that your subform recordsource is definitely set to a table and that there is no criteria in the filter property in the form properties it may have a "(False)" sat in there from previously running code.