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

Hi:

I'm still a little fuzzy on what you want to do, but I can give you a
hint.
Instead of all the SQL/recordset stuff you can use the Domain
Aggregate functions.
dim CutCount as long
dim Scount as long
' get cutid from form

cutcount = dcount("CutId","Coloncutlog","CutId = " & me.cutid)
A number > 0 tells you there is a matching cut record for the slide.

Do the samething for the slides
Scount = dcount("SlideID","ColonSlides","CutId = " & me.cutid & " and
SlideNumber = " & me.slidenumber )

Same deal here. If 0 insert record, otherwise its a dupe.

I hope this helps.

Good Luck

Ira Solomon


On Sun, 12 Sep 2004 23:23:27 GMT, allyn44 (AT) cox (DOT) net wrote:

HI--thaks for the idea--it worked!!

On Mon, 13 Sep 2004 17:32:10 -0400, Ira Solomon <isolomon (AT) solomonltd (DOT) com> wrote: