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

[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.

 

[Sylvain Lafontaine]

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.

 

[NoClue]

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.

 

[Sylvain Lafontaine]

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

 

[dbahooker]

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

 

[NoClue]

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.

 

[dbahooker]

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

 

[NoClue]

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.

 

[dbahooker]

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)