involved question about default value from a control

[Ian D]

Firstly apologies for the convoluted question. I found this problem whilst
building a larger database. I've distilled it down to as small as possible
and can send a 200k example to anyone who has the time to have a play.
Am I doing something silly or is this an Access problem (or other)?

Access 2000

3 tables
tblID contains ID (autonumber), Data (text)
tbl1 contains ID (Number, default = nothing, Primary Key), bool1 (yes/no)
tbl2 contains ID (Number, default = nothing, Primary Key), bool2 (yes/no)

1 Query
SELECT tblID.*, tbl2.*, tbl1.*
FROM (tblID LEFT JOIN tbl2 ON [tblID].[ID]=[tbl2].[ID]) LEFT JOIN tbl1 ON
[tblID].[ID]=[tbl1].[ID];

2 forms based on this query:
FormOK - Field list used to drag and drop: tblID.ID, Data, bool1, bool2
(Default Value =False)

Allows data to be entered fine.

FormNoGo
Same as formOK but with bool1 Default Value =False as well.

Attempting to add a new record gives:

"Index or Primary Key cannot contain a Null Value"

I can see from the form that the ID field isn't null - it's autonumbering as
expected when text is entered in the data textBox.
I don't usually use bound forms so maybe I've taken the wrong approach
entirely.
Anyone shed any light?

Ian D

- return address spamtrapped but working if you do the obvious

[Wayne Morgan]

The way you have it set up, you have to enter information into Data, then
Bool2, then Bool1 for it to work. That way Access will have an ID number to
pass to the next linked table in the line. If you enter the data out of
sequence, it won't work. So, on your form where Bool1 has a default of
False, if you put a value in for Bool2 (true or false instead of the default
of Null) then it will work.

--
Wayne Morgan
Microsoft Access MVP
"Ian D" <de*************@btopenworld.com.andthis> wrote in message
news:bp**********@hercules.btinternet.com...

Firstly apologies for the convoluted question. I found this problem whilst
building a larger database. I've distilled it down to as small as possible
and can send a 200k example to anyone who has the time to have a play.
Am I doing something silly or is this an Access problem (or other)?

Access 2000

3 tables
tblID contains ID (autonumber), Data (text)
tbl1 contains ID (Number, default = nothing, Primary Key), bool1 (yes/no)
tbl2 contains ID (Number, default = nothing, Primary Key), bool2 (yes/no)

1 Query
SELECT tblID.*, tbl2.*, tbl1.*
FROM (tblID LEFT JOIN tbl2 ON [tblID].[ID]=[tbl2].[ID]) LEFT JOIN tbl1 ON
[tblID].[ID]=[tbl1].[ID];

2 forms based on this query:
FormOK - Field list used to drag and drop: tblID.ID, Data, bool1, bool2
(Default Value =False)

Allows data to be entered fine.

FormNoGo
Same as formOK but with bool1 Default Value =False as well.

Attempting to add a new record gives:

"Index or Primary Key cannot contain a Null Value"

I can see from the form that the ID field isn't null - it's autonumbering as expected when text is entered in the data textBox.
I don't usually use bound forms so maybe I've taken the wrong approach
entirely.
Anyone shed any light?

Ian D

- return address spamtrapped but working if you do the obvious

[Ian D]

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:ya******************@newssvr32.news.prodigy.c om...

The way you have it set up, you have to enter information into Data, then
Bool2, then Bool1 for it to work. That way Access will have an ID number to pass to the next linked table in the line. If you enter the data out of
sequence, it won't work. So, on your form where Bool1 has a default of
False, if you put a value in for Bool2 (true or false instead of the default of Null) then it will work.

--
Wayne Morgan
Microsoft Access MVP

Thanks for taking the time to reply Wayne

I agree that tblID is the 'master' table so Data has to be entered first.
What I think I've found is that Access can't pass more than 1 default value.
I've made 3 similar forms which show all 3 ID fields:
frmNodefault (both bools unset) - This sets tblID.ID when data is entered
and the ID for either bool table when the checkboxes are clicked.
frm1default (bool1 = True) - sets tblID.ID and tbl1.ID as soon as data is
entered. Otherwise works as above.
frm2defaults (bool1 =True, bool2 =True) - sets tblID.ID and tbl2.ID as above
but *tbl1.ID never gets set* - which gives my original error.

This looks like a problem with Access to me. I've tried it on 2 machines
with the same result. Can you confirm or deny my findings? I can send you
the db if that helps (17k zipped).

regards

Ian D

[Wayne Morgan]

You are correct. If you want to do this, you will probably need to fill in
the ID field of the other bool table using code in the form's BeforeInsert
event.

--
Wayne Morgan
MS Access MVP
"Ian D" <de*************@btopenworld.com.andthis> wrote in message
news:bq**********@titan.btinternet.com...

"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:ya******************@newssvr32.news.prodigy.c om...

The way you have it set up, you have to enter information into Data, then Bool2, then Bool1 for it to work. That way Access will have an ID number to

pass to the next linked table in the line. If you enter the data out of
sequence, it won't work. So, on your form where Bool1 has a default of
False, if you put a value in for Bool2 (true or false instead of the

default

of Null) then it will work.

--
Wayne Morgan
Microsoft Access MVP

Thanks for taking the time to reply Wayne

I agree that tblID is the 'master' table so Data has to be entered first.
What I think I've found is that Access can't pass more than 1 default

value. I've made 3 similar forms which show all 3 ID fields:
frmNodefault (both bools unset) - This sets tblID.ID when data is entered
and the ID for either bool table when the checkboxes are clicked.
frm1default (bool1 = True) - sets tblID.ID and tbl1.ID as soon as data is
entered. Otherwise works as above.
frm2defaults (bool1 =True, bool2 =True) - sets tblID.ID and tbl2.ID as above but *tbl1.ID never gets set* - which gives my original error.

This looks like a problem with Access to me. I've tried it on 2 machines
with the same result. Can you confirm or deny my findings? I can send you
the db if that helps (17k zipped).

regards

Ian D