combo box failure

[Fred Loh]

i have a table with a yes/no field call "Edited". i have a button that runs
code to set the "Edited" field of a selected record to "yes". the code also
set the rowsource (using SQL Update and Set statement) of a combo box to
display only records with the "Edited" field = "No". the button and the combo
box are on the same form. the record is selected from the combo box.

when i step through the code in debug mode, the combo box nicely displays
correctly only records with the "Edited" field = "No". which could mean
nothing wrong with the code? but when i click the button in open form mode
and open the combo box, the combo box displays both the record with the
"Edited" field = Yes and records with "Edited" field = No. for the record
where the "Edited" field has been set to "Yes", the "edited" field displayed
in the combo box shows a "No". the "Edited" field change to a "Yes" only
after i open the combo box twice. the record with the "Edited" field = "Yes"
disappears from the combo box only after i have set another record's "Edited"
field to "yes". this does not happen all the time though.

what have i done wrong? what is the right way to do it so that the combo box
correctly display only records where "Edited" field ="No" at all times?

thanks for your help.

 

[Klatuu]

All you really need to do is use a query for the rowsource property of your
combo box that has a Where Clause of
WHERE [Edited] = False

Then in the button code where you set the field value of the current record,
you need to requery your form. The issue you will have with that is that a
Requery will cause the form to go back to the first record in the form's
recordset. That can be handled by saving the primary key field value of the
current record before the query then repositioning the form's current record
to the saved primary key value. This means you need to have the recordset's
primary key field bound to a text box on your form. It can be hidden if you
don't want the user to see it. Here is an example (it is "air code" with
made up names)

Dim lngPrimeKey As Long

'Do your SQL Update query here

lngPrimeKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngPrimeKey
If Not .NoMatch Then
Me. Bookmark = .Bookmark
End If
End With

 

[Clif McIrvin]

In the example below, couldn't the line

lngPrimeKey = Me.txtPrimeKey

be replaced with

lngPrimeKey = Me.RecordsetClone![PrimeKeyField]

thus bypassing the need for a bound field?

 

[Klatuu]

Now why in 10 years of doing Access I haven't thought of that. Yes, it would
work just fine.
--
Dave Hargis, Microsoft Access MVP

In the example below, couldn't the line

lngPrimeKey = Me.txtPrimeKey

be replaced with

lngPrimeKey = Me.RecordsetClone![PrimeKeyField]

thus bypassing the need for a bound field?


--
Clif

All you really need to do is use a query for the rowsource property of your
combo box that has a Where Clause of
WHERE [Edited] = False

Then in the button code where you set the field value of the current record,
you need to requery your form. The issue you will have with that is that a
Requery will cause the form to go back to the first record in the form's
recordset. That can be handled by saving the primary key field value of the
current record before the query then repositioning the form's current record
to the saved primary key value. This means you need to have the recordset's
primary key field bound to a text box on your form. It can be hidden if you
don't want the user to see it. Here is an example (it is "air code" with
made up names)

Dim lngPrimeKey As Long

'Do your SQL Update query here

lngPrimeKey = Me.txtPrimeKey

Me.Requery

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngPrimeKey
If Not .NoMatch Then
Me. Bookmark = .Bookmark
End If
End With

 

[Clif McIrvin]

Now why in 10 years of doing Access I haven't thought of that.  Yes, it would
work just fine.

Gee; and it's been at least 10 years since I have done any serious
programming <g>

I appreciate your time in sharing your knowledge with others!

 

[Klatuu]

So, who said I was serious <g>

 

[Clif McIrvin]

fun aside, I was intrigued by your use of

With Me.RecordsetClone

I've used RecordsetClone, but always with a Dim / Set = combination as
per the MS example code.

Are there general 'rules of thumb' to help one determine when 'with'
is appropriate, and when to use 'Dim / Set' ? Or is it more a matter
of personal preference and coding style?

 

[Klatuu]

Microsoft coding examples are suspect at best.
I believe they try to keep it as simple as possible and actually things that
show some different things in the same example.

I see no point in enstansiating a recordset when you already have one.
I use With constructs extensively. According to Access 2002 Developers
Handbook by Litwin, Getz, and Gunderoy, it is a performance enhancer. Once
you have established the reference with With, Access doesn't have to figure
it our for each line. And, as you see, it is fewer lines of code. Being the
lazy type, fewer lines of code attracts me.

 

[Klatuu]

Sorry, Clif, I don't think my previous reply actually answered your question.

If an object doesn't exist, then a Dim/Set is required.
If it already exits and you are going to be addressing properties or methods
of the object contiguously, then the With/End With is appropriate. As I
stated previously, it is a performance enhances and to my eye it is easier to
read and visually brackets the code related to the object. But, even if it
is necessary to use a Dim/Set, once enstanciated, I still use the With/End
With to address it if I am coding move than one line that references it.

As to using it with a form recordset or recordsetclone, they are objects
that exist. I see no point in enstanciating an object that references and
existing object. I wont say there are exceptions, but I can't think of any
at the moment.

 

[Clif McIrvin]

Thank you. Not instantiating a reference to an existing object makes
sense to me! And I agree that the code is both simpler and more
readable -- like you, less code has a definite appeal for me!