Insert Null problem

[Guest]

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

 

[Sylvain Lafontaine]

First, it would be a good ideal to use aliases in order to make your
statement more readable:

SELECT ES.ExhibitorID, ES.ExhibitorShowID,
E.ExhibitorShortName, E.ExhibitorName, E.Notes, E.ProductDescription,
E.DivisionID, E.ShowCategories, E.NewExhibitor
FROM ExhibitorsShows ES
INNER JOIN Exhibitors E ON ES.ExhibitorID = E.ExhibitorID

SELECT ES.ExhibitorShowID, ES.ExhibitorID, ESB.BoothName
FROM ExhibitorsShows ES
INNER JOIN ExhibitorShowBooths ESB ON ES.ExhibitorShowID =
ESB.ExhibitorShowID

Second, this is probably because you have forgotten to set the UniqueTable
property and Resync command for both the main form and the subform.

Finally, it's totally unclear what is the table that you want to add a
record to in your subform; it is ExhibitorsShows or ExhibitorsShowsBooths?

If it's ExhibitorShowBooths then you must select
ExhibitorShowBooths.ExhibitorShowID and not ExhibitorShows.ExhibitorShowID
in the record source of the subform.

 

[Guest]

Thanks for the suggestions about the alias, I did that and it does make it
cleaner and easier to read. I also changed my subform select to select the
ESB.ExhibitorShowID instead of the ES.ExhibitorShowID since the table I am
trying to add rows to is teh ESB Table.

I was unaware of the unique table property and resync command, therefore I
had them blank... Should the main form unique table be set to ExhibitorsShows
and the subform set to ExhibitorShowBooths? as far as the resync command, I
am not sure of the purpose of this.. I started researching it and did not
have much luck

Again thanks for your help
ToniS

 

[Guest]

I think I am getting closer... I am now getting the following error "Insert
Statement conflict wtih column foreignkey constraint
FK_ExhibitorShowBooths_ExhibitorShows on table ExhibitorShows Column
ExhibitorShowID

Below is more information on the 3 tables I am working on

EXHIBITORS
ExhibitorID
ExhibitorName

 

[Sylvain Lafontaine]

First, using upper and lower cases will make your name easier to read: it's
much better to write ExhibitorShowBooths instead of EXHBITORSHOWBOOTHS.

In your last answer, you did't tell us if you have been able to write the
ResyncCommand and set the UniqueTable properties for both the form and the
subform. By searching this newsgroup with Google, you should easily find
some examples on how to use them.

Finally, without telling us the record source that you have used for both
the main form and the subform, it's impossible to understand the error
message that you are showing us.

 

[Guest]

I am using the same record source in my first post with the suggestion of
using
the ESB.ExhbitorShowID instead of the ES.ExhibitorShowID.

Here is my Main form Record source

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 "

Form_Frm_Exhibitor.RecordSource = StrSQL

Subform Record source is set to:

SELECT ES.ExhibitorID, ESB.ExhibitorShowID, ESB.BoothName
FROM ExhibitorsShows ES INNER JOIN
ExhibitorShowBooths ESB ON ES.ExhibitorShowID = ESB.ExhibitorShowID

I have tried setting the unique table property for the main (tried
Exhibitor, and ExhibitorShows)
and subform Unique Table property to (tried ExhibitorShowBooths) I did not
set the resync
property yet, I am still researching it.. I am not sure what I need it for
or why I need the Unque Table property for, (I know very little
about access) I will keep googling on the subject to learn more.........

Here is some additional information on the application
user can add exhibitors (Exhibitor Table), Exhibitors can be added to shows
(ExhibitorsShows)
For exhibitors going to a show, booth information can be added
(ExhibitorShowBooths)
This is where I am running into problems, the error that I am now getting is
"Insert tatement conflict wtih column foreignkey constraint
FK_ExhibitorShowBooths_ExhibitorShows on table ExhibitorShows Column
ExhibitorShowID"

I am not sure what the best solution would be for this situation

any suggestions would be greatly appreciated.
Thanks
ToniS

 

[Sylvain Lafontaine]

There is a bug with the UniqueTable property in the GUI: you must
temporarily set the record source to the name of the table that you want to
use as the UniqueTable before setting this property; after that, you can
reset the record source to its old value. Another possibility would be to
use VBA code to set it in the OnOpen event for the form/subform.

For the subform, the following record source is probably wrong:

SELECT ES.ExhibitorID, ESB.ExhibitorShowID, ESB.BoothName
FROM ExhibitorsShows ES INNER JOIN
ExhibitorShowBooths ESB ON ES.ExhibitorShowID = ESB.ExhibitorShowID


If ExhibitorShowBooths is the unique table and ExhibitorID is the foreign
key, then probably you should use instead:

SELECT ESB.ExhibitorID, ESB.ExhibitorShowID, ESB.BoothName
FROM ExhibitorShowBooths ESB

 

[Guest]

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?

 

[Guest]

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

 

[Sylvain Lafontaine]

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.

 

[Guest]

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