Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10

    Unanswered: Selecting record for query criteria

    I'm using Access 2000. I have developed a db for patient medical records that I use in consulting. One form is a continuous subform which identifies the different medications the patient is taking. I am trying to set up a pop-up dialog box which will display prescibing information for the particular medication when the "drug name" field on that record is clicked. I have tried setting an OnClick macro which opens a form containing the info. The problem I am having is selecting the criteria in the query to indicate the drug selected (the one which is clicked). I have tried building an event to set the criteria as an expression "Forms![Pt/med relationship subform query subform]![drug name]", but I guess this won't work because it is a continuous form and the expression is ambiguous. Is there any way to specify the field which has the focus?
    Thanks for any help offered.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Something to remember about continuous forms: With the current record selected, the names of the controls (textbox, combobox, etc ...) in the detail section are your window into that particular record... So you merely reference the info thru those control names.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Thanks for the reply. You confirmed what I thought. Oh well, back to the drawing board.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    You can open a form from a subform using a button or control Click or DblClickble event to display information. To do this information relating to the opening forms recordset primary key(s) is available in the subform and/or the parent form.


    For example

    Code:
    Private Sub Comments_DblClick(Cancel As Integer)
    
    On Error GoTo Err_Comments_Click
    
        Dim stDocName As String
        Dim stLinkCriteria As String
        
        If IsNull(CHPID) Or IsNull(ReadingDate) Then Exit Sub
        
        stLinkCriteria = "CHPID = " & CHPID & " AND [ReadingDate] = #" & Format(ReadingDate, "mm/dd/yy") & "#"
        
        stDocName = "Comments"
        
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    Exit_Comments_Click:
        Exit Sub
    
    Err_Comments_Click:
        MsgBox Err.Description
        Resume Exit_Comments_Click
        
    End Sub
    The above code opens a form (Comments) showing the comments (in a larger/editable window) from the double click event of the Comments control on a subform.

    The form ‘Comments’ is based on a table having a combined primary key of CHPID and ReadingDate. The link criteria for both fields is obtained from field in the subform.

    If some info is only available in the parent form, for instance, say the CHPID in this case, then the code would be

    stLinkCriteria = "CHPID = " & Me.Parent.lstCHPID & " AND [ReadingDate] = #" & Format(ReadingDate, "mm/dd/yy") & "#"

    where lstCHPID is the list control on the parent form.


    [Not this is based on UK date format]

    Hope this gives you some ideas

    MTB

  5. #5
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Thanks, Mike, I'll give it a try.

  6. #6
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Thanks, Mike, I'll give it a try.

  7. #7
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Thanks, I'll give it a try.

  8. #8
    Join Date
    Feb 2004
    Location
    Iowa
    Posts
    10
    Sorry for the redundancy. My browser appears to be having a meltdown/

Posting Permissions

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