Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2008
    Posts
    5

    Unanswered: Database design question

    Hello

    I'm sure this is an easy question for you all but I could really do with some help.

    I have a form (frmA) that once its opened creates a new record, the recordsource is a query that interrogates 3 different tables. Data will be entered into frmA and this data will populate certain fields in each of the 3 tables. My problem is every time frmA is opened it creates a new record and elsewhere in my database I have tables linked by ID. By frmA opening each time it means all my other tables are out of sync.

    What is the best way to get round this problem? Have I designed the db incorrectly or is there a way to create a new entry in the other 2 tables everything frmA is opened?

    Thanks

    Matt

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Hey Matt,

    Typically, I will design each of my forms based only on 1 table (i.e. I don't link tables in the recordsource for data entry forms). And then I will add subforms (or popup forms) to the main form where each subform is based on a relational table (again, each subform or popup form based only on 1 table.) The important thing to remember is that on the subforms, I will have the ID field of the relational table on the subform and set it's default value to equal the ID field on the main form (which is again, based only on the main table.) If you want to automate populating values in a relational table, you could then consider writing a bit of code after a new record is added to the main form which in turn adds a new record to the relational table (again, remembering to set the ID field of the relational table to the new ID value on the main form and ONLY after the ID is generated on the main form.)

    I usually don't immediately add a new record to a main form when it is opened but instead have a button on the main form ("Add New Rec") which simply goes to a new record but I've also had it where some main forms immediately add a new record when opening (I usually have the Add New Rec button on a Menu type form where I'll open the form and then issue a docmd.gotorecord,,acnewrec.) Having an "Add New Rec" button would allow you to not only go to a new record on the main form, but you could also put in some code to add a record to the relational table (after you've updated some field on the main form to create the ID in the main table so that ID can then be transposed to the relational table.)

    In your relational table design, make sure to enforce referential integrity (linking the tables) so you don't end up with an "orphaned" record where you might have a record in the relational table which has an ID that does not match any ID in the main table. Linking the tables correctly in your relational design view will help insure that your design of forms doesn't produce orphaned records (ie. I usually will select cascade update/delete when linking the tables.)
    Last edited by pkstormy; 03-11-08 at 17:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2008
    Posts
    5
    Hi

    Thanks for the reply.

    There are about 15 fields in frmA and 12 of those are populating just one table. The other 3 fields are populating 2 other tables, are subforms the best way still? I think it would make the main form quite messy and difficult to use.

    In my database the only way to add a new record is via frmA, from there other tables will be populated through other forms.

    Ideally when frmA opens it would create a new record in all 3 tables and then referencial integrity could still work maybe?

    Thanks in advance and I hope this makes sense

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Makes perfect sense and you don't have to use subforms but I personally wouldn't do a query linking all the relational tables as the recordsource for the main form and then use that recordsource to populate the main table as well as all the relational tables all on 1 form (just me and my way of doing things). I'd either use a subform or code in something to populate the relational tables (after the ID is gotten on the main form). When you link all the tables into 1 recordsource for 1 form, your relational table ID fields in the query (that they are linked on) will be something like myrelationaltable1.ID and myrelationaltable2.ID (if you add all the ID fields). I just (again personally) don't like this way (guess I'm a subform/popup form type of guy although I can't say I've never done it this way - but only with 2 tables - not 3 (I think you're limited to 2 tables anyway)).

    But Access does demonstrate doing it this way in Northwinds so I can't say that it's the wrong method. It's more of a personal preference and I can't remember exactly why I like to use a subform verses 1 form - guess it's habit from the old days more than anything.

    I think it would be good to poll the rest of us and see who does it this way verses subforms.
    Last edited by pkstormy; 03-11-08 at 22:18.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Mar 2008
    Posts
    5
    Thanks again

    I am going to take the route of coding I think. Unfortunately i've never had to do this and I would still consider myself an Access beginner. Would you be able to help me write some code to populate a new record in another table with the ID generated from frmA?

    Thanks

  6. #6
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    How are the three tables related, one-one or one-many? I've used both methods (multitable query and subforms) the type of relationship between the tables plays a large role in determining which method I use.

  7. #7
    Join Date
    Mar 2008
    Posts
    5
    They are one-one.

    Thanks

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's some code (in ADO). If you want to use DAO, search this site and you should be able to find an example using DAO.

    (first refresh or make sure an ID number is generated on the form. If you don't have an ID number, you should get an error in the routine below something to the effect that the field cannot be null. If you don't get an error (and the ID number was null on the main form, check your relationships.)

    Refresh (optional - this refreshes the Main form - again, run this code after a field is updated so that an ID number has been generated.)
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myRelationalTable"
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    rs.addnew
    rs!ID = Forms!MyFormName!ID
    rs!SomeField = SomeValue '(I might have an unbound text box on the form and do something like rs!SomeField = Forms!MyFormName!MyUnboundField1 or prompt for this value. You could set SomeValue to a default value or something. Just make sure if the user updates the unbound field on the form, this value is updated in the relational table (see 2nd example below) - nothing worse than a user updating an unbound field on a form and it doesn't update the value in the table!!! You could also put in a test to see if the unbound field is blank and if it is - stop this procedure and tell the user they must update the unbound field on the form before this relational record can be added - again - be careful here and make sure SomeValue gets a value somewhere AND that the user doesn't update the unbound field on the form and it doesn't update the record. I sometimes like to put a default value in the unbound field on the form just to make sure but you may have to do something if a user updates the value of the unbound field. This is a drawback to writing code and populating rs!SomeField with an unbound value on a form as you have to make sure the unbound field has not been updated on the form and this code doesn't update the value! - I hope this makes sense.)
    rs!SomeOtherField = SomeOtherValue '(or again, have an unbound text box on the form and do something like rs!SomeOtherField = Forms!MyFormName!MyUnboundField2)
    rs.update
    rs.close
    set rs = nothing
    msgbox ("Relational Record has been added with ID = " & Forms!MyFormName!ID & ".") '(remove in final - I like to put this here for testing to make sure the record has been added - msgbox are great things to put in code anywhere for testing to display checkpoints on where code is at.)

    Optionally, you could also put this code in (which checks to see if a record already exists in the relational table. If it does, it displays a prompt to overwrite.)

    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string
    strSQL = "Select * from myRelationalTable where ID = " & Forms!MyFormName!ID & ""
    rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic
    if not rs.eof and not rs.bof then '(if not end of file and not beginning of file (ie. there is a record) then...)
    dim QA as integer
    QA = msgbox("There is an existing record in the relational table for this ID. Do you want to overwrite the values?",vbyesno)
    if QA = vbyes then
    rs!SomeField = SomeValue
    rs!SomeOtherField = SomeOtherValue
    rs.update
    rs.close
    set rs = nothing
    else '(user answered no so just close recordset without updating and exit sub or exit function)
    rs.close
    set rs = nothing
    exit sub '(or exit function if this is a function)
    end if
    else
    rs.addnew
    rs!ID = Forms!MyFormName!ID
    rs!SomeField = SomeValue
    rs!SomeOtherField = SomeOtherValue
    rs.update
    rs.close
    set rs = nothing
    end if
    Last edited by pkstormy; 03-12-08 at 12:56.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2008
    Posts
    5
    Thank you that works perfectly.

    One more question - once another field is updated on the form by the user I want to append data to a record by linking the IDs together. I was hoping I could do the below, but that doesn't work:

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM tblUsers INNER JOIN tblOfferProForma ON tblUsers.IDTest = tblOfferProForma.ID "
    rs.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    rs!UserName = Forms!frmOfferProForma!UserName
    rs.Update
    rs.Close
    Set rs = Nothing

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure I understand what you mean by append data to a record by linking the IDs together. Your strSQL joins 2 tables together. Do you need to join the tables in the strSQL if you're just updating the tblUsers?

    Could you please elaborate?
    Last edited by pkstormy; 03-12-08 at 13:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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