display multiple records in one form

[Kinger43]

I have a form that displays the quantity of tools on each truck. there are a little over 500 tools and some 40 trucks. the table is set up with every tool listed for every truck (500x40 records). I filter the form by the tool I am looking for and I want to display the quantity of that tool for each truck. When I select the tool it filters the table down to 40 records (1 per truck). How do I display all of the quantity values on the form? Is this possible, or will a form only display one record at a time?

 

[heedaf]

A little more detail would help. But, it depends on how you have the form displayed and you could also use subforms.

DeWayne

 

[Kinger43]

Subforms won't work. They must all be displayed at once. Subforms would too much of a hassle.
Essentially the form is laid out with two combo boxes in the form header that are used to select the tool group then the type. The way the database was initially set up there was a field in the record source table for each truck that contained the quantity. Because the truck numbers change yearly it was a pain to go through and change every table, query, report, and form. So I rearranged the table so that there would only be one quantity field.
The form needs to display the tool selected, its price, etc. There is then one text box for each truck that needs to display the quantity of that tool there is on that truck. All of this is in the detail section of the Form. It is necessary to have on one form so if I want to know which trucks are carrying a particular tool, I can pull it up and easily see which ones have it; or if I get a new tool and I am going to put one on 10 trucks, I can easily add the new tool and update the quantity per truck without going between multiple subforms.

 

[heedaf]

I'm having a hard time following what you are trying to do. Is the form based on a single table? Subforms are pretty easy to use and would be all visible in a single form.

 

[Kinger43]

The form is based on a single table. Maybe I'm not understanding what a subform is or how to make one.

 

[Kinger43]

I've done some research and I don't think that subforms are going to work for me in this case. If I could make them work I would have to have 40 forms plus the ones that I already have. What I need to know is this, is it possible to display more than one record at a time from a single table in a single form. I need to narrow the table to the records that share the same tool group and tool type (this should narrow it down to 40 records, 1 per truck) then display the truck number and quantity of all of the records left after I select the tool I am looking for.

 

[Dennisk]

If you can obtain all the data in a query, then you could use ADODB and treat the query as a disconnected table.

i.e. use the query as a recordsource.
then read the data from the recordsource and load into controls on the form.

 

[Kinger43]

Thank you Dennisk, that is what I had started to do. I kept trying to build off of someone elses work and desided to just start from scratch and do it that way. The problem I am running into now is that several fields are coming back with #Name? in the control on the form. I don't understand this as the control source is set to a field in the query.

 

[CraigDolphin]

I'm a bit confused by your question.

It sounds like you have one table with three fields of interest: truckId, toolID, quantityoftoolidIntruckID

If so, then your form's recordsource should be just a parameter query of the table which uses the two combos on your form header as criteria, and selects all three of the fields you want to see.

It should be a trivial matter to simply select the quantity field in the query to bind to a textbox control on your form.

What am I misssing here? Are you calculating the quantities on hand from a transactions table of some kind? Even if you are, you should still be able to create a query that shows the totals for each pairing of truck and tool and use it in the same way.

 

[Kinger43]

In the end, yes it was a trivial matter, the problem was that I didn't start from scratch and do it my way in the first place, I tried to keep things the way they were to begin with. I inherited this database from someone else. I am on to even more trivial problems now about how to make it look good. I solved the problem with the #Name? in the control by closing the database and reopening it. I now have a new problem. I am using a continuous form view so that I can view all records returned by the query. How do I make multiple columns so that I don't have to scroll down to see everything when I open the form?

 

[CraigDolphin]

Maybe create one subform for each 'column'? The subforms would be bound to different queries that each list different records from your main query based on truckID.

 

[Kinger43]

That is much more work than is necessary. I didn't know if there was a setting somewhere that I can't find that would tell it to make a new column after so many records.

 

[CraigDolphin]

I didn't know if there was a setting somewhere that I can't find that would tell it to make a new column after so many records.

I've never found such a setting either. Would be interested to know if it exists myself. The question is, if it did exist, how would it handle more records than can fit in the columns that you could see on your screen at one time? Come to that, even if you implemented my suggestion, you'd need to determine how to deal with that same issue.

That is much more work than is necessary.

Really? Well, let us know when you find that setting then. I'm always willing to learn something new about Access.

 

[Kinger43]

Well, I'm not going to be the one using it, he's a big boy, he can scroll. It's a lot better now than it was; he should be happy.

 

[TRiley0269]

I understood what you said from your first post. I know because I want to do the same thing, but I want to be able to update the quantities. I have four separate inventory sheets for the same parts but for each inventory sheet the quantities and find numbers can be different. After an installation is done I want to know what parts have been used so I will be entering the parts remaining. I always know what parts are shipped, I'm just getting into Access myself and its been a bunch of years since I did any programming.