Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2007
    Posts
    11

    Unanswered: Editing 2 tables with one form

    I am currently converting an old dbIV database to Access, but I'm handicaped by the fact that I'm learning entirely from scratch and there's no one at my company that knows how to use Access. The database keeps track of revisions done on drawings and issues Drawing Change Notices (DCN) when drawings are added, revised, or deleted within the system. I've been able to figure out almost everything I need to be able to get the system to work properly but have now run into kind of a major issue. I apologize for the length of this post, but I'll try to explain exactly what's going on.

    I had two tables:

    Drawing Log (Information about all the drawings)
    -Drawing Number
    -Description
    -Revision Level
    -Date of Revision
    This table is updated by my 'Add/Revise' form. Users can click a button to add a new drawing to the database. There is also a button which locates a drawing for them, then another button 'revises' the drawing. When this button is clicked, the current record is duplicated except the Rev level is increased and the date is updated. (I need to have each record so that there is a history of all the revisions. This makes assigning a primary key nearly impossible)

    DCN Info (Information required for the DCN to be issued)
    -Many fields not concerned with the problem
    This table is updated by a big form called 'DCNs' A DCN must include all the info on the drawings that are changed, which is taken care of by subreports linked to queries of the Drawing Log table.

    I have now realized that to get the database to work properly, I need to have each DCN linked to the specific drawings involved, so I updated the DCN Info table to:

    DCN Info
    -all old same fields
    -DCN Date (this will correspond to revision date of drawings on Drawing Log so that the correct revision is looked up)
    -Drawing Involved (linked to Drawing # on Drawing Log)

    This means that if there are 5 drawings involved on DCN 07-15, there are 5 records on DCN Info with all identical info but the 'Drawing Involved' field is different.

    What I'm trying to do is to change the Add/Revise form so that it still does the same thing to the Drawing Log table (that works perfectly) but also adds the needed records to the DCN Info table. So far querying both tables and basing the form on that doesn't work. Will a subform help, or is what I want to do impossible and I will have to use several totally separate forms?

    Sorry again for the length ...

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In order to be nice and tidy,

    (1) Move Revision Level and Date of Revision from the Log to the DCN table. You can, however, have a Current Revision and Last Date of Revision fields in the Log just for current info. I assume the Drawing Number field already exists in the DCN table - it doesn't make sense not to. If it doesn't yet exist, add it.

    You can now delete all the duplicate records in the Log, once you've correctly added the above data to the new fields in the DCN table. Presto, you've made each record in the Log unique on the Drawing number field. Ergo, make that field the primary key; any more updates will only update the Current Rev and Last Date fields, never duplicating the record.

    (2) In the Relationships screen, make a one-to-many relation between the Log (the "1") and the DCN table (the "many"), based on the Drawing Number fields in both tables. Keep the default relation "when they are both equal". The Drawing Number field from the Log is the "foreign key" in the DCN table.

    (3) Using the wizard, create a sub-form, with the master and child related on the Drawing Number fields as above.

    By the way, you can really go to town with the possibilities of this DB. For instance, you can have a DCN Log, which would house the DCN number (primary key again), date initiated, date approved, initiator (name or emp number), date approved, and any other master data. The DCN Details would, of course, include the DCN number (foreign key), drawing involved, description of change to that drawing, date implemented (ha-ha), implementor (name or emp number), etc. You can make a one-to-many relationship between these two tables as well, and create a form and sub-form to add/modify/delete DCN info. By adding a DCN to a drawing, you automatically update the revision level of that drawing in the Drawing Log, as well as changing the date of last revision.

    There's more that can be done yet, just start slow and build your level of Access confidence.

    Hope this helps,

    Sam

  3. #3
    Join Date
    Aug 2007
    Posts
    11
    So, wait, would the subform be my Add/Revise form with the record source the Drawing Log? Then it would somehow add records to the DCN Info table while editing the existing info on the Drawing Log?

    Thanks so much, by the way, I'm really flying totally by the seat of my pants, here.

    "There's more that can be done yet, just start slow and build your level of Access confidence."

    I know exactly what you mean. Someone who seems to know as much about Access as you do wouldn't have wanted to know me a month ago when I was staring blankly at the 'Get External Data' link wondering if maybe that was what I wanted ...

    I'm learning a lot every day, though.
    Last edited by Psycholicon; 08-01-07 at 16:06.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    So, wait, would the subform be my Add/Revise form with the record source the Drawing Log? Then it would somehow add records to the DCN Info table while editing the existing info on the Drawing Log?
    Subforms are very versatile. However you'll use it is valid.

    When you use the wizard to create the subform, said wizard initializes the sf with everything it needs to work. You don't have to program the record source.

    If I were you, I would

    (1) Make a menu form (I forget what they call it; "something" "board"; I never use the wizard for this part) to choose either the Drawing DB or the DCN DB.

    (2) The Drawing db would be for the purpose of adding and deleting drawings only. The subform info (you can change this in the subform wizard itself) can be insulated from deletions (read up on subforms in the help file; they're beautiful!), but it can be designed to display all DCNs against a drawing, for example, and the exhaustive details about said DCNs.

    (3) The DCN db would be for the purpose of adding/modifying/deleting DCNs. The master form would display the DCN master record, if you will, and the subform can be identical to the Drawing db subform, only using a different foreign key in the relationship. As I said in the previous post, whatever changes are made here can - and must, by the way, if you want to maintain tight data integrity - be programmed to make corresponding changes in the Drawing table(s).

    (4) Besides having the entry to the DCN db from the menu form, it would be nice to add a command button to enter the DCN db directly from the Drawing form; it makes things a bit easier with no loss of integrity.

    By the way, does your company have a Change Board? Do they run by the book? A table - and form/subform and menu form entry - can be designed for that too, to include such tidbits as drawing number (foreign key, again), and any Change Request Number (or whatever your company's board calls it), resulting DCNs after approval (also foreign key - maybe, caution here!), and date approved (same date appears in the DCN master record, remember?).

    I must admit - it's apparent anyhow, from my enthusiasm about the design - that when I'm programming, I'm in my element. That's when I lose track of time. Forget about sitting on the beach! That also has it's time, but I'll take the opportunity for a juicy program any day!!!

    If you'll take your time, and your bosses allow you to do the same, you'll also learn the joys of creating your very own machine. Only it'll be called a "program."

    Sam

    ps. I was the Quality Dept. rep on the Change Control Board in a previous life. That's part of the reason I know so much about change documentation.

    Edit: added ps
    Last edited by Sam Landy; 08-01-07 at 16:08.

  5. #5
    Join Date
    Aug 2007
    Posts
    11
    Wow, this is great! Your tips on rearranging the tables is cleaning up the whole DB! The data I was given to convert is all convoluted, but I can't really tell what's necessary and what's expendable so I've been hesitant to get creative. I've been racking my brain and sifting through the help files of both Access and VBA and it looks like Subforms are gonna be able to help me with almost all of my current issues.

    You've been a livesaver, Sam. Don't be surprised if in a few days I post with a new snag, but it looks like clear sailing at the moment!

    As for the specs on the company, I don't think so on the Change Board. I'm not positive. This work I'm doing is Intern work over the summer before I go off to college to study for a beachelors in (surprise) computer science! I love programming and this is great, albeit 'bash-head-against-wall,' experience.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Good for you! And good luck!

    Sam

    ps. The menu form I was trying to remember is called a "switchboard."

    Edit: added ps

  7. #7
    Join Date
    Aug 2007
    Posts
    11
    Hi Sam, I've been working with the subforms and have been able to fix most of the problems that I've run into, but now have a new one that I can't figure out.

    So right now I have one large form with all the master data for the DCN, looking up to the table 'DCN Log' (I did what you suggested by splitting the DCN info into two tables and joining them, thanks for the tip!) In that form is a subform with the data on drawings involved with the DCN, which looks up to the table 'DCN Details' and that subform has another subform with the master data of the drawing which looks up to the table 'Drawing Log.' The three commands that I need to have at my disposal are to Add a drawing, Revise an existing drawing, and Delete an existing drawing (not delete the record, just modify it to read deleted). I have linked the controls between the three forms so that Adding a drawing works fine, with each form pulling the information it needs to create a new record from the others, but for Revising or Deleting, since on the 'DCN Details' subform the command button uses the 'acNewRecord' command, it doesn't try to look up the drawing in the 'Drawing Log' subform, but instead tries to add a new drawing to it, which interferes with the primary key on that table. Here's the code that I'm using for the Revise command button:

    Private Sub ReviseDrawing_Click()

    Dim RevDrawingNumber As String, ChangesMade As String

    RevDrawingNumber = InputBox("Enter the DRAWING NUMBER of the Drawing you would like to Revise")
    If RevDrawingNumber = "" Then Exit Sub
    RevDrawingNumber = UCase(RevDrawingNumber)

    If MsgBox("Are you sure you want to revise the drawing " & RevDrawingNumber & " ?", vbYesNo) = vbYes Then

    DoCmd.GoToRecord , , acNewRecord

    Me.Add_Revise__Subform_.Form.Controls("Drawing Number") = RevDrawingNumber
    Me.[DCN Number] = Parent.[DCN Number]
    Me.Add_Revise__Subform_.Form.Controls("DATE") = Parent.[DCN Date]
    Me.Drawing_Number = RevDrawingNumber

    ChangesMade = InputBox("What CHANGES were made to the Drawing?")

    Me.Changes = ChangesMade
    Me.Change_Type = "Revision"

    DoCmd.Save

    End If

    End Sub

    I do need to add a new record to the DCN Details table, but need it to look up the info on the Drawing Log table. How could I do this?

    Thanks!
    Derek

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, Derek,

    It's gratifying to know that my suggestions resonated with you and that you are successful with them.

    In that form is a subform with the data on drawings involved with the DCN, which looks up to the table 'DCN Details' and that subform has another subform with the master data of the drawing which looks up to the table 'Drawing Log.'
    I am not sure of the wisdom of this, as you might be compromising the hierarchy of the tables: Drawing Log, according to the previously-designed scheme, is pretty much the highest level of table, with everything else reporting, in one way or other, to it. For it to function as a subform of a subform scares me; I don't know what's gonna happen here.

    What I would do - and this addresses your question as well - is to have only the one subform. All other lookups should be performed as the result of a query lookup, perhaps by the entry of a value in a text box, or VBA running a SQL statement or two, based on the value of a field in the record in the subform.

    You can have another unbound text box, whose control source is set as:

    Code:
    =(SELECT DrawingNum from tblDrawingLog WHERE tblDrawingLog.FieldName = Forms!frmSubFormName!txtTextBoxName.Value)
    (You need both the leading equals sign and the parentheses. The equals sign tells Access that the source of the data is not a field in the form's record source, and the parentheses tell Access that it's dealing with a SQL statement.)

    You now have the Drawing Number in a text box, where it's easily retrieved.

    Two notes:

    1) I'm only using drawing number as an example. The SQL statement can easily be modified to pull whatever data you need from the table.

    2) You don't necessarily have to enter a value in a text box. If there is sufficient information in the subform without it, such as in the detail records already available to the subform, you can modify the above SQL statement to select the info based on the subform's data, such as from a particular field, e.g.

    Code:
    =(SELECT DrawingNum from tblDrawingLog WHERE tblDrawingLog.FieldName = Forms!frmSubFormName!fldFieldName.Value)
    and the information can either be stored in the text box, or, more appropriately, do the lookup in VBA and follow through with an update SQL statement right there in VBA, without bothering to even bring in a new text box.

    As I said, this whole design is very versatile. The name of the game, as least the way I play it, is Experiment, experiment, experiment.

    HTH,
    Sam

  9. #9
    Join Date
    Aug 2007
    Posts
    11
    Thanks, Sam, by the time I got your post, the second subform was already long gone, I realized the deep rooted problems inherent in having it. It really complicated a lot of things. I'm now fighting with making the text boxes return the data from the Drawing Log table, though. In my research, though, you might be interested to note, when a person had trouble with SQL statements as a controlsource they recieved this reply:

    "An Access listbox can take a SELECT statement in its RowSource, execute it and build its list from the results. A textbox by contrast displays a single string, and its ControlSource has to be an expression that returns a string - which a SELECT statement does not do. convert your SELECT statement into an expression using DLOOKUP()"

    However, I am now unable to find any syntax version of the DLookup function that works. Even matching exactly to the Access Help version of DLookup as a Controlsource I am constantly thwarted by the return of '#Name?' and I think that if I see it again I'm going to scream obscenities from my cubicle. Here is my DLookup right now (I've also tried using the SQL statement and the DLookup in VBA and been unsuccessful):


    =DLookUp("[Revision]","[Drawing Log]","[Drawing Number] =" & [Forms]![DCN Details subform]![Drawing Number])

    This is, line for line, the Help File's suggested command, with my data inserted, but it won't work. I'm ripping my hair out over here! No modifications of this syntax will return anything other than #Name?

    Thanks
    Derek

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hi, Derek,

    I think the problem lies with the DLookup() itself, and I quote:
    =DLookUp("[Revision]","[Drawing Log]","[Drawing Number] =" & [Forms]![DCN Details subform]![Drawing Number])
    The first argument is a string. The word "[Revision]" is a string, all right, but you really want it to look up the field called Revision, not the string "[Revision]". Assuming that Revision is a control on the form, you might try
    =DLookUp(Me.Revision,"[Drawing Log]","[Drawing Number] =" & [Forms]![DCN Details subform]![Drawing Number])
    Or, if one is the subform, modify as necessary.

    Sam

  11. #11
    Join Date
    Aug 2007
    Posts
    11
    Ok, Sam (sry for the delay, I've been on vacation). I've got the controls to return the data, and that works well, but because I'm just retrieving the information from the table, I can't edit it while it's in those controls. You had said that by swapping the subform for these controls, I could do both adding to one table and editing of another, so what am I missing? Do I need to use VBA code to go into the table and edit the records? (I think that VBA is already necessary to add a whole new drawing, anyway, since I can't really see a way to add it without an UPDATE statement)
    Last edited by Psycholicon; 08-14-07 at 13:50.

  12. #12
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Psycholicon,

    Please forgive me, but I'm also just back from vacation and my brain's still pleasantly fried. I need time to rethink the thread. In the meantime, if anyone else wants to take a stab at it, why go ahead!

    Sam

  13. #13
    Join Date
    Aug 2007
    Posts
    11
    Thanks, Sam, I was able to figure it out. I'm using the DLookup to feed the boxes and then using UPDATE statements in VBA to edit the master table (not represented). To add a new record I'm using an INSERT INTO statement and am through the muck of sytax (after about 10 hours of it ... ugh).

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •