prevent auto insert

[Guest]

Hi,
I have a subform with in a main form. the subform has a stored procedure
that inserts to two tables (DealerPersons and DealerPersonsShows). When I
bind the text fields in the subform to the DealerPersons table Access
automatically inserts a row to that table (DealerPersons). My stored
procedure already inserts to that table, therefore I am getting a duplicate
record. Is there a way to prevent access from automatically inserting a row,
but allow the user to enter data into the fields so my stored procedure will
insert the information.

Thanks
Toni

 

[Sylvain Lafontaine]

You don't really give any information about this SP that make the insertion
and its relationship with the subform and between the subform and the main
form. If this SP is also used as the Record Source of the form, then maybe
this is not a good idea and you should split this SP into two SP.

You could try setting the Allow Additions property to false and requerying
the subform when necessary.

Another possibility would be to set the Record Source to nothing and reset
it only when the call to the SP has been made.

Another possibility - similar to the previous one - would be to set the
Record Source of the subform to an EXEC statement calling the SP used as the
Record Source with the right parameters after the return of the SP used to
create the entries into the tables.

 

[Guest]

sorry about the lack of info, I am new to all of this, I really appreciate
the response. The main from recordsource is as follows:
SELECT DealersShows.Showid, DealersShows.MemberID, MemberList.Store#,
MemberList.StoreName, MemberList.ShowCategories, memberlist.Address1, add2 etc

FROM DealersShows INNER JOIN
MemberList ON DealersShows.MemberID =
MemberList.MemberID INNER JOIN
DirMemberLocations ON MemberList.MemberID =
DirMemberLocations.MemberID
WHERE dealersShows.ShowID = pubShowID
ORDER BY MemberList.Store#

The subform Recordsource is as follows:
SELECT DealerPersons.DealerPersonID, DealerPersons.MemberID,
DealerPersons.FirstName, DealerPersons.LastName
FROM DealerPersons INNER JOIN
DealerPersonsShows ON DealerPersons.DealerPersonID
= DealerPersonsShows.DealerPersonID
WHERE (DealerPersonsShows.ShowID = pubshowid.

There are basically 5 tables involved, Shows, DealersShows,memberlist,
SealerPersonsShows and DealerPersons. The DealerPersonsShows table is to
handle the many to many relationship between dealerPersons and Shows.

The subform basically prompts the user for first and last name
(Dealerpersons table) on the afterupdate event I execute the Stored
procedure, which inserts a record into the dealerpersons and
DealerPersonsShows. The parm passed into the stored procedure are showid,
memberid, firstname and lastname.

Any suggestions would be greatly appreciated.
Thanks
Toni

 

[Sylvain Lafontaine]

Well, you will have to set the Allow Additions property to false and to
requery the subform after the return call from the SP (the one that is
adding the new record).

Also, I don't see why you need to pass the first name and last name of a new
dealer to a SP in order to create a new record: you could leave this work to
be done by the subform only.

 

[Guest]

Thanks for the quick response... When I set the allow additions property to
False, the user can not add a dealerperson(first and last name) Maybe I am
missing something.

I originally was trying to let access add the record for the first and last
name, but I needed to also update the table DealerPersonsShows and was not
sure how to get the last inserted record from the DealerPersons table to
populate the DealerpersonsShows. Below is a layout of both tables

DealerPersons Table
DealerPersonID GUID
MemberID Foreign key to memberlist
FirstName Varchar
LastName varchar

DealerPersonsShows Table
DealerPersonsShowID GUID
DealerPersonsID Foreign key to DealerPersons
ShowID Foreign key to Shows

The Stored procedure is also being used on a web based applcation and since
one has already been written by another developer, I thought maybe I could
just use that one as well, since It does everything I want it to (populate
both tables)

Any suggestions are greatly appreciated.
Thanks
Toni