Switching from a Query to Form

[mrt1069]

I'm relatively new to access and I'm having trouble figuring out if my idea is possible



Currently, I am having to run a query in access in order to look up individual items and I would like to be able to put all of the items into a form and have a report display all of the query results for each item.

Here's a more specific sort of example of what I'm talking about: I'm a purchaser for a company and we use access systems to log all of our purchases. When I need to buy a new item, I get our part number and run a query on the part number for the following information: Supplier Name, Date Purchased, PO Number, Price. I then have to record all of this info a move on to the next item.

The requests I receive generally have many many items on them, so I would like to be able to just put all of the part numbers down in one place and get all of the results back at one time so nothing gets lost along the way.


Can anyone give me some ideas about how I might go about doing something like this?

[ranman256]

I have a list of clients, (continuous form)
user picks the client, clicks a DETAIL button on the header,
this opens that client in a single record form, with sub form: docmd.openform "frm1Client",,,"[clientID]=" & txtID

now the 1 client detail is in the top part of the form, name,addr,etc,
the subform has either, purchases, history, etc.
I can change the sub form via a combo box, or TAB control.
the tab control has 3 or 4 tabs, labeled Purchases, Billing, Contacts, Calls.
this swaps out the sub form (can also be done with a combo box)

sub cboBox_Afterupdate
   select case cboBox
         case "Purchases"
              subfrm.sourceobject = "frmPurchSub"
         case "Billing"
              subfrm.sourceobject = "frmBillingSub"
  end select
end sub

[mrt1069]

Thanks for the quick reply!

hmm, I could probably do something like that and just swap out the client name for the part number and have the details display the purchasing info.

How do your users pick the client?

Also, I had another idea that I wanted to try out that I was looking for some help with.

I would like to first make a table/form where I would enter all of the items needed for purchase. Then, I would like to create a report that prompts for a supplier name/number and then generates a report based on the items that the supplier could supply.

For example. Let's say I'm buying 10 Piece of copper and 10 Pieces of stainless steel and 10 pieces of carbon steel. Supplier 1 only supplies stainless and carbon steel and supplier 2 only supplies copper and stainless steel. I would like to be able to generate a report that prompts for the supplier (1 or 2) and then creates a report showing their respective items.

Does this make sense? I've spent days trying to figure this out, but it seems over my head.