Query not combining and filtering values based on a form

[GinaWhipp]

Well, it's tomorrow and I have finally got a minute to type a reply, so here you go...

1) You need to explain to me how you did this. Seriously. It's the whole eat fish for a day or learn to fish. If I don't understand how this is done, I'm going to be coming back on here in a couple of weeks or months and asking the same question for a different db.

If you look at the Event Procedure behind the *Set Filter* button all the code is there. Basically, that code builds the WHERE statement for the Forms Record Source. So, here is your Record Source...

SELECT Equipment_Spec.Picture, Equipment.Unit_Cost, Equipment.Quantity_Purchase, [Unit_Cost]*[Quantity_Purchase] AS Total, Equipment.Project_Name, Equipment.Supplier_Name, Equipment.[Location/Affiliate_Name]
FROM Equipment INNER JOIN Equipment_Spec ON Equipment.Equipment_ID = Equipment_Spec.ID;

The bolded fields are the three Combo Boxes on your Form.

This code...

    If Not IsNull(Me.[COLOR=red]cboLocation[/COLOR]) Then
        strWhere = strWhere & "([[B]Location/Affiliate_Name[/B]] = """ & Me.[COLOR=red]cboLocation[/COLOR]& """) AND "
    End If
 
    'Another text field example. Use Like to find anywhere in the field.
    If Not IsNull(Me.[COLOR=red]cboProject[/COLOR]) Then
        strWhere = strWhere & "([[B]Project_Name[/B]] Like ""*" & Me.[COLOR=red]cboProject[/COLOR]& "*"") AND "
    End If
 
    If Not IsNull(Me.[COLOR=red]cboSupplierID[/COLOR]) Then
        strWhere = strWhere & "([[B]Supplier_Name[/B]] = " & Me.[COLOR=red]cboSupplierID[/COLOR]& ") AND "
    End If

After you make your selection and click the button this...

        strWhere = Left$(strWhere, lngLen)
[COLOR=#bfbfbf]    'Finally, apply the string as the form's Filter.[/COLOR]
        Me.Filter = strWhere
        Me.FilterOn = True

...sets the WHERE statement for your Forms Record Source and the last two lines applies it to the Form.

2) Is the form still using the DropDown qry test query (is that the event procedure On Click for the Set Filter button?)? If so, can I add the Madeup code column back in to the query results form?

You can change the Record Source to anything you like BUT you must either keep the three field’s bolded above OR change the Combo Boxes accordingly. So, you can add and/or take away fields but you must remember the three fields above. Oh, and you can rename the query to anything you like, the fields are important the name is not.

3) Can I have the results be none editable? So basically just to see the results not be able to make changes to the records? (I tried changing the Allow Additions or Allow Deletions or Allow Edits to no in the property sheet but that just made the drop downs not work)

You can set the Controls in the Detail Section to Locked = Yes and Enabled = No. Then they can look but no touch. I would also recommend making Allow Edits = No so there is no new record line at the bottom.

4) How can I add labels above the results columns to reflect what the column is? Yeah, I thought I could just move the query results down and add labels but that didn't work out so well

In Design View add the Labels right above the Detail *bar*.

As for your concerns of the db as a whole:
"1. Reserved Words

Table: Equipment spec
Field: Type

Using words reserved for Access as field names will cause problems for you because it confuses Access. You need to change the name of that field. (I have already checked all the other names)."
If I change the name now won't that mess up everything that's dependent on that table/field? The forms, the queries...

Yes, you will have to update anything that name is used in but not changing it will cause you issues down the road. We had a case of that last month and it took quite a while to figure out what the problem was.

"2. Field Names

You have included spaces, underscores and slashes in your field names (and table names). This really makes coding a problem as the ones with spaces and slashes will require bracketing. I'm lazy and considering the amount of code I have to type I want to save all the key strokes I can. You need to adapt a Naming Convention, here's mine...
http://www.access-diva.com/d1.html
I understand. I'm still obviously new at designing db's and this is one of my first few db's I designed I will start to implement this with my next db.

If you get the chance you should try to fix what you can in this one. If I was coming in behind you I would have to stop and fix all of that before I could move forward, why? Because it just makes coding harder and the more you get into it the more you will realize how important it is to fix these things before they become issues. No pressure

"3. I do not recommend using any field that contains names as a Primary Key, i.e. your Location/Affililate table. Use a Autonumber as your Primary Key. Suppose you have to Locations with the same name? What are you going to do then? I strongly recommend you put a Primary Key that is an Autonumber in all your tables. After all, that number is for Access to maintain database integrity so no one needs to see it anyway."
Understood. Can I add an Autonumber to my tables now? Won't that affect all the forms and all the data that's already in the tables?

Probably, but using names is like walking on egg shells because if you get a duplicate in one of those columns you are going to have to stop everything and fix it anyway.

Your database is small now, I know it might appear like a lot to you but call me when you have 100 Forms, countless queries, modules and tables Implementing these changes now is not only a good idea but great practice and databases tend to take on a life of their own. They start growing and the next thing you know you end up with this...

http://www.access-diva.com/dm12.html

...and even that one is a baby. You want to make sure you are on the right path right from the beginning. AND, since you’re in the early stages of database development here are some links that might help...

Jeff Conrad's resources page...
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page...
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP)...
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials...
http://allenbrowne.com/links.html#Tutorials

UtterAccess Newcomer's Reading List
http://www.utteraccess.com/forum/Newcomer-s-Reading-List-t1998783.html

Help with Normalization
http://www.access-programmers.co.uk/forums/showpost.php?p=1146957&postcount=2

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

Other helpful tips…
Setting up a Model Database
http://www.access-diva.com/d11.html

My Database Standards...
http://regina-whipp.com/blog/?p=102

 

[mayestom]

Okay, I looked at the code behind the Set Filter button. It's obviously all Greek to me. As I mentioned before I don't know SQL (would like to start to learn). I know basic Java so I can sorta kinda follow what's going on but not really (I'm going to look at the code more thoroughly this weekend to try and get my head around it). And certainly not enough to be able to change it or the Record Source in order to add in the Madeup Code I mentioned before. Would you be able to walk me through adding that in to the results window in the Detail section at the bottom of the form?

I was able to change the Control for each object in the Detail Section to Locked = yes and Enabled = No. But I can't find where the Allow Edit is? Is that for each individual object in the detail section or the whole form or...?

Added Labels (Looks much better above the Detail bar , thank you).

I understand the need to make the changes you stated to the db in general. I will try to implement them in this db. Not sure I will be able to but will try (bc I'm already behind on making it go live).
Speaking of which, since you are kind of familiar with this db, how long would you say it would take to design and build it? Considering it would be your second or third one you have built. Just trying to compare that to what others (who don't know how to build them) think it would take to build it.

Thank you for all the resource links I will start to look at them.

And again, I can't mention it enough, thank you for all your time and effort in helping me with this.

 

[GinaWhipp]

I will be able to walk you thru adding to the Record Source, changing the the Form around, etc, etc.. No problem.

Allow Edits - Sorry that was me typing and not enough coffee. It should be Allow Additions to No and that is on the Form level.

Hmm, you're asking to me to think back many moons ago! I think it took me the better part of a month but fokks were adding stuff in for two or three months afterwards.

That said, you cannot judge my experience by most. I had a great teacher... I got a job many moons ago at a software company and a Client wanted changes to the software which was written entirely in Access. Well, I, who had never done anything like that before, said I would do it because there were only 4 of us and everybody had to pitch in. My coworker, who was also my boss, gave me a book and helped me make the initial changes and then said read the book. I read and read and read and she guided me and answered questions until one day she said... "The student has surpassed the teacher!" and a new Access developer was borne. And now, here I am, trying to pay forward what was done for me!

 

[mayestom]

"I will be able to walk you thru adding to the Record Source, changing the the Form around, etc, etc.. No problem."
Great, eagerly awaiting your instructions.

"Allow Edits - Sorry that was me typing and not enough coffee. It should be Allow Additions to No and that is on the Form level."
Got it, thank you.

"Hmm, you're asking to me to think back many moons ago! I think it took me the better part of a month but fokks were adding stuff in for two or three months afterwards.

That said, you cannot judge my experience by most. I had a great teacher... I got a job many moons ago at a software company and a Client wanted changes to the software which was written entirely in Access. Well, I, who had never done anything like that before, said I would do it because there were only 4 of us and everybody had to pitch in. My coworker, who was also my boss, gave me a book and helped me make the initial changes and then said read the book. I read and read and read and she guided me and answered questions until one day she said... "The student has surpassed the teacher!" and a new Access developer was borne. And now, here I am, trying to pay forward what was done for me!"
Well, you have certainly paid if forward with me many times over. And once I am good enough I will be doing the same for others that are less knowledgeable with Access.

 

[GinaWhipp]

Okay, so which one do you want to do first? Adding the *MadeUp* code to the Record Source?

 

[mayestom]

Yes, please.

 

[GinaWhipp]

I have a 2:00 pm meeting I am getting ready for, I slacked this AM. I will type in instructions either shortly, if I get myself together by 2pm or after the meeting... sorry for delay!

 

[mayestom]

Are you kidding!?! You do not need to apologize to me. I total understand.

 

[GinaWhipp]

Okay, I am back and before we start are the changes above the only changes you have done to the Form? Just want to set mine up the same way so when I type instructions I'm not telling you incorrectly.

 

[mayestom]

I changed the order of the Detail section to: Project_Name, Supplier_Name, Location/Affiliate_Name, Unit_Cost, Quantity_Purchase, Total, Picture. But... You're going to hate me for this. Instead of the Madeup Code from the DropDown qry I need to add the Description field (which is in the Equipment_Spec table) that's associated with the Equipment_ID in the Equipment tbl (not to be confused with Equipment table, which is grayed out). Did I make any sense? Basically with the results right now you can't tell what equipment you bought for $450 or $8,000 for Project such and such. The Description is needed instead of the Madeup Code to make the query any use. I'm sorry for this change.

 

[GinaWhipp]

Change is not the problem and that is why I am checking so we are on the same page...

Let's start with the beginning.

Do you want to change the name of the Form?
Do you want to change the name of the query?

 

[mayestom]

I guess they should be changed. Right now nothing is dependent on them so it's a good time to change them. Not sure what to call them? Maybe frmMultiSelect and qryMultiSelect? If that's not good, please change to what you think is best.

 

[GinaWhipp]

Oh, those will work..

Me? I thought you were following instructions. Your first task is to change the name of the Form then the Query. Then open the Form in Design View and from the Properties window of the Form on the top line change the Record Source of your Form to the new query name (pick it from the list).

 

[GinaWhipp]

I just noticed you hace Track Autoname on so no need to change the Record Source it should upfate by itself.

 

[mayestom]

I am following instructions but something like the name of the form or query is up to me.

Wait, I'm getting confused. Right now the Record Source for the form is qryDropDownTest? Is that right? If so, what is DropDown qry test? Is that used at all? bc if it's not being used by the form I should get rid of it so I don't confuse myself some more.

 

[mayestom]

Okay, just realized you wrote about the auto update but I was to late. I already changed the form name to frmMultiSelectqry and changed DropDown qry test to qryMultiSelect. I have not touched the Record Source in the form. It says qryDropDownTest. I tried using the form and it still seems to work. So I'm thoroughly confused.

Sorry for confusion, what should I do know?

 

[GinaWhipp]

The one you need to change the name of is... qryDropDownTest

I am not using that other one so you can delete it.

 

[mayestom]

Okay changed qryDropDownTest to qryMultiSelect. And the form name is frmMultiSelectqry. And I got rid of DropDown qry test. Phew!

Now the Record Source in the form automatically changed to qryMultiSelect. Nice, I didn't even know I had that feature selected to track auto update Where is that option btw?

 

[GinaWhipp]

Hmm, that feature is under Options BUT always recommended to uncheck as it has been known to cause corruption, see...
http://www.access-diva.com/d11.html

Okay, I have lost rack, are we adding Description now?

 

[mayestom]

Gottcha.

Yes, would like to add Description field to the query results.