auto fill Primary Key

  •  

    Building an access project MS SQL back end

     

    I have two tables table 1 uses an integer column with an Identity that moves up one with each record.  It also has 5 other random fields.  The second table has an integer column as Key and a text column (used for notes).  I want create a quarry that will join the two on the Key fields (easy enough)  but when adding a record I need the Key integer field from table 2 to be auto filled with the Identity from the key in table 1.  And if no information is typed in to the text field then no record is created in the table 2

     

     

    Thanks for your help I did look but did not find any thing on this problem I might of not know the correct word or words to search for.

  • You don't want to create a query at all. This is simply a form and subform example.

    The subform is based on table2 and the main form is based on table1. The master and child fields are the key field.

    There are several ways of getting to the next part depending on how you want to do it.

    The safest is to require that table2 text field does not allow nulls and then do some error trapping in Access. Alternately you can use an instead of insert trigger and check that the text field is not empty, if it is then you can roll the transaction back.

  • I would like the form to be continus.  I am trying to match a tracking sheet the operators fill out and then there floor lead inputs in to the form.  the lay out is like this

    T1 field 1 (date)

    T1 field 2 (int) operator #

    T1 field 3 (int) machine #

                              Subform

    T1 field 4 (int)    T1 field 5 (int)     T1 field 6 (decamal)   T2 (text)

    part#                reason                amount                     notes

     

    T1 field 1-3 are constant for each form

    T1 field 4-6 and T2 have multipul entries I would like to use tabular list or somthing simaler.

    using linked tables in to and mdb every thing works when I use same form in adp the key field in T2 is not updated and I get a other user modified data error when I try to save a recored.

     

  • It's still form and subform. Or in this case subform and sub- subform.

    The layout is a bit of a problem, but you can do the same thing by adding T2 on a sub form to your subform and getting the linking to work.

    Part of the problem is that adp thinks it knows how to do things and often doesn't. mdb is oftem simpler because you  do the work not Access.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply