Solved

How to empty combo box contents

Posted on 2004-09-20
26
1,406 Views
Last Modified: 2012-05-05
I have the followng After update event

Private Sub CategoryID_AfterUpdate()
Me!ProductID.Requery
 If Me.ProductID.ListCount <> 0 Then
   ProductID = ProductID.ItemData(0)
 End If
Me!ProductID.Requery

End Sub

The purpose is to only show Products in the Product Combo box which are associated with the chosen category. It works fine as long as there are products in a chosen category, however if the category happens to be empty, what displays in the Product combo box is whatever was last listed there. In other words, if I choose a category, choose product A, then change the category to a category that has no products, product A is still listed in the combo box.

What change do I have to make to make the Product combo box go back to empty if the catebory is empty?

Thanks.
RWW
0
Comment
Question by:PstWood
  • 9
  • 8
  • 5
  • +2
26 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 12104129

Private Sub CategoryID_AfterUpdate()
Me!ProductID.Requery
 If Me.ProductID.ListCount <> 0 Then
   ProductID = ProductID.ItemData(0)
else
   ProductId = ""
 End If
Me!ProductID.Requery

End Sub


Why the requery before and after....if not needed delete...they will slow your form down.

Mike
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12104295
I would suggest ProductID = Null, except it may be necessary to do this before the query, as opposed to afterward.

To rephrase, it may be simplest to always set the ProductID combo box value equal to Null before re-populating the combo box list.

-- Miqro
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12104688
Private Sub CategoryID_AfterUpdate()
    Me.cboProductID.Requery
    If Me.cboProductID.ListCount <> 0 Then
       Me.cboProductID = Me.cboProductID.ItemData(0)
    Else
        Me.cboProductID = Null
 End If
End Sub

Steve
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:PstWood
ID: 12104889
Data-Man & Migro:

Both of your responses produced:

Run-time error3162
You tried to assign a Null value to a variable that isn't a Variant data type.



0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12104933
my code assigned an empty string...

Me.cboProductId = ""


By the way if you have column heads turned on then you will need to get the 2nd item and not the 1st
ProductID = ProductID.ItemData(1)

Mike
0
 

Author Comment

by:PstWood
ID: 12105024
DataMan, I get the same error with yours, and I don't have column heads turned on.

RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12105049
Try explicitly referencing your combo box:
Me!ProductID=Null

Does that produce the same error?
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12105090
What is the name of your combo box?

you should prefix the name of your combo box with cbo.

This will help minimize confusion.
me!ProductID is a field from the recordsource on the form

me.cboProductID is a combo box on the form

I think that the your code may be referencing the wrong control/field

Mike


0
 

Author Comment

by:PstWood
ID: 12105306
Shane: The explicit reference doesn't help,

DataMan: I've never used cbo for a combo box reference and always am careful to give unique names to everything, I don't think that's the problem.

What DID fix it was to put:

Else: ProductID = 0

So now 'splain that to me. I have no row in the Product table that has a ProductID of 0, but it works perfectly to clear the contents of that combo box when the Category is changed to one that has no products.

Since you confirmed what I already thought that it would be corrected through adding an ELSE statement and put me on the track of finding a solution, I'll give the points to the one who can tell me why this works and the two logical answers of "" and NULL did not.

Thanks.
RWW
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 100 total points
ID: 12105383
I suspect that the first column of your combo box is and ID field and hidden.

Also, if the comb box is 'Bound' to a field then the underlying field (at the table level) may not accept an empty string or null values.

Mike
0
 

Author Comment

by:PstWood
ID: 12105566
Data-Man:

That's correct on both counts. It's a hidden column and also the box is bound to a table which not unsurprisingly requires an entry in that field. I should have figured that the "No Null" would be the problem.

Thanks. You get the points.
RWW
0
 
LVL 18

Expert Comment

by:Data-Man
ID: 12105584
:-) Thanks....Mike
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12107352
PstWood, I find that for many reasons it is worth the effort to design unbound forms for any complex data entry task.  Doing so also brings you a step closer to a wizard structure, which I recommend where possible.  Bound forms tend to be good for display purposes, whereas unbound forms allow me to validate all entries and not give direct access to records.
0
 

Author Comment

by:PstWood
ID: 12109342
Could you elaborate a bit or point me to a source to expand on what you're referring to?

Thanks.
RWW
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12112354
An unbound form actually has no data source.  It is created the same way with controls, labels, and formatting, but none of the control values are "bound" to the database.   Instead, one creates an extra button such as "Save" (or Next in the case of a Wizard) and then writes a VBA program that evaluates all of the controls for appropriate inputs, opens it's own recordset, and then ads or changes the database on behalf of the user.  This data input structure has several advantages:

1.  No need for record locking while the user makes changes, unless explicitly required by the VBA code.

2.  No accidental changes while viewing data, because there are separate a read-only display forms and edit-only data input forms (or wizards).

3.  Required fields and validation rules can be defined dynamically.  Ever had a case where you'd like one column required when another column has a certain value?  This can only be done programmatically.  With a well-designed front-end application, there should be little or no reliance on back-end data validation.

