Force Record in Subform

[szag via AccessMonster.com]

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.

 

[Jeanette Cunningham]

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]

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!

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.

 

[Jeanette Cunningham]

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!

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.

 

[szag via AccessMonster.com]

Perfect! thank so much.

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]

 

[szag via 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

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]

 

[Douglas J. Steele]

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!)

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
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.

 

[szag via 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?

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]

 

[Douglas J. Steele]

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!)

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?

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.

 

[szag via AccessMonster.com]

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

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]

 

[John W. Vinson]

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

What is the value of lngID (if you step through the code)?

The Execute statement will only be run if that number is less than zero.

Also, what's qryDelete? Does it return only one record?

Perhaps a bit more context would be helpful...

 

[Douglas J. Steele]

You added quotes around lngID that weren't there before: that's what's
causing your problem now.

What you have means that the literal text lngID will be included in your SQL
statement:

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

You want the value of the variable, so it cannot be in quotes.

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


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

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

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.