1 Replies - 5809 Views - Last Post: 16 November 2012 - 10:13 AM

#1 depricated  Icon User is offline

  • Conversion Software Version 7.0
  • member icon

Reputation: 1618
  • View blog
  • Posts: 4,772
  • Joined: 13-September 08

Ensuring a Parent Record exists before creating Child Records

Posted 04 October 2012 - 12:26 PM

I'm trying to find a more elegant way to do this.

I have one table set up as having absolute authority, with it being parent to several other tables. For most child records, the parent record already exists in the table when they're created. However, I'm running into a situation where I'm trying to create a child record when the parent record is still brand new in the form. It may or may not have been saved. I want to create the parent record before appending to the child, but I don't want to /keep/ saving the parent record.

I've tried doing a Form_Load DoCmd.Save, it would appear to not have the record reserved at that point.

Here's what I'm doing right now:

Private Function SubmitUpdate(step As String)

DoCmd.RunCommand acCmdRefresh 'this forces the record to save, preventing a conflict where 
'we try to create a child record without a relevant parent record in the parent table.

Me.txtStep.Value = step 'used to pass the variable to a field so it can be used in the query

DoCmd.SetWarnings False 'don't need to ask the user for permission to create the log record constantly
DoCmd.OpenQuery "qappProcessLog", acViewNormal, acEdit
DoCmd.SetWarnings True


End Function

Any suggestions would be more than welcome. What I have now functions as I need it to, but I would prefer not to save every time that function is run. Since the function is creating an activity log that records whenever an action is performed, it's fairly frequently. It's possible to open the form and have that be run in the first Subroutine performed by the user, so I need it saved before that point.

Is This A Good Question/Topic? 0
  • +

Replies To: Ensuring a Parent Record exists before creating Child Records

#2 burrina  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 33
  • Joined: 09-November 12

Re: Ensuring a Parent Record exists before creating Child Records

Posted 16 November 2012 - 10:13 AM

You could try something like this;Substituting you table and field names;

If DCount("*","yourtablehere","[fieldnamehere] = #" & Me.[fieldnamehere] & "#
and [fieldname] = """ & Me.[fieldname] & """ and [fieldname] = """ &
Me.[fieldname] & """") > 0 Then
MsgBox "This record already exists"
Cancel = true
End If
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1