Adding Related Records to New Record

[Guest]

I have a form in an adp project that opens to a new record in tblTransaction,
which has an identity TransactionID field as its key field. There are a
number of fields for the user to fill out that are saved as fields in
tblTransaction, and an unbound combo box from which the user selects an item,
then clicks the "Add item" button.

The Add Item button is supposed to add a related record to
tblTransactionLineItem with its foreign key, TransactionID, set to the ID of
the new record in tblTransaction, then display the new record in a list box.

I have been unable to grab the new TransactionID to use in my insert. My
code is as follows:

If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
sql = "execute uspNewItem " & me.cboSelectBarCode & ", " &
me.TransactionID
...

I get an "Invalid Use of Null" error when trying to evaluate
me.TransactionID. When I try replacing Me.Refresh with Me.Recordset.Update,
I get runtime error 3021 "Either BOF or EOF is True, or the current record
has been deleted."

How can I force the form to save the current new record without closing and
then get the value of the identity field?

 

[Dirk Goldgar]

I have a form in an adp project that opens to a new record in
tblTransaction, which has an identity TransactionID field as its key
field. There are a number of fields for the user to fill out that
are saved as fields in tblTransaction, and an unbound combo box from
which the user selects an item, then clicks the "Add item" button.

The Add Item button is supposed to add a related record to
tblTransactionLineItem with its foreign key, TransactionID, set to
the ID of the new record in tblTransaction, then display the new
record in a list box.

I have been unable to grab the new TransactionID to use in my insert.
My code is as follows:

If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
sql = "execute uspNewItem " & me.cboSelectBarCode & ", " &
me.TransactionID
...

I get an "Invalid Use of Null" error when trying to evaluate
me.TransactionID. When I try replacing Me.Refresh with
Me.Recordset.Update, I get runtime error 3021 "Either BOF or EOF is
True, or the current record has been deleted."

How can I force the form to save the current new record without
closing and then get the value of the identity field?

Hmm, normally I'd expect Me.Refresh to work, but I'm not as experienced
in the quirks of ADPs as I am with MDBs. Try this instead of
"Me.Refresh":

If Me.Dirty Then Me.Dirty = False

 

[Guest]

Thanks for your reply, Dirk. I tried substituting "If me.Dirty then me.Dirty
= False" for "Me.Refresh", but I continue to get runtime error 3021 "Either
BOF or EOF is true, or the current record has been deleted."

 

[Dirk Goldgar]

Thanks for your reply, Dirk. I tried substituting "If me.Dirty then
me.Dirty = False" for "Me.Refresh", but I continue to get runtime
error 3021 "Either BOF or EOF is true, or the current record has been
deleted."

Did you remove the code that you had that tries to force an update via
"Me.Recordset.Update" ? That's what you said gave you that error
before. Please post the whole code from the AddItem button's Click
event procedure.

 

[Guest]

I changed my code a little for testing purposes. It presently reads like this:

If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
If Me.Dirty Then Me.Dirty = False
MsgBox Me.Recordset.Fields("TransactionID")

Commenting out either "Me.Refresh" of "If Me.Dirty..." still produces the
same error ("...EOF or BOF...") when trying to get a value for TransactionID.
The expression "me.TransactionID" evaluates to Null.

However, if I close the form, the record is added to the table with a new
incremented value of TransactionID.

 

[Dirk Goldgar]

I changed my code a little for testing purposes. It presently reads
like this:

If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
If Me.Dirty Then Me.Dirty = False
MsgBox Me.Recordset.Fields("TransactionID")

Commenting out either "Me.Refresh" of "If Me.Dirty..." still produces
the
same error ("...EOF or BOF...") when trying to get a value for
TransactionID. The expression "me.TransactionID" evaluates to Null.

However, if I close the form, the record is added to the table with a
new incremented value of TransactionID.

After executing Me.Refresh, it is not possible for Me.Dirty to be True,
so there's no point in having both statements.

I ask again, please post the complete code from the button's Click event
procedure, and indicate exactly which line raises the error.

 

[Guest]

Private Sub btnAddReel_Click()

Dim sql As String
Dim r As Integer
If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
sql = "uspAccessReel " & Me.cboSelectBarCode & ", " & Me.TransactionID
CurrentProject.Connection.Execute sql, r
End If

End Sub

I get an error on the execute sql line: "Incorrect syntax near ','."

 

[Dirk Goldgar]

Private Sub btnAddReel_Click()

Dim sql As String
Dim r As Integer
If Not IsNull(Me.cboSelectBarCode) Then
Me.Refresh
sql = "uspAccessReel " & Me.cboSelectBarCode & ", " &
Me.TransactionID CurrentProject.Connection.Execute sql, r
End If

