Delete recodes between two forms

[Ac]

Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[strive4peace]

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Ac]

Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[strive4peace]

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[Ac]

Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[strive4peace]

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[Ac]

Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[strive4peace]

Hi Ac (what is your name?)

"I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty"

you should put code on the form BeforeUpdate event so the update can be
canceled if required information is not filled out -- them, there is no
need to delete the record as it will not be saved <smile>

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~
'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

if you have a SAVE button on your form, here is some code:

'~~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
'~~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.


:

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[Ac]

Hi Crystal,

I tried to write the code to combine cmdSave and Form_BeforeUpdate together
in Form_BeforeUpdate, but I could not make them to work well. I would like to
save the record if the user fills in the ClassID, ClassName and auto sign
the StudentID, otherwise, the recode will be deleted.

Here is the code I have now as a separate code. It is works but may not
looks good. Do you have any advice? Thanks!


Private Sub cmdSaveClass_Click()

On Error GoTo Err_cmdSaveClass_Click

If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
Exit Sub
End If

If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
Exit Sub
End If


Me.StudentID = Forms!fStudents.StudentID


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveClass_Click:
Exit Sub

Err_cmdSaveClass_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClass_Click

End Sub
____________________________________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ClassID) Or IsNull(Me.ClassName) Or
IsNull(Me.StudentID) Then

Cancel = True

End If

End Sub

Hi Ac (what is your name?)

"I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty"

you should put code on the form BeforeUpdate event so the update can be
canceled if required information is not filled out -- them, there is no
need to delete the record as it will not be saved <smile>

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~
'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

if you have a SAVE button on your form, here is some code:

'~~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
'~~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.


:

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[strive4peace]

Hi Ac,

In the BeforeUpdate event, you should use a MsgBox to tell the user what
information is missing so they know that the record is NOT saved and why...

this code should go in the BeforeUpdate event, not in the code for your
save button

'~~~~~~~~~~~~~~~~
If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
CANCEL = true
Exit Sub
End If

'little confused ... if you have ClassID,
'WHY do you also need the Classname?
' you should only store ClassID
If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If

'if this is a subform* and StudentID is a LinkChildField
'then there is no need for this check...
If IsNull(Me.SudentID) Then
MsgBox ("Student is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If
'~~~~~~~~~~~~~~~~

*read the mainform/subform section of 'Access Basics'

~~
when saving a record, instead of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

consider updating your code to not use the DoMenuItem (which is based on
menu choices in older versions of Access and is not very readable)

DoMenuItem does not specify WHICH record to save ... instead, do this:
if me.dirty then me.dirty = false

this will trigger the code in the BeforeUpdate event of the form you are
behind and only save if the record NEEDS to be saved.
~~

if you are in code behind a subform of fStudents and StudentID is a
LinkChildField, then there is no need for this assignment...

Me.StudentID = Forms!fStudents.StudentID



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I tried to write the code to combine cmdSave and Form_BeforeUpdate together
in Form_BeforeUpdate, but I could not make them to work well. I would like to
save the record if the user fills in the ClassID, ClassName and auto sign
the StudentID, otherwise, the recode will be deleted.

Here is the code I have now as a separate code. It is works but may not
looks good. Do you have any advice? Thanks!


Private Sub cmdSaveClass_Click()

On Error GoTo Err_cmdSaveClass_Click

If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
Exit Sub
End If

If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
Exit Sub
End If


Me.StudentID = Forms!fStudents.StudentID


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveClass_Click:
Exit Sub

Err_cmdSaveClass_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClass_Click

End Sub
____________________________________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ClassID) Or IsNull(Me.ClassName) Or
IsNull(Me.StudentID) Then

Cancel = True

End If

End Sub

Hi Ac (what is your name?)

"I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty"

you should put code on the form BeforeUpdate event so the update can be
canceled if required information is not filled out -- them, there is no
need to delete the record as it will not be saved <smile>

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~
'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

if you have a SAVE button on your form, here is some code:

'~~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
'~~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

:

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.


:

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

if MsgBox("Delete Student and all realted records?" _
,vbyesno, "Delete Student?") = vbNo then
exit sub
end if

