Clear all fields in a form

A

Amy E. Baggott

I have a form that has a combo box lookup. When the combo box is updated, it
clones the recordset, finds the company in the lookup box, and resets the
bookmark to the bookmark of that company, which fills in all the other fields
in the form with the company's information. However, some of the companies
in my database are marked "Inactive" and should not have orders created for
them (the purpose of this form). When an "Inactive" company is selected, a
dialog box opens giving the user the option of reactivating the company or
not. If they choose not to reactivate the company, they get a dialog telling
them that they cannot create an order for this company and to choose another.
Then I clear the combo box, but I cannot get the remaining fields to clear.
Any ideas on how I can fix this? The code for the combo box is:

Dim objRs As Object

Set objRs = Me.Recordset.Clone
objRs.FindFirst "[exh_masid] = '" & Me![CompanyLookup] & "'"
If Not objRs.EOF Then
Me.Bookmark = objRs.Bookmark
End If

'Check to see if the company is inactive.
If Me.Inactive = True Then
Dim vbResponse2 As Long

vbResponse2 = MsgBox( _
"This company has been marked as 'Inactive'." & vbCrLf & " You
should only reactivate it if you are certain you have the right company." &
vbCrLf & "" & vbCrLf & "Do you wish to reactivate this company?", _
vbYesNo + vbExclamation + vbDefaultButton1, _
"Inactive Record")
If vbResponse2 = vbYes Then 'User wants to reactivate the company
Me.Inactive = False 'Reactivate the company
Dim rsExclude As DAO.Recordset
Set rsExclude = CurrentDb.OpenRecordset("SELECT * FROM tblExclude
WHERE MasID ='" & Me.exh_masid & "'")
rsExclude.Edit 'Clear previously set exclusions.
rsExclude![NoMail] = False
rsExclude![NoFax] = False
rsExclude![NoEmail] = False
rsExclude.Update
rsExclude.Close
Else
Call MsgBox( _
"You may not create a new order for an inactive company." & vbCrLf
& "" & vbCrLf & "Please choose another company.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Inactive Record")
Me.CompanyLookup = ""

Exit Sub
End If
End If

Me![AddOrderBtn].Visible = True

Please help. TNX
 
D

Dirk Goldgar

Amy E. Baggott said:
I have a form that has a combo box lookup. When the combo box is updated,
it
clones the recordset, finds the company in the lookup box, and resets the
bookmark to the bookmark of that company, which fills in all the other
fields
in the form with the company's information. However, some of the
companies
in my database are marked "Inactive" and should not have orders created
for
them (the purpose of this form). When an "Inactive" company is selected,
a
dialog box opens giving the user the option of reactivating the company or
not. If they choose not to reactivate the company, they get a dialog
telling
them that they cannot create an order for this company and to choose
another.
Then I clear the combo box, but I cannot get the remaining fields to
clear.
Any ideas on how I can fix this? The code for the combo box is:

Dim objRs As Object

Set objRs = Me.Recordset.Clone
objRs.FindFirst "[exh_masid] = '" & Me![CompanyLookup] & "'"
If Not objRs.EOF Then
Me.Bookmark = objRs.Bookmark
End If

'Check to see if the company is inactive.
If Me.Inactive = True Then
Dim vbResponse2 As Long

vbResponse2 = MsgBox( _
"This company has been marked as 'Inactive'." & vbCrLf & " You
should only reactivate it if you are certain you have the right company."
&
vbCrLf & "" & vbCrLf & "Do you wish to reactivate this company?", _
vbYesNo + vbExclamation + vbDefaultButton1, _
"Inactive Record")
If vbResponse2 = vbYes Then 'User wants to reactivate the company
Me.Inactive = False 'Reactivate the company
Dim rsExclude As DAO.Recordset
Set rsExclude = CurrentDb.OpenRecordset("SELECT * FROM tblExclude
WHERE MasID ='" & Me.exh_masid & "'")
rsExclude.Edit 'Clear previously set exclusions.
rsExclude![NoMail] = False
rsExclude![NoFax] = False
rsExclude![NoEmail] = False
rsExclude.Update
rsExclude.Close
Else
Call MsgBox( _
"You may not create a new order for an inactive company." &
vbCrLf
& "" & vbCrLf & "Please choose another company.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Inactive Record")
Me.CompanyLookup = ""

Exit Sub
End If
End If

Me![AddOrderBtn].Visible = True

Please help. TNX
Click to expand...


Why not check and handle the inactive status of the company before ever
positioning the form to the selected company. You could do it something
like this:

'----- start of revised code -----
With Me.RecordsetClone

.FindFirst "[exh_masid] = '" & Me![CompanyLookup] & "'"

If .NoMatch Then

MsgBox _
"Error: unable to locate company '" & _
Me![CompanyLookup] & "'", _
vbExclamation, _
"Can't Find Company"

Else
' Having found the company, is it active?
If !Inactive = True Then

' Inactive company; reactivate it?
If MsgBox( _
"This company has been marked as 'Inactive'." & _
vbCrLf & " You should only reactivate it " & _
"if you are certain you have the right " & _
"company." & _
vbCrLf & "" & vbCrLf & _
"Do you wish to reactivate this company?", _
vbYesNo + vbExclamation + vbDefaultButton1, _
"Inactive Record") _
= vbYes _
Then 'User wants to reactivate the company

' Update company record in recordsetclone
.Edit
!Inactive = False 'Reactivate the company
.Update

' Update Exclusion table
CurrentDb.Execute _
"UPDATE tblExclude SET " & _
"NoMail = False, NoFax = False, " & _
"NoEmail = False " & _
"WHERE MasID = '" & !exh_masid & "'", _
dbFailOnError

Else
MsgBox _
"You may not create a new order for an inactive " & _
"company." & vbCrLf & vbCrLf & _
"Please choose another company.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Inactive Record"

Me.CompanyLookup = ""

' Leave this procedure now.
Exit Sub

End If ' If MsgBox( ...

End If ' If Inactive = True ...

' Move the form to the company record we found.
Me.Bookmark = .Bookmark

' Make the Add Order button visible.
Me!AddOrderBtn.Visible = True

End If ' If .NoMatch ...

End With ' done with RecordsetClone
'----- end of revised code -----

That's all air code, of course, and I may have made some mistakes.
 
A

Amy E. Baggott

So I should move the "set bookmark" statement to the end of the sub just
before I enable the "Add Order" button? Should I then replace the
"me.inactive" with objRs.inactive (the recordset clone)? Also, how do I set
the Inactive to false if the user wants to reactivate the company? If I
change the value in the clone, will it change it in the recordset?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


Dirk Goldgar said:
Amy E. Baggott said:
I have a form that has a combo box lookup. When the combo box is updated,
it
clones the recordset, finds the company in the lookup box, and resets the
bookmark to the bookmark of that company, which fills in all the other
fields
in the form with the company's information. However, some of the
companies
in my database are marked "Inactive" and should not have orders created
for
them (the purpose of this form). When an "Inactive" company is selected,
a
dialog box opens giving the user the option of reactivating the company or
not. If they choose not to reactivate the company, they get a dialog
telling
them that they cannot create an order for this company and to choose
another.
Then I clear the combo box, but I cannot get the remaining fields to
clear.
Any ideas on how I can fix this? The code for the combo box is:

Dim objRs As Object

Set objRs = Me.Recordset.Clone
objRs.FindFirst "[exh_masid] = '" & Me![CompanyLookup] & "'"
If Not objRs.EOF Then
Me.Bookmark = objRs.Bookmark
End If

'Check to see if the company is inactive.
If Me.Inactive = True Then
Dim vbResponse2 As Long

vbResponse2 = MsgBox( _
"This company has been marked as 'Inactive'." & vbCrLf & " You
should only reactivate it if you are certain you have the right company."
&
vbCrLf & "" & vbCrLf & "Do you wish to reactivate this company?", _
vbYesNo + vbExclamation + vbDefaultButton1, _
"Inactive Record")
If vbResponse2 = vbYes Then 'User wants to reactivate the company
Me.Inactive = False 'Reactivate the company
Dim rsExclude As DAO.Recordset
Set rsExclude = CurrentDb.OpenRecordset("SELECT * FROM tblExclude
WHERE MasID ='" & Me.exh_masid & "'")
rsExclude.Edit 'Clear previously set exclusions.
rsExclude![NoMail] = False
rsExclude![NoFax] = False
rsExclude![NoEmail] = False
rsExclude.Update
rsExclude.Close
Else
Call MsgBox( _
"You may not create a new order for an inactive company." &
vbCrLf
& "" & vbCrLf & "Please choose another company.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Inactive Record")
Me.CompanyLookup = ""

Exit Sub
End If
End If

Me![AddOrderBtn].Visible = True

Please help. TNX
Click to expand...


Why not check and handle the inactive status of the company before ever
positioning the form to the selected company. You could do it something
like this:

'----- start of revised code -----
With Me.RecordsetClone

.FindFirst "[exh_masid] = '" & Me![CompanyLookup] & "'"

If .NoMatch Then

MsgBox _
"Error: unable to locate company '" & _
Me![CompanyLookup] & "'", _
vbExclamation, _
"Can't Find Company"

Else
' Having found the company, is it active?
If !Inactive = True Then

' Inactive company; reactivate it?
If MsgBox( _
"This company has been marked as 'Inactive'." & _
vbCrLf & " You should only reactivate it " & _
"if you are certain you have the right " & _
"company." & _
vbCrLf & "" & vbCrLf & _
"Do you wish to reactivate this company?", _
vbYesNo + vbExclamation + vbDefaultButton1, _
"Inactive Record") _
= vbYes _
Then 'User wants to reactivate the company

' Update company record in recordsetclone
.Edit
!Inactive = False 'Reactivate the company
.Update

' Update Exclusion table
CurrentDb.Execute _
"UPDATE tblExclude SET " & _
"NoMail = False, NoFax = False, " & _
"NoEmail = False " & _
"WHERE MasID = '" & !exh_masid & "'", _
dbFailOnError

Else
MsgBox _
"You may not create a new order for an inactive " & _
"company." & vbCrLf & vbCrLf & _
"Please choose another company.", _
vbOKOnly + vbExclamation + vbDefaultButton1, _
"Inactive Record"

Me.CompanyLookup = ""

' Leave this procedure now.
Exit Sub

End If ' If MsgBox( ...

End If ' If Inactive = True ...

' Move the form to the company record we found.
Me.Bookmark = .Bookmark

' Make the Add Order button visible.
Me!AddOrderBtn.Visible = True

End If ' If .NoMatch ...

End With ' done with RecordsetClone
'----- end of revised code -----

That's all air code, of course, and I may have made some mistakes.

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

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

Dirk Goldgar

Amy E. Baggott said:
So I should move the "set bookmark" statement to the end of the sub just
before I enable the "Add Order" button? Should I then replace the
"me.inactive" with objRs.inactive (the recordset clone)? Also, how do I
set
the Inactive to false if the user wants to reactivate the company?
Click to expand...

The code I suggested does all that. It was conceived as a total replacement
of the code you posted. Have you tried replacing your code with it, to see
how it works?
If I change the value in the clone, will it change it in the recordset?
Click to expand...

Yes.
 

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

Docmd Send Object - multiple CC fields from a form 3
Clearing Fields in a Word Form 5
filter combo box in subform withe a combo box in main form. 1
Show employees and others in combo box 3
Update a date field in sub-form from dates fields on main form 1
Require to clear all feilds in the form 8
Requiring Data in form fields 1
Set a combo to an entry and clear all 1

Top