Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2007
    Posts
    348

    Red face Unanswered: form dependencies

    Ok, so my dirty little secret is out. I can't create form dependencies.
    I can make table dependencies and I can make subforms but where can I look for a good tutorial on multiple forms in which one needs to be fed by the other. example:
    PtTable(PtNum(pk),LName,FName,MI,DOB)
    VisitTable(VisitNum(pk),PtNum(fk),AdmitTime,AdmitD ate,DisChTime,DisChDate)

    Two different forms, PatientFRM and VisitFRM. A patient can visit many times. Once I find or enter a patient into the system I want to pop up any visits they may have on a different form.

    I realize the example is really remedial and could be done with subforms but some things can't. I always thought that doing it with Relationships would work but it doesn't. I can look in Dependencies and see table dependencies but I can't seem to create a form dependency.
    A book, a website anything like that would be helpful.
    I have Using Access 2000 by Roger Jennings and I have looked several times and am either just missing it or it's not there. If there is another manual, that works better, I'll be glad to pick up a copy.

    Sorry for the newbie question, it just seems to be often glossed over and I'm being asked to help develop more access type stuff that people will actually use (rather than just running stuff through queries that just goes back out to an Excel spreadsheet) and I have run into numerous time where I need to do this and just haven't gotten it yet.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I don't think you can create a "dependency" for a separate form. You could create a form based on the visit table (it would show all visits if opened from the database window). Then on your first form, use the wherecondition argument of Docmd.OpenForm to restrict the second form to the visits for the patient on the first form.

    I suppose another thought is that the second form could be based on a query that used the first form as a criteria. It's less flexible, but perhaps what you're after.
    Paul

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    That sounds about right, I think I just used the wrong wording.

    to try and illistrate my example better...
    I open the DB and have it set up so that the Patient form loads automatically to show all patients. I click through until I find the one I want and click, "show visits". I want to show only the visits of the patient that is currently displayed on the patient form.
    So I am thinking that the visit form is "dependent" on the patient form because it can only show data based on what is showing onthe patient form. It's just like a subform but it it's a seperate form, not embedded.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, with a subform you can set the master/child links. I can't think of a similar setting for a separate form, so I think you'll have to use one of the methods I described.
    Paul

  5. #5
    Join Date
    Feb 2007
    Posts
    348
    Thanks Paul, I'll study up on the where condition/Docmd.Openform thing. :-)

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    I hope this isn't considered off topic but I've been working on that whole doCmd.OpenForm Where clause and it keeps opening to all records at tis point. I was hoping you could offer some correction.

    Code:
    Private Sub IssueBtn_Click()
    Dim IssueID As Integer
    IssueID = Me!IssueID
    MsgBox IssueID, vbOKOnly, "This is your current IssueID"
    DoCmd.OpenForm "TestUpdate", , , Me!IssueID = IssueID
    End Sub
    I probably have "IssueID" in there too many times to make sense but basically, for this test that I am working in, IssueID is an autonumber assigned to each issue, they can have many updates so that appears on the update table as well. Last I created a variable called IssueID that I was using to pass it because trying to do it without a variable kept throwing me errors. Any idea why I keep getting all records?

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    DoCmd.OpenForm "TestUpdate", , , "IssueID = " & IssueID
    Paul

  8. #8
    Join Date
    Feb 2007
    Posts
    348
    that did it :-)
    awesome! thanks
    now to use that 10,000 on other projects.

  9. #9
    Join Date
    Feb 2007
    Posts
    348
    I'm a little confused. When I use your code on anything I built, it works, example:

    Code:
    Private Sub IssueBtn_Click()
    Dim IssueID As Integer
    IssueID = Me!IssueID
    MsgBox IssueID, vbOKOnly, "This is your current IssueID"
    DoCmd.OpenForm "TestUpdate", , , "IssueID = " & IssueID
    End Sub
    I have a bunch of Access databases I have been asked to clean up, all of which were built by one guy who pretty much used wizards to do everything. When I use the same format the form pops up but shows all records and what is more, it over rights the field I am using as the key from the parent, on the first record only though. So if i open from record 7 with ID 5678, the ID on the first record of the new form is 5678. If I close the form then got to record 9 with ID 7890, then open the form. The same records opens (all the data is the same) except the ID field on that now says 7890 and when I look in the table it has now changed.

    Does this sound at all familiar?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post a sample db?
    Paul

  11. #11
    Join Date
    Feb 2007
    Posts
    348
    I'll be happy to, it'll be tomorrow before I do though.

  12. #12
    Join Date
    Feb 2007
    Posts
    348
    Let me know if it doesn't come through. Attached is the database I was given to clean up. There is no patient or sensitive information within. Feel free to add/modify/delete away.
    Start with the form named Demographics, there are four buttons at the bottom. We would like these to open to records that only relate to the patient currently on the demographics page. I have tried a variety of thing including the Filter method. It either doesn't work at all or it opens to all records but the patient ID of the first record gets overwritten with the patient ID currently showing on demographics.

    I will warn you right now. There is one form (and thus table) that is opposite to normalization, I am aware of this and have made them aware. That will also be a part of the cleanup.

    EDIT: thanks for looking at this.
    Attached Files Attached Files

  13. #13
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This is basically what we had earlier as a working wherecondition:

    "IssueID = " & Me.IssueID

    This is what you've got now:

    Me!ID

    See the difference?
    Paul

  14. #14
    Join Date
    Feb 2007
    Posts
    348
    oh, dang it. Sorry
    You totally already explained that.
    Thanks again, I apologize.

Posting Permissions

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