Dissociationof primary key and its data

  • Thread starter Thread starter Robert S. Johnson
  • Start date Start date
R

Robert S. Johnson

Has anyone ever encountered this problem. If so how did
you overcome it?

Even though the application says that initial record is
saved only the control number is saved.

I get an Error 2501: The Open Form Action was canceled
when entering the initial record.

When I look at the table BARequest there is no data in the
first record other than the control number(primary key).

If I continue to try adding records, each subsequent
record establishes a new line with a control number.
However all the rest of the data for the record overwrites
the data in the first record (eg.the second attempt write
a record to the table results in the data associated with
record two goes to record 1. There is a second record line
established with control number 2 but no other data.

I have tried recreating the DB from scratch twice with the
same results each time.

This is the code that is suppose to add records to the
table.

'Adds a new record to the table.
Private Sub cmdAddNew_Click()
On Error GoTo Fix_cmdAddNew_Click_Err

Dim lngNext As Long

lngNext = GetNextID("tblBARequest")
XSQL "INSERT INTO tblBARequest(fldCtlNum) SELECT " &
lngNext
DoCmd.OpenForm "frmBARequest", , , "fldCtlNum = " &
lngNext

Exit_cmdAddNew_Click:

Fix_cmdAddNew_Click_Err:
blnOk = Err.Number = 0
If Not blnOk Then
MsgBox "Error (" & Err.Number &
Err.Description & ")" & vbCrLf, vbCritical
Err.Clear
End If

End Sub
 
Num, you don't mention if this code is being run from the actual form, or,
if in fact the form is being opened?

You could certainly just open the form in addmode, and place your custom
NextId code in the on-insert event. This would simplify code a good deal.
Also, does GetNextID simply return a the next id, or does it also add a
record? I would assume that this function ONLY returns a id?

You also don't show your XSQL function, but I will for the time being assume
that works correctly also (does it add the new record to the table? can you
go to table view and see this new record?

Your sql does not look 100% either. You are missing some spaces.

So, I would do the following:

Dim lngNext As Long
dim strSql as string

lngNext = GetNextID("tblBARequest")
strSql = ""INSERT INTO tblBARequest (fldCtlNum) values (" & lngNext &
")"

debug.Print strSql

currentdb.Execute strSql
DoCmd.OpenForm "frmBARequest", , , "fldCtlNum = " & lngNext

Note the above addition of the debug.print. After you run the above, you can
open the debug window, and look at the sql you tried to use. Does it look
ok? Can you paste that sql into a query, and run it ok? (again, great little
tip to check things).

As mentioned, you could probably remove all of the above code, and simply
use the on-insert event of your form, and that would only take one line of
code:

me.fldCtlNum = GetNextID("tblBARequest")

Then, to open the form, you simply use:

docmd.OpenForm "frmBARequest",,,,acFormAdd

The above will bring the form up in add mode. In fact, you should still set
the forms property allow additions to "no", since you only want "one" record
to be added, and the above will do that for you. In addition, you should set
the forms cycle property to current record, so stuff like the page down key,
and bumping the mouse wheel can't confuse your users (and go to another new
record). And, if you only want one record to be added, then you might as
well hide the navigation buttons also. So, you can use all of the built in
settings to create a bullet proof ADD ONLY ONE record form. You don't need
all your code.....
 
Back
Top