End Sub

I get an error on the execute sql line: "Incorrect syntax near ','."

What is the value of the sql variable at that time? Set a breakpoint on
the CurrentProject.Connection.Execute line, and when it breaks there,
hover you cursor over the sql variable and report back on what the popup
value tip shows.

 

[Guest]

The sql variable comes up as "uspAccessReel 2,"

The number preceding the comma varies depending upon what I select in the
cboSelectBarCode combo box. The value of me.TransactionID is supposed to go
after the comma, but it always comes up blank.

 

[Dirk Goldgar]

The sql variable comes up as "uspAccessReel 2,"

The number preceding the comma varies depending upon what I select in
the cboSelectBarCode combo box. The value of me.TransactionID is
supposed to go after the comma, but it always comes up blank.

That sure looks like the TransactionID is Null, all right. But
Me.Refresh, in my experience, moves the form back to the start of the
recordset, so I don't think you should be using it. However, I can't
reproduce your problem in an ADP of my own, even when I use this code in
the Click event of a command button:

MsgBox "Initially " & IIf(IsNull(Me.CCGroupID), "Null",
Me.CCGroupID)
If Me.Dirty Then Me.Dirty = False
MsgBox "Afterward " & IIf(IsNull(Me.CCGroupID), "Null",
Me.CCGroupID)

What is the definition of your TransactionID field? What version of
Access are you using? I tested with Access 2002.

 

[Guest]

TransactionID is the key field of my tblTransaction. It's an int identity
field.

I launch the form using docmd.openform specifying acAdd. The TransactionID
field has a textbox on the form which displays "(autonum)" (or something like
that; I'm not at my home computer right now where I can look), and I have not
found any way to make it change to a number.

Since I have a deadline to meet, I have come up with a kludge that
effectively side-steps this issue. Before opening the form, I use ADO to add
a new record. I get the ID of the new record and open the form in edit mode
to that record.

 

[Dirk Goldgar]

TransactionID is the key field of my tblTransaction. It's an int
identity field.

I launch the form using docmd.openform specifying acAdd. The
TransactionID field has a textbox on the form which displays
"(autonum)" (or something like that; I'm not at my home computer
right now where I can look), and I have not found any way to make it
change to a number.

Since I have a deadline to meet, I have come up with a kludge that
effectively side-steps this issue. Before opening the form, I use
ADO to add a new record. I get the ID of the new record and open the
form in edit mode to that record.

You tried following the example I posted in my last message? As I said,
it works for me -- after setting Me.Dirty = False, the autonumber field
has a value. Of course, that was working with a test ADP linked to a
local SQL Server back-end. If you get a different result, maybe it has
to do with a latency period in communicating with the back-end.

The only other thing I can think of is that you may have failed to dirty
the main-form record before attempting to save it. Naturally, the
record won't be saved unless it has been modified in some way. I
suppose that's something you may possibly have overlooked.

Your kludge doesn't sound horrible to me, but I still don't see why you
need it. The code I posted works for me. I'd say, "Send me your ADP",
but then you'd have to send me your SQL Server database as well, or a
script to create it, and that begins to go beyond the scope of newsgroup
support.

 

[Dirk Goldgar]

(sending again, as first post hasn't appeared)

TransactionID is the key field of my tblTransaction. It's an int
identity field.

I launch the form using docmd.openform specifying acAdd. The
TransactionID field has a textbox on the form which displays
"(autonum)" (or something like that; I'm not at my home computer
right now where I can look), and I have not found any way to make it
change to a number.

Since I have a deadline to meet, I have come up with a kludge that
effectively side-steps this issue. Before opening the form, I use
ADO to add a new record. I get the ID of the new record and open the
form in edit mode to that record.

You tried following the example I posted in my last message? As I said,
it works for me -- after setting Me.Dirty = False, the autonumber field
has a value. Of course, that was working with a test ADP linked to a
local SQL Server back-end. If you get a different result, maybe it has
to do with a latency period in communicating with the back-end.

The only other thing I can think of is that you may have failed to dirty
the main-form record before attempting to save it. Naturally, the
record won't be saved unless it has been modified in some way. I
suppose that's something you may possibly have overlooked.

Your kludge doesn't sound horrible to me, but I still don't see why you
need it. The code I posted works for me. I'd say, "Send me your ADP",
but then you'd have to send me your SQL Server database as well, or a
script to create it, and that begins to go beyond the scope of newsgroup
support.

 

[Guest]

I tested your code and it does indeed automatically update and provide the
new id number. I am rushing to meet a deadline and have not had time to do
much experimentation, but perhaps the difference in my form is its inclusion
of a subform. Whatever, my workaround seems to be working. Thanks for your
help.