Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change table data from a form (access 2k, win2k pro)

    This is probably easy but i'm bogged down in Object Browser....

    I have a form that asks to confirm a client discharge want to move the selected record from Intake table to Discharge table. The forms are based on queries from the main tables. I could, more simply, just set a flag on the Intake table to Yes/No and not copy/delete records (which seems simpler and involves no potential loss of data) but, at this time, I am not clear on how to use VBA to find a record and alter a field or find a record and copy/move the contents to a different table.

    Any suggestions would be appreciated.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change table data from a form (access 2k, win2k pro)

    If the form is bound to a table or query, all you need to do is have a checkbox bound to that field and allow the user to check it. I'm not clear on why you think you need to use code to set the value, or for that matter, why you think you need to move the record to another table. The latter should not be necessary at all, since you have already changed the category of the record by seting the value of the Yes/No field.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change table data from a form (access 2k, win2k pro)

    thanks for the quick reply!

    I'm having a little trouble implementing your suggestion, however. The field I am trying to alter is not a field from the source table for the form but from another table, so creating a control bound to a field not in the source table doesn't seem to work -- Access says it can't be edited. I am trying to set the field value to Yes or No (or whatever Boolean value) with a button rather than a check box, which is why I'd like to do the operation in code.

    The db has two tables: IntakeForm and DischargeForm. I want to set the app up to open to the Intake form and, if a discharge is called for, there is a button that takes you to the discharge form (open to display a specific record). Once all the info is entered for the Discharge, user clicks on a button called 'Submit Discharge' than triggers a flag in the Intake table to Yes OR, alternatively, cuts and pastes the Intake information to a new table. The point in moving the records to a different table is that you have a stack of records that are Intake only rather than a stack of records that are mixed -- but if you set your filters correctly it's not a big deal to make two stacks. It just seems cleaner to have the Discharged people physically separated from the undischarged ones. More or less a philosophical point; I am not a partisan really.

    Abstractly, I think clicking on the Submit Discharge button should trigger an action where the IntakeForm table is read, the specific record found, and the DischargeYN field set to Yes for that record. That sounds like SQL for the first part and a DoCmd type statement for the second, all of it in code for the Submit Discharge button. I am not clear on how to set up and execute SQL in code using Access VBA, altho I have done plenty of DB interaction with ASP and, of course, queries.

    This Discharge form collects different data for the same client than the Intake form does, but you can't submit a Discharge for a client that does not exist in the Intake table. There is a Client ID generated by the system that determines the linkage between the tables.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change table data from a form (access 2k, win2k pro)

    It may seem cleaner from the viewpoint of physical files, but in a database, it is not necessarily cleaner. What you need is a client table to hold the basic information about the person, an Intake information table for intake information and a discharge information table for the discharge-specific info. Then you could base your main form on the client and use subforms for intake and discharge, insert the clientID into the intake table and when you are ready to discharge the person, insert the clientID into the discarge table and create a new record there. You would not, in fact move any data between the tables and any corrections made to the client information would only need making in the client table.
    Charlotte

  5. #5
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change table data from a form (access 2k, win2k pro)

    point taken on the issue of separating records.

    I do not want to implement subforms as the Intake form and the Discharge form are fairly complex and embedding the Discharge into the Intake form will be unweildy. Logically the Discharge form *is* a subset of Intake but I want to maintain separate form pages in the UI. The root problem is creating a control on the Discharge form that alters a field in the Intake table. Perhaps I should base the Discharge in a query tying the two together via the Client File Number.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change table data from a form (access 2k, win2k pro)

    If you use a tab control with a subform on each page, you needn't display both subforms at once. You could set the the tab control style to buttons rather than tabs, which would give you a similar effect to what you're describing. On the other hand, if you *want* to pop up a separate form, you can pass a WhereCondition string to the called form so that it automatically opens up at either the existing discharge record for that intake or a new discharge record for that intake, allowing the user to enter the relevant details. Why would you alter a field in intake in the first place? If a discharge record exists, do you actually need a field changed in the intake record? Trying to do it the way you describe means that you have extra work to keep the two tables in sync, while just putting a discharged date in the discharge table would give you the same information and would not require extra maintenance.

    I would still suggest a separate client table, where you could maintain basic information across multiple intakes and discharges, if necessary, which would give you a better history. The clientID would then become a foreign key in your intake table, while the intakeID would become a foreign key in the discarge table, so that for any given client, you could have mutliple intakes, and for each intake you could have a single discharge. Does that make sense to you. Then you would simply need a query to give you the information about a client's intakes and discharges all at once.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Feb 2003
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change table data from a form (access 2k, win2k pro)

    That's a good point about registering a discharge in the discharge table rather than the Intake table. I suppose I was thinking in terms of the UI: you enter Intake info, etc. etc. and then at some later date you find a client and do a discharge, which brings up the discharge table and all the associated data fields. One way of looking at it is you are querying an existing set of data (Intakes) and 'marking' some as discharged but, really, it's not strictly necessary to do that. To go even further, I was pondering actually moving records from one table to another upon a discharge, but, as you say, none of that is really necessary. The amount of information that is duplicated is not large (2 or three fields) across the two and it probably would be useful to farm those out to a seperate table and put in the keys and create a query or two to stitch the tables together. The one data element that is really key (no pun intended) is the Client File Number, which is auto-generated after a drop-down is selected. That would be stored in the seperate Client table and, from there on, Intake doesn't know what Discharge is doing.

    Well, I'll work on that then. I must confess, altho I have been involved with Access for some time, all this Primary Key and Foreign Key stuff still feels confusing. Some people are born to db design, others have it forced upon them...

    Anyway, thanks for your help and advise -- i feel a little more clarity once again after posting to the Wonderful Office Lounge.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Change table data from a form (access 2k, win2k pro)

    I highly recommend you get yourself a copy of the OReilly book Access Database Design & Programming, by Steven Roman. It has a good overview of relational design and will serve as a handy quick reference when working with Access databases. Good luck, and feel free to post back with any further questions.,
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •