bound subform: filling a new redord

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?

Note: It's ADP (SQL Server)

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" wrote in message
...
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?

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
:-(


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

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" wrote in message
...
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
:-(


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

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" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
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" wrote in message
...
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
:-(


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

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 -
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA


"Raider" wrote in message
...
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" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ
ÓÌÅÄÕÀÝÅÅ: ...
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" wrote in message
...
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
:-(


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