Using vba to create new record and assign to fk, good or bad?

[Kryst51]

Hey all,

In another thread of mine the question came up that maybe my design, or what I was attempting to do would not be the best idea. I have written code, with the help of you all, to create a new record in one table (tblTagRelationships), find that record and assign it's pkID to the fkID in another table (tblTags). All this is done in the before update event on a form bound to "tblTags".

It was suggested to me that this may not be the best way to do things, as in a multiuser environment my code to find the most recent record of "tblTagRealtionships" could backfire on me as someone could potentially create a brand new record while this code is running and thus return the wrong pkID to store in "tblTags".

My thought, given my limited understanding, is that all of this code runs very quickly, and that this scenario is unlikely. Am I wrong? And should I consider other options?

For an explanation of what I am trying to accomplish see this thread.

 

[HiTechCoach]

My thought, given my limited understanding, is that all of this code runs very quickly, and that this scenario is unlikely. Am I wrong? And should I consider other options?

Things to things about:

It will probably never run fast enough with multiple users.

Why?

As the load increases in these areas:

• number of database users over 1
• Workstation performance, other software running
• network traffic (streaming media, etc)

the speed decreases.

Some of there are hard to control or predict, thus your chances of issue go up if you are dependent on constant fast performance.

Note: This is not an issue limited to just Access. This is an issue with all multiple user database, even SQL Servers.

 

[LPurvis]

Hi

We never did really talk through what the result of the raised flag was.
So let's also consider what you've said in this thread too.

>> create a new record in one table (tblTagRelationships), find that record and assign it's pkID to the fkID in another table (tblTags). All this is done in the before update event on a form bound to "tblTags".

So the first question that springs to my mind - why are you creating this record?
The form in question is bound to tblTags - but the record created is in tblTagRelationships, but this then inserts into a FK in another record in tblTags? (This is not the same record as currently displayed on the form?)

I'm just not sure of the overall business process being modelled here.

Back on to the particular technical issue...
It's not just a question of whether there's much risk of multiple-users causing concurrency problems, it's that if there's a more logical alternative.
As far as fetching a value from an inserted row, there surely is. There must be.

I touched on this point briefly last time - but let's readdress it again.
Either the PK value you're pulling back is:
AutoNumber
The value is immediately available for retrieval without requiring a request of any Max value
Entered By The User
Some reference manually entered - that action of manually entering offers the window to hold it for subsequent use
Generated Automatcally
Again the action by which it's generated should open the opportunity to store the last inserted value.
In either of the last two scenarios though - if you're also using an Autonumber then you can fetch that value to determine which row was just inserted (which information you can the use to determine whatever detail was also inserted in that row).

However fast or unlikely for error the Max method may be - it opens a window of opportunity for problem. Not only a problem - but one you wouldn't necessarily know anything about until some later date when child records were missing for some parent (having been inaccurately assigned to another parent row by mistake).

Consider also - if you're considering values such as a Date, in a FileServer database like Jet/ACE the time inserted is that of the User's PC. If that's inaccurate (for example fast) you could easily have that person inserting values which appear to be more recent than anything any other users is inserting.
(The thought of which is enough to give anyone the willies).

In other words - there are other options, you should consider them.
But what exactly you're fetching is key.

Cheers.

 

[GaryPanic]

whats the relationship between the two tables ?

If you are after the two tables to be linked ( andI will assume that there is one entry in each table ) then you could (Note Could) use add. this would run after you have created your record in table A and then it will create another record in table B - but with the PK of table A (either as the PK of table B or as a FK)

I have a table Main(A) and I have a compliance table(B)
there will only ever be 1 record in each table that relate to each other - now this might not totally kosha - but the multi user problem disappears (99.99%) of the time

now if you are going to have multi records in table B then there are alternatives to thin
this allows tables to be kept this (ie not wide) -

I wish I had taken this approach earlier on as it would of saved a lot of headaches ..

