MyRec Feature

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using this code attached to a button so it inserts some data when I click
the button into another table.

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select * From Callnotes WHERE False")
MyRec.AddNew
MyRec!Status = "Open" ' You can add other field here in the same way
MyRec!Priority = "1" '
MyRec!Department = "Cancellations" '
MyRec!Subject = "Cancellation Inquiry" '
MyRec!Indicator = "Trouble" '
MyRec!CustomerID = Me.CustomerID
MyRec.Update

THE PROBLEM:

I have another table tied to the above one, called CallNotesDetails, which
basically has one important field called notes, so I can have one record in
callnotes, but many in callnotesdetails (trouble tickets with lots of
responses, etc)

The field that ties callnotesdetails to callnotes is called:

In callnotes (customerID is main field tying it main table) Another field
called CallNotesID, which is then used to tie it to callnotesdetails.

How would you modify the above so it inserts the above information, but then
creates a record in callnotesdetails AS WELL, but using the appropriate
CallNotesID so it is tied to the appropriate record in callnotes?

Thanks
Curtis
 
add related record using DAO AddNew
---

Hi Curtis,

firstly, since you are insserting record, you do not need to select just
certain records from CallNotes, you can do this:

MyDb.OpenRecordset("Callnotes", dbOpenDynaset)

Since you want to add data to a related table, you can capture
CallNotesID before you do MyRec.Update

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
dim mCallNotesID as Long

dim MyDb as Database
dim MyRec as dao.RecordSet

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Callnotes", dbOpenDynaset)
MyRec.AddNew
'other statements
mCallNotesID = MyRec!CallNotesID
MyRec.Update
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

then, you can do this:

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MyRec.close
Set MyRec = MyDb.OpenRecordset("CallnotesDetails", dbOpenDynaset)

MyRec.AddNew
MyRec!CallNotesID = mCallNotesID
'other statements
MyRec.Update

'release object variables
MyRec.close
set MyRec = nothing
set MyDb = notning
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
AWESOME!!!

Leaving out the DIM's, does this look ok? My question is the first group
which I didn't list before, all in the same button code, should I have the
first one worded differently for performance reasons? It works, but wasn't
sure if the first group should be worded differently for whatever reason.

======THIS ONE==========

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("FollowupNotes", dbOpenDynaset)
MyRec.AddNew
MyRec!Subject = "EMAILED Account Cancellation Letter" ' You can add other
field here in the same way
MyRec!CustomerID = Me.CustomerID
MyRec.Update

======THIS ONE==========

Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Callnotes", dbOpenDynaset)
MyRec.AddNew
MyRec!Status = "Open" ' You can add other field here in the same way
MyRec!Priority = "1"
MyRec!Department = "Cancellations"
MyRec!Subject = "Cancellation Inquiry"
MyRec!Indicator = "Trouble"
MyRec!CustomerID = Me.CustomerID
mCallNotesID = MyRec!CallNotesID
MyRec.Update


MyRec.Close
Set MyRec = MyDb.OpenRecordset("CallnotesDetails", dbOpenDynaset)

MyRec.AddNew
MyRec!CallNotesID = mCallNotesID
MyRec!Notes = "EMAILED cancellation letter"
MyRec.Update

MyRec.Close 'release object variables
Set MyRec = Nothing
Set MyDb = Nothing
 
Hi Curtis,

Looks good, just a couple comments.

~~~

It is best to include the Dim statements when you post code. Access
lets you not Dim variables but that is not a good idea! I always add

Option Explicit

to the top of my modules because I do not want to let Access allow me to
do this

~~~

If Priority is numeric, you will want to remove the quote delimiters

MyRec!Priority = 1

~~~

only do this once:

Set MyDb = CurrentDb

~~~~

before
Set MyRec = MyDb.OpenRecordset("Callnotes", dbOpenDynaset)

you will want to close the recordset for FollowupNotes

MyRec.Close

~~~




Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
It is best to include the Dim statements when you post code. Access
lets you not Dim variables but that is not a good idea! I always add

Option Explicit

to the top of my modules because I do not want to let Access allow me to
do this
Click to expand...


When you say modules, do you mean in the code section of the form at the top
where it says Option Compare Database?

If Priority is numeric, you will want to remove the quote delimiters

MyRec!Priority = 1
Click to expand...


It is a number, like 1, 2 or 3, etc. I thought I need quotes if I want
content inserted, not referring to a field in a table?

Thanks for the rest of the tips!!!

Curtis
 
Hi Curtis,

"When you say modules, do you mean in the code section of the form at
the top where it says Option Compare Database?"

yes, on the line just below that

You can and should make this a default so Access will put it there for
you when you next create a module...

when you are in a module -->
Tools, Options --> check 'Require Variable Declaration'

"I thought I need quotes..."

you only need quotes if the field in the table is a string

MyRec!stringField = "string"

if the field in the table is a number, you do not use any delimiter

MyRec!numField= 99

if the field in the table is a date, use #

MyRec!dateField= #1/1/2007#


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top