Re: Insert Null problem
- From: ToniS <ToniS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 24 Jul 2007 08:32:07 -0700
Sorry that I am not explaining this very well, it is pretty complicated
application (for me) I will try and explain it better. Just an FYI, I am
using an instead of insert trigger with in this application to update 2
tables at one time (probably not the best solution but it works). The
scenario that I am currently seeking help on is as follows: There are
basically 4 tables involved Shows S, Exhibitors E, ExhibitorsShows ES and
ExhibitorShowBooths ESB. The relationships are as follows: the ES table is
used to handle the many to many relationship between S and E. (one to many E
to ES and one to many S to ES) The relationship between ES and ESB is also
one to many) The user can add a new Exhibitor (populates the Exhibitors
table) and then can add them to the Show by clicking on a command button (as
soon as this happens I added an insert statement to populate the ES table)
The user then can add booths for that exhibitor (which is one of the subforms
on the main form, the main form is the form used to add exhbitors) At this
point, Access ‘tries’ to insert a row into the ESB table but I get the
following error: “Invalid input parameter check the status values for
details”
Main form RecordSource is set to:
strSQL = "SELECT ES.ExhibitorID, E.ExhibitorShortName,
ES.ExhibitorShowID, " & _
"E.ExhibitorName, E.Notes, E.ProductDescription, " & _
"E.DivisionID, E.ShowCategories, E.NewExhibitor " & _
"FROM ExhibitorsShows ES " & _
"INNER JOIN Exhibitors E ON ES.ExhibitorID =
E.ExhibitorID " & _
"WHERE showid = " & " '" & pubShowID & "'" & _
"ORDER BY E.ExhibitorShortName "
Subform RecordSource is set to
strSQL = "SELECT ES.ShowId, ES.ExhibitorID, ESB.ExhibitorShowID,
ESB.BoothName " & _
"FROM ExhibitorsShows ES " & _
"INNER JOIN ExhibitorShowBooths ESB ON ES.ExhibitorShowID =
ESB.ExhibitorShowID " & _
"WHERE showID = " & "'" & pubShowID & "' " & _
"ORDER BY BoothName"
If I have the subform set to the above recordSource, I can not even add
booth number (does not let me type anything into the text field) If I have
the above RecordSource BUT NO where clause,
I get the following error: “Invalid input parameter check the status values
for details” I have the subform’s Link Child Fields and Link Master Fields
set to ExhibitorID
I am at a complete lose and have no idea on what to do to fix this
Again any help would be greatly appreciated
ToniS
"Sylvain Lafontaine" wrote:
Honestly, I'm lost here..
Usually, you don't have to use a trigger to make insertions into a
sql-server table using ADP; however, you have (or should have) to make your
insertions on one table at a time. For example, you cannot create a new
entry in the tables ExhibitorsShows and ExhibitorShowBooths at the same
time; particularly when you have a N-N relationship.
I don't remember exactly what you can and can't do with ADP; so you will
have to make some tests before getting used to it.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"ToniS" <ToniS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:06C50668-76B2-4D3F-916D-C634B0502A9F@xxxxxxxxxxxxxxxx
I really like your suggestion to use VBA on the onOpen event.. I was
thinking
along
the lines of adding code to insert the information into the table ESB, but
how do I
"tell Access to not insert automatically?" I tried adding a instead of
insert trigger
and ran into some problems, I think one of the problems I ran into is I
can
not pass
in a parameter into a trigger, I then thought of using a Stored Procedure,
but I would
still have the problem of "access trying to do the insert as well"
ugh, this is giving me a huge headache! :)
ToniS
"ToniS" wrote:
I will try the suggestion on the UniqueTable property.... I am a little
confused on the recordsource for the subform... the table ESB has
ExhbiitorShowID that is a foreign key to ExhibitorsShows and the table
ExhibitorsShows has ExhibitorID that is a foreign key to Exhibitors So I
am
guessing It should be something like what I have?
"ToniS" wrote:
I am trying to add a row w/i the subform and get the following error,
"Cannot
insert the value NULL into column 'ExhibitorShowID'table
ExhibitorShowBooths
column does not allow nulls, INSERT fails"
Below is my main form recordsource:
SELECT ExhibitorsShows.ExhibitorID, ExhibitorsShows.ExhibitorShowID,
Exhibitors.ExhibitorShortName, Exhibitors.ExhibitorName,
Exhibitors.Notes, Exhibitors.ProductDescription, Exhibitors.DivisionID,
Exhibitors.ShowCategories, Exhibitors.NewExhibitor
FROM ExhibitorsShows
INNER JOIN Exhibitors ON ExhibitorsShows.ExhibitorID =
Exhibitors.ExhibitorID
My subform recordsource is as follows:
SELECT ExhibitorsShows.ExhibitorShowID, ExhibitorsShows.ExhibitorID,
ExhibitorShowBooths.BoothName
FROM ExhibitorsShows
INNER JOIN
ExhibitorShowBooths ON ExhibitorsShows.ExhibitorShowID =
ExhibitorShowBooths.ExhibitorShowID
I have tried setting the Link child Fields and Link Master Fields to
ExhibitorID and then
tried setting both to ExhibitorShowID
The table Structure for ExhibitorShowsBooths is as follows:
ExhibitorShowBoothID (unique key)
ExhibitorShowID (Foreign key to ExhibitorsShows)
BoothName
I also tried adding a txt and a cmbo box with in the subform that is
invisible that would be used to store
the exhbitorShowID. I was able to set the cmbo box w/ the correct data
but
still
was gettign the above error
Any suggestions would be greatly appreciated
Thanks
TSharp
- References:
- RE: Insert Null problem
- From: ToniS
- Re: Insert Null problem
- From: Sylvain Lafontaine
- RE: Insert Null problem
- Prev by Date: Re: Access 2007 Runtime pulled
- Next by Date: abc
- Previous by thread: Re: Insert Null problem
- Next by thread: update table with sub form
- Index(es):
Relevant Pages
|
