Re-displaying a blank data entry form

M

Mike

I have created a form that uses list boxes and text boxes to allow the user
to enter in data and then click a command button to add the record. I have
also created some VBA code to check and make sure that all the values are
valid before inserting the record into the table in the database. Once the
record is inserted, I would like to have the form re-display the same as if
it was opened for the first time. Currently, the data for the record just
entered stays in the fields.
 
S

Stuart McCall

Mike said:
I have created a form that uses list boxes and text boxes to allow the user
to enter in data and then click a command button to add the record. I
have
also created some VBA code to check and make sure that all the values are
valid before inserting the record into the table in the database. Once
the
record is inserted, I would like to have the form re-display the same as
if
it was opened for the first time. Currently, the data for the record
just
entered stays in the fields.
Click to expand...

There's more than one way to achieve this, but I think the method you want
is to set the form's DataEntry property to Yes/True. Give it a try and see
what happens.
 
D

Dirk Goldgar

Mike said:
I have created a form that uses list boxes and text boxes to allow the user
to enter in data and then click a command button to add the record. I
have
also created some VBA code to check and make sure that all the values are
valid before inserting the record into the table in the database. Once
the
record is inserted, I would like to have the form re-display the same as
if
it was opened for the first time. Currently, the data for the record
just
entered stays in the fields.
Click to expand...


Is this a bound form, or an unbound form? If it's a bound form, add a line
of code to tell the form to go to a new record:

RunCommand acCmdRecordsGoToNew

If it's an unbound form, you'll have to go through the form's controls and
set each editable control to Null (or to a default value, if the control has
one).
 
M

Mike

Dirk,
I am fairly new to this but I think it is an unbound form. Once the
user clicks the button, it updates only one table, but the form itself has 3
list boxes that are used to get information from other table. I tried
setting the controls for each field on the form and changed it to null, but
still the data stayed on the form after adding the record to the table. I
have also changed the form's properties as Stuart had suggested.
 
D

Dirk Goldgar

Mike said:
Dirk,
I am fairly new to this but I think it is an unbound form. Once the
user clicks the button, it updates only one table, but the form itself has
3
list boxes that are used to get information from other table. I tried
setting the controls for each field on the form and changed it to null,
but
still the data stayed on the form after adding the record to the table. I
have also changed the form's properties as Stuart had suggested.
Click to expand...


Stuart's suggestion was a good one, but much depends on how you have the
form set up. Please post the form's Record Source property -- if that
property is blank, it's an unbound form -- and also the code or macro that
is executed by the button.
 
M

Mike

The record source is blank so it is unbound. Here is the code that I have
when the user clicks the add record button:

Private Sub Add_Record_Click()

' RS is for the audit name table
' RS1 is for the HR Dept Name Table
' RS2 is for the HR SR Mgr Name Table
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim tmpAuditEntityNum As Integer, tmpHRDeptNum As Integer, tmpHR_SRmgrNum As
Integer
Dim tmpAuditEntityName As String, tmpHRDeptName As String, tmpHR_SRmgrName
As String
Dim Response As Integer, Response1 As Integer, FormBlank As String


Response = MsgBox("This action will insert the Audit Record into the
main Audit table." & _
vbCrLf & vbCrLf & "Are you sure?", vbYesNo, "Confirmation...")

If Response = vbNo Then Exit Sub

' Check to ensure data entered in key fields before adding records.

FormBlank = "False"
FormBlank = IsNull(Me.frmAudit_Name)
FormBlank = IsNull(Me.frmAudit_Entity_Name)
FormBlank = IsNull(Me.frmAudit_Start_Date)
FormBlank = IsNull(Me.frmTarget_Close_Date)
FormBlank = IsNull(Me.frmHR_Dept_Name)
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)

If FormBlank = "True" Then
MsgBox ("Empty fields not allowed to add form." & _
"Fix errors and try again")
Exit Sub
End If


' Form input fields from table use the first column when accessing the field.
' That is why name is moved to number.

tmpAuditEntityNum = Me.frmAudit_Entity_Name
tmpHRDeptNum = Me.frmHR_Dept_Name
tmpHR_SRmgrNum = Me.frmHR_SR_Mgr_Name

Set db = CurrentDb

DoCmd.Hourglass True
DoCmd.SetWarnings False


