Searching for Data

[jimbo1283]

Hi everyone.



I'm completely new to Access and have no previous experience, so apologies for any daft questions or for my complete lack of understanding.

I'm creating a database using the services template in access 2010. I'm looking to be able to quote products for customers and track these quotes. I've managed to get my self through most of this and have changed the basic template quite a bit to fit in with what I need it to do. I'm struggling a little when it comes to the products though.

At present we have something close to 900 products. The quote form on the template lets you select the item by product code and then fills in the details on the rest of the form. It allows you to search by product ID and lists the description to the side. As per below


What I'm trying to do is get it to search by either the item code or by the default description and then populate as before.

If it helps, this is the text that I found in the property sheet under row source,

SELECT ProductLineItemCodes.ID, ProductLineItemCodes.LineItemCode, ProductLineItemCodes.DefaultDescription FROM ProductLineItemCodes WHERE (((ProductLineItemCodes.LineItemCode)=[LineItemCode])) OR (((ProductLineItemCodes.LineItemCode)=[DefaultDescription]));



Can anyone point me in the right direction of how I can do this. Please bear in mind my complete lack of knowledge.

Thanks in advance

[Micron]

Looks to me like you have a 3 column combo and are using the first column (ProductLineItemCodes.ID) as the criteria when you want to use the second (ProductLineItemCodes.LineItemCode). Look at the property sheet for the bound column property. In 2007 it is under the row source type, so they must have moved it in your version. Try setting it to 1. A combo box column count is zero based, so the first column is 0, not 1.

[jimbo1283]

thanks for the help on this.

I found the column property bit. It's in the format tab on the properties sheet. I changed this from 3 to 1 as you suggested however this just removed the product code and also the description from the drop down box.

On the quotes that already have data in, it uses the ID, eg 1,2,3,4 or 5 etc instead of the product code.

It's letting me type the start of the product code and filtering down this way but not by the description. I've read somewhere about using "like" but I can't figure out how or where this should go?

[ssanfu]

I downloaded the Services 2010 template but it appears to be a web dB. I didn't spend much time on looking at the objects, but it made me want to pound my face against a wall.

Kudos to you for being able to modify the dB as much as you have!

So you have

SELECT ProductLineItemCodes.ID, ProductLineItemCodes.LineItemCode, ProductLineItemCodes.DefaultDescription FROM ProductLineItemCodes WHERE (((ProductLineItemCodes.LineItemCode)=[LineItemCode])) OR (((ProductLineItemCodes.LineItemCode)=[DefaultDescription]));

Notice the RED text. Wherever you got this SQL, I would think the "LineItemCode" would never equal the "DefaultDescription".

[Micron]

I found the column property bit. It's in the format tab on the properties sheet. I changed this from 3 to 1 as you suggested

I think you changed the column count, not the bound column as I suggested. Changing the bound column would not do what you described. However, ssanfu has a good point...

[jimbo1283]

I've been tempted to throw the laptop at the floor many a time so far.

The code part is copied exactly from the system how the database is. I'm scared to change much as every time i do it either stops displaying the item or wont populate the rest of the fields next to it.

I'm at the point where I'm thinking of paying someone to do this but I really don't like admitting defeat.

Micron. I'm strugglingto find where the bound Column section is.

I downloaded the Services 2010 template but it appears to be a web dB. I didn't spend much time on looking at the objects, but it made me want to pound my face against a wall.

Kudos to you for being able to modify the dB as much as you have!

So you have

Notice the RED text. Wherever you got this SQL, I would think the "LineItemCode" would never equal the "DefaultDescription".

[orange]

One thing you could try is making a copy of your database, do a compact and repair, then zip the file and attach to a post.
Readers could look at exactly what you have and make suggestions/confirm some ideas/code.

Is this suppose to be a web application or desktop?
If you want to find/search by description, then you need a construct in the query to do so.
As ssanfu says, it is highly unlikely that productcode will ever match default description.

[Micron]

As ssanfu says, it is highly unlikely that productcode will ever match default description.

Unless the code is the default description.
Your version might look a little different, but my bound column property is here on the property sheet for the selected combo box when the form is in design view:

Your original post suggested to me that when you made a combo box selection, you were not getting the result you desired. While it is probably not always true that the cause is the wrong column is being used for whatever comes next, it is a very likely one. Consider the suggestion to compact/repair, zip and upload. That is probably the point where I'll have to drop out as I am stuck in v 2007 here at home. Often I cannot open newer versions posted here, and I think you are having enough trouble without suggesting that you strip out whatever prevents me from opening a db. Sometimes I think about spending $110 to upgrade, but I figure volunteering shouldn't cost dough as well as time!

P.S.: Throwing the laptop might cost more than hiring somebody, but then again, you'd have to get a new one. Hmmmm...

[jimbo1283]

One thing you could try is making a copy of your database, do a compact and repair, then zip the file and attach to a post.
Readers could look at exactly what you have and make suggestions/confirm some ideas/code.

Is this suppose to be a web application or desktop?
If you want to find/search by description, then you need a construct in the query to do so.
As ssanfu says, it is highly unlikely that productcode will ever match default description.

Quote Database.zip

I've attached the database.

It will be going onto a sharepoint folder. I'm under the impression that it will work from individuals desktops and then update on the sharedrive in the background?

Unless the code is the default description.
Your version might look a little different, but my bound column property is here on the property sheet for the selected combo box when the form is in design view:
Attachment 24396
Your original post suggested to me that when you made a combo box selection, you were not getting the result you desired. While it is probably not always true that the cause is the wrong column is being used for whatever comes next, it is a very likely one. Consider the suggestion to compact/repair, zip and upload. That is probably the point where I'll have to drop out as I am stuck in v 2007 here at home. Often I cannot open newer versions posted here, and I think you are having enough trouble without suggesting that you strip out whatever prevents me from opening a db. Sometimes I think about spending $110 to upgrade, but I figure volunteering shouldn't cost dough as well as time!

P.S.: Throwing the laptop might cost more than hiring somebody, but then again, you'd have to get a new one. Hmmmm...

Apologies for the misleading original post.

The results and the data that it finds are correct. What happens when you try to select a product it that you can start typing the code for example 123.... and it will start to find these. What i want to be able to do is start typing the code OR the Description and then fill in the line as it does at the moment.
So if I had a product that had code 1234 and description Test product.

I could type either 12.... OR tes... and it would find the product. At the moment I can only type the code part.

I hope this makes sense.

[Micron]

As I expected, I can't open your db.

when you try to select a product it that you can start typing the code for example 123

If you already have the behaviour with one combo, it's likely that you have the Auto Expand property set to Yes (Properties>Data Tab v2007). Both controls need to have that setting, BUT you'll have to clear what's in the one control at the start of typing in the other. Otherwise, your filter is probably trying to return records using both controls at the same time. Hopefully someone else will take over for you since I can't open your db. Sorry.