Force Record in Subform

I have a main form (F_Ticket) and a subform (F_Ticket_Sub). A user enters
general ticket info in the F_Ticket form then line item detail in the
F_Ticket_Sub form. My problem is that I want to enforce that at least one
line item is entered in the F_Ticket_Sub form. If not I dont want a new
record created in the main form - because ther should never be a new record
in the main form without at least one record in the subform that relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Unfortunately, the way access works, you must create the parent record
before you can enter a child record.
You could allow creation of the parent record, then if no child record has
been entered, when user moves on to next record, delete the parent record
that has no child records.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"szag via AccessMonster.com" u2885@uwe wrote in message
news:a48ffff598627@uwe...
I have a main form (F_Ticket) and a subform (F_Ticket_Sub). A user enters
general ticket info in the F_Ticket form then line item detail in the
F_Ticket_Sub form. My problem is that I want to enforce that at least one
line item is entered in the F_Ticket_Sub form. If not I dont want a new
record created in the main form - because ther should never be a new
record
in the main form without at least one record in the subform that relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

I like that. Next problem - do you have an example of code you could use to
do that. Can't think of how that would go. I am a VBA wannabe. Thanks!

Jeanette Cunningham wrote:
Unfortunately, the way access works, you must create the parent record
before you can enter a child record.
You could allow creation of the parent record, then if no child record has
been entered, when user moves on to next record, delete the parent record
that has no child records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form (F_Ticket) and a subform (F_Ticket_Sub). A user enters
general ticket info in the F_Ticket form then line item detail in the
[quoted text clipped - 3 lines]
record
in the main form without at least one record in the subform that relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
possible.

qryCheckParentNoChild is a saved query to check for parent records that
have no child records.

Base the query on both the parent table and the child table.
Create a join between the 2 tables using the Primary key of the parent
table.
Use the Primary key field from the parent table and the same field in the
child table - which will be the foreign key field.

Change the join to choose all the records from the parent table and only the
matching records from the child table.
In the criteria row under the foreign key field for the child table enter
Is Null

Save the query as qryCheckParentNoChild

Replace TableName with your table's name.
Replace ID with the name of the primary key of the parent table.
The code below is placed in the current event of the parent form.


--start sample code ----
Private Sub Form_Current()
Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [TableName] " _
& "WHERE [TableName].ID = " & lngID

