Creating an order form with multiple items Results

I am trying to create an order form which would list all the furniture/items that go in all the different room/area types for a hotel.

It would list all the different categories that each of the items fall underthe furniture that goes in that appropriate category w/the room/area that they each go in
(list furniture/item once w/room/area under it, along w/quanity required for each area) Attached Files (187.6 KB, 6 views) Reply With Quote 06-18-2012, 08:48 AM #2 Bob Fitz MS Community Contributor Windows XP Access 2003 Join Date May 2011 Location Essex UK Posts 806 Can you attach a version of your db in mdb (A2003) format


I'm working on a new Access project that needs to have a control that will be bound to a decimal number stored in a table, but display the number converted to english distance units: 19.625 displayed as 1'-7 5/8".

Here's what's been done so far:
I have created a simple activex control in VB6 using 2 textboxes. Normally, one is not visible (it contains the decimal value and is bound to the field in the table) and one that is visible (displays the formated number). When the control receives focus, it swaps the visiblity of the two fields, allowing a new decimal number to be entered. When it loses focus, the formating takes place.

Here's the problem:
I'd like to have multiple instances of this control on a continuous form that acts as the subform portion of a form/subform relationship. MS Access does not allow Bound Activex Controls to be used on continuous forms. The project really requires continuous forms to be effective. It involves a designer creating an order consisting of multiple line items, each of which is a varying length of building material.

Sooooo . . .
I either have to come up with a way to format a distance to make it easier read without having to use an Activex Control, or find a way to incorporate the control into something other than a continuous form and acheive the same type of master/detail relationship that a continuous subform allows.

If any body has any suggestions or ideas, I'd really appreciate the help.


Hello Everyone,

I am having a bit of an issue trying to get an order form to function properly with a subform.

I have several tables designed around various things, that will be utilized in an order.

ProdList (Contains ID, ProductName, ProductPartNo, ProdDesc, BrandID, CategoryID)
ProdBrand (Contains ID, BrandName)
ProdCat (Contains ID, CatName)
OrderHeader (Contains ID, CustID, DateMade, StatusID)
OrderDetail (Contains ID, OrderID, ProdId, Qty, UMID)
UnitsMeasure (Contains ID, UnitMeasure)

What I am trying to create is an order form for multiple rows

The user loads the customer information above and then enters in the items they wish to order

[Qty of Item] [UnitMeasure *Dropdown*] [BrandName *Dropdown*] [CatName *Dropdown based on BrandName Selected] [ItemName *Dropdown based on CatName Selected*] [ProdPartNumber *Based on ItemName*] [ProdDesc *Based on ItemName*]


Any thoughts on the best way to go about this and allow them to enter in new OrderDetail rows, while utilizing information across multiple tables?


hey there just a weee quick question, i am creating an order tracking database, now i have a form with all the customer details on it. then i have a subform which displayes all of the previous order information. now the link between the customer and order is the customer ID, which is unique to ech feild. and in the order subform the order number is unique... however, sometime there cane be multiple orders made @ one time...e.g a man orders 3 jumper and 2 pairs of trousers, i want these to have teh same order number but to be displayed as to diffrent items on the order form... is there any way to do this.. cheers


Sorry for loading so many questions into one post but i'm getting inundated with problems. I basically devised a system for work to log orders for materials from numerous suppliers. as i've used it i've improved it, then re-written it and now i'm halfway through creating the third version.

in my earlier systems i had an orders table with space for 6 items, in the form of category, item, quantity, unit, rate - category2, item2, quantity2, unit2, rate2 etc etc. however this was prohibitive in only having 6 items per order and also made queries to search for prices very complicated. as such i've adopted the northwind style order form with a sub form for the details and the generic aspects e.g. supplier, phone number etc in the main form. the problem i have now is that due to the number of items i have to order i'd grouped them by category , whereby the category was selcted and the afterupdate field generated the row source for item.

