Using Bound Form and controls to inquire and update with other requirements

[Ronald Dodge]

Most of the stuff I have done in Access mainly dealt with unbound forms due
to the fact that I have issues with how error checking is done. It's been a
long while since I have worked with Access and now having to get back into
it, but only this time with bound forms and controls. I am still needing to
use my custom error checking code to get around the fact that without such
code, when doing data validation on individual controls (when moving from
one control to another) as opposed to doing data validation on the whole
form (moving from one record to another record), data validation can only
either "ALWAYS" take place on individual controls or "NEVER" take place on
individual controls, thus why I have to use my custom error checking code to
get around this limitation, so as error checking and data validation on
individual controls only takes place at appropriate times. Yes, I'm a real
stickler when it comes to data validation.

At present, I'm working on setting up a standard form, but using one of the
tables as the form itself is bound to as a basis for setting up the standard
form. I have not liked some of Access' default behavior, so I'm using
certain things to modify those behaviors, most of which done via VBA.

The form has an ID field, and some other fields. The other fields should be
able to operate like normal, but the ID field has certain unique things
about it that I want it to work differently from the other fields.

The ID field is a textbox.

User can use the field to inquire on records, and it returns the record that
matchs to the value of the textbox, provided it's a valid ID value.

The ID value must not be allowed to be changed on any record as it's what
identifies the record.

For new records, Access will take care of assigning new ID values, though
the method is dependent on a table by table basis.


On this standard form, it will have 4 command buttons in the footer section,
Inquire, Add, Update, and Delete. In order to Update or Delete, one must
inquire on the record first.

Given Access uses DAO by default, I added the DAO 3.60 COM to the reference,
so as to stick to the same model, though eventually, I will also have to get
use to the ADO disconnected database model as I'm also learning more of the
..NET stuff towards my MCSD cert.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[SteveM]

So what is your question??

Incidentally, the ADO in Access is not ADO.NET

Steve

 

[Ronald Dodge]

The question is: How do I get the ID control to inquire on the record based
on the text in it, but yet, don't allow it to change the data in the ID
field of the table behind the scene?

As you might see, as a developer, I want to learn as much of the behavior as
I can of all the various interactions, so as I can see things much more
easily as I come to them. You might think I'm too detail oriented, but it's
what has allowed me to get through a lot of things.

Sorry for the delay as I was pulled off this project temporarily to take
care of other things. I'm attempting to give bound forms/controls a second
chance, but so far not having too much luck. I fully understand ADO in
Access isn't the same as ADO.NET for various reasons not to mention the Jet
Engine limits ADO quite a bit, which made ADO not as useful as I would have
hoped. It seems any of the 3 general ways I go within Access, I run into
road blocks.

ADO on a Jet Engine doesn't allow for dynamic cursor keyset, thus have to
assume a disconnected model, but only without many of the features that
ADO.NET has.

DAO has certain bugs in it dealing with unbound forms/controls such as the
EditMode property doesn't work properly 100% of the time as expected, thus
have to create my own set of properties and objects to deal with such
issues. As a result of this, it was looking to be more and more and more
like a lot of work on a form by form, or control by control basis. Not only
that, but DAO seems to be a dead language overall, even though Access uses
DAO rather than ADO (Can't speak for Access 2007 as I don't know this to be
true for that version, however, other than for a few minor things, there's
no real difference between Access 2000, 2002, and 2003).

In the past, the biggest issue for bound forms was the error checking issue,
but I managed to be able to create my own error checking code to address
that issue, though it took me 3 months of R&D to resolve that issue mimicing
the VB6 CausesValidation Property and Validate Event. At that point, I
thought I could only use it for unbound forms/controls, but after I ran into
some major road blocks with the unbound forms, and so many others were
telling me that Access couldn't do it or that I had to forego one thing or
another, one person in particular noticed what I was attempting. He stated
some different things, which then has me realize I could use my custom code
with bound forms/controls, I just wouldn't be able to set the "Cancel"
property/variable within the various events of error checking codes to
"True" due to this "Always" or "Never" error check. This is where I would
need to set a form level variable to a value to indicate that the focus
would need to return back to the previous control, should it be required,
thus that's where the custom error checking code came into play. While it
didn't do the actual error checking any more after this was pointed out, it
still had to handle all of the focus and other form related stuff. Why I
didn't think of "NOT" using the Cancel property of the various events, it
was a case that I was too deep into the forest. On that note, a lot of
people said that Access would have to be totally rewritten to allow it to
have such feature as the CausesValidation and Validate Event, which I have
since proven that it would not be a total rewrite. As a matter of fact, if
Access is truly using OOP philosophy in the codes, then it should be
relatively easy to put into place. For how I have my validation code setup,
it would need to set the "Cancel" fields to hidden and only allow the codes
behind the scenes to use them, which then each control would have a
"CausesValidation" property and a "Validate" event.

After I stated it as such, some people mentioned it's as if I didn't know
about the form level error checking, which was even then far from the truth.
Given my days of doing data entry, I would rather correct things as I go
along, especially if I was doing nothing but data entry all shift long, like
I did when I worked at the IRS doing nothing but entered the data into the
computer from the various tax forms that was submitted to the IRS. As a
matter of fact, I used both control level and form level error checking,
though certain form controls seems to not work too good either for as long
as they have focus such as the ListBox, so I once again had to create my own
ListBox Class to mock the ListBox behavior for while it has focus, but even
then, ran into other bugs dealing with such things as selection issues via
the keyboard method.

I have attempted to create something, which when I typed in a different ID
value to inquire, it changed the value of the record that it's on. I have
also attempted to tie it to a query with the query criteria based on the
text field of the ID control, but yet, also have the ID field "Unbound" as
the only unbound data control on the form. When attempting to requery on
the form, it does nothing. However, when I open up the query itself, it
runs automatically and it works just fine as expected.

I also purposely built the BE separately from the FE with linking the tables
in the BE to the FE DB. Security stuff has already been put in for the most
part, just haven't converted it to MDE as it's in the design process still.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000