Display several results!!

[jer]

Hi
Im back again!! So i will dive straight in. I have my db nearly complete but I have to create one more search form. I created a form form my list of tools. Each with a serial no, tool no and unique id. However all these tools are stored in pallets and several could be stored in one pallet!! I setup the form with a combo box. The problem is that in my table i have each tool recorded individually, thus i have the name of the pallet they are stored in recorded with each tool and this causes my combo box to have the same pallet box repeated several times. Im trying to create it in such a way that there could be a hundred tools divided into 10 pallets. thats 10 tools to a pallet. I want the combo box to just show the 10 different pallets from which i pick one and a subform then populats with the tool numbers and serial numbers of the tools within that pallet.. Also is there any way of then clicking one of those tools that appears in the subform to take me to that tools record?? I know its alot but I keep hitting walls on this one!! any help would be great!!

Jer

 

[Mr. B]

I am assuming that you are populating your combo box with the results of a query. If you are not, you will need to. With that query in design view (QBE), display the properties for the query and in the General tab of properties window find the "Unique Values" property and set it to Yes.

This will display only one instance of each of your pallets.

You do not necessarily need a "sub-form" for this. You can just us your form. Make sure that the combo box where you are selected the Pallet is unbound. Then add a reference to that combo box as the criteria for the query that is used for the record source of your form.

Using this method, you will need to have one line of VBA code in the after update event of your combo box:

Me.Requery

 

[smig]

do you put only the palete in the combo query? if so, you can either use the Groping option to group the query by palate or set the query property to show Unique records only

 

[jer]

hi smig
how do i use the grouping option? I have no idea!!

 

[jer]

I have done this. But how do I set it up such dat when i Press on the pallet im lookin for all the tool no of the tools within that pallet make a list on the form?

 

[jer]

Any ideas Mr. B?

 

[smig]

do a search for cascading lists
this one might help http://www.fontstuff.com/access/acctut10.htm

 

[jer]

That link is not working it says service unavailable

 

[vbaInet]

Regarding your first question, don't you have a Palettes table that you can use as the source of your combo box?

For your second question, have a look at this link:

http://baldyweb.com/wherecondition.htm

 

[jer]

i have the combo box working, such that it only reads unique values i.e only each pallet once. The problem i have is i dont know how to display the info. When I click on a entery in the combo box (a pallet) i want a list of all tools in that pallet to appear in the form but i got no clue how to do that!

 

[vbaInet]

Is your subform in Datasheet view or Continuous?

 

[jer]

its in datasheet view. When I select the pallet from my combobox it just gives the first tool in the list of tools meant to be in that pallet and not all of them! Any ideas??

 

[vbaInet]

How many columns does the Combo box have? And what is the data type of the Bound Column?

Do you know how to reference a combo box from within a query?

 

[jer]

Just 1.. It has a list of all the names of the pallets i.e c-c45, c-c46 etc. All data is text format. and i dont know how to reference a combo box from a query...

 

[vbaInet]

Final few questions:

* Is the subform based on a query? If it isn't, it should. Create a query for it.
* What is the name of the field that should link the combo box with the subform?

 

[jer]

the subform is based on a query. the name of the field is "Assigned Pallet"

 

[vbaInet]

* In the query under the Assigned Pallet field, put this:

[Forms]![[COLOR=Red]FormName[/COLOR]]![[COLOR=Red]SubformControlName[/COLOR]]![[COLOR=Red]ComoboxName[/COLOR]]

Substitute the names in red for the right names.
* In the After Update event of the combo box, put this:

Me.[COLOR=Red]SubformControlName[/COLOR].Form.Requery

I'm sure you now know what to do with the red bit

 

[jer]

subformcontrolname... is that the name of the textbox containing the name of the pallet?

 

[vbaInet]

SubformControlName = Subform control name = name of the subform control.

 

[smig]

That link is not working it says service unavailable

It worked for me now