now this might not be what you are after - but its a thought ..
regards
gp

 

[Kryst51]

Hi

We never did really talk through what the result of the raised flag was.
So let's also consider what you've said in this thread too.

>> create a new record in one table (tblTagRelationships), find that record and assign it's pkID to the fkID in another table (tblTags). All this is done in the before update event on a form bound to "tblTags".

So the first question that springs to my mind - why are you creating this record?
The form in question is bound to tblTags - but the record created is in tblTagRelationships, but this then inserts into a FK in another record in tblTags? (This is not the same record as currently displayed on the form?)

*Yes, it is assigning the new pk from the other table to the fk of the form table in the current record.

I'm just not sure of the overall business process being modelled here.

Back on to the particular technical issue...
It's not just a question of whether there's much risk of multiple-users causing concurrency problems, it's that if there's a more logical alternative.
As far as fetching a value from an inserted row, there surely is. There must be.

I touched on this point briefly last time - but let's readdress it again.
Either the PK value you're pulling back is:
AutoNumber
The value is immediately available for retrieval without requiring a request of any Max value
Entered By The User
Some reference manually entered - that action of manually entering offers the window to hold it for subsequent use
Generated Automatcally
Again the action by which it's generated should open the opportunity to store the last inserted value.
In either of the last two scenarios though - if you're also using an Autonumber then you can fetch that value to determine which row was just inserted (which information you can the use to determine whatever detail was also inserted in that row).

*The business practice being modelled - I don't know if there is one really, I tried to explain in the other thread, but it's hard to explain and I don't know how, the only reason I can build this DB at all is because I've done the job, and the work, so I know how quierky it is. Basically the DB manages and deals with any and all data concerned with nonconformances that arise in our company, from defective material (physical, mecanical or chemical), credits to customers, debits to vendors, pricing issues, weight discrepancies (we are a nonferrous metal distribution co.), procedural errors, freight claims... you name the error and it could happen. We have one person who deals with it all and they are given a form with the basic info, and then has to deal with every aspect until the issue is completely resolved. So I designed this DB when I was still doing this job, as it was originally summarized on spreadsheets, but that got to be unwieldy. All information is gathered here, then passed to the people who do the things such as - pass the credit to the customer based off the information given to them from the DB. Same with a debit to the vendor.

*The value is an autonumber.

However fast or unlikely for error the Max method may be - it opens a window of opportunity for problem. Not only a problem - but one you wouldn't necessarily know anything about until some later date when child records were missing for some parent (having been inaccurately assigned to another parent row by mistake).

Consider also - if you're considering values such as a Date, in a FileServer database like Jet/ACE the time inserted is that of the User's PC. If that's inaccurate (for example fast) you could easily have that person inserting values which appear to be more recent than anything any other users is inserting.
(The thought of which is enough to give anyone the willies).

In other words - there are other options, you should consider them.
But what exactly you're fetching is key.

Cheers.

- I really appreciate what you've brought to the table for me to consider. (I can't tell you how much ) Now, its just figuring out how to move forward.

Since your post on my other thread, I have tried to create the subform (continuous) using the tblTagRelationship table, with a subform for tblTag, but it won'd allow continuous forms when there is subform on it. So, I need to come up with another way to achieve what I want. And I guess, this isn't as easy as I hoped for. I'll think about it a few more days, and when I come up with a way, I'll be sure to post back.

In the meantime, any comments you have, I certainly appreciate.

 

[Kryst51]

Things to things about:

It will probably never run fast enough with multiple users.

Why?

As the load increases in these areas:

• number of database users over 1
• Workstation performance, other software running
• network traffic (streaming media, etc)

the speed decreases.

Some of there are hard to control or predict, thus your chances of issue go up if you are dependent on constant fast performance.

Note: This is not an issue limited to just Access. This is an issue with all multiple user database, even SQL Servers.