'delete related records
dim strSQL as string
strSQL = "DELETE * FROM [Tablename] " _
& " WHERE StudentID =" & me.StudentID & ";"
currentdb.execute strSQL

'refresh the tables
currentdb.tabledefs.refresh
DoEvents

'delete student record
DoCmd.RunCommand acCmdDeleteRecord
'~~~~~~~~~~~~~~~

WHERE
Tablename is the name of the Classes table
StudentID is the name of the StudentID field (assuming it is the same in
both the related and main table)

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi,

I have created two forms, fStudent which data source is Student table with
a primary key StudentID and fClass which data source is Class table with a
primary key ClassID; the StudentID is also a foreign key for Class table. If
a delete button on the f Student form is clicked to delete a student record,
I would like to delete all classes that this student took at same time
automatically. How can I write the code and in where? Thanks.

 

[Ac]

Hi Crystal,

Thanks.

The code only works for the record that does not need to save. I also would
like to save the record if the user filled in the ClassID and ClassName; I
try the code, it does not save the record.

Hi Ac,

In the BeforeUpdate event, you should use a MsgBox to tell the user what
information is missing so they know that the record is NOT saved and why...

this code should go in the BeforeUpdate event, not in the code for your
save button

'~~~~~~~~~~~~~~~~
If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
CANCEL = true
Exit Sub
End If

'little confused ... if you have ClassID,
'WHY do you also need the Classname?
' you should only store ClassID
If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If

'if this is a subform* and StudentID is a LinkChildField
'then there is no need for this check...
If IsNull(Me.SudentID) Then
MsgBox ("Student is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If
'~~~~~~~~~~~~~~~~

*read the mainform/subform section of 'Access Basics'

~~
when saving a record, instead of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

consider updating your code to not use the DoMenuItem (which is based on
menu choices in older versions of Access and is not very readable)

DoMenuItem does not specify WHICH record to save ... instead, do this:
if me.dirty then me.dirty = false

this will trigger the code in the BeforeUpdate event of the form you are
behind and only save if the record NEEDS to be saved.
~~

if you are in code behind a subform of fStudents and StudentID is a
LinkChildField, then there is no need for this assignment...

Me.StudentID = Forms!fStudents.StudentID



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I tried to write the code to combine cmdSave and Form_BeforeUpdate together
in Form_BeforeUpdate, but I could not make them to work well. I would like to
save the record if the user fills in the ClassID, ClassName and auto sign
the StudentID, otherwise, the recode will be deleted.

Here is the code I have now as a separate code. It is works but may not
looks good. Do you have any advice? Thanks!


Private Sub cmdSaveClass_Click()

On Error GoTo Err_cmdSaveClass_Click

If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
Exit Sub
End If

If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
Exit Sub
End If


Me.StudentID = Forms!fStudents.StudentID


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveClass_Click:
Exit Sub

Err_cmdSaveClass_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClass_Click

End Sub
____________________________________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ClassID) Or IsNull(Me.ClassName) Or
IsNull(Me.StudentID) Then

Cancel = True

End If

End Sub

Hi Ac (what is your name?)

"I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty"

you should put code on the form BeforeUpdate event so the update can be
canceled if required information is not filled out -- them, there is no
need to delete the record as it will not be saved <smile>

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~
'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

if you have a SAVE button on your form, here is some code:

'~~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
'~~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

:

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.


:

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

 

[strive4peace]

Hi Ac,

the form BeforeUpdate event needs to be expanded from what you wrote --
closer to what I gave you -- so that you can see WHY the record is not
being saved...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

Thanks.

The code only works for the record that does not need to save. I also would
like to save the record if the user filled in the ClassID and ClassName; I
try the code, it does not save the record.

Hi Ac,

In the BeforeUpdate event, you should use a MsgBox to tell the user what
information is missing so they know that the record is NOT saved and why...

this code should go in the BeforeUpdate event, not in the code for your
save button

'~~~~~~~~~~~~~~~~
If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
CANCEL = true
Exit Sub
End If

'little confused ... if you have ClassID,
'WHY do you also need the Classname?
' you should only store ClassID
If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If

