Adding a new record to the recordset of a form using ADO (adp file)

  • Thread starter Thread starter NoClue
  • Start date Start date
N

NoClue

I get the following error when adding a new record to the underlying
recordset of my form:

data added database won't displayed form because doesn't satisfy
criteria underlying record source.

The reason is because i am selecting the record set based on a specific
ID, which is an autonumber in my sqlserver database. When i create a
new record the error above appears because the new record has a new ID
which does not fit the original recordset source.

Does anyone know away around this?

e.g. modifying the recordset source dynamically

Thanks in advance.
 
Not sure what you mean by adding a new record: are you using a bound form,
an ADODB recordset or manipulating directly the database with an INSERT
statement?

Usually, adding a ResyncCommand, setting the UniqueTable property and make
sure that all the stored procedures have the SET NOCOUNT ON option set at
their beginning should give ride of this problem.

Take a look with the SQL-Server profiler to make sure that you are really
knowing what's happening on the SQL-Server when this error message occurs.
 
In this instance i am setting the forms recordsource using a
"select..." statement and specifying a specific ID in the where clause.

When i use the form to create a new record (DoCmd.GoToRecord , ,
A_NEWREC) and then saving the record using (DoCmd.RunCommand
acCmdSaveRecord0 i get the error, this is because i am using a new ID
which of course does not fit the original select statements criteria.

Would this be corrected if i used a ADODB recordset for the forms
recordsource.
 
Oh, you are using a sql string like "Select * From MyTable Where IdTable =
1". You're right, this is a bad idea. In your case, the solution is
simply to replace this Select statement with a Stored Procedure and set the
RecordSource to an EXEC statement (or use the InputParameters property) that
will call the SP with its arguments:

CREATE PROCEDURE dbo.qf_Routes
(
@IdRoute int
)
As
Select IdRoute, NomRoute
From dbo.Routes
Where IdRoute = @IdRoute
Order by NomRoute
GO


And for the RecordSource:

EXEC qf_Routes 1
 
I disagree.. you should ALWAYS bind forms to a single record.

you should be using a TSQL statement to add the record; grab the
@@identity value and then set the recordsource to the new identity
field

hope that helps


-Aaron
 
I understand both of the above arguments and would be able to implement
both solutions but the problem i wish to solve is when creating a new
record, how can the form now handle the original record plus the a new
one, is this possible. The identity field is a autonumber also.
 
you shouldn't have the form handle both.

either

a) have 2 forms.
b) create a new record and change the recordsource.

are you talking about subforms or something? i just don't understand
the complexity that you're facing.

I use TSQL to write new records; not docmd.GoToRecord acNew for example

-Aaron
 
An example of what i am wanting to do is:

Display data on the form by,
Set the forms recordsource to "select * from myTable where ID = 1"

Have a 'New' button on the form which creates a new record in the
SqlServer database (DoCmd.GoToRecord , ,A_NEWREC).
ID is a autonumber in the database table, once the new record is
created in the database the form should allow the user to navigate
through both records.
 
1) have you recordsource where ID = 1
2) insert a new record, via TSQL; get the @@identity
3) set the recordsource where ID = 17 (or the value of identity)
 
Back
Top