Thank you so much! I understand now, more fully, why my idea won't work. Back to the drawing board, so to speak.

 

[Kryst51]

whats the relationship between the two tables ?

If you are after the two tables to be linked ( andI will assume that there is one entry in each table ) then you could (Note Could) use add. this would run after you have created your record in table A and then it will create another record in table B - but with the PK of table A (either as the PK of table B or as a FK)

I have a table Main(A) and I have a compliance table(B)
there will only ever be 1 record in each table that relate to each other - now this might not totally kosha - but the multi user problem disappears (99.99%) of the time

now if you are going to have multi records in table B then there are alternatives to thin
this allows tables to be kept this (ie not wide) -

I wish I had taken this approach earlier on as it would of saved a lot of headaches ..

now this might not be what you are after - but its a thought ..
regards
gp

Basically, tblTagRelationships is a one to many with Tags...

I don't understand what this means:

now if you are going to have multi records in table B then there are alternatives to thin
this allows tables to be kept this (ie not wide) -

 

[LPurvis]

I think it's just been a struggle to envisage the roles of the tables based on their names.
tblTag sounds like a clear entity defining table. You have some "Tag" things.
tblTagRelationships sounds like a table which relates Tags (either to each other or some other "stuff" - I dunno all this technical talk huh? ;-)

But what you're actually doing is creating a new TagRelationship and entering its primary key value (an AutoNumber) into a foreign key field in the current Tag form record...

Just accepting that this models your business for now - then the process could be really simple.

For example - I don't know how you're creating your TagRelationship record.
Let's suppose it's with a simple INSERT statement.
Your entire process could be as simple as:

With CurrentDb
    .Execute "INSERT INTO tblTagRelationships (FieldX, FieldY, FieldZ) VALUES ('" & strValX & "', '" & strValY & "', '" & strValZ & "')", dbFailOnError
    lngNewID = .OpenRecordset("SELECT @@Identity")(0)
    Me.FKField = lngNewID
End With

Actually - you have been using ADO now I think about it. Almost identical...

With CurrentProject.Connection
    .Execute "INSERT INTO tblTagRelationships (FieldX, FieldY, FieldZ) VALUES ('" & strValX & "', '" & strValY & "', '" & strValZ & "')"
    lngNewID = .Execute("SELECT @@Identity")(0)
    Me.FKField = lngNewID
End With

Now how you choose the values to insert etc I can't say.
But inserting, fetching the new autonumber and inserting it locally can be that simple.

As for how you're wanting to display this in forms (continuous etc) that's harder to know. Depends if this is many to many, just one to many etc.

Cheers.

 

[Kryst51]

I think it's just been a struggle to envisage the roles of the tables based on their names.
tblTag sounds like a clear entity defining table. You have some "Tag" things.
tblTagRelationships sounds like a table which relates Tags (either to each other or some other "stuff" - I dunno all this technical talk huh? ;-)

OK.... Outside the DB world.... We have material that we purchase, it is recieved in as bundles or whatever from vendors, and each bundle or box has a particular "Heat" or "Lot" number, PO-number, and other identifying info. This material is received in and each bundle is given a tag number that is unique to that bundle and ties it to this other information, plus includes how many pieces are in the bundle and the weight of the bundle. Hence the tag. At this point if something was found to be wrong with the material an NCR would be filled out with the tag details, and what was wrong, it would be considered "In-House" as its currently in our warehouse. When a tag is shipped from the packing list is given a new tag number, what I refer to as a "ship tag" which identifies only what shipped to the customer. if any or all of that ship tag is returned to us for any reason it gets a "return tag". So the in my db the only ones that need to relate are the "Ship tags" and the "return tag". It had been suggested to me in an earlier thread that the way I could keep this normalized was to create a tag relationship table, so I did.

But what you're actually doing is creating a new TagRelationship and entering its primary key value (an AutoNumber) into a foreign key field in the current Tag form record...

Just accepting that this models your business for now - then the process could be really simple.