using the sub form method when i enter the second category it updates the item field and removes my selection in item 1 (well clears the display but the data remains in the orders table) i need to be able to flick through the orders and see what has been ordered etc so this isn't suitable. is it possible to categorise my items any other way bearing in mind that it will be in a sub form later on, i considered putting my items in different tables according to category however i'm unsure if this would be in keeping with a good structure for my database.

my second issue is with my query to look up telephone numbers. for example in my order form i input supplier a and site a - i hit a button and it runs a query searching for previous instances of this combination and returns the phone number used (some suppliers will have different numbers for different sites etc). the trouble is if i place an order with the same supplier for the same site more than once i get a mass of identicle phone numbers returned - is there a way to filter out identicle results?

finally with my old system i created a query to find orders relating to specific sites, in the results i got a one line response for every order. with the sub table system as described i now get a line for each item ordered. i.e. if order no. 003 had 12 items on it i'd now get 12 lines in the results relating to the same order - is there a way to make the results collapasable, whereby they expanded to show all the items if clicked etc.

It seems I continue to run into this problem.

A company sells products in packages.

Example: Let’s say you build PCs

All of your PCs are sold as a complete product with 10 or so components

Your choices are to reenter every item each time you make a new quote, or automate the process.

This is the current method

Create multiple tables identical with the orders detail table, and create all of the records needed to satisfy the order packages. Each table is one order package.

Step 2:
Create an order as you normally would.

Step 3:
Change the linking number in the package table (to the appropriate order number)

Step 4:
Paste the contents if the table into the Order Detail Table.

This works well but is labor intensive, and is not error proof.

So finely the question. Is there a way to automate this process with the selection being made form either a drop down list or a query .

My gut tells me it a macro thing but I don’t use macros for much and my VBA skills are limited to adapting code that I pick up here and there.

Any guidance would be greatly appreciated.


Good afternoon. I am trying to figure out how to create an order summary or packing slip style report. I have an order number that the information will be sorted on and I need to list all items purchased under that unique order number. Each order number can have multiple items and each item is entered individually. I have been unable to find a way to do this. This report will also be printed out on a preprinted form. I have been unable to get the some of the fields to remain in the same spot. If they move then it will throw off the alignment with the form itself. Can anyone help?


Hello Everyone,

I am having a bit of an issue trying to get an order form to function properly with a subform.

I have several tables designed around various things, that will be utilized in an order.

ProdList (Contains ID, ProductName, ProductPartNo, ProdDesc, BrandID, CategoryID)
ProdBrand (Contains ID, BrandName)
ProdCat (Contains ID, CatName)
OrderHeader (Contains ID, CustID, DateMade, StatusID)
OrderDetail (Contains ID, OrderID, ProdId, Qty, UMID)
UnitsMeasure (Contains ID, UnitMeasure)

What I am trying to create is an order form for multiple rows

The user loads the customer information above and then enters in the items they wish to order in the paired subform

[Qty of Item] [UnitMeasure *Dropdown*] [BrandName *Dropdown*] [CatName *Dropdown based on BrandName Selected] [ItemName *Dropdown based on CatName Selected*] [ProdPartNumber *Based on ItemName*] [ProdDesc *Based on ItemName*]

Extra information on the Fields

Qty is a text field, which will save on the Order Detail table.

Units of Measurement is a dropdown field (populated from the UM table) now this field will need to be saved on the Order Detail table but as the ID not the displayed name. Example - UM table has Case, Each, and lets say Gallon. If a user enters in a new item for the order and selects Case, I need to take and store the value as 1 on the Order Detail table (2 for each, 3 for Gallon).

BrandName is a visual dropdown listing off the Brand Names from the ProdBrand table. It has no value to be stored on the Order Detail table but will need to be used for populating dropdowns.

CatName is a visual dropdown listing off the Category Names from the ProdCat table that match the BrandName previously selected. For example, Brand A contains Cat 1A, 2A, 3A. While Brand B contains Cat 1B, 2B, 3B. If I select Brand A in the previous dropdown, I want to only see Cat 1A, 2A, 3A for this dropdown (Outside of a subform I have already gotten this to work via VB-script)