'if this is a subform* and StudentID is a LinkChildField
'then there is no need for this check...
If IsNull(Me.SudentID) Then
MsgBox ("Student is required, please fill it in")
Me.ClassName.SetFocus
CANCEL = true
Exit Sub
End If
'~~~~~~~~~~~~~~~~

*read the mainform/subform section of 'Access Basics'

~~
when saving a record, instead of this:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

consider updating your code to not use the DoMenuItem (which is based on
menu choices in older versions of Access and is not very readable)

DoMenuItem does not specify WHICH record to save ... instead, do this:
if me.dirty then me.dirty = false

this will trigger the code in the BeforeUpdate event of the form you are
behind and only save if the record NEEDS to be saved.
~~

if you are in code behind a subform of fStudents and StudentID is a
LinkChildField, then there is no need for this assignment...

Me.StudentID = Forms!fStudents.StudentID



Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Crystal,

I tried to write the code to combine cmdSave and Form_BeforeUpdate together
in Form_BeforeUpdate, but I could not make them to work well. I would like to
save the record if the user fills in the ClassID, ClassName and auto sign
the StudentID, otherwise, the recode will be deleted.

Here is the code I have now as a separate code. It is works but may not
looks good. Do you have any advice? Thanks!


Private Sub cmdSaveClass_Click()

On Error GoTo Err_cmdSaveClass_Click

If IsNull(Me.ClassID) Then
MsgBox ("Class ID is required, please fill it in")
Me.ClassID.SetFocus
Exit Sub
End If

If IsNull(Me.ClassName) Then
MsgBox ("Class Name is required, please fill it in")
Me.ClassName.SetFocus
Exit Sub
End If


Me.StudentID = Forms!fStudents.StudentID


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveClass_Click:
Exit Sub

Err_cmdSaveClass_Click:
MsgBox Err.Description
Resume Exit_cmdSaveClass_Click

End Sub
____________________________________________________________________
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.ClassID) Or IsNull(Me.ClassName) Or
IsNull(Me.StudentID) Then

Cancel = True

End If

End Sub


:

Hi Ac (what is your name?)

"I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty"

you should put code on the form BeforeUpdate event so the update can be
canceled if required information is not filled out -- them, there is no
need to delete the record as it will not be saved <smile>

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'~~~~~~~~~~~~~~~~~~~~
'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

if you have a SAVE button on your form, here is some code:

'~~~~~~~~~~~~~~~~~~~~
if me.dirty then me.dirty = false
'~~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

I will.

I have another problem related to this program. I have a Save button on the
fClass Form and the code to handle if the user left the StudentID as empty;
but if a user input only few information on the Class Form, instead of saving
it, he closes the form and left the SudentID as blank; how can I delete this
record from Class table with the StudentID as blank?

:

"you have a very good Access tutorial website"

thank you, Ac

"Do you have a SQL Server 2005 tutorial website too?"

no, I do not -- but if you find a good one, please let me know!
strive4peace2006 at yahoo.com

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Thanks again.

I appreciate your information; you have a very good Access tutorial website.
Do you have a SQL Server 2005 tutorial website too? Thanks.


:

Hi Ac (what is your name?)

you're welcome

"dirty" is a property that gets set to TRUE if a record has changes that
need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record

currentdb.tabledefs.refresh
refreshes the table definitions of the current database with changes
made by other users or processes

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Ac wrote:
Hi Crystal,

Thank you for your great help, it works well!

I also want to learn some part of your code which I have never used before.

1. What is the “dirty†(if me.dirty then me.dirty = false)? and
2. What is the “Tabledefs†(currentdb.tabledefs.refresh)?

Thanks again.


:

put this code behind the form for the Click event of your commend button
(the property sheet will say [Event Procedure])

'~~~~~~~~~~~~~~~
'save record if changes have been made
if me.dirty then me.dirty = false

if me.NewRecord then
msgbox "You are on a new record",,"Cannot delete"
exit sub
end if

 

[Ac]

Thank you very much for all of your help!

Hi Ac,

the form BeforeUpdate event needs to be expanded from what you wrote --
closer to what I gave you -- so that you can see WHY the record is not
being saved...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[strive4peace]

you're welcome, Ac happy to help

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*