lngID = Nz(DLookup('*", "qryCheckParentNoChild"),0)


If lngID 0 Then
If lngID Me.ID Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If

End Sub
----end sample code ---


"szag via AccessMonster.com" u2885@uwe wrote in message
news:a492952b4a49f@uwe...
I like that. Next problem - do you have an example of code you could use to
do that. Can't think of how that would go. I am a VBA wannabe. Thanks!

Jeanette Cunningham wrote:
Unfortunately, the way access works, you must create the parent record
before you can enter a child record.
You could allow creation of the parent record, then if no child record has
been entered, when user moves on to next record, delete the parent record
that has no child records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form (F_Ticket) and a subform (F_Ticket_Sub). A user enters
general ticket info in the F_Ticket form then line item detail in the
[quoted text clipped - 3 lines]
record
in the main form without at least one record in the subform that
relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Perfect! thank so much.

Jeanette Cunningham wrote:
Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
possible.

qryCheckParentNoChild is a saved query to check for parent records that
have no child records.

Base the query on both the parent table and the child table.
Create a join between the 2 tables using the Primary key of the parent
table.
Use the Primary key field from the parent table and the same field in the
child table - which will be the foreign key field.

Change the join to choose all the records from the parent table and only the
matching records from the child table.
In the criteria row under the foreign key field for the child table enter
Is Null

Save the query as qryCheckParentNoChild

Replace TableName with your table's name.
Replace ID with the name of the primary key of the parent table.
The code below is placed in the current event of the parent form.

--start sample code ----
Private Sub Form_Current()
Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [TableName] " _
& "WHERE [TableName].ID = " & lngID

lngID = Nz(DLookup('*", "qryCheckParentNoChild"),0)

If lngID 0 Then
If lngID Me.ID Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If

End Sub
----end sample code ---

I like that. Next problem - do you have an example of code you could use to
do that. Can't think of how that would go. I am a VBA wannabe. Thanks!
[quoted text clipped - 13 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via http://www.accessmonster.com

Jeanette I am getting the message:

Runtime error 3075: Syntax error (missing operator) in query expression '*'.

for: lngID = Nz(DLookup("*", "qryDelete"), 0)


My complete code:

Private Sub Form_Current()

Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [T_Jobs] " _
& "WHERE [T_Jobs].JobNumber = " & lngID

lngID = Nz(DLookup("*", "qryDelete"), 0)

If lngID 0 Then
If lngID Me.JobNumber Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If
End Sub



Jeanette Cunningham wrote:
Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
possible.

qryCheckParentNoChild is a saved query to check for parent records that
have no child records.

Base the query on both the parent table and the child table.
Create a join between the 2 tables using the Primary key of the parent
table.
Use the Primary key field from the parent table and the same field in the
child table - which will be the foreign key field.

Change the join to choose all the records from the parent table and only the
matching records from the child table.
In the criteria row under the foreign key field for the child table enter
Is Null

Save the query as qryCheckParentNoChild

Replace TableName with your table's name.
Replace ID with the name of the primary key of the parent table.
The code below is placed in the current event of the parent form.

--start sample code ----
Private Sub Form_Current()
Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [TableName] " _
& "WHERE [TableName].ID = " & lngID

lngID = Nz(DLookup('*", "qryCheckParentNoChild"),0)

If lngID 0 Then
If lngID Me.ID Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If

End Sub
----end sample code ---

I like that. Next problem - do you have an example of code you could use to
do that. Can't think of how that would go. I am a VBA wannabe. Thanks!
[quoted text clipped - 13 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via http://www.accessmonster.com

You can't use * with DLookup: you must use the name of a field.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"szag via AccessMonster.com" u2885@uwe wrote in message
news:a4b50e5fb8fcf@uwe...
Jeanette I am getting the message:

Runtime error 3075: Syntax error (missing operator) in query expression
'*'.

for: lngID = Nz(DLookup("*", "qryDelete"), 0)


My complete code:

Private Sub Form_Current()

Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [T_Jobs] " _
& "WHERE [T_Jobs].JobNumber = " & lngID

lngID = Nz(DLookup("*", "qryDelete"), 0)

If lngID 0 Then
If lngID Me.JobNumber Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If
End Sub



Jeanette Cunningham wrote:
Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
possible.

qryCheckParentNoChild is a saved query to check for parent records that
have no child records.

Base the query on both the parent table and the child table.
Create a join between the 2 tables using the Primary key of the parent
table.
Use the Primary key field from the parent table and the same field in the
child table - which will be the foreign key field.

Change the join to choose all the records from the parent table and only
the
matching records from the child table.
In the criteria row under the foreign key field for the child table enter
Is Null

Save the query as qryCheckParentNoChild

Replace TableName with your table's name.
Replace ID with the name of the primary key of the parent table.
The code below is placed in the current event of the parent form.

--start sample code ----
Private Sub Form_Current()
Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [TableName] " _
& "WHERE [TableName].ID = " & lngID

lngID = Nz(DLookup('*", "qryCheckParentNoChild"),0)

If lngID 0 Then
If lngID Me.ID Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If

End Sub
----end sample code ---

I like that. Next problem - do you have an example of code you could use
to
do that. Can't think of how that would go. I am a VBA wannabe. Thanks!
[quoted text clipped - 13 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via http://www.accessmonster.com

Thanks Doug. Ok it runs without error now, but nothing happens. There are a
100+ records in the qryDelete results. All of them should be deleted if the
code works right. Any other thoughts?

szag wrote:
Jeanette I am getting the message:

Runtime error 3075: Syntax error (missing operator) in query expression '*'.

for: lngID = Nz(DLookup("*", "qryDelete"), 0)

My complete code:

Private Sub Form_Current()

Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [T_Jobs] " _
& "WHERE [T_Jobs].JobNumber = " & lngID

lngID = Nz(DLookup("*", "qryDelete"), 0)

If lngID 0 Then
If lngID Me.JobNumber Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If
End Sub

Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
[quoted text clipped - 45 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

You're creating the SQL Statement that refers to the value of lngID before
you've looked up the value for lngID. That means that regardless of what's
returned by the DLookup, you're going to be running

DELETE FROM [T_Jobs] WHERE [T_Jobs].JobNumber = 0


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"szag via AccessMonster.com" u2885@uwe wrote in message
news:a4b5b9908b311@uwe...
Thanks Doug. Ok it runs without error now, but nothing happens. There are
a
100+ records in the qryDelete results. All of them should be deleted if
the
code works right. Any other thoughts?

szag wrote:
Jeanette I am getting the message:

Runtime error 3075: Syntax error (missing operator) in query expression
'*'.

for: lngID = Nz(DLookup("*", "qryDelete"), 0)

My complete code:

Private Sub Form_Current()

Dim lngID As Long
Dim strSQL As String

strSQL = "DELETE FROM [T_Jobs] " _
& "WHERE [T_Jobs].JobNumber = " & lngID

lngID = Nz(DLookup("*", "qryDelete"), 0)

If lngID 0 Then
If lngID Me.JobNumber Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If
End Sub

Here is some sample code.
I have kept the code to a minimum and used saved queries as much as
[quoted text clipped - 45 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

Thanks for the patience. still nothing happens. My code:

Private Sub Form_Load()
Dim lngID As Long
Dim strSQL As String


lngID = Nz(DLookup("fkJob", "qryDelete"), 0)

strSQL = "DELETE FROM [T_Jobs] " _
& "WHERE [T_Jobs].JobNumber = " & "lngID"

If lngID 0 Then
If lngID = Me.JobNumber Then
CurrentDb.Execute strSQL
Me.Requery
End If
End If

End Sub


Douglas J. Steele wrote:
You're creating the SQL Statement that refers to the value of lngID before
you've looked up the value for lngID. That means that regardless of what's
returned by the DLookup, you're going to be running

DELETE FROM [T_Jobs] WHERE [T_Jobs].JobNumber = 0

Thanks Doug. Ok it runs without error now, but nothing happens. There are
a
[quoted text clipped - 34 lines]
in the main form without at least one record in the subform that
relates.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1