ItemName is a visual and stored dropdown listing off the various items in the Category. The displayed value is the name of the item, however, it will need to store the ID of the item on the Order Detail entry. For example, Item A has an ID of 6 and belongs to Category 1A. So if the user previously selected Brand A and Category 1A, they should be able to select Item A off a dropdown list. When saving the order, it needs to enter the ID of 6 into the Order Detail table.

Product Description is a non-editable text field that displays the Description of the product as provided by the selected Item A. For example: Once the user selects Item A, I want to display Item A's description (held on the same row as Item A in ProdList) on the following field. This value doesn't need to be saved anywhere.


Basically what I am trying to do is create an area to enter in line items for an order.

(Example, Order #1 has 5 different items purchased, I want to save on OrderHeader, the order itself, then on OrderDetail save all 5 lines of items associated to Order #1, the order number.)

Any help/programming would be greatly appreciated, this has been a long standing issue for me, and I need to get it resolved. I have tried numerous things to no avail whatsoever.

Thanks again,

OK guys, here is the case:
I want to create a 'Purchase orders' Form. the form would look something like this:

Item number. Item description. Item quantity. other notes.

now, the purchase order could contain multiple entries(Items), but not specific to a certain number. like for example, in a single order you could place one item or two or five or ten. I could create a specific number of text boxes for the user to enter a certain amount of items, like seven or ten but this would be inefficient, non-practical and would clutter the form.

So, what would be the best practice of creating such a form?

Hi everybody. This is my first post here. Thanks to all who donate their time and knowledge.

I work at a dry cleaner. We still use 3-part carbonless forms to hand enter order details and amounts. There is no record keeping to speak of, not even customer info; I have to ask repeat customers for their phone number every time. So I have a used Windows 98 PC I want to put up at the shop and start a database.

I started by creating a customers table, and an invoice table. They are related so that my form has customer info with a subdatasheet with their invoice number, date of drop, total price, yes/no paid, and date of pick up as fields. This all works pretty well; I would just enter that data from the invoices at the end of each day.

I would like to take it a step further and enter the actual order details (2 pants, 3 shirts, etc.) and the subtotals directly into the database with these order details as a subdatasheet of the invoice subdatasheet.

My problem is with trying to look up the price of a garment as I select the item from a dropdown list. I have an Items table with fields ItemID, Item, and Price. And I have an OrderDetails table with Quantity, Item, and Price fields. I have entered about six items and their prices to get started. I have created a lookup list based on the Item column in the Items table, but I am unable to get the price to automatically populate the Price field in the OrderDetails table. (Eventually, I will have to figure out how to calculate the Quantity * Price to get a total and then relate it to the proper invoice number and proper customer, but I can't get past the dlookup).

I have created a form from the OrderDetails table. In design view, I right click on the Item field, go to properties, event, afterUpdate and enter this:

Private Sub Item_AfterUpdate()
Price = DLookup("Price", "Items", "Item=" & Item)
End Sub

My research shows this is the way but I get multiple error messages. I even sometimes just get the name of the item instead of the price. I have tried adding [] around some fields, to no avail.

Any ideas? Thanks for the help.

Best wishes, Keith.......

Hi all,

I'm looking to create a stock control log where by we can record goods moving in and out of our company. We are a Not for Profit organisation that looks after Video equipment for schools and colleges and so have many items coming for our suppliers and moving to and from various studios. We need to keep track or ALL individual serial numbers. We also have a need to link in test records on this equipment.

The built in inventory DB in Access 2007 is quite good, but I'm sure there would be a way to add a sub-form/table to the "Inventory Transaction List" whereby multiple items in/out can have individual serial number recorded (using a hand-held bar code scanner). Of course, I could just add one line in this table for each individual item received or sent, but this will be time consuming, especially if we have multiple items (or the same type - e.g. 20 specific camera models) move at one time. We don't really need to track re-order levels but again we do need to track where an individual item is and any given time.

On top of this we, test these individual items to make sure they all work properly. These test are relatively simple but are different for each item - e.g. a Camera will be tested for Pan/Tilt (up/down/left/right), Zoom in/out, and focus; whereas a screen may be tested for showing an image at specific resolutions.

I was not sure if I should simply put all possible tests for all items as individual field in a single table, but this would be very wasteful of space. Better (I think) would be to create individual tables for tests and then somehow link these to the sub-table of the "Inventory Transaction List" so that only the correct test will be shown for the item concerned. It guess it would be possible to Use the "Category" field in the "Inventory" table to link through individual items to the correct test schedule.

I'm thinking as I type this how it might be possible, but my first concern is to get multiple entries of same type items with individual serial number working. I am very rusty in this department.

Help please...

New here, so if I posted in the wrong forum, please to forgive.

Here is the question in a nutshell: How do you have duplicate entries for a primary key made up of two foreign keys when the data type is an autonumber field in Access 2007? If possible, does it require the use of VBA code or can it be done with a query?

I'm trying to store multiple items into a single record on a junction table (tbl SalesOrderDetail) that uses the primary keys from two other tables (SalesOrderID from tbl Sales Orders and ProductID from tbl Inventory). I have the data type of SalesOrderID from tbl SalesOrders being generated as an autonumber field then being stored in the junction table as one half of the primary key in a number data type field, do-able so long as both fields have long integer field sizes, which they do.

I want to be able to generate the autonumber from SalesOrderID on tbl SalesOrders and have it remain static in tbl SalesOrderDetail in order to be able to store multiple items from the inventory onto a single sales record with that number in the junction table. My assumption is that because tbl SalesOrders is generating the autonumber and tbl SalesOrderDetail is storing it as a general, static number that it will NOT re-increment upon said storage to the junction table but merely store the already generated autonumber.

I also want to mirror this exact scenario on the purchasing side.

Basically, I'd like to tie this situation (both sales and purchasing) to corresponding data entry forms that, when opened, snap to the next autonumber in the sequence at the top of the form, along with the general details like date, customer ID, etc., and allow the user to enter the product details in a continuous datasheet subform below that that is linked to the junction table via either a query, VBA code or some other mojo I'm unaware of and populate tbl SalesOrderDetail consistently in the above stated manner.

Will this keep the records simple, neat and clean or will it create a clusterf*ck? Is there an easier way to tie multiple inventory items to a single sales/purchase record in Access 2007?

Thanks for any help...

Los Angeles, CA

I have a db with various tables. The main table 'ProductOrders' holds the majority of info (there are others linked to this but for the purposes of this question I doubt have too much relevance).

The db allows users to place orders for items to be purchased; these can be one item or anything up to 20. I need to assign a common number (or id) to any group of orders, i.e if someone orders two items, these need to be grouped; 20 items, these would be another group and so on. I have set up a table called 'Group' with a primary key autonumber field and a field in 'ProductOrders' called 'ProductGrroupOrders' with the intention to create this link and record an 'instance' in the 'Group' table but not sure how to proceed from here.

The user places items onto a pad from a form (merely an aesthetic aspect) and then once finished clicks a button 'Place Order' which whilst the order has already been entered into the table performs additional operations such as firing off email notifications to the respective people who need to administer the orders etc. Is it at this point I need to assign a number/unique ID for the preceding items? Or is there a way of simply linking these two tables (one to many from the 'Group' table) which will automatically provide a unique reference?

I'm sure it should be more simple than I'm thinking it is. Anyone able to point me in the right direction please?

I realize this is an issue that has been touched on a number of times. Unfortunately, I have no been able to secure a working result, and I am desperate to find a working solution. I am running Access 2007.

The end result of the project is to create a summarized report of data types from a linked SQL database.

I have the following elements:
Three linked SQL databases:ClientDB ProjectDB ItemsDB A form ("Job Selection Form") containing:One combo-box (single select) that lists clients from ClientDB. A change in the chosen client results in a load of ProjectsDB for the client, read from the ProjectDB database into: A simple multi-select listbox with two fields (ProjectID,ProjectName) named JobList The form works as intended. A SQL query that is intended to utilize the selected ProjectID fields, and pull only the appropriate summary data from the SQL database. (broken) I have created a Query that reads as follows:

	SELECT Extension, Count(ItemID) AS FileCount, Sum(PageCount) AS Pages, Sum(ItemFileSize) AS SizeKB
WHERE ProjectID IN(GetCriteria())
GROUP BY Extension
ORDER BY Extension;

After examing a ton of example code, including this site, I've utilized the following for the GetCriteria() function, placed in a module:

	Public Function GetCriteria() As String
    Dim stDocCriteria As String
    Dim VarItm As Variant
    For Each VarItm In Forms![Job Selection Form]!JobList.ItemsSelected
        stDocCriteria = stDocCriteria & Forms![Job Selection Form]!JobList.Column(0, VarItm) & ","
    If stDocCriteria  "" Then
        stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 1)
        stDocCriteria = "True"
    End If
    MsgBox "GetCriteria: " & stDocCriteria
    GetCriteria = stDocCriteria
End Function

The function works as intended when I only select one project. However, when I select multiple items, I get NONE of the results from the database.

If anyone has any suggestions, I would very, very much appreciate the assistance.

Hello all, I've got a small problem with multiple subforms in Access 2003 (tied to an SQL backend):

I have three tables, Jobs, JobsSub, JobsDetails for a project tracking database I inherited. Three forms are tied to those tables, which I've renamed frmJobs, frmJobsSub, and frmJobsDetails, and they are nested in that order.

This database has run fine for a long time with this arrangment, but I accidentally discovered something today as I was working with the form layout to make the interface a little cleaner.

Portions of all three forms are displayed at once inside frmJobs with button/tabs hiding the least important data. Logically, the user would enter info into frmJobs, which creates a unique ID based on an Autonumber. From there, they click farther down into the frmJobsSub to enter information regarding a specific instance of work at a project site (which Autonumbers a new SubID), and then from there they go to the frmJobsDetails to enter line-items of what is to be done.

If, however, this is not done in order, the frmJobsDetails does not "inherit" (for lack of a better word) the SubID from frmJobsSub, and even though the data is stored, the connection to the proper SubID is lost.

I cannot come up with a way to programmatically assign a new SubID (in tblJobsSub) if the user skips the frmJobsSub fields and goes straight for the frmJobsDetails.

Does this make sense?

Keep in mind I've never taken a VBA class, and am self-taught based on help screens and google searches. So, speak slowly, I'll try to keep up.

I appreciate all help in advance.

In VBA I have a procedure that while it is looping through 2 record sets it adds new records to another SQL database table.
I want to move this program to a web application using (visual studio 8) however as I am rather new to I donot know what is the best method I should use and how to go about it. Below I have incuded a simplified example of my code in VBA, I would really appreciate it if someone can help me in the right direction, including an example.

The function is looking at a table with multiple machines in a quotation (TBLCOMPROOM)
It is then creating a service schedule in hours based on the service items in the Service Table(TBLTEMPSERVICEPARTS), into a Temporary Calculation Tample (TBLTEMP)
It is looping through the TBLCOMPROOM to find the first machine and its parameters
It looks up the service parts in TBLTEMPSERVICEPARTS gets the first part, looks at the hours and adds a new Record to TBLTEMP it will then loop through the service duration and add the same part with the next value of Service Hours until it reaches the end of the service duration, goes to the next part and does the same thing. When it finished this loop it will select the next machine, find the first part etc etc. (the loops in this example or not 100% correct as I had to remove a lot of fields to shorten the example)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim f, g, h, start As Double
Dim sqldata. SQLCompRoom As String

Set db = CurrentDb()
SQLComproom = "SELECT TblCompRoomContract.comprooomid, TblCompRoomContract.quoteref, TblCompRoomContract.runhours, TblCompRoomContract.starthours, TblCompRoomContract.Actualrunhours " & _
"FROM TblCompRoomContract " & _
"WHERE (((TblCompRoomContract.quoteref) = '" & [Forms]![frmquote].[Quotenumber] & "')) " & _
"ORDER BY TblCompRoomContract.comprooomid;"

Set rs1 = db.OpenRecordset("TblTemp", dbOpenDynaset, dbSeeChanges)
Set rs2 = db.OpenRecordset(SQLComproom, dbOpenDynaset, dbSeeChanges)

If rs2.EOF = False Then
Do Until rs2.EOF '1st loop
varcomp = rs2!comprooomid
Start = rs2!starthours
Averagerunninghours = rs2!runhours
Actualrunhours = rs2!Actualrunhours
g = 0
g = (ContractLenght * Averagerunninghours) + Start

sqldata = "SELECT TblTempServiceParts.* FROM TblTempServiceParts WHERE (((TblTempServiceParts.comproomID)=" & varcomp & ") AND ((TblTempServiceParts.Use)=True));"
Set rs = db.OpenRecordset(sqldata, dbOpenDynaset, dbSeeChanges)
Do Until rs.EOF 'rs!comproomID varcomp And Not rs.EOF ' 2nd loop
'Do Until rs.EOF
h = 0
f = rs!servicehours
If f g
If h = 0 Then
rs1!Hrs = f
rs1!Hrs = h
End If
rs1!comprooomid = rs!comproomID

End If
h = rs1!Hrs + rs!RepHours '+ f
Loop ' 2nd loop
End If

End If
End If
Loop ' 2nd loop
End If


Attached is a modified version of the Goods database from Ms.

For the moment, the tool is invoicing per order, but I would like to build the structure so that per item invoicing becomes possible. For example one order would have 3 invoices. The user should be able to select items to invoice and change quantities (from an invoicing form that would link to the order). The way I was contemplating that is with a new query or a buffer table but I am not yet sure on the way to build the whole thing.

June7 was suggesting:

Possibly control this in report set up as invoice document. Design report to force new page after each item. Good topic for a new thread when you have specific issues with report design.
Discretionary selection of items to invoice requires applying filter criteria. Maybe a Yes/No field in table. Invoice items checked yes?

Any advice? Other ideas?

Attached Files (660.6 KB, 31 views) Reply With Quote 11-13-2012, 12:42 PM #2 rpeare VIP Windows XP Access 2003 Join Date Jul 2011 Posts 1,917 I can't read 2010 files but I'd think you'd want to mark the line items with the date they were invoiced and that would become your identifier if you, say, wanted to reprint an invoice for a customer or print an invoice for any 'new' line items (items that didn't have an invoice date or pick from a list of items that didn't have an invoice date). I don't think there would be a foolproof way to handle creating invoices that didn't bill the same customer multiple times for the same charge if someone accidentally reprinted an invoice.

Good afternoon everyone!

I'm new to the forums and somewhat new to Access. I took a college course several years ago on Access 2003 and I've forgotten a lot of that information. Right now, where I work, someone on my team designed a Consumable Inventory system in Excel. We are able to scan someone's badge, place an order in for supplies, and it logs the transaction and the cost associated to that person's ID number. Although, it's not too bad, I think that for what it should be doing, it needs to be made in Access. I also want to tweak it to run certain reports, and measure restocking levels.

I have the ideas on how I want it to work all written down, but I lack the knowledge on getting started with Access. So, I would like to ask for any advice or help on what to do.

The inventory design I have in mind should open up a Form that looks like a Login Page. It should contain a single text box that if our team can enter or scan an Employee ID number (Oracle number it's called at our work) and it will automatically bring up a Supply Order Form that's linked to that Oracle Number. It should display "Employee First Name, Employee Last name" - "Oracle Number", "Department" in the Form Header. The login page should also have a button labeled, "Admin" where it unlocks the program and allows our team to edit any information, such as adding new employees, adding new inventory, updating Cost of Unit, etc.

The Supply Order Form I had in mind should be like ordering online. It will contain a list of all the supplies we have in our office available to sign out, and have check boxes so the user can click on one or multiple items to "Add to Cart". Next to the check box, are column headings that should read in order: Item, Item #, Cost Per Unit, QTY In Stock, QTY Ordered, and Total Cost for each item selected. Eg. Item = Pencils, Item # 123456, Cost per Unit = $0.30, QTY in Stock = 50, QTY Ordered (Have a text box to manually enter the number they would like), and the Total Cost is auto updated based on Cost per Unit * QTY Ordered. At the very bottom of the form, it will have the grand total of their projected transaction.

At the bottom, there should be two buttons: A Cancel Order button, where it will clear the transaction, not record it, go back to the Login Page. The second button will be to "Add to Cart." This will bring up a smaller, popup form that will be used for the User to Review the Transaction Summary before processing the order, just to verify the order is correct. It should have the option to bring up a warning prompt or proceed to a verification form:
The warning prompt is for Data Validation and should prevent the user from continuing with the order if the QTY Ordered will make the QTY in Stock less than 0. It should say something like "QTY Ordered exceeds QTY in Stock. Please change order amount,' and redirect the cursor to the item that is affected.The verification form will ask, "Is the below information correct?" It should show a summary of all items that were checked on the Order Form, with the following information: Item, Item #, Cost Per Unit, QTY in Stock, QTY Ordered, Total Cost, and QTY after Order is complete. At the end of this, there should be two buttons again. One to Cancel and go back to the Order Form, and one to Proceed with the Transaction. Once proceeded, it should do several things:
It should briefly prompt a message saying the Transaction was successful, and if possible, return to the Login Screen.It will update the QTY In Stock - QTY Ordered.It will record the entire transaction done with the following information: Date/Time of Transaction, Employee First Name, Employee Last Name, Oracle Number, Item, Item #, QTY Ordered, and Total Cost.

I want to be able to run and print several reports.
A Restock Item Report. This would only display items that fall below a set Restock level for a particular item. This report anyone could print so that our manager can look at it and order the supplies.A Transaction History Report by Item.A Transaction History Report by Employee.

Also, I'd like to be able to do this in Access 2003. We have two networks where I work, and the computer that will be running this only is allowed to use Access 2003, for reason's beyond me. That's really all I can think of at this time. I had tried creating tblEmployees, tblInventory, and tblTransactions to get started, but that's what I'm stuck with. I deeply apologize for such a lengthy post and not having enough knowledge in Access. I'm just looking for a way to make something more automated and make our jobs just a little easier.

Any help would be immensely appreciated.

Thank you!


Our office runs MS Access 2003. I’ve been working on a little database. It’s what you could probably call a “skills” database. A report on the different skill sets is generated from the database and uploaded to the web. Prior to my volunteering to automate all the work that was being done manually people were editing separate look a like word documents, printing to PDF and uploading to the intranet. A lot of it seemed like a waste of time to me. Me and my big mouth.

Up until a short while ago I was stumped as to how I could;

1. Print each group to a separate file, and;
2. Print the files as PDF.
I worked out how to output the reports as RTF (though not separately, but as everyone will no doubt know you lose all your report formatting (lines, graphics, etc) when outputting your report as RTF. It wasn’t good enough.

I hit on the idea of hacking together the code that let me print to RTF and the lebans code that lets you print a report to PDF. This is how I did it;


And Module modReportToPDF

An access database with a query based report that is grouped on one particular data item. Lets call it DB1.
A form (menu or otherwise) that could hold a combobox and a command button . Lets call it FormA.


1. Copy dll files to same folder as the database or system folder as described on
2. Copy Module modReportToPDF to the module library of database DB1
3. Create a global string variable for use as the individual file names. Lets call it GroupVar

e.g - Global Groupvar as String

4. Create an unbound combobox on FormA. Lets call it cboCombo0
Rowsource Type = Table/Query
If Index field is the field your report is grouped on then the bound column would be 1. If it’s a second field then the bound column would be 2.

5. Put a command button on Form A (lets call it cmdReportGen.
6. Open your report in Design View
7. Make a note of the name of the field that displays your group name
8. In the “On Format” event for the section of the report within which your group field is place the following; Groupvar = fieldname
9. Place the following code (appropriately amended) against the “On Click” event of cmdReportGen;

Private Sub cmdReportGen_Click()
'Posted by ADezii 5.23.2009 on
'Modified by Gina Whipp 11.4.2009 to OutputTo Multiple Files
'Tested in Access 2003
'Adapted to print multiple PDF files based on report grouping
'and save files named for report groups - Stephan 15 September 2010

Dim intCounter As Integer
Dim cboCode As ComboBox
Dim blRet As Boolean
Dim stDocName As String

stDocName = "ReportName"

Set cboCode = Me![cboCombo0]

'If Your Data Type is a String
For intCounter = 0 To cboCode.ListCount - 1
DoCmd.OpenReport stDocName, acViewPreview, , "[Field name for Report grouping] = '" & cboCode.ItemData(intCounter) & "'"
blRet = ConvertReportToPDF(stDocName, vbNullString, _
"D:Temp" & GroupVar & ".pdf", False, True, 150, "", "", 0, 0, 0)
'The variable "GroupVar" has beeen declared globally and is initialised at the "OnFormat" event of report generation
'It's then passed to the file name here and cleared just before the end of each loop
GroupVar = ""
DoCmd.Close acReport, stDocName

MsgBox "Your files have been saved to D:temp with individual category names", vbOKOnly

End Sub

There’s no error control and it’s hacked to bits but it works. I hope somebody else can use it.


I'm trying to update(create a Change Order) based on a table of Extra work items( Extra Log) for different projects. I use a form bound to (Extra Log) to easily enter items as needed. I Also use a check box to allow only those items that are checked.To print a change order on this table the data needs to be split into 2 tables first ( Change Order) Second(Change Order details) with a common field Change Order Id to relate them. There are multiple line Items for Each Project and then a Change Order is created From this. I have the Following Code but I get an Error message (Wend Without While). I'm also having difficulty creating a (ssql) statement to filter the data to specific Projects that have a yes/no field checked (True).
The first part of this sub works. I create a special change Order Id based on the project id, then a record is created in the (Change Order) Table.It's creating the line Items that causes the errors. At some point I will use a similar sub to create an Invoice on this created Change Order.

Private Sub cmdchangeorderDetail_Click()

Dim Db As DAO.Database
Dim rs As DAO.Recordset2
Dim rsa As DAO.Recordset2
Dim CustomerID As Long
Dim statusID As Long
Dim projectID As String
Dim ChangeOrderID As String
Dim changeDate As Date
Dim changeType As String
Dim Description As String
Dim Quantity As Long
Dim Price As Currency
Dim Num As Integer
Dim Ctr As Integer
Dim ssql As String

Set Db = CurrentDb

' set the Counter variable to zero so
Ctr = 0
' Will use this variable to fill the last part of the Change order ID
Num = 1

'Set the variables for the change order function
statusID = 0
projectID = Me.Project_ID
CustomerID = Me.[Customer ID]

' String the change Order ID :Will Format ( Project ID & CHA & Num)

ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
Do Until Not Changeorders.IsChanged(ChangeOrderID)
Num = Num + 1
ChangeOrderID = Me.Project_ID & "-" & "CHA" & "-" & Num
' Open the Change Ordr Table and Add a record
If Changeorders.CreateChangeOrder(CustomerID, statusID, projectID, ChangeOrderID) Then
End If

' set ssql to criteria string for opentables function
'ssql = "[Customer ID]= " & CustomerID And "[Status ID]= " & statusID And "[Project ID]='" & projectID & "'"

' open the extra log table and fill the variables to update the Change Order
Set rs = Db.OpenRecordset("Extra Log")
With rs
While Not rs.EOF

If (![Change Ordered]) = True And (![Status ID]) = 0 Then
![Extra Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID
Set rsa = Db.OpenRecordset("Change Order Details")
With rsa
![Change Order ID] = ChangeOrderID
![Change Date] = changeDate
![Change Type] = changeType
![Description] = Description
![Quantity] = Quantity
![Price] = Price
![Status ID] = statusID

Set rsa = Nothing


End If

Set rs = Nothing

End Sub

Thanks for any Help


Not finding an answer? Try a Google search.