Auto Update a Table

[Altin]

Hi

I have an Access 2003 database for tracking requested updates to Standard Operating Procedures in work. Currently I have just 1 table with fields for SOP Number, Details of update & other specific details like requestor & date. Any 1 SOP number can have a number of updates logged against it, & I understand that strictly speaking, information should only exist once in databases. That would mean having another table for SOP numbers & establishing a relationship between the 2 tables.

It would not be practical to populate the SOP number table with all SOPs, in case new SOPs get written, but would it be possible to write code to have Access check if the SOP number is in the table, & if not then add it in?

Thanks

 

[spikepl]

Have a peek at this http://www.access-programmers.co.uk/forums/showthread.php?t=211285

 

[Altin]

Hi

He seems to have a different problem to mine. I am trying to get a one-to-many relationship working between 2 tables, where the entry in the 'one' table (the SOP number) may not exist yet. My tables are:

tblSOP:
AutoID (Autonumber)
SOPNumber (Text)

tblRecords:
RecordID (Autonumber)
SOPNumber (Number)
Details (Memo)
Date (Date)

I want to make a relationship between tblSOP.AutoID and tblRecords.SOPNumber. I want to be able to open a form with tblSOP.SOPNumber, tblRecords.Details & tblRecords.Date in order to add new records. Ive only been able to get this to work if the values in tblSOP.SOPNumber already exist & I use a combo box in tblRecords, but I want people to type them in & if the value is not there already, then it gets added to tblSOP.SOPNumber. Im sure there will have to be code involved to make it work.

I hope Im explaining this right

 

[spikepl]

Your relations should be like this:

tblSops:
SopID (Autonumber)
SOPNumber (Text)

tblRecords:
RecordID (Autonumber)
SopID (Foreign Key)
Details (Memo)
Date (Date)

Provided it is the same SOP we speak of. In this way you have a one-to-many relation between a given SOP and the corresponding records (a bit unfortunate name - at least Details would be more descriptive)