Contain all current records, but only show those that currently meets criteria

[Ronald Dodge]

Access 2002
DAO Coding

Is there a way to setup a recordset to be able to view all of the records of
a table (such as the dbOpenTable Type), so as it can see the changes that
may take place since the time that the recordset was set (such as the
dbSeeChange Option), but yet, still be able to hide certain records based on
some sort of criteria such as how the "WHERE" part of a SQL statement works,
when the information is bound to the RowSource of a combo box?

Example:

Table Reason

ID
Description
Enable

Be able to contain all of the records in the Reason Table, but only show
those records whose "Enable" field is CURRENTLY set to True (value of -1),
of which this could change by another user between the time that the
recordset was openned and when the user that is using the combo box clicks
on the down arrow or types in the ID number of the Reason.

The below is a statement of why I don't use bound forms/controls to resolve
the above issue.

I would bind the control to a field in the table, or even bind the form to
the table, but due to how Access Events works with bound forms/controls, it
makes it NOT so user friendly for mouse users. Main issue with this that I
have, try typing something in a control textbox portion, then click on a
control that in theory doesn't require the previous control to be validated
first, such as either a help or form reset command button. Well in Access,
all of the events are ran first before the mouse event on that control has
even had a chance to take place, but if the Cancel Integer variable is set
to either False or the value of 0 within either the BeforeUpdate Event or
the Exit Event of the control that the user typed something in, the control
that the user clicked on won't ever take place for that particular click
event, just simply cause the changing of the focus has been cancelled out.
This is the sole reason why I do not use bound forms/controls. Yeah, this
sort of behavior also can happen to keyboard users like using the tab key or
the Alt key and the letter, but at least with the keyboard method, that can
be caught via the KeyDown/KeyPress/KeyUp events on the control unlike the
Mouse Events on the control that has a textbox portion won't even be ran
when a command button is clicked on, which is understandable and fine. It
just would have been nice for access to have something like what VB 6.0 has
with it's CausesValidation Property and Validate Event, of which I have
created my own Access version of that, but it only works for UNBOUND forms.

 

[Tim Ferguson]

Be able to contain all of the records in the Reason Table, but only
show those records whose "Enable" field is CURRENTLY set to True
(value of -1), of which this could change by another user between the
time that the recordset was openned and when the user that is using
the combo box clicks on the down arrow or types in the ID number of
the Reason.

' build the query: just never open a dbOpenTable recordset...
' the order by clause will help to find the record again
strSQL = "SELECT ID, Description " & vbCrLf & _
"FROM Reason" & vbCrLf & _
"WHERE Enable" & vbCrLf & _
"ORDER BY ID;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

' do some stuff....
' until it's time to look up the same query again
' do it like this
rs.Requery


' finally don't forget to
rs.Close


Hope that helps


Tim F

 

[Ronald Dodge]

Thanks, it's the Requery bit that I have forgotten about.

Tell the truth, I have only been working with DAO for the last few months,
but it feels as though I already know it fairly well, but then the one thing
that has helped me progress with my project, I for the most part have a high
tendency of understanding the different help files, but yet, that's only if
the help files provides enough information about the different
methods/properties along with what each of the settings are for. Of course,
the planning that I have done prior to the developing that I'm doing now,
that also helps with the steps and other information that's involved to
create the DB program that I'm in the midst of now.