strSQL = ("Select Audit_Entity_Name ")
strSQL = strSQL & ("From tblAudit_Entity")
strSQL = strSQL & (" Where (tblAudit_Entity.Audit_Entity_Num = " &
tmpAuditEntityNum & ")")
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
MsgBox ("Audit Entity not valid in table. Contact the application
administrator")
Exit Sub
Else
tmpAuditEntityName = rs!Audit_Entity_Name
End If

strSQL = ("Select HR_Dept_Name ")
strSQL = strSQL & ("From tblHR_Dept")
strSQL = strSQL & (" Where (tblHR_Dept.HR_Dept_Num = " & tmpHRDeptNum &
")")
Set rs1 = db.OpenRecordset(strSQL)
If rs1.RecordCount = 0 Then
MsgBox ("HR Department not valid in table. Contact the application
administrator")
Exit Sub
Else
tmpHRDeptName = rs1!HR_Dept_Name
End If

strSQL = ("Select HR_SR_Mgr_Name ")
strSQL = strSQL & ("From tbl_HR_SR_Manager")
strSQL = strSQL & (" Where (tbl_HR_SR_Manager.HR_SR_Mgr_Num = " &
tmpHR_SRmgrNum & ")")
Set rs2 = db.OpenRecordset(strSQL)
If rs2.RecordCount = 0 Then
MsgBox ("HR SR Manager not valid in table. Contact the application
administrator")
Exit Sub
Else
tmpHR_SRmgrName = rs2!HR_SR_Mgr_Name
End If

Response1 = MsgBox("Audit Entity Number is " & tmpAuditEntityNum & "." & _
vbCrLf & vbCrLf & "HR Dept Number is " & tmpHRDeptNum & "." & _
vbCrLf & vbCrLf & "HR SR Mgr Number is " & tmpHR_SRmgrNum &
"." & _
vbCrLf & vbCrLf & "You Sure", vbYesNo, "Confirmation...")

Call Add_Audit_Rcd(Me.frmAudit_Name, tmpAuditEntityNum,
Me.frmAudit_Start_Date, _
Me.frmTarget_Close_Date, tmpHRDeptNum, tmpHR_SRmgrNum, Me.frmSOX,
Me.frmOperational)

DoCmd.SetWarnings True
DoCmd.Hourglass False

MsgBox "Record Added", , "Done"



End Sub
 
D

Dirk Goldgar

Mike said:
The record source is blank so it is unbound. Here is the code that I have
when the user clicks the add record button:

Private Sub Add_Record_Click()

' RS is for the audit name table
' RS1 is for the HR Dept Name Table
' RS2 is for the HR SR Mgr Name Table
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim tmpAuditEntityNum As Integer, tmpHRDeptNum As Integer, tmpHR_SRmgrNum
As
Integer
Dim tmpAuditEntityName As String, tmpHRDeptName As String, tmpHR_SRmgrName
As String
Dim Response As Integer, Response1 As Integer, FormBlank As String


Response = MsgBox("This action will insert the Audit Record into the
main Audit table." & _
vbCrLf & vbCrLf & "Are you sure?", vbYesNo,
"Confirmation...")

If Response = vbNo Then Exit Sub

' Check to ensure data entered in key fields before adding records.

FormBlank = "False"
FormBlank = IsNull(Me.frmAudit_Name)
FormBlank = IsNull(Me.frmAudit_Entity_Name)
FormBlank = IsNull(Me.frmAudit_Start_Date)
FormBlank = IsNull(Me.frmTarget_Close_Date)
FormBlank = IsNull(Me.frmHR_Dept_Name)
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)

If FormBlank = "True" Then
MsgBox ("Empty fields not allowed to add form." & _
"Fix errors and try again")
Exit Sub
End If


' Form input fields from table use the first column when accessing the
field.
' That is why name is moved to number.

tmpAuditEntityNum = Me.frmAudit_Entity_Name
tmpHRDeptNum = Me.frmHR_Dept_Name
tmpHR_SRmgrNum = Me.frmHR_SR_Mgr_Name

Set db = CurrentDb

DoCmd.Hourglass True
DoCmd.SetWarnings False


strSQL = ("Select Audit_Entity_Name ")
strSQL = strSQL & ("From tblAudit_Entity")
strSQL = strSQL & (" Where (tblAudit_Entity.Audit_Entity_Num = " &
tmpAuditEntityNum & ")")
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
MsgBox ("Audit Entity not valid in table. Contact the application
administrator")
Exit Sub
Else
tmpAuditEntityName = rs!Audit_Entity_Name
End If

strSQL = ("Select HR_Dept_Name ")
strSQL = strSQL & ("From tblHR_Dept")
strSQL = strSQL & (" Where (tblHR_Dept.HR_Dept_Num = " & tmpHRDeptNum &
")")
Set rs1 = db.OpenRecordset(strSQL)
If rs1.RecordCount = 0 Then
MsgBox ("HR Department not valid in table. Contact the
application
administrator")
Exit Sub
Else
tmpHRDeptName = rs1!HR_Dept_Name
End If

strSQL = ("Select HR_SR_Mgr_Name ")
strSQL = strSQL & ("From tbl_HR_SR_Manager")
strSQL = strSQL & (" Where (tbl_HR_SR_Manager.HR_SR_Mgr_Num = " &
tmpHR_SRmgrNum & ")")
Set rs2 = db.OpenRecordset(strSQL)
If rs2.RecordCount = 0 Then
MsgBox ("HR SR Manager not valid in table. Contact the
application
administrator")
Exit Sub
Else
tmpHR_SRmgrName = rs2!HR_SR_Mgr_Name
End If

Response1 = MsgBox("Audit Entity Number is " & tmpAuditEntityNum & "."
& _
vbCrLf & vbCrLf & "HR Dept Number is " & tmpHRDeptNum & "."
& _
vbCrLf & vbCrLf & "HR SR Mgr Number is " & tmpHR_SRmgrNum &
"." & _
vbCrLf & vbCrLf & "You Sure", vbYesNo, "Confirmation...")

Call Add_Audit_Rcd(Me.frmAudit_Name, tmpAuditEntityNum,
Me.frmAudit_Start_Date, _
Me.frmTarget_Close_Date, tmpHRDeptNum, tmpHR_SRmgrNum, Me.frmSOX,
Me.frmOperational)

DoCmd.SetWarnings True
DoCmd.Hourglass False

MsgBox "Record Added", , "Done"



End Sub
Click to expand...


Okay, I see that it is unbound, and that you call another routine,
Add_Audit_Rcd, to add the record. After that, I gather you want to clear
the form. To do that, I would expect that code along these lines would do
the job:

Me.frmAudit_Name = Null
Me.frmAudit_Entity_Name = Null
Me.frmAudit_Start_Date = Null
Me.frmTarget_Close_Date = Null
Me.frmHR_Dept_Name = Null
Me.frmHR_SR_Mgr_Name = Null
Me.frmSOX = Null
Me.frmOperational = Null

It's possible I've "nulled" some fields there that should not be nulled, and
I may have left out some fields that should have been included. You'll have
to check that.

If any of those fields should be reset to a default value instead of Null,
you can set them with reference to their DefaultValue properties like this:

Me.<controlname> = Eval(Me.<controlname>.DefaultValue)

Please let me know if this approach works for you.

Aside from that, I'm concerned about somethings I see that may be errors in
your code. For example, the code that checks for blank fields:
Dim Response As Integer, Response1 As Integer, FormBlank As String [...]
' Check to ensure data entered in key fields before adding records.

FormBlank = "False"
FormBlank = IsNull(Me.frmAudit_Name)
FormBlank = IsNull(Me.frmAudit_Entity_Name)
FormBlank = IsNull(Me.frmAudit_Start_Date)
FormBlank = IsNull(Me.frmTarget_Close_Date)
FormBlank = IsNull(Me.frmHR_Dept_Name)
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)

If FormBlank = "True" Then
Click to expand...

First, FormBlank should be declared and tested as Boolean, not String:

Dim FormBlank As Boolean

FormBlank = False
[...]
If FormBlank = True Then

But on top of that, the code you posted will result in FormBlank set to True
(or "True") only if Me.frmHR_SR_Mgr_Name is Null. That's because this is
the last statement executed in the series of tests:
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)
Click to expand...

So the result of that IsNull() expression is assigned to FormBlank, and
that's the end of it.

Instead, you want to do something like this:

FormBlank = _
IsNull(Me.frmAudit_Name) _
Or IsNull(Me.frmAudit_Entity_Name) _
Or IsNull(Me.frmAudit_Start_Date) _
Or IsNull(Me.frmTarget_Close_Date) _
Or IsNull(Me.frmHR_Dept_Name) _
Or IsNull(Me.frmHR_SR_Mgr_Name)

There are other, possibly more efficient, ways to accomplish this, but that
should be pretty clear.

I'm also concerned about your use of "DoCmd.SetWarnings False". I see no
reason within this procedure for doing that, and the scope of SetWarnings
False should be as minimal as possible. You may have a reason to use it
inside the Add_Audit_Rcd procedure, in which case you should limit it to
that procedure. And any procedure where you turn off warnings should also
have error-handling in place so that there is no way to exit the procedure,
even in the event of an error, with warnings turned off.

In this procedure you have posted, there are lots of ways to leave the
procedure with both warnings turned off and Hourglass turned on. You should
correct that, so that (a) warnings are never turned off in this procedure at
all, and (b) there is no way to exit the procedure without turning off the
Hourglass.
 
M

Mike

Dirk,
Your suggestion of setting the fields individually to null worked
perfectly. I also made the other code changes you suggested. THanks a
lot. As a newbie to doing some of this I am just copying what others had
done before me and fixing as I learn more.

Dirk Goldgar said:
Mike said:
The record source is blank so it is unbound. Here is the code that I have
when the user clicks the add record button:

Private Sub Add_Record_Click()

' RS is for the audit name table
' RS1 is for the HR Dept Name Table
' RS2 is for the HR SR Mgr Name Table
Dim rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String
Dim tmpAuditEntityNum As Integer, tmpHRDeptNum As Integer, tmpHR_SRmgrNum
As
Integer
Dim tmpAuditEntityName As String, tmpHRDeptName As String, tmpHR_SRmgrName
As String
Dim Response As Integer, Response1 As Integer, FormBlank As String


Response = MsgBox("This action will insert the Audit Record into the
main Audit table." & _
vbCrLf & vbCrLf & "Are you sure?", vbYesNo,
"Confirmation...")

If Response = vbNo Then Exit Sub

' Check to ensure data entered in key fields before adding records.

FormBlank = "False"
FormBlank = IsNull(Me.frmAudit_Name)
FormBlank = IsNull(Me.frmAudit_Entity_Name)
FormBlank = IsNull(Me.frmAudit_Start_Date)
FormBlank = IsNull(Me.frmTarget_Close_Date)
FormBlank = IsNull(Me.frmHR_Dept_Name)
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)

If FormBlank = "True" Then
MsgBox ("Empty fields not allowed to add form." & _
"Fix errors and try again")
Exit Sub
End If


' Form input fields from table use the first column when accessing the
field.
' That is why name is moved to number.

tmpAuditEntityNum = Me.frmAudit_Entity_Name
tmpHRDeptNum = Me.frmHR_Dept_Name
tmpHR_SRmgrNum = Me.frmHR_SR_Mgr_Name

Set db = CurrentDb

DoCmd.Hourglass True
DoCmd.SetWarnings False


strSQL = ("Select Audit_Entity_Name ")
strSQL = strSQL & ("From tblAudit_Entity")
strSQL = strSQL & (" Where (tblAudit_Entity.Audit_Entity_Num = " &
tmpAuditEntityNum & ")")
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount = 0 Then
MsgBox ("Audit Entity not valid in table. Contact the application
administrator")
Exit Sub
Else
tmpAuditEntityName = rs!Audit_Entity_Name
End If

strSQL = ("Select HR_Dept_Name ")
strSQL = strSQL & ("From tblHR_Dept")
strSQL = strSQL & (" Where (tblHR_Dept.HR_Dept_Num = " & tmpHRDeptNum &
")")
Set rs1 = db.OpenRecordset(strSQL)
If rs1.RecordCount = 0 Then
MsgBox ("HR Department not valid in table. Contact the
application
administrator")
Exit Sub
Else
tmpHRDeptName = rs1!HR_Dept_Name
End If

strSQL = ("Select HR_SR_Mgr_Name ")
strSQL = strSQL & ("From tbl_HR_SR_Manager")
strSQL = strSQL & (" Where (tbl_HR_SR_Manager.HR_SR_Mgr_Num = " &
tmpHR_SRmgrNum & ")")
Set rs2 = db.OpenRecordset(strSQL)
If rs2.RecordCount = 0 Then
MsgBox ("HR SR Manager not valid in table. Contact the
application
administrator")
Exit Sub
Else
tmpHR_SRmgrName = rs2!HR_SR_Mgr_Name
End If

Response1 = MsgBox("Audit Entity Number is " & tmpAuditEntityNum & "."
& _
vbCrLf & vbCrLf & "HR Dept Number is " & tmpHRDeptNum & "."
& _
vbCrLf & vbCrLf & "HR SR Mgr Number is " & tmpHR_SRmgrNum &
"." & _
vbCrLf & vbCrLf & "You Sure", vbYesNo, "Confirmation...")

Call Add_Audit_Rcd(Me.frmAudit_Name, tmpAuditEntityNum,
Me.frmAudit_Start_Date, _
Me.frmTarget_Close_Date, tmpHRDeptNum, tmpHR_SRmgrNum, Me.frmSOX,
Me.frmOperational)

DoCmd.SetWarnings True
DoCmd.Hourglass False

MsgBox "Record Added", , "Done"



End Sub
Click to expand...


Okay, I see that it is unbound, and that you call another routine,
Add_Audit_Rcd, to add the record. After that, I gather you want to clear
the form. To do that, I would expect that code along these lines would do
the job:

Me.frmAudit_Name = Null
Me.frmAudit_Entity_Name = Null
Me.frmAudit_Start_Date = Null
Me.frmTarget_Close_Date = Null
Me.frmHR_Dept_Name = Null
Me.frmHR_SR_Mgr_Name = Null
Me.frmSOX = Null
Me.frmOperational = Null

It's possible I've "nulled" some fields there that should not be nulled, and
I may have left out some fields that should have been included. You'll have
to check that.

If any of those fields should be reset to a default value instead of Null,
you can set them with reference to their DefaultValue properties like this:

Me.<controlname> = Eval(Me.<controlname>.DefaultValue)

Please let me know if this approach works for you.

Aside from that, I'm concerned about somethings I see that may be errors in
your code. For example, the code that checks for blank fields:
Dim Response As Integer, Response1 As Integer, FormBlank As String [...]
' Check to ensure data entered in key fields before adding records.

FormBlank = "False"
FormBlank = IsNull(Me.frmAudit_Name)
FormBlank = IsNull(Me.frmAudit_Entity_Name)
FormBlank = IsNull(Me.frmAudit_Start_Date)
FormBlank = IsNull(Me.frmTarget_Close_Date)
FormBlank = IsNull(Me.frmHR_Dept_Name)
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)

If FormBlank = "True" Then
Click to expand...

First, FormBlank should be declared and tested as Boolean, not String:

Dim FormBlank As Boolean

FormBlank = False
[...]
If FormBlank = True Then

But on top of that, the code you posted will result in FormBlank set to True
(or "True") only if Me.frmHR_SR_Mgr_Name is Null. That's because this is
the last statement executed in the series of tests:
FormBlank = IsNull(Me.frmHR_SR_Mgr_Name)
Click to expand...

So the result of that IsNull() expression is assigned to FormBlank, and
that's the end of it.

Instead, you want to do something like this:

FormBlank = _
IsNull(Me.frmAudit_Name) _
Or IsNull(Me.frmAudit_Entity_Name) _
Or IsNull(Me.frmAudit_Start_Date) _
Or IsNull(Me.frmTarget_Close_Date) _
Or IsNull(Me.frmHR_Dept_Name) _
Or IsNull(Me.frmHR_SR_Mgr_Name)

There are other, possibly more efficient, ways to accomplish this, but that
should be pretty clear.

I'm also concerned about your use of "DoCmd.SetWarnings False". I see no
reason within this procedure for doing that, and the scope of SetWarnings
False should be as minimal as possible. You may have a reason to use it
inside the Add_Audit_Rcd procedure, in which case you should limit it to
that procedure. And any procedure where you turn off warnings should also
have error-handling in place so that there is no way to exit the procedure,
even in the event of an error, with warnings turned off.

In this procedure you have posted, there are lots of ways to leave the
procedure with both warnings turned off and Hourglass turned on. You should
correct that, so that (a) warnings are never turned off in this procedure at
all, and (b) there is no way to exit the procedure without turning off the
Hourglass.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Mike said:
Dirk,
Your suggestion of setting the fields individually to null worked
perfectly. I also made the other code changes you suggested. THanks a
lot. As a newbie to doing some of this I am just copying what others had
done before me and fixing as I learn more.
Click to expand...


Very good.

I just noticed something else: you should close your various recordsets
when you're done with them. That may involve rearranging your code slightly
(if you haven't already done so) so that you don't exit the procedure before
you close the recordsets.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Automatically Send a notification email once a record is added 0
Data Entry = No 6
Need combo box to refresh when moving to new record 2017 1
Tab Form Coding 4
Data entry for a table from a form 1
Data Entry/New Record question 2
Delay loading data 1
Testing for required data entry in form fields. 6

Top