Newb getting confused.

[Iggy]

I am trying to build a database for my PO's and I have almost everything finished and figured out, but I have one thing that is driving me mad. I'm sure there is a simple solution, but I don't know anything about this program. Sound like newb right? LOL



So here are the tables I have. I will put [...] for what ever information is irrelevant.

-Purchase Order
--OrderID
--VendorID
--[...]

-Item
--ItemID
--VendorID
--ItemNumber
--ItemDescription
--[...]

-Vendors
--VendorID
--[...]

-Order Details
--OrderDetailID
--OrderID
--ItemNumber
--ItemDescription
--[...]

From here I have a query to take the order details and add up a subtotal.

The problem I am having is I created a PO form with an order details subform for the items. I can't for the life of me figure out how to limit items by vendor.

Right now I have a dropdown for all the vendors on the PO form. When you select vendor XYZ I can't get the subform to only display items in the drop down that match that vendor. This is my main problem.

My next one is once I get the items filtered by vendor, I would like to select an ItemNumber from the drop down and have ItemDescription match that number and vice versa. Right now I can select a item number and description.

I would imagine this is simple to, and I don't mind reading a tutorial that helps me understand what is going on, but I would appreciate any help in this. I have spent the last two days building this and everything else is working dandy.

Thanks!!
Iggy

[jzwp11]

You will need some code in both the on current event of the main form (your purchase order form) as well as the after update event of the vendor combo box in the main form.

In your order details table, you should just reference the ItemID as a foreign key; as such you would not need the ItemNumber and ItemDescription fields in the order details table.

I have attached an example database that has the code and the table structure changes I discussed.

[Iggy]

jz thanks!

I'll take a look at what you gave me and report back.

[Iggy]

jz thanks again for the help!

I figured this out using another method though. I created two queries, one for PO's and one for the details. I had the query on the details based on the criteria of the PO vendor ID. I put a bit of code to requery the subform when the vendor ID was updated.

I still haven't solved the update both fields issue yet, but i have ItemID displaying the ItemNumber and Description in the combo box.

Thanks!!

[jzwp11]

Just let us know if you need additional help or have more questions.

[Iggy]

Thanks! I really appreciate it. My next challenge is to make a report of a single PO to print for my records. Why can't the reports look like forms too!

[jzwp11]

Unfortunately, reports are handled differently. I generally struggle through reports; it usually take me a while to get them formatted the way I want. My recommendation is to create a query that brings in the same tables used in the form/subform. Then create the report based on that query. You will have to play with the grouping levels to get the report to look like you want. The report will show all records (that is OK). Then on your main form, add a command button that uses the wizard to print the report. You can then edit the code to just print the report for the current record.

The initial code that Access creates via the wizard will look something like this (my report was named myRPT)

Private Sub Command20_Click()
On Error GoTo Err_Command20_Click
    Dim stDocName As String
    stDocName = "myRPT"
    DoCmd.OpenReport stDocName, acPreview
Exit_Command20_Click:
    Exit Sub
Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click
 
End Sub

You will only need to edit the DoCmd statement to reference the primary key of the current record and use that to filter the report. The statement will look something like this (you will have to substitute your own field and control names); I have set the command to do a print preview rather than a direct printing of the report.:

DoCmd.OpenReport stDocName, , , "OrderNumberID=" & Me.OrderNumberID, acPreview

the OrderNumberID refers to the primary key field in the query which should be the underlying field in your form as well
the Me.OrderNumberID refers to the control on the form

[Iggy]

Thanks again! I'm going to give it a try. Can I make a query of a query? I made the PO form using two queries not tables.

Reports should be easy since I struggle through everything. LOL

[jzwp11]

Yes, you can make a query that is based on another query.

Let us know if you are successful.

[Iggy]

jz,

I got this to work. I actually found a nice shortcut that worked really well and was really quick. I first selected the form, since I wanted the report to look just like form, and used save as form.

I created the button and inserted the code like you explained and voila! I have a Report and I can edit that looks just like the form.

[jzwp11]

Glad to hear that you found a solution. Good luck with your project.