|
| |
|
|
JenNeedsHelp
Posts: 5
Joined: 4/14/2002
From:
Status: offline
 |
Update Access Database based on Checkboxes - 4/14/2002 10:05:19
I know this is long winded, but I always leave important information out when asking for help, so I'm trying to get past that 
I'm trying to create a code management system. I want people to be
able to search for codes by email address (their's or anyone else's -
this part is no problem, I have that working fine). But I'd also like
them to be able to create a master list for themselves of whatever
code they choose. They can add any code in the table to their list.
I have an Access table with the following fields:
email_to, code (text), primary(text - yes or no is it's populated
with), updatedate (timestamp), primaryremoved (text field - yes or no
is what it's populated with)
In one page I have database results that shows the code for every
email_to (searchable by drop down email address).
I want people to be able to check a box that makes them primary (this
means they are the primary owner of that code, so when they do that,
they must turn the other person off - so the checkbox has 2 functions,
1. to make themselves the primary owner and 2.turn the other person
off)
I also want them to just be able to add themselves to that code
without making themselves the primary owner, this will involve
inserting a record with the primary field being "No" and keeping
whoever is the primary owner, still the primary owner.
So I'd like a total of 3 checkboxes - one to make them the primary
owner - which will also add them to the list, one to add the code to
their list without making them the primary owner, and one to delete
the code from their list.
So far, I've added checkboxes to the database results rows and I'm
trying to pass them to an update page with a few update queries on it,
but it's just not working.
Any suggestions/help/ideas?
Thanks so much!
jen
|
|
|
|
alveyuk
Posts: 80
Joined: 4/4/2002
From: Lincs United Kingdom
Status: offline
|
RE: Update Access Database based on Checkboxes - 4/14/2002 17:39:21
Jen
Yep, no matter how much you try you always leave things out!! I'll start by asking my questions:-
1) in your table description you mention two columns primary and primaryremoved but do not mention primaryremoved in your narrative so I assume this is to be populated to Yes when the primary is populated to No
2) you have column names of code and primary which are obviously working so they are OK but be careful with names like that as they are very close to clashing with reserve words.
3) you mention 3 checkboxes, two to add to the list and one to delete, now that really confuses me as this is insert and delete but you seem to be talking about an update page which is not one of the options!!
4) you did not mention what happens if they wish to delete a code from their list and they are currently the primary, who would then be the primary?
5) do you want to physically delete the code from their list or do you want to remove it from the display and preserve an audit trail of what codes have been allocated in the past?
Anyway, making sweeping assuptions for those points I would suggest the following for your update page:-
1) the first action is a conditional Database Results Wizard (DRW) to remove the old primary. The DRW itself would update the primary to No and the primaryremoved to Yes where the code matched the input code. It would need VB script before and after which would control whether it ran or not. e.g.
Before:- <% if request("primary")="Yes" then %>
After:- < end if %>
2) the second action is to apply the setting for the current email address, slightly complicated by being either an insert or an update for which I would suggest 3 DRW's
2a) the first of these DRW would be an unconditional select count(*) as count_used from table where email_to='::email_to::' and code='::code::' However, instead of displaying the results, you place the following VB code as a replacement for the display (thus inside the database region) <% count_used = fp_rs("count_used") %> This will save the value in a VB variable
2b) this would be a conditional DRW to insert the current settings. The insert would be straight forward but would have the following VB code before and after:
Before:- <% if count_used = 0 then %>
After:- <% end if %>
2c) this would be a conditional DRW to update the current settings. The update would be straight forward but would have the following VB code before and after:
Before:- <% if count_used > 0 then %>
After:- <% end if %>
If you wished you could combine 2b and 2c and separate them with <% else %> the result is the same.
The delete? Personally I would have the delete check box as a button in a separate form that routed to a delete page. If you want to route it to the update page then I would suggest you place VB code in the header to redirect it to a delete page. If you really want to handle it on the update page then you need to make the whole page I have outlined conditional so that it looks in principle like (I'm leaving the syntax to you)
if delete requested then
action delete
else
action update as in 1, 2, 3 above
end if
Bear in mind with the delete that you may need to include a select count(*) as primary_count on a similar basis to the used in 2a above. This is to check whether they are trying to delete a primary on the assumption that they should have made somebody else the primary first. That is why I think it would be easier to maintain if it was a separate page.
Hope that somewhere in all that you can find something to help.
Ken Alvey
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
