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]

Re: involved question about default value from a control

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" <deletethis.dna i@btopenworld.c om.andthis> wrote in message
news:bpt949$2so $1@hercules.bti nternet.com...[color=blue]
> 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[/color]
as[color=blue]
> 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
>
>[/color]

[Ian D]

Re: involved question about default value from a control

"Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:yaLwb.3007 8$Vt1.8463@news svr32.news.prod igy.com...[color=blue]
> 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[/color]
to[color=blue]
> 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[/color]
default[color=blue]
> of Null) then it will work.
>
> --
> Wayne Morgan
> Microsoft Access MVP
>[/color]

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]

Re: involved question about default value from a control

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" <deletethis.dna i@btopenworld.c om.andthis> wrote in message
news:bq1q7o$qju $1@titan.btinte rnet.com...[color=blue]
> "Wayne Morgan" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in[/color]
message[color=blue]
> news:yaLwb.3007 8$Vt1.8463@news svr32.news.prod igy.com...[color=green]
> > The way you have it set up, you have to enter information into Data,[/color][/color]
then[color=blue][color=green]
> > Bool2, then Bool1 for it to work. That way Access will have an ID number[/color]
> to[color=green]
> > 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[/color]
> default[color=green]
> > of Null) then it will work.
> >
> > --
> > Wayne Morgan
> > Microsoft Access MVP
> >[/color]
>
> 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[/color]
value.[color=blue]
> 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[/color]
above[color=blue]
> 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
>
>[/color]