4.  All error checking is performed before the recordset is opened, so it is easier to abandon changes.

5.  Complex additions or changes can be carried out by the underlying code based on simplified inputs.

6.  The changes to the database can be wrapped into a transaction, where appropriate.
0
 

Author Comment

by:PstWood
ID: 12112943
You undoubtedly have a good case for what you're saying, however, I don't know how practical it is for me personally. If I'm understanding you correctly, using unbound forms requires VBA to populate such things as combo boxes, etc. and my ability falls pretty far short of that. I don't, like many of the participants in this forum, do db design on a routine basis, and consequently don't have the need to learn VBA to the level necessary for what you propose. I see the value but don't have the need to do what's necessary to learn to put it in practice.

Thanks for the definition, and perhaps over time, I'll pick up some of the skills to do what you suggest.
RWW
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12113154
It's something to keep in mind.  Actually, combo boxes have completely separate row sources and data sources.  You can specify a query in the row source while leaving the data unbound.  This simply means that selecting a value from the combo box list has no affect on the database.

Enjoy,
-- Miqro
0
 

Author Comment

by:PstWood
ID: 12113854
Are you saying that by simply making a query built on the table or tables desired for the form, then using the query as the basis for the form, the form fields will be unbound?

Thanks.
RWW
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12113904
Nope.  : )  I was just talking about the combo box control.  There is still no data source for the form itself.
0
 

Author Comment

by:PstWood
ID: 12114209
I guess I'm not following you. How do you build a form that has a dropdown containing a list of, for example, ItemNames that's in a table that contains ItemID, ItemCost, etc. without binding the combo box to that table or a query built on that table?
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12114321
I recommend toying with some of the properties of a combo box control.  You should find that there are separate data sources for the control's value and for the control's list rows.

The list in the combo box is based on a query, but it is not actually 'bound' to the database.  If changes were made to the underlying records, then the combo box list would have to be requeried to display those changes.

The control's value, on the other hand, needs no data source.  If a selection is made in an unbound control, then the value is stored in a property of the control instead of in a database field.
0
 

Author Comment

by:PstWood
ID: 12118810
Ok. So how do those values stored in the form control property then get written to their final destination in one of the db tables? I've used temp tables to store form data, then had a submit buttion write it to its final destination table, but I don't think that's what you're refering to.

Thanks.
RWW
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12118898
I'd like to add my views on unbound forms, and in particular the points raised by miqrogroove:

> 1.  No need for record locking while the user makes changes, unless explicitly required by the VBA code.
Agreed, a very important part of unbound forms. However, I have *never* had a problem with multiple users trying to change the same record, although this is due to the way the database fits in to the business process rather than through programming around concurrency issues.

> 2.  No accidental changes while viewing data, because there are separate a read-only display forms and edit-only data input forms (or wizards).
Again, very handy but since I log data edits on *every* field in the key tables (for tracking workflow and other processes), I've never found this to be a problem.

> 3.  Required fields and validation rules can be defined dynamically.  Ever had a case where you'd like one column required when another column has a certain value?  This can only be done programmatically.  With a well-designed front-end application, there should be little or no reliance on back-end data validation.
The logic can also be added into a bound form - unbound forms aren't required for this. I do agree in principle on wizard-type data entry up to a point though. I generally provide wizard-type data entry for new record additions and certain business processes which require changes and calculations made to multiple records.

> 4.  All error checking is performed before the recordset is opened, so it is easier to abandon changes.
Similar thoughts on this as point 3. So far as abandoning changes goes, the Undo command makes this quite easy - I haven't encountered too many limitations.

> 5.  Complex additions or changes can be carried out by the underlying code based on simplified inputs.
Agreed, but this isn't always necessary or appropriate.

> 6.  The changes to the database can be wrapped into a transaction, where appropriate.
As point 5.

The key is the words "where appropriate". Bound forms and unbound forms both have their advantages as far as both data entry and viewing are concerned, and it's important in good design to choose the best solution for the job. Using bound forms in some circumstances will cause problems whereas unbound forms are inappropriate for other circumstances.
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12119228
shanesuebsahakarn, what would be an example of a circumstance making unbound data entry inappropriate?
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 12119240
PstWood, the value would be copied directly from the unbound control's Value property to the target field.  This is ideally accomplished using the ADO model.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12119329
A lot of incidental data entry can certainly be made bound, particularly when you're entering via a continuous form view. An example might be invoice lines, which generally do need to be entered via a continuous form. You can certainly use data grids or similar controls, but they have their own issues.

I suppose the term "inappropriate" is wrong - what I meant was, there are no absolute advantages in using either method. From a personal point of view, if there is no major advantage either way, I would use a bound form over an unbound one, the advantage from my point of view being shorter development time. The most complex form in my database at present has 18 tabs and a large number of related subforms and sub-subforms, and is bound - I've not had any problems with users editing data as it contains all of the validation logic, BUT I use unbound forms for creating the record in the first place, as the complexity of setting it up requires both data logic and business process logic.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

777 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