×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help w/ Linking tables in a Database

Help w/ Linking tables in a Database

Help w/ Linking tables in a Database

(OP)
I am currently working on a database using Access 97.
I am entering in blue print numbers i.e. a30084, c32566,etc.
Right now I have one table that contains all the blue print numbers and description etc. But I wanted to make another table for only the electrical prints. The problem is I dont' want to use the same print number twice between the two tables. Is there a way to prevent duplicates in two or more tables?

Thank you Joel

RE: Help w/ Linking tables in a Database

Hi Joel,
How about this: 1 table with 1 extra field: Yes/No and call it "Electrical".
You do all your entries in the one table, if it's "electrical", flip the switch...no pun intended...to True.
Based on your blue print numbers being unique you'll be all set. You can now sort by "Electrical" True or False or not at all.  Bingo!

Gord
ghubbell@total.net

RE: Help w/ Linking tables in a Database

(OP)
I was more or less thinking that there was a relationship that would protect the unique value. Heres the low down, There is a Drawlog Table, in which all prints get entered in by. Now the electrical dept wants there own table for there prints. Between The Drawlog and electrical tables we need to prevent duplicates from being entered between the two tables.
Exactly like a primary key, but two tables are sharing the primary key.
Maybe this helps you more

Thanks Joel

RE: Help w/ Linking tables in a Database

Right. Exactly, if you work right from the "Drawlog" table:

UniqueID
Description
Dwg Number
Department (or Yes/No)

For the electrical guys, show all records where department = "Electrical"  (or where = Yes)

For the others, show all records where department = "Whatever" (or where = No)

For you who has to maintain it and keep watch over the integrity, Show all records.

The electricians wouldn't even know any better if this was put through a form that was built off of a query as above.

Ditto for the others.

But for you...easy work...

Perhaps the electricians want to enter data themselves? Just make sure the default value of the "Department" or Yes/No on their form is set appropriately. You can even hide this too. They'll never know.    :)

Gord
ghubbell@total.net

RE: Help w/ Linking tables in a Database

(OP)
I see what your saying about it. This makes sense, but what if they are going to enter their prints strictly through the Electrical Table, and not the Drawlog? How would we prevent duplicate prints numbers?
 
-joel-

RE: Help w/ Linking tables in a Database

Here Joel,
In the after update event of a form's field for the drawing number you would do something like this:

    Dim SQL1 As String, Rs As Recordset, Db As Database, Ctl As Control
    Set Db = CurrentDb()
    If IsNull(Me.YourFieldName) Or Me.YourFieldName = "" Then
        MsgBox CurrentUser() & ", please add an appropriate drawing number.", vbInformation, "Drawing number required..."
        Set Ctl = Me.YourFieldName
        Response = acDataErrContinue
        Ctl.Undo
        Ctl.SetFocus
    Else
        SQL1 = "SELECT YourTableName.* FROM YourTableName"   'check for dup names
        Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset)
        If Rs.RecordCount = 0 Then
            Rs.Close
            GoTo NewDrawing
        End If
        Rs.MoveFirst
        Do Until Rs.EOF
            If Rs!YourDrawingField = Me.YourFieldName Then
                Rs.Close
                MsgBox "There is already a drawing with number '" & Me.YourFieldName & "'. Please chose an alternate.", vbInformation, "Drawing Number Exists!"
                Me.YourFieldName = Empty
                Exit Sub
            End If
            Rs.MoveNext
        Loop
        Rs.Close

NewDrawing:
        SQL1 = "SELECT YourTableName.* FROM YourTableName"
        Set Rs = Db.OpenRecordset(SQL1, dbOpenDynaset, dbAppendOnly)
        Rs.addnew
        Rs!YourDrawingField = Me.YourFieldName
        Rs.Update
        Rs.Close
    End If


You would have to change the names of YourTableName, YourDrawingField, and YourFieldName.

Looks heavy but it's not and, it does work...!

Gord
ghubbell@total.net

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close