Transmitting Form changes to SQL Server?

  • Thread starter Thread starter el zorro
  • Start date Start date
E

el zorro

I have a form in Access mdb that I am trying to get to work in adp. In the
mdb version, a query underlies the form. One of the fields in the query is a
check box, the value of which is pre-determined by whatever item is selected
in another field on the form. In other words, there is a table of items, some
of which have the checkbox checked, some of which do not. This table is part
of the query that underlies the form. Depending on what item I select, the
checkbox is either checked or not.
-
FOr example, if the user checks "A" from the list on the form, the check box
shows a check (TRUE). If the user checks "B" on the list, the check box shows
no check (FALSE). The (read only) check box on the form responds instantly
depending on what item is selected on the list. I am then able, through VBA
code on the AfterUpdate event for the list, to use the status of the checkbox
to trigger certain other functions on the form (such as enabling/disabling a
form field).
-
Making the conversion to adp, the query that underlies the form is now a
View that resides on the server. You probably see the problem already. THe
checkbox no longer responds in real time to input from the user. If I select
"A" from the list, the checkbox is null until I exit the record and return,
when it will show a check. This, of course, will not work to
activate/deactivate fields for the user when the record is being created.
-
I tried to requery the view on AfterUpdate, but that just brings up a new,
blank record, so that didn't work. I tried doing a save on AfterUpdate, but
no dice again (the checkbox did not respond).
-
My question is how do I transmit to the server that I have made selections
on the list box, so that I can use VBA to trigger events based on the status
of the related checkbox?

THanks!
 
I'm not sure to really understanding what you are doing here. My latest
impression is that you are changing the value bound to the checkbox directly
on the backend (the sql-server) instead of doing it in the frontend; ie.,
changing the value of the checkbox itself.

It's a certainty that changing the value of a bound control directly on the
sql-server will bring you a lot of trouble; especially when using an ADP
project or an ODBC linked table because the association between the bound
control and the underlying field on the table is not as direct as with a JET
table.

In my opinion, you should change the code in order to directly change the
value of checkbox control in the frontend.
 
I guess that was alittle confusing. I am not changing the value of the
checkbox at all. The checkbox has a status assigned to it depending on what
record on a table it belongs to. Item 'A' on the Item table has a TRUE
checkbox. Item B on the table has a FLASE checkbox. The user cannot change
these values.

A Large query that includes the Items table is the data source for the Form.
The Item table is the recordsource for a list box on a Form. (There's a lot
of other information besides the checkbox). In Access mdb, when the user
entered data for a record on the form, and selected an Item from the List
Box, the corresponding value was entered into the form record, and all the
associated data for the Item instantly appeared in other read-only fields for
the user to see. THis included the value of the checkbox, so I was able write
VBA code triggered by the AfterUpdate event of the list that said "based on
the value of the checkbox, enable/disable a form field (Field.Enabled =
[Checkbox].Value).

This isn't working in adp/SQL. The value of the checkbox does not
immediately change as the user selects items from the List Box. Even saving
the record does not work-- the checkbox is not updated until after the form
is closed and re-opened(which is too late).

AS a test, I byoassed the Form and looked dorectly at the view that is the
data sopurce for the form. In the mdb query, as soon as I change an item
value, the checkbox responds with the appropriate checkbox value. In the SQL
view, it does not.

I guess the issue is that in the SQL server version, the server accepts the
Item number that the user selects ("A" or "B", it gets that) , but does not
compute al lthe associated attributes of that selection until after the form
is closed. Because of that, the form has lost functionality because I can no
longer use those values in VBA code, triggered by events, to do things like
as enabling/disabling a field.

Any ideas? (I'm probably missing some fundamental bit of SQL knowledge here.)
 
Back
Top