bound subform: filling a new redord

Raider

unread,

Jul 13, 2004, 4:34:56 AM7/13/04

to

Hello. I have a problem with bound subform.

I have a MAIN table with following fields:
[id] (int - identity - not null - primary key), [title] (nvarchar) ...

and the SUB table with
[sid] (int - identity - not null - primary key), [mid] (int - not null),
[desc] (nvarchar), ...

I created form MAINFRM based on MAIN recordset and subform based on SUB
records. They are linked via [id]=[mid].

If user creates new record by MAINFRM, types [title] then fills [desc]s -
all works fine. But if in a new MAINFRM's record user try to fill [desc] as
a first step, the error message appears (about setting non-variant field to
Null).

I undersnad that new record have no [id] (i.e. =Null) and Access tries to
set [mid] to Null, so it fails.
But HOW TO GET ROUND THIS?

Raider

unread,

Jul 13, 2004, 4:43:48 AM7/13/04

to

Note: It's ADP (SQL Server)

Allen Browne

unread,

Jul 13, 2004, 4:50:25 AM7/13/04

to

Cancel the BeforeInsert event of the subform, if the main form is at a new
record:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Fill in the main form first."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Raider" <sra...@yandex.ru> wrote in message
news:eQ%23DQRLa...@tk2msftngp13.phx.gbl...

Raider

unread,

Jul 13, 2004, 5:06:39 AM7/13/04

to

Thanks!
But how can I give users an ability to fill subform first?

Private Sub Form_BeforeInsert(Cancel As Integer)

' try to create main record
If Me.Parent.NewRecord Then Me.Parent![title] = null
End Sub

gives [mid]=1
:-(

Allen Browne

unread,

Jul 13, 2004, 9:25:58 AM7/13/04

to

You can't.

Assuming a one-to-many relation between the main form's table and the
subform's table, you have to enter the main form record before you can
create the related records in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Raider" <sra...@yandex.ru> wrote in message

news:eM9x9iLa...@TK2MSFTNGP10.phx.gbl...

Raider

unread,

Jul 13, 2004, 9:58:55 AM7/13/04

to

Okey, I'm unable to create subform's record without having record in the
main form. And the possible solution is to auto-create record in the main
form then trying to create a subform record. But how to implement it?

"Allen Browne" <Allen...@SeeSig.Invalid> сообщил/сообщила в новостях
следующее: news:ureBxzNa...@TK2MSFTNGP12.phx.gbl...

Malcolm Cook

unread,

Jul 14, 2004, 3:53:08 PM7/14/04

to

Raider,

If you really must, in the parent, get a blank (except for the IDENTITY
column) row inserted as follows:

sub form_current ()
if .newrecord then
me.setfocus SomeFieldThatIsOtherwiseUpdateable
me.dirty = true
me.dirty = false
end if
end sub

However, that requires there are NO fields declared as NOT NULL in MS SQL...
(other than your surrogate IDENTIRY primary key) (which is rather odd thing
to do).

And that you have a column that is updateab;le.

Good luck,

--
Malcolm Cook - m...@stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

"Raider" <sra...@yandex.ru> wrote in message

news:u44IMGOa...@TK2MSFTNGP11.phx.gbl...