subform help

[guitarstar_86@h]

I'm fairly new to access, so bear with me. My problem concerns 2 forms.

FrmItem contains all details of products in stock - ie Item No, Description, Qty in Stock, Re-Order Qty, etc.

FrmRequest is a basically used for transactions - when stock is taken away. Additionally, it contains a subform, subItem.

What I require is as follows -
The user selects an Item No from a combo box in FrmRequest. All information held on that item is then displayed in the subform, SubItem. This way, the user can see all the information about a product before they make a 'request' for it - Quantity In Stock, Item Description, etc. I know this is possible as I got it to work in a prototype form, but I haven't been able to replicate it in my actual final system. The rest of the form appears to work fine.

Any help would be greatly appreciated.

James

 

[nishantd]

James,

I think this is what you are trying to do, but correct me if I'm wrong. You have a table that contains information about particular items. You are trying to achieve the following:
Pull up information on an item based on a selection in a list box. If there is sufficient stock, then proceed with a request.

If you have just one table (not sure if you do), then you could create a form from that paritcular table. Then, you can place a combo box or listbox and use the combo or listbox wizard to create a combo/listbox for you. WHen you do this, you want to select the "Find a record based on the value I select in the box" option within the wizard. You then specify the values that will populate that combo or listbox. When you finish the wizard, it will put a combo or listbox on your form and when you select information from that combo or listbox, the current form will "jump" to the record that contains information related to the selection.

I'm not sure if you have more than one table here, but it seems you just have one table that contains all information with regards to an item.

nishant

 

[guitarstar_86@h]

Thanks thats kind of it! I now have something to work from! Basically, thats along the lines of what I want it to do.

But thats not it exactly...I need it to show the records from a different table - 'tblItem'. How can I make it do this?

 

[nishantd]

James,

Assuming you have relationships set between these tables, the form wizard can you help you again. So, if you have relationships set, access the form design wizard. You will then be asked to select the fields you want to display on your form. You can select items from tableA and then also select items from tableB. When you hit Next, I believe you'll be asked to select how you would like to display the form. Here you want to select "Form with Subform(s)". Hit next and finish out the wizard. Now, Access has created a form with a subform and has taken care of the linkage in between the two forms. Now, you could go back and add a combo or listbox on the main form (tableA in this example) and tell the wizard to "Find a record based on the value I select". Now, this combo or listbox that was added, will allow you to select values and then the corresponding items will appear in the subform based on what is selected.
You'll need to make sure you have relationships set up for this.

Nishant