For example - I don't know how you're creating your TagRelationship record.
Let's suppose it's with a simple INSERT statement.
Your entire process could be as simple as:

With CurrentDb
    .Execute "INSERT INTO tblTagRelationships (FieldX, FieldY, FieldZ) VALUES ('" & strValX & "', '" & strValY & "', '" & strValZ & "')", dbFailOnError
    lngNewID = .OpenRecordset("SELECT @@Identity")(0)
    Me.FKField = lngNewID
End With

Actually - you have been using ADO now I think about it. Almost identical...

With CurrentProject.Connection
    .Execute "INSERT INTO tblTagRelationships (FieldX, FieldY, FieldZ) VALUES ('" & strValX & "', '" & strValY & "', '" & strValZ & "')"
    lngNewID = .Execute("SELECT @@Identity")(0)
    Me.FKField = lngNewID
End With

Now how you choose the values to insert etc I can't say.
But inserting, fetching the new autonumber and inserting it locally can be that simple.

As for how you're wanting to display this in forms (continuous etc) that's harder to know. Depends if this is many to many, just one to many etc.

Cheers.

I am using ado, with the insert thing. And at the point I am doing this I only NEED the pk, but decided to the date time stamp to help me find the record. which I could have just found the max pk, but I've been told that autonumbers are not reliable so I didn't think that would be the best way to do things. On my form, there will only be a one-one relationship... Later I will be adding on only one other tag (a return tag, if necessary) to that relationship.

 

[Kryst51]

Oh. yeah, LPurvis, I already have the code to do the insert... I'll post my entire code if you want. But I don't think I am going to continue down this vein as I think you and Hi-Tech Coach are right, in that this will cause problems later.

 

[LPurvis]

>>I am using ado, with the insert thing.
So the example is already directly relevant - you can replace your own insert statement there. Cool.

>> And at the point I am doing this I only NEED the pk,
Makes it easier.

>> but decided to the date time stamp to help me find the record. which I could have just found the max pk, but I've been told that autonumbers are not reliable so I didn't think that would be the best way to do things.
I think you may have been... well, mis-advised is maybe a bit strong... You may have been overly put-off one course of action by a comment which possibly meant something else.
(Either that - or you were just plain mis-advised ;-)

