Show related data on a form

[Accessgrasshopper]

Hello,



Need help, please. I am creating a sales quote form in order to quote customers various items. I would like to somehow see if I have the item or items I will enter on the sales quote form in stock. Is there any way to display the inventory data from the inventory table on the sales quote form so I can see if I have the item that I am quoting?

Any help is appreciated. Thank you.

Accessgrasshopper.

[wdrspens]

Assuming the two tables are linked with a relationship, probably one to many, then use a button to open a form showing the inventory details of the item chosen in the main form.
Insert a button on the main form and follow the instructions to open a new form (In the Command Button Wizard, chose Form Operations and the choose Open Form, select the Inventory Form, tick the "open form and find specific data to display" Select the field and click the related button, decide how you want the button displayed and give the button a name).

[Accessgrasshopper]

Thank you WDRSPENS,

I appreciate you taking the time to respond. Yesterday while I was toiling into the wee hours of the night I stumbled upon a solution. I created query using three tables including the sales quote table and the inventory table in order to pull the specific item on the inventory table and display it as a second subform on the main form.

It worked quite nicely. The only problem that I am having now is that I have to figure out how to requery the subform (that is based on the query) so that after I enter the item PN on the sales quote subform, it will update and display whether I have stock, or not.

I could not get the field in subform1 to recognize the field in subform 2.

Any help would be appreciated.

Accessgrasshopper.

[wdrspens]

I am sorry Accessgrasshopper it will be necessary for someone else to help you as the question you now ask is beyond my comfort zone. I assume you have done the obvious and ensured that the necessary relationships are correct.

[Accessgrasshopper]

I am sorry Accessgrasshopper it will be necessary for someone else to help you as the question you now ask is beyond my comfort zone. I assume you have done the obvious and ensured that the necessary relationships are correct.

Hi wdrspens,

Thank you for your assistance anyways. I was able to figure this one out too, but it was a tough one. Stayed up til 3:00 am. There are pages and pages all over the internet with people trying to resolve this problem. I got lucky that I found a piece of missing code from an Access user in Australia.

I will post my solution here for anyone else who is having this issue. Basically you have to enter the your code in the after update event field of the combo box of subform1 so that it will trigger the result that you are looking for in subform 2. The trick is that Acces does not recognize subforms as forms. Instead it looks at them as controls and consequently they need to be referenced correctly in your code composition, or you will get the dreadful error message saying Access cannot find or recognize the field. Well here it is:

Private Sub PN_AfterUpdate()
Me.Dirty = False
Forms![sqform]![testq].Requery
End Sub

The above code is in the PN Combo Box Field of Subform1. I was missing the second line "Me.Dirty=False" without this it will not update the subform2 on the fly. Also, another important piece of information; for Access to see your subforms, you need to first reference your main form in your statement. In my case it's Forms! [SqForm]!. Where SqForm is my Main form; top level form. Then you list the subform2 as shown in my statement and Access will see it and requery it. There is no need to list subform1 which is where you are entering the code in the corresponding combo box.

I hope this helps someone else.

Regards,

Accessgrasshopper.