Hello--I have 2 tables:
Coloncutlog: CutId (autonumber), histnum, block--indexed on those 2 fields, other info fields
Colonslides: SlideID (autonumber) CutID (from Cutlog), slide number, other data fields.
a one to many relationship between the tables--for each cut there can be numerous slides.
I originally had the slide table set up as a subform of the cutlog table so when creating a new
slide record the CutID went in automatically-but this was not preferred by the user becasue they do
not want to filter for each Cutlog record--they want to be able to enter a batch of different
records quickly and have the checking occur as they enter.
Slide data is entered in batches--the histnum and block are entered on an unbound form (along with
other fields that are common for all records entered.) The slide may exist and it may not so when
creating new slide records I want to check to see if they exist already. I have tried 2 methods:
Number one: This involves adding the histnum and block fields to the slide table
Dim db As Database
Dim rs As Recordset
Dim sql As String
Set db = CurrentDb
sql = "SELECT * from ColonSlides WHERE HistNum = '" & Me.tmpHistNum & "' And Block = '" &
Me.tmpBlock & "' And Slide = '" & Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
If rs.RecordCount > 0 Then 'record in slide table exists
rs.Edit 'Edits existing record
rs!staindate = Me.tmpstaindate
snipped other statements
rs.Update
Else
rs.AddNew 'Creates new record
rs!HistNum = Me.HistNum
rs!Block = Me.Block
rs!Slide = Me.Slide
rs.Update
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
ON THE FORM I also have code to check the cutlog table for the existence of the histlog/block index.
The problem with this approach is that data is entered in both places and it would also appear that
slides with Hist/block #s that don't exist in the cut log could be entered--even though I have code
that checks for that. Additionally, the relationship between the 2 tables, when not based on the
CutID, is indeterminate. I have tried to pull the CutID in to the slide table when creating a new
record but it is not working. If I could insert that data into the new slide record this approach
may be ok.
Number 2: sql staement is a query betwene the 2 tables--the problem with this is that new records
are created in the Cutlog for each slide entry if the entry does not already exist
Dim db As Database
Dim rs As Recordset
Dim sql As String
Set db = CurrentDb
sql = "SELECT * from qryjoincutslide WHERE HistNum = '" & Me.tmpHistNum & "' And Block = '" &
Me.tmpBlock & "' And Slide = '" & Me.tmpSlide & "'"
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
If rs.RecordCount > 0 Then 'record in slide table exists
rs.Edit 'Edits existing record
rs!StainDate = Me.adate
snipped other statements
rs.Update
Else
rs.AddNew 'Creates new record
rs!HistNum = Me.tmpHistNum
rs!Block = Me.tmpBlock
rs!Slide = Me.tmpSlide
rs.Update
End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Any help would be appreciated--if more clarity is needed pleaes let me know
thank you
Bob Stafford