Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old May 2nd, 2005, 03:51 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default Autocompleting information on a Subform

I am using Access 97 for Job Control. I have set up the receiving form to add the information to tblOrders when the data is entered. A report is then generated displaying all the order details. Ie. Work Order Number (which is a sequential number)Customer and order details. When the order is finished, I need to generate a Shipping Ticket. I need to make a Subform that will pull some information from table tblOrders along with some information from tblShippingTag and tblSoldBy. I would like to create a Command Button that would fill in the fields that are common to the tblOrders along with Combo Boxes for the SoldBy and other information from the tblSoldBy and tblShippingTag tables. I will need to have all information stored in the tblShippingTag table for furture reference.

Any help would be appreciated.

Thanks,

DBartelt

D. Bartelt
__________________
D. Bartelt
  #2 (permalink)  
Old May 5th, 2005, 10:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   Once again, structure is vague.

   What is the underlying table structure and fields you are working with?

   It seems to me that tblShippingTag should be qryShippingTag. Why store duplicate data?

   You should be able to do all of this by creating a query that pulls all your data together, and then creating a report from that query. Then have your main form look up the proper record, and allow users to select from order and soldby. Then pass the selections to the report as criteria.

Would this work?


mmcdonal
  #3 (permalink)  
Old May 5th, 2005, 12:43 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply. I am basically a beginning programmer for Access here at work. I am reviewing our current database that we had created outside of our company and trying to build new database's for different purposes using that information. Ie.: Requisition and Purchase Orders and Order Control. I am not sure how to build queries correctly. I am learning more as I try them. I bought Microsoft Access Application Development by Helen Feddema last month and am in the process of reading it.

D. Bartelt
  #4 (permalink)  
Old May 5th, 2005, 01:18 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Well you're in luck. If you state the exact table structure, and ther results you want, there will be lots of posts about how to write the query.

The beauty of Access is visual query building, but if you copy and paste the code froma post, and open a new query in design view, and then switch to SQL view, you can just paste the code right in.

mmcdonal
  #5 (permalink)  
Old May 5th, 2005, 05:18 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I use several tables within my Tool Order data entry form. I use Combo Boxes on the Order Entry Form for ALL “ID” fields except the “ToolWorkOrderID” field.

tblToolOrders is the table I use to store all of the information at Order Entry. I need help creating a Query that will gather certain information from that table and create a “Shipping Ticket” report that will be printed and accompany finished parts. The “ShippingTicket” will need to be sequentially numbered at increments of + 1 that will be different than the “TWOrderNumber”. Duplicate numbers are not permitted on either sequential field. Do I need to make a separate table for the “Shipping Tickets” since the Shipping Ticket number will be generated as the last process? Or can the Shipping Ticket number field be added to tblToolOrders as filled in at report generation. I do have the sequential numbering fields formatted and functional. Please note that a future search of the database may be necessary using either the “TWOrderNumber” and/or the “Shipping Ticket” numbers as the search criteria.

Following are some of the field types and usage.

ToolWorkOrderID = Autonumber field – Not visible on Order Entry Form. Not used as an identifying number on the Order Form or Report.

TWOrderNumber = Text field – Sequential number at + 1 increments. Generated when “Add New Order” command button is clicked. NO DUPLICATES CAN BE ALLOWED. This number is used as a unique identifier of a particular order.

CustomerNameID = Number field – Converts to text in Form View to reveal Customers Name. Data is from a separate table – tblCustomers

PartNumber = Text field

MaterialID = Number field – Converts to text in Form View to reveal Material (type of Steel) Name. Data is from a separate table – tblMaterial


Any query advice would be appreciated.


D. Bartelt
  #6 (permalink)  
Old May 6th, 2005, 07:00 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

This sounds fairly standard. A good starting place is to go to Help>Sample Databases>Northwind Sample Database, and look atht efeatures and functions there since it is doing similar things to your database.

It sounds like Shipping should be a seperate table becuase it should be possible to do partial shipments on a single order. Is that right?

Do you know how to make queries using "Create Query in Design View?"


mmcdonal
  #7 (permalink)  
Old May 6th, 2005, 07:09 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

General Comments on the table:

The PK of the table should be named after the name of the table, minus "tbl" and with ID added. General Set theory also dictates that the table name should not be plural, but that is just splitting hairs.

So your table name should be "tblToolOrders", as it is, with Pascal casing, and the PK should be "ToolOrdersID". Changing up the PK name can be confusing when it comes time to write queries, since technically "ToolWorkOrderID" should be a foreign key, and not the PK of your table.

That being said, open the design view of a new query, and then add the following tables:

tblToolOrders
tblCustomers
tblMaterial

You should see that their relationships show up between the tables. If they don't you will need to create them in the query.

Then click on the following fields in the following tables:

tblToolOrders
ToolWorkOrderID
TWOrderNumber
PartNumber - This should be a foreign key to a tblParts table.

tblCustomers
(I am assuming)
LastName
FirstName
Address
City
State
Zip
etc?

tblMaterial
TypeOfSteel (?)

This should get you started with this first query.
Please note, you do not add the foreign key from your main table since it will only display a number on your report. You have to add the fields from the foreign tables themselves, and the query will use the relationships (joins) to keep the data lined up.

HTH



mmcdonal
  #8 (permalink)  
Old May 6th, 2005, 08:30 AM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

tblCustomers is a table with the companies name, contacts name and phone number. I would need to pull only the company name for the shipping ticket, as the other information isn't necessary on the shipping ticket.

tblMaterial only has the steel type in it. Ie.: 12L14, 8620, A-2. That is the information I need to pull for the Shipping Ticket.

Yes it may be necessary to send partial shipments so I will need to accommodate that.

This will be the 1st time I build a query. New to me. I am just learning Access. My forte is metallurgy. The company that made the database for our company is no longer in business. That database is very massive. I am trying to make smaller and simpler databases. I am learning by trial and error and by my mistakes. After I get my 1st database under my belt, the rest will be easier.

Thanks for your help.

D. Bartelt
  #9 (permalink)  
Old May 6th, 2005, 09:31 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

To make partial shipments work, you will need to create a new table (tblShipments) with the following structure. This is just a suggestion since I am not sure what items will be in the orders, and if you can ship from two orders at once to the same customer.

tblShipments
ShipmentsID - PK autonumber
ToolWorkOrderID - FK to tblToolOrders
ShippedItems - text or memo
etc

This is getting a little more complicated since you will have multiple part numbers for each order, perhaps, and your initial table structure doesn't allow for this.



mmcdonal
  #10 (permalink)  
Old May 6th, 2005, 02:04 PM
Authorized User
 
Join Date: Apr 2005
Location: , , USA.
Posts: 56
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I made a command button “GenerateShippingTicket”. When I click the Button I receive a “Compile Error” – Block If without End If. Would you please review the following and provide any suggestions. I am trying to use similar structure from our larger database. The company that designed it did duplicate a lot of the same information in different tables. I will try queries with my next database. I just need to get the Tool Order database up and running. Thanks.


CLASS Module


Private Sub GenerateShippingTicket_Click()
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblShippingTickets")

rst.MoveNext

If Not IsNull(Forms![frmToolOrders]![TWOrderNumber]) Then
    If IsNull(Me.TWOrderNumber) Then

        Me.ShippingTicketNumber = DMax("ShippingTicketNumber", "tblShippingTickets") + 1
        Me.TicketDate = Date
        Me.TWOrderNumber = Forms![frmToolOrders]![TWOrderNumber]
        Me.Customer = Forms![frmToolOrders]![CustomerNameID]
        Me.PONumber = Forms![frmToolOrders]![PONumber]
        Me.PartNumber = Forms![frmToolOrders]![PartNumber]
        Me.Material = Forms![frmToolOrders]![txtMaterialID]
        Me.Process = Forms![frmToolOrders]![txtProcessID]
    End If


Exit_GenerateShippingTicket_Click:
    Exit Sub

Err_GenerateShippingTicket_Click:
    MsgBox Err.Description
    Resume Exit_GenerateShippingTicket_Click


End Sub


D. Bartelt




Similar Threads
Thread Thread Starter Forum Replies Last Post
further information dhoward Crystal Reports 0 February 21st, 2008 03:57 PM
Information on C# student_help C# 1 December 16th, 2005 07:34 PM
Need Client information kfarooqguru JSP Basics 1 October 3rd, 2005 10:55 AM
Scheduled Information? stacy Beginning PHP 5 September 26th, 2004 10:42 AM
Filter Subform B Based on Field in Subform A SerranoG Access VBA 3 June 18th, 2004 12:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.