Autonumbers aren't at all unreliable. (That was probably a poor choice of words if someone used them to you - and has no doubt encouraged your mistrust of them).
They do exactly what they're supposed to do - and no more.
You absolutely can't assume that the maximum value is the one that you've just inserted - just as you can't entirely assume that the maximum date value is the one you just inserted. :-s
What you absolutely can do is determine the value of the autonumber that was just inserted by your previous connection's request.
(That's what the @@Identity retrieval is in the code I listed above).
This fetches the last autonumber value - which you can then use in your form, e.g.
Me.FKField = lngNewID
Test it out. See how you go.

Cheers.

 

[Kryst51]

>>I am using ado, with the insert thing.
So the example is already directly relevant - you can replace your own insert statement there. Cool.

>> And at the point I am doing this I only NEED the pk,
Makes it easier.

>> but decided to the date time stamp to help me find the record. which I could have just found the max pk, but I've been told that autonumbers are not reliable so I didn't think that would be the best way to do things.
I think you may have been... well, mis-advised is maybe a bit strong... You may have been overly put-off one course of action by a comment which possibly meant something else.
(Either that - or you were just plain mis-advised ;-)

Autonumbers aren't at all unreliable. (That was probably a poor choice of words if someone used them to you - and has no doubt encouraged your mistrust of them).
They do exactly what they're supposed to do - and no more.
You absolutely can't assume that the maximum value is the one that you've just inserted - just as you can't entirely assume that the maximum date value is the one you just inserted. :-s
What you absolutely can do is determine the value of the autonumber that was just inserted by your previous connection's request.
(That's what the @@Identity retrieval is in the code I listed above).
This fetches the last autonumber value - which you can then use in your form, e.g.
Me.FKField = lngNewID
Test it out. See how you go.

Cheers.

Ohhhhh! Very useful..... I will try this first thing tomorrow morning! Thank you!

 

[Kryst51]

I am finally able to come back to this DB after being too busy at work with other things. Before I try to change my code to try LPurvis' suggestion above I wanted to make sure the current code worked.

It works sporadically. When I enter records it will sometimes do what I ask it to, and other times it won't. When I step through the code using a break and then F8 it always works with no errors, so I really don't know what is going on. I am attaching my current db sans any sensitive info. On the main form that opens on startup click the "Add Items" button and this will open the form where I am entering tag info. None of the combo boxes should work right now as those will normally pull from odcb linked tables. The subform is where you enter info and after each record, on the before update event the code "should" run.

I have looked at the code and can see no errors. I have tried many combinations of data to see if it was the data I was entering that could cause it to behave this way. If anyone could look at it and tell me what I am doing wrong I would really appreciate it.


For convenience sake here is my current code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
'Add Record to tblTagRelationships declarations
Dim ctl As Control
Dim strError As String
 
'Find New TagRelationship Record and insert into current
'tag declarations and SQL
Set MasterDbConn = CurrentProject.Connection
Dim rsMaxRTId As ADODB.Recordset
Dim rsMaxRTIDSQL As String
Dim MaxRTID As Integer
Set rsMaxRTId = New ADODB.Recordset
 
rsMaxRTIDSQL = "SELECT tblTagRelationships.pkTagRelationshipID" & _
               " FROM qryTagRelationshipMax" & _
               " INNER JOIN tblTagRelationships" & _
               " ON qryTagRelationshipMax.MaxOfDateTimeStamp" & _
               " = tblTagRelationships.DateTimeStamp;"
 
'Add Record to tag relationships
'This checks each visible control for a value
'and returns an error if anyone of them
'does not have an appropriate value
For Each ctl In Me.Controls
   If ctl.Tag = "DATA" Then
      If Len(ctl.Value & "") = 0 Then
         strError = strError & ctl.Name & ", "
      End If
   End If
Next ctl
 
'If an error exists then this displays a messagebox
'which throws the user back onto the form without
'updating the record
If strError <> "" Then
   If Right(strError, 2) = ", " Then
      strError = Left(strError, Len(strError) - 2)
   End If
   MsgBox "You are MISSING DATA in these fields: " & vbCrLf & strError
   Cancel = True
   Exit Sub
End If
 
'this checks if all neccessary fields have a value
'if they do then this will go through a procedure to assign
'a tag relationship and then assign that relationship to the NCR
If Not Len(Me.TagNum) = 0 And Nz(Me.TagPcs) And Nz(Me.TagWt) And Nz(Me.cmbType) And Nz(Me.cmbWtUom) Then
 
        Dim NewTagRelQRY As String
        Dim AssignNCR As String
        Dim NCRID As Integer
        Dim fkTRID As Integer
 
        NewTagRelQRY = "Insert INTO tblTagRelationships(DateTimeStamp)" & _
        " VALUES (Now());"
 
        AssignNCR = "Update tblTagRelationships" & _
        " Set fkNCRHeaderID = NCRID" & _
        " Where pkTagRelationshipID = fkTRID;"
 
            'If the record is a new record then a Tag Relationship
            'is created and assigned. But if it is not a new
            'record then this has already been done, and there
            'will be problems if it is given a new one.
            If Me.NewRecord = True Then
 
                'This will add a new record in the tag relationships
                CurrentDb.Execute NewTagRelQRY
 
            'This will put that new record number into the tag so that they are related
            With rsMaxRTId
                .Open rsMaxRTIDSQL, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
            MaxRTID = rsMaxRTId!pkTagRelationshipID
 
            rsMaxRTId.Close
            Set rsMaxRTId = Nothing
            End With
 
            Me.fkTagRelationshipID = MaxRTID
            NCRID = Me.Parent.txtNCRHeaderID
            fkTRID = Me.fkTagRelationshipID
 
                    AssignNCR = "Update tblTagRelationships" & _
                    " Set fkNCRHeaderID =" & NCRID & _
                    " Where pkTagRelationshipID =" & fkTRID
 
 
            End If
 
            'This will assign the current NCR to the tagrelationship
            If Me.NewRecord = True Then
            CurrentDb.Execute (AssignNCR)
            End If
    Else
    Exit Sub
    End If
End Sub

 

[LPurvis]

I'm not seeing an attachment. Nor am I seeing any use of the @@Identity function mentioned last time out?
It seems that it's back to the old Max query?

The thing that stands out to me in your procedure is the mixture of ADO and DAO methods. They maintain distinct connections to the data, and cache updates differently (ADO is a more notorious cacher).
This could easily result in some apparently (though not really) sporadic behaviour.

Though I'm a firm believer in the use of both libraries - I don't mix them within the same procedure. There's just never a real need to do so - but other considerations to weigh if you do.

Consider both the @@Identity function and choosing a single data access API to begin with.

Cheers.

 

[Kryst51]

I'm not seeing an attachment. Nor am I seeing any use of the @@Identity function mentioned last time out?
It seems that it's back to the old Max query?

The thing that stands out to me in your procedure is the mixture of ADO and DAO methods. They maintain distinct connections to the data, and cache updates differently (ADO is a more notorious cacher).
This could easily result in some apparently (though not really) sporadic behaviour.

Though I'm a firm believer in the use of both libraries - I don't mix them within the same procedure. There's just never a real need to do so - but other considerations to weigh if you do.

Consider both the @@Identity function and choosing a single data access API to begin with.

Cheers.

Hi LPurvis, I haven't implemented you suggestion yet, because I didn't want to stray too far from what I had done when I didn't even understand why it wasn't working as it is. I'll go ahead and implement the Identity function tomorrow afternoon. Honestly, I thought I was using all ADO, I'll have to check it out. I don't know much about the two, and this is my first time using ADO at all. It would be cool if doing these two things fixes the problem and as always cleaner and correct code is always better.

Concerning the attachment, I could have sworn I put it on there, I'll have to do that tomorrow too for completeness sake. It seems as if I posted today for naught, I apologize for that. I should have just tried what you said first. I was hoping to avoid complete confusion for myself later.

 

[LPurvis]

No worries.

I should have been more explicit too.
All your object variables are using ADO, but your methods aren't. (i.e. Access build in objects and methods for data access will frequently use one or the other, returning an object of the type belonging to one or the other, but without the library actually needing to be present).

For example:
CurrentDb.Execute (AssignNCR)
is a DAO method.
CurrentDb returns a DAO database object. That it's created implicitly (and then sliently destroyed) doesn't change the fact that it is there and used.
And the result of that can be as I mentioned, the two libraries connections operating out of sync.

When you feel it's all OK as it is, then you'll be able to consider trying the @@Identity method at your leisure.

Cheers

 

[dfenton]

I can nowhere find any justification for using ADO at all. If your back end is Jet/ACE, then DAO is the preferred interface. If, however, your back end is SQL Server, you really want to use SCOPE_IDENTITY() instead of @@IDENTITY, since an insert that has triggers attached could lead to an incorrect value returned for @@IDENTITY, but will not do so for the alternative.

Now, that said, I don't think ADO is the source of the problem, but there's been so much lengthy discussion that I'm not really sure what the problem is, nor even why this has to be done in code. It's usually done with forms and subforms, in fact, with no code at all.

 

[Kryst51]

I can nowhere find any justification for using ADO at all. If your back end is Jet/ACT, then DAO is the preferred interface. If, however, your back end is SQL Server, you really want to use SCOPE_IDENTITY() instead of @@IDENTITY, since an insert that has triggers attached could lead to an incorrect value returned for @@IDENTITY, but will not do so for the alternative.

Now, that said, I don't think ADO is the source of the problem, but there's been so much lengthy discussion that I'm not really sure what the problem is, nor even why this has to be done in code. It's usually done with forms and subforms, in fact, with no code at all.

I would prefer to do form/subform, but don't see how to get the flexibility I need without code, and as to get the help I need here would require a ton of time for people and a ton of knowledge about what the job is this is being designed for, I don't know that anybody would be up for the challenge without getting paid.

 

[Kryst51]

No worries.

I should have been more explicit too.
All your object variables are using ADO, but your methods aren't. (i.e. Access build in objects and methods for data access will frequently use one or the other, returning an object of the type belonging to one or the other, but without the library actually needing to be present).

For example:
CurrentDb.Execute (AssignNCR)
is a DAO method.
CurrentDb returns a DAO database object. That it's created implicitly (and then sliently destroyed) doesn't change the fact that it is there and used.
And the result of that can be as I mentioned, the two libraries connections operating out of sync.

When you feel it's all OK as it is, then you'll be able to consider trying the @@Identity method at your leisure.

Cheers

Yay! It Works!

OK..... I think I have it just about right. I have commented out parts of code I don't want to delete just yet in case I have made a mistake.... So here is my updated code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Sets ADO connection
Set MasterDbConn = CurrentProject.Connection
'Declarations
Dim rsNewRTId As ADODB.Recordset
'Dim rsNewRTIDSQL As String
Dim NewRTID As Integer
 
Set rsNewRTId = New ADODB.Recordset
'Add Record to tag relationships
Dim NewTagRelQRY As String
Dim AssignNCR As String
Dim NCRID As Integer
Dim fkTRID As Integer
NCRID = Me.Parent.txtNCRHeaderID
'rsNewRTIDSQL = "SELECT tblTagRelationships.pkTagRelationshipID" & _
'               " FROM qryTagRelationshipMax" & _
'               " INNER JOIN tblTagRelationships" & _
'               " ON qryTagRelationshipMax.MaxOfDateTimeStamp" & _
'               " = tblTagRelationships.DateTimeStamp;"
 
'SQL to create a new record in the tblTagRelationships
NewTagRelQRY = "Insert INTO tblTagRelationships(DateTimeStamp)" & _
" VALUES (Now());"
AssignNCR = "Update tblTagRelationships" & _
" Set fkNCRHeaderID =" & NCRID & _
" Where pkTagRelationshipID =" & fkTRID
 
    'If the record is a new record then a Tag Relationship
    'is created and assigned. But if it is not a new
    'record then this has already been done, and there
    'will be problems if it is given a new one.
If Me.NewRecord = True Then
 
    'This will add a new record in the tag relationships
    'and put that number into the tag foreign key
    With CurrentProject.Connection
        .Execute NewTagRelQRY
        NewRTID = .Execute("SELECT @@Identity")(0)
        Me.fkTagRelationshipID = NewRTID
        fkTRID = Me.fkTagRelationshipID
    End With
 
        'This will put that new record number into the tag so that they are
        'related
        'With rsNewRTId
        '    .Open rsNewRTIDSQL, MasterDbConn, adOpenKeyset, 
        '    adLockOptimistic, adCmdText
        '    NewRTID = rsNewRTId!pkTagRelationshipID
        '    rsNewRTId.Close
        '    Set rsNewRTId = Nothing
        'End With
End If
 
'This will assign the current NCR to the tagrelationship
If Me.NewRecord = True Then
    CurrentProject.Connection.Execute AssignNCR
End If
End Sub

Now I just need to look at what you mean by the closing of the connection bit.

 

[LPurvis]

I have no idea what I'd have meant by that in this thread - actually I can't find anything either. :-s

Glad you're sorted now though!