By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,937 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Working with recordsets

P: n/a
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

Nov 13 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.