none
Improving Dlookup results

    Question

  • I am using Access in a mfg. environment  and I am trying to improve thoroughness and accuracy.  Currently when we produce an new work order of a common part we end up just copying and pasting old orders...so we are retaining very much data, and things are always brought up-to-date with improvements.  I am trying to move to  a system where we have a parts table and a work orders table; using dlookup I would like to be able to call the info from the parts table into the work orders table.  I'm new to the dlookup function, but sometimes it works really awesome, then the next time I get a bunch of "#name?".  I've tried to write the function a couple different ways, using the control containing my part number and my part# field.  Because the parts db was created from my work orders db I am wondering if this is causing me problems, that's why I've added an extra T in one example.  Any ideas?  Thanks

    =DLookUp("opp6","[parts table]","[ourpart#]=" & [Forms]![WORK ORDERS]![Textpart])

    =DLookUp("opp1","[parts table]","[ourpartt#]=" & [Forms]![WORK ORDERS]![ourpart#])

    Thursday, October 17, 2013 9:00 PM

Answers

  • You'll just have to try it. I'm afraid I've lost track of the setup in this extremely long thread - sorry.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jswan1001 Thursday, February 27, 2014 3:33 AM
    Thursday, February 20, 2014 3:09 PM

All replies

  • It might be better to create a query based on the parts table and the work orders table, joined on the appropriate field, and use that as record source for a form. You wouldn't need DLookup.

    But is one work order may involve several parts, and one part may be involved in several work orders, you have a many-to-many relationship, and you'd need a third table to store combinations of parts and work orders. To work with a many-to-many relationship, you can use a main form and a subform. See for example http://www.databasedev.co.uk/many_to_many_example.html


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 17, 2013 9:16 PM
  • =DLookUp("opp6","[parts table]","[ourpart#]=" & [Forms]![WORK ORDERS]![Textpart])

    =DLookUp("opp1","[parts table]","[ourpartt#]=" & [Forms]![WORK ORDERS]![ourpart#])

    Hi jswan,

    It has many advantages not to use space or other "strange" characters in field names or control names.
    So instead of  [parts table]  , you can better use parts_table or PartsTable or tblParts.
    The same holds for [WORK ORDERS]. And for [ourpart#]: The #-character even has a special meaning for dates.

    Imb.

    Thursday, October 17, 2013 9:33 PM
  • I think you're both right, I've inherited this db some i'm trying to do my best, dlookup may be the easy way out.  I think Hans is on the right track with a query of some sort.  I'd like to have a table which holds unique info about an order (dates, price, raw material lot#'s, testing info etc.)  I'd like to call the mfg. processes (several fields) etc from an established Parts Table which would ensure consistency and reduce the amount of data we'd be storing.  What would be the best way to call-up a part based on an Autonumber field?  I'd like to enter "part x" and have it's processes come right up.  Also, there would always only be one part# per order#.

     

    Thanks,

    js

    Friday, October 18, 2013 12:19 AM
  • We'll need to know how parts and work orders are related.

    Does a work order involve only one part number, or can multiple part numbers be associated with the same work order?

    And can a part number be used for one work order only, or can a part number be used in multiple work orders?

    Thanks in advance!


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 18, 2013 9:17 AM
  • Ok Hans,

    The parts table is new to this db so it's not exactly related, however I think it should be fairly easy to establish a relationship.  The parts table is an altered copy of my Work Orders table from which I've removed fields that would change with each order(dates, serial numbers, customer po#s etc.)  I did leave in the [customer name] field which is a Primary key(along with an Auto id#) in my 'Customer Info Table' and a foreign key to my work orders table.  So to generate an order for a new customer, they must first be added to the 'Customer Info Table'(given id# etc) and a new part would need to be generated, either by scratch or altering an existing part#. 

    Again, there will always be one part# and one part# only per order(everything is made custom to order).  Even if a customer PO has multiple line items, a unique order# is generated for each.  And yes a part# could certainly be used in multiple orders.

    I hope that kind of explains my situation.

    Thanks,

    js

    Friday, October 18, 2013 1:18 PM
  • If a work order has only one part number, but the same part number could be used in several work orders, the work order table should contain a part number field that links to the parts table. The parts table should not contain a field that links to the work orders table - that wouldn't work since one part can be used in multiple work orders.

    Here is a screenshot of a possible relationship structure (the real tables would contain more fields, of course).

    A query based on the two tables can be used as record source for forms and reports:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, October 18, 2013 9:38 PM
  • Ok, I think I get it Hans.  So keeping WorkOrderId as my primary key and PartId as my query parameter I should be able to call up any information I need from the parts table, correct?  From my standpoint this should work great!

    Thanks Hans,

    js

    Saturday, October 19, 2013 3:58 PM
  • Entering or selecting a PartID will automatically fill in the other fields from the parts table in the query.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 20, 2013 5:19 PM
  • This seems to be working pretty nicely Hans...But what would I need to do if I wanted to add a third table to the query right now I have: tblWorkOrders>tblParts, but I would like to add tblCustomerinfo, with a CustID field, which would provide addresses, contact info etc.  When I try to add this info to my query and open the WorkOrders form I get just a blank screen...Would this be a different type of join?

    Monday, October 21, 2013 5:40 PM
  • Double-click the join line from tblWorkOrders to tblCustomerInfo in the query, and select the option to return all records from tblWorkOrders, then click OK. With the default option (the first one), you won't see work orders if you haven't assigned a customer to them yet.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 21, 2013 8:48 PM
  • Thanks Hans, everything seems to be working properly in my Source Query, I am going to link it up with my forms and see how that works.  Since I am using a Query as the Control Source would you recommend creating a View (which I've just been reading about), to essentially be a copy of the Source Query so that users could access the data more readily and create queries of their own?

    Thanks,

    js

    Tuesday, October 22, 2013 1:03 PM
  • As far as I know, Access doesn't have views, only queries. Views are a feature of SQL Server, the big brother of Access.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 22, 2013 3:00 PM
  • Ok Hans, my next task is to introduce my Purchase Orders table in this query so we can track orders and link Puchase Orders with the Work Orders that the material has been purchased for.  The problem here is that sometimes we Purchase material for up to four different Work Orders on a single Purchase Order.  I have things all connected and working for the first Work Order, but I'm not exactly sure how to get it working for Work Orders 2,3 & 4.  Is this a situation where I would need to use an Alias Table for Work Orders Table  in the Query?  And if so, do all the joins need to be the same as the original Work Orders Table?

    Thanks,

    js

    Saturday, October 26, 2013 5:45 PM
  • So one Purchase Order can be used for multiple Work Orders.

    Can one Work Order use multiple Purchase Orders?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, October 26, 2013 6:59 PM
  • Yes Hans, that is correct.  If one supplier does not have enough of the quantity required we would have to order the remainder from one or more additional suppliers to fulfill the order.

    Thanks,

    js

    Sunday, October 27, 2013 3:32 PM
  • That means you have a many-to-many relationship between work orders and purchase orders. To implement this, you need a "junction" table that contains fields corresponding to the primary keys of the work orders table and the purchase orders table.

    Each record in this table will represent a unique combination of a work order and a purchase order.

    If one work order requires several purchase orders, the junction table will contain several records with the same work order ID, but different purchase order IDs.

    Conversely, if one purchase order is used for several work orders, the junction table will contain several records with the same purchase order ID, but different work order IDs.

    A many-to-many relationship is too complicated to handle in a single query or form.

    You can create a main form based on work orders, with a subform displaying the purchase orders associated with the work order.

    You can also create a main form based on purchase orders, with a subform displaying the work orders associated with the purchase order.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, October 27, 2013 4:33 PM
  • Ok Hans, this is my first time dealing with this type of relationship, I am having some trouble understanding exactly how this will work, so please bare with me.  Now when I create this junction table will the keys in this table workorderid, purchaseorderid be taking the place of these same keys that I have put into my Datasource Query? 
    Monday, October 28, 2013 6:11 PM
  • Perhaps it'll help to look at a simple example. See Many2Many on DropBox.

    This database has three tables:

    • Students
    • Courses
    • Participation

    The Participation table describes which students attend which courses.

    There is a main form Courses with a subform sbfStudents, and a main form Students with a subform sbfCourses.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 28, 2013 10:55 PM
  • Very good, that was simpler than it sounded.   So I have made Work Orders my main form and added a few key subforms on Tabbed Page Controls, this is very user friendly and allows us to see all data related to a particular order, but now my problem is that I get an Appcrash when I select Print Selected Record(s), which seems understandable.  Could I alleviate this by adding a Control Button and Click event for each form? 

    Thanks,

    js

    Tuesday, October 29, 2013 5:38 PM
  • You should create a report (perhaps with a subreport) to print records; forms aren't really suitable for printing. If necessary, you can open the report filtered by the form.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 29, 2013 5:44 PM
  • Using reports for printing seems like it will work out just fine, I copied and pasted my forms into reports and made appropriate queries to operate them.  Another problem I've just become aware of is that the Check Box Controls we use to Toggle a Order Open and Closed etc. don't seem to function and I get a message at the bottom the the Recordset is not updateable.  Is this due to a property setting or perhaps something to due with my query?  I know I've operated Check Boxes through queries before.

    Thanks,

    js

    Tuesday, October 29, 2013 8:53 PM
  • I reviewed my SQL statement and it looks like I had messed something up and so it works again.  I think the problem was caused by a table I am trying to bring in to my Source Query, 'TableMaterialCert'.  For each order we need to provide Material Certification Paperwork, which would be a 1:1 relationship with Work Orders Table.  Perhaps I am incorrect about the join, because when I remove the table everything seems to work just fine?

    js

    Tuesday, October 29, 2013 9:29 PM
  • One-to-one relationships are seldom useful...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 29, 2013 10:30 PM
  • That makes sense, I already have all the data I need, I just need to organize it and redisplay.  I was wondering you can help me with a little code that should really tie everything together.  I've got a 2 column combobox which uses a Query of [custid] and [custname] from CustInfoTable for a row source.  However I need each of these values to be bound to a field.  I would like one to be bound to the combo box and the other(likely custid) just to display in a text box.  Would I use an afterupdate() sub for this situation?

    Thanks,

    js

    Wednesday, October 30, 2013 9:42 PM
  • Unless the user needs to be able to edit the value of the text box, you don't need code for this.

    If you want to display the first column of the combo box in the text box, you can set the Control Source of the text box to

    =[NameOfComboBox].[Column](0)

    and if you want to display the second column:

    =[NameOfComboBox].[Column](1)

    where NameOfComboBox is the name of the combo box. Columns start counting at 0, hence Column(0) is the first column, etc.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 30, 2013 10:10 PM
  • That seems to work just fine for just displaying data, but does it enter data?  The combobox I am usings queries the keys for the main form where the data entry begins so I think it needs to be bound for everything to work properly with the subforms etc...

    js

    Wednesday, October 30, 2013 11:15 PM
  • It doesn't enter data in a table, but that shouldn't be necessary.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 30, 2013 11:23 PM
  • I think I've got it mostly figure out now thanks, long day yesterday.  The only problem that I am having is that the ID number is entering exactly 2 values off from that displayed.  For example if I want to have CustId# 239 show up in my source query I need to select #241 in my combo box  and if I want #241, I must enter #243.  Any idea what might cause this?

    Thanks,

    js

    Thursday, October 31, 2013 5:20 PM
  • I'd need to know more about the setup.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2013 5:21 PM
  • I've made a form Work Orders which has additional pages within it, for cust info and certification info.  The sales order page is where the process starts, by selecting [customer] and [custid] from a combo box with TableCustInfo as the row source.  The other pages use custid as foreign key to call up address info etc, once the record is selected from the combo box.  However when I've been doing this the custid the value has been off by 2 records, I've tried resetting the custid and now it's off by just 1 record.  The strange thing is that when I change the control to a regular text box everything seems to work just fine, the ids, addresses etc match up.  I thought maybe it was a relationship problem, but that doesn't seem to make sense because it works fine with the text box.  Any ideas? 

    Thanks,

    js

    Thursday, October 31, 2013 7:27 PM
  • Have you set the Bound Column property of the combo box to 0 by any chance? Normally, it should be set to 1.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2013 8:21 PM
  • I will have to check on that, for now I am content that it at least works with a text box.  So now, I am successfully calling Parts data with the partid and customer info with the custid.  But I am not so sure I've got the Work Orders and Purchase Orders linked up properly.  The source query allows me to Add (or start to add) a new record, but it is not committing it to the database.  If I close the database and reopen any recently added records are gone.  Maybe something to do with the many:many relationship, I am still a little confused about making it  work when it comes to forms.  If I have multiple [orderid] for 1 Purchase Order, what control do I use for orders 2,3 & 4?  I have the same question with multiple Purchase Orders([poid]) for 1 Work Order?  I hope that makes sense. 

    Thanks,

    js

    Thursday, October 31, 2013 9:55 PM
  • Are you using a main form based on work orders with a subform for purchase orders (using the junction table) or vice versa?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 31, 2013 10:00 PM
  • To tell you the truth I am not really sure how I should be using junction table.  But now that you mention it, should I be using the join table keys for the primaries respective PO and Work Order Forms?  Currently I have a main form WorkOrders that which is my Order Entry form and also calls and displays the info from my parts and customer info tables.  My Purchase Orders table is currently a separate form, I tried it as a subform(which I'm not too versed at), but I kind of confused myself about how I wanted it to work (we also use Purchase Orders for things other than Work Orders; tooling, materials, service, etc.) so I decided to keep it separate while I was trying to figure out the parts and customer aspects.  Getting POs and Work Orders to work together and then introduce quoting are my last big steps in making this app awesome.  Thanks.

    js

    Thursday, October 31, 2013 10:53 PM
  • Please study the sample database that I referred to.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 01, 2013 12:04 AM
  • Allright Hans, I think I finally got it figured out.  I've imported all my new forms into my working database, created the source query and everything seems to be working good.  My question is how do I get pre-existing records (which already have an OrderID) from my WorkOrder Table into my Source Query(which require a New OrderID to add records)?  Do I need to remove the Primary key temporarily and run the Query or something along those lines?

    Many Thanks,

    js

    Wednesday, November 06, 2013 2:14 AM
  • If you have a Work Orders form, it should display existing records too, unless you have set its Data Entry property to Yes.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 06, 2013 7:19 AM
  • I do have a Work Orders form (data entry property is set to 'No'), that when the control source is set to Work Orders table (which uses orderid as Autonumber Primary Key, displays 4400 pre-existing records.  But if I set the control source for the Work Orders form to my MasterQuery, which also uses OrderID for the Auto Primary Key it displays no records.  I would expect it not to display data that will now be called from the Parts and Customer info table, but I can't understand why it doesn't at least display the data from the Work Orders table.  Could it be something with the Property settings in my Query?

    js

    Wednesday, November 06, 2013 2:42 PM
  • Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or register at www.eileenslounge.com (it's free) and post a message in the Access forum. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 06, 2013 5:22 PM
  • Thanks Hans, I've pasted this link Trouble Shoot AccDb to give a pared down version of what I am working on.  I believe right now the Source for Work Orders Form is currently set to Work Orders table and you will notice it displays all the records in the table.  Sales Orders is what I am trying to make into my main table, it's source is set to Masterquery, you will notice it only displays records that I've added through the Masterquery.  Thanks again for all your help.

    js

    Wednesday, November 06, 2013 6:23 PM
  • Double-click each of the join lines in the query in turn and select the option to return all records from the Work Orders table, then click OK.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 06, 2013 9:21 PM
  • Excellent, you are too wise Hans!  This is great, now it looks like it's just a matter getting all my records up to date with some sql statements and some formatting and I think we're ready to roll.  Thanks again.

    js

    Thursday, November 07, 2013 2:02 PM
  • Although Hans, I just notice i am getting what appears to be an error.  When I double-click my Sales Order Form to open it in Form View, I get a message about 'Exclusive Access' like Access thinks I am trying to open the form in Design View.  Any idea what causes something like this?

    Thanks,

    js

    Thursday, November 07, 2013 6:40 PM
  • I can't reproduce the error in the database that you made available yesterday...

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 07, 2013 8:21 PM
  • I'm not sure what might have caused that, after a couple times it opens up, that file is started to get a bit congested, I think I will do a rebuild and import all my necessary objects to try and cleanup and avoid any problems.  A question on split databases: should I create my relationships on the back end or front end?  I'm causing some confusion for myself and I think I should try to standardize how/where I make joins.  Could being inconsistent with this cause any problems? 

    Thanks,

    js

    Thursday, November 07, 2013 10:25 PM
  • Joins between tables should be created in the backend. The frontend will automatically "see" these joins.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 07, 2013 10:30 PM
  • Hans, did you notice how the work orders, parts and quotes Tab Controls in the Sales Order from my above-posted link scroll down automatically when clicked?  Is there a way to prevent that?  It's driving me crazy. 

    Thanks,

    js

    Friday, November 08, 2013 2:22 AM
  • No, because you didn't include all subforms in the database.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 08, 2013 11:40 AM
  • Troubleshoot1... this is an even more trimmed down version.  Sales Order and Certification are Pages within the Form, while Work Order is a Subform which obviously uses Work Orders as the Source Object.  When Cert and Sales are clicked the page doesn't scroll, but when Work Orders is clicked it scrolls down so that the Page Tabs disappear, it's just inconvenient and I want this to be as simple as possible for the user.  I think it must have something to do with the formatting?

    js

    Friday, November 08, 2013 3:26 PM
  • Dropbox is giving me trouble today...Here is the link Troubleshoot1...I hope
    Friday, November 08, 2013 3:58 PM
  • I'm not sure what causes the problem. I'd make the subform less tall so that it fits on the screen (use a tab control if necessary)

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 08, 2013 4:02 PM
  • I think I will go with a simpler design than that, seems like there is too much going on in that form...I am trying to update my tables so that the foreign and primary keys match in my Work and Purchase Orders Table.  Prior to using Autonumber keys, we were using a text box for a Primary Key and typing that key into Purchase Orders to match up the records.  Now that I am using an Autonumber [OrderId]for Work Orders, I need to get this key into matching records in the Purchase Orders Table so that every thing links up nicely.  I know I have many matching records using the old Text Box key, but I am not quite sure about how to update the Purchase Orders table to reflect this new field.  I know this will likely require some sql...Can you shed some light on this?

    Thanks,

    js

    Wednesday, November 13, 2013 3:21 PM
  • Let's say the old key field in the Work Orders table is named Old.

    Create a query based on Work Orders and Purchase Orders.

    If Access automatically joins them on OrderID in Work Orders vs the matching field in Purchase Orders, delete that join by clicking on it and pressing Delete.

    Next, join the tables on the old key field.

    Click Update in the Query Type group on the Design tab of the ribbon.

    Add the field from Purchase Orders corresponding to the new OrderID key to the query grid.

    In the 'Update to' line, enter

    [Work Orders].[OrderID]

    Click the Run button on the ribbon (the red exclamation mark)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 3:36 PM
  • That is great Hans, took me about 2 minutes to update about 5000 records.  I wonder if you could help me with a simple date expression?  I am creating my material certification off my master query and I want the cert to show the date the record was created.  I know there are date expressions now() or today() that show the current date, but what do I need to do to display the 'record creation date'?

    thanks,

    js

    Saturday, November 16, 2013 6:29 PM
  • Access itself does not keep track of when a record was created or modified. You can do so yourself for records created from now on.

    Add a date/time field named DateCreated to each table for which you want the record creation date. Set its Default Value property to =Now()

    If you also want a record modification date, add a date/time field named DateModified.

    On each form in which users edit the data in the table, create a Before Update event procedure:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Me.DateModified = Now
    End Sub

    If you place text boxes bound to DateCreated and/or DateModified on the form, make sure that you set their Locked property to True, or hide them by setting Visible to False - you don't want users to edit their values.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 7:15 PM
  • Ok Hans, I've installd this DB and we've gone to muli-user today and fundamentally everything seems to be working great.  But I've had a few random messages regarding Exclusive Access, which goes away after a few OK clicks, but I'm not really sure what causes it.  I've set up a display form like a Home Page, which uses buttons and embedded macros to other forms in the DB.  Do you think using VBA procedures as opposed to Macros might function better to open forms?  I am trying to eliminate possibilities as to what causes my 'Exclusive Access Issues'.

    Thanks,

    js

    Tuesday, November 19, 2013 5:13 PM
  • I don't think that macros or VBA make a difference in this respect.

    Make sure that users cannot save the design of database objects, either intentionally or by accident.

    For example, if the user filters a form or changes the sort order, then closes the form, Access will try to save the filter/sort order with the form. This requires exclusive access to the database.

    I usually disable the close button in the upper right corner of forms, and provide a command button to close the form, with code like this:

    Private Sub cmdClose_Click()
        DoCmd.Close acForm, Me.Name, acSaveNo
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 19, 2013 5:35 PM
  • Thanks Hans, I think maybe I will need to create a front end for each individual user, which I hope would alleviate any problems.  My next concern now that this database is active is backing up my data.  I've recently noticed a couple records have disappeared, now I am not quite sure if it was user error or what, but in any case I'd like to set up something for automatic backup.  Do you know of any code to use to backup every day or every week at a certain time?

    Thanks,

    js

    Wednesday, November 20, 2013 4:40 PM
  • If you search Google (or Bing) for microsoft access backup backend you'll find lots of suggestions.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 20, 2013 5:15 PM
  • This has been working well for me Hans, but now I've got a little bug I still need to work out.  My Work/Sales Order has a Purchase Orders Subform which displays various Purchase Orders associated with a Work Order on [Orderid].  However I am having a problem with the Purchase Order Form.  The form accepts info about a PO and I have 4 combo boxes with a 'Work Orders Query' record source on [Orderid] field for the user to select the proper Work Orders.  The problem is I can only have 1 [orderid] on a form, the other 3 combo boxes I named [Orderid2], [orderid3] and [orderid4].  As a result, when a Work Order is listed as #2, 3 or 4 on the Purchase Orders Form the  Purchase Orders subform does not display the record on the Work Orders Form.  How can I establish the connection to display these records properly? 

    Thanks,

    js

    Tuesday, February 18, 2014 12:38 AM
  • If you have multiple OrderIDs, each should be displayed in a separate record, instead of in multiple combo boxes.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 18, 2014 7:04 AM
  • Ok I think I am starting to grasp this.  Will I need to create a separate table to store records for each [orderid](2,3 & 4) after the original on Purhcase Orders Form.  I could then tie them to Purhcase Orders with a Query and then establish a relationship b/t Work Orders and Puchase Orders Tables 2,3 &4?  Does that make sense?

    Thanks,

    JS

    Tuesday, February 18, 2014 6:29 PM
  • I wouldn't create 4 work orders tables, but a single table for all work orders.

    If a purchase order has 3 work orders, there'd be 3 records in the work orders table with the same Purchase Order ID, but with different Work Order IDs.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 18, 2014 7:50 PM
  • Ok, so now I am thinking I will create [order association table] to store data regarding what Work Orders a PO is for.  My thinking is that I could you [POid] which is my primary key for Purchase Orders table, to connect these tables with [Poid] as foreign key in [order association table].  So this would need to be a One-to-Many relationship (Purchase Orders Table-to-Order Association table)?

    Thanks,

    js

     
    Tuesday, February 18, 2014 8:19 PM
  • Yes, that's right.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, February 18, 2014 9:37 PM
  • Excellent I can copy the PO table and I will just need to create new records in this table where [orderid] was entered as [orderid2], [orderid3] and [orderid4]. Do I need to use an append query for this?  I am no SQL expert, but I know I need to create records where [orderid2,3,4] Is Not Null.  And I suppose POid would=POid One other question:  Should this new table not have a primary key? 

    Thanks,

    JS

    Tuesday, February 18, 2014 11:51 PM
  • Once again, I recommend that you study the sample database referred to higher up in this thread.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, February 19, 2014 6:36 AM
  • Well it seems to me that it should not need a Primary key, I will be using [Poid] and [orderid], respectively to connect to [Purchase Orders] and [Work Orders] in One-to-Many relationships.  But now that I think about it and per your advice I've looked at the join table we used to create a Many-to-Many relationship between Work Orders and Purchase Orders; the one-many relationships already exist there.  Should I be using that join table to be storing this information???

    Thanks,

    Js

    Wednesday, February 19, 2014 9:35 PM
  • I think I might have it Hans.  The association must have a Primary Key, [AssociaterID].  I will create a Query that combines info from the PO table and Association, Work Orders etc.  I will then will create a Sub-Query as part of my Purchase Orders Form instead of using Combo Boxes to enter Work Order info.  If I include the [AssociaterId] in this Query a new record will be created in that table when I enter an [orderid].  I can then put a subform/query based on the newly created query in my Sales Order which should now display any Purchase Orders where that [orderid] appears.  Does this make sense?

    Thanks,

    Js

    Thursday, February 20, 2014 2:45 PM
  • You'll just have to try it. I'm afraid I've lost track of the setup in this extremely long thread - sorry.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by jswan1001 Thursday, February 27, 2014 3:33 AM
    Thursday, February 20, 2014 3:09 PM
  • It took a while for my brain to comprehend the concept, but I think it's going to work great!  Thank you Hans, hopefully this was my the last major but with this application.

    Regards,

    JS

    Thursday, February 27, 2014 3:33 AM