Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Clearing inforamtion from Access form

Posted on 2014-01-17
23
Medium Priority
?
369 Views
Last Modified: 2014-01-17
Hello!
I am trying to write code that will clear all the fields in my form. I have a combo box, a list box, and two text boxes.

I have used this code in the past, but it is only working to clear the combo box now.

Private Sub btnNew_Click()

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox, acTxtBox
ctl.Value = Null
Case Else
End Select
Next ctl


End Sub

Open in new window


Can someone tell me what I am doing wrong?

Thank you!
0
Comment
Question by:Megin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 3
23 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789068
repalce
Case acComboBox, acListBox, acTxtBox

with

Case acComboBox, acListBox, acTextBox
0
 

Author Comment

by:Megin
ID: 39789078
Done, but I receive an error now.

"You can't assign a value to this object."
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789124
try using

ctl.Value = ""
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Author Comment

by:Megin
ID: 39789141
I am still getting the same error. If it matters, the error is "Run-time error '2448.'

Just in case, here is the code again with the change you suggested"

Private Sub btnNew_Click()

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox, acTextBox
ctl.Value = ""
Case Else
End Select
Next ctl


End Sub

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789161
separate the control types and see which one is raising the error

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = ""
Case  acListBox
ctl.Value = ""
Case  acTextBox
ctl.Value = ""
Case Else
End Select
Next ctl
0
 

Author Comment

by:Megin
ID: 39789180
When it errors, this is the part that is highlighted:

Case acTextBox
ctl.Value = ""

Actually, it is specifically that second line, but I needed to show which instance of that line.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789211
is this textbox bound to a field in the table? if this is, you don't need to clear the content, since it clear itself when you go to New record.
0
 
LVL 75
ID: 39789214
As a side note, set the value to Null, not "" (Zero Length String)

mx
0
 

Author Comment

by:Megin
ID: 39789303
Nothing on the form changes when  go to Add New Record.

The text boxes are unbound. They are a place for the user to type in a new record. I have a button that then adds the record to a table, but after that the data just stays in the boxes.

If it matters: The combo box and the list box are getting their information from two different tables. The first is pulling information from a single table. The second is using a query so that, depending on the choice of the combo box, different items populate the list.

If the user wants to add a record to the list, he/she types the information into the text boxes.

All of this works great.

The only problem is getting the text boxes to clear when everything is done.

DatabaseMX: I originally had it set up with Null instead of "", but it wasn't working then either.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789308
upload a copy of the db
0
 

Author Comment

by:Megin
ID: 39789309
Also, I used the same code I stared with in another form and it worked great. I think the problem is just using it with the text box.
0
 

Author Comment

by:Megin
ID: 39789316
Okay.

The form I am working on is "frmAddSTO."
PMAC-Weekly-Report-Database.accdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789351
try changing this


Case  acTextBox
ctl.Value = ""

with

Case  acTextBox
me(ctl.name) = ""
0
 

Author Comment

by:Megin
ID: 39789367
It still didn't work. I got the same error.

But, am I supposed to enter the name of the text box where it says "ctl.name"?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789375
the control that is raising the error is TOID, bound to field TOID of table SubtasksOrders


use these codes


Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
Case acListBox
ctl.Value = Null
Case acTextBox
If ctl.ControlSource = "" Then
   ctl = ""
End If
0
 

Author Comment

by:Megin
ID: 39789392
I am sure it is not this simple, but I went ahead and removed that field from the form. That didn't fix the error, so I am assuming that I need to do more to get that field out of there.

What do I do?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789398
there are still bound controls  in your form... you don't need to remove them

just use these codes

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox, acListBox, acTextBox
If ctl.ControlSource = "" Then
   ctl = ""
End If


Case Else
End Select
Next ctl
0
 

Author Comment

by:Megin
ID: 39789401
Wait!
I just removed all of those invisible boxes from the form and the button worked to remove the text from the text boxes!!!!  WOOHOOO!!!!

However, it did not remove the text from the list box. :(

Any advice with that one?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 39789425
<However, it did not remove the text from the list box. :(>
you mean you want to clear the list ? nothing to show in the listbox ?

think again?

Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acComboBox,acTextBox
If ctl.ControlSource = "" Then
   ctl = ""
End If

case  acListBox

ctl.rowsource=""

Case Else
End Select
Next ctl
0
 

Author Closing Comment

by:Megin
ID: 39789438
Worked!
You are the best!  Thank you so much for hanging in there with me. I am at the beginning stages of learning how to code things and am not picking it up quickly.

I am grateful for all of your help!
0
 
LVL 75
ID: 39789496
"DatabaseMX: I originally had it set up with Null instead of "", but it wasn't working then either."
Well ... that comment was about Best Practice, not why it wasn't working.

Read Here  about why using Zero Length Strings is a bad idea. (scroll down to Zero Length String)

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39789558
these are unbound controls...
0
 
LVL 75
ID: 39789608
still ...
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question