Help w/ Linking tables in a Database 1

[Joel27]

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

 

[ghubbell]

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

 

[Joel27]

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

 

[ghubbell]

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

 

[Joel27]

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-

 

[ghubbell]

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