bound subform: filling a new redord

[Raider]

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]

Note: It's ADP (SQL Server)

 

[Allen Browne]

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]

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]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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]

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?

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Malcolm Cook]

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 - (e-mail address removed)
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

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?

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.

Reply to group, rather than allenbrowne at mvps dot org.

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