Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    21

    Unanswered: Updating recordset/filter for table based on query

    I have two tables:

    tblPatientID - includes Patient ID and Name, ID is Primary Key
    tblPatientInformation - includes Patient ID and a bunch of other information about the Patient, ID is Primary Key

    These tables are related on ID

    I have a query (qryPatientMaster) which is simply:
    SELECT tblPatientID.*, tblPatientInformation.*
    FROM tblPatientID INNER JOIN tblPatientInformation ON tblPatientID.IDSUBJECT = tblPatientInformation.IDSUBJECT;

    I want users to be able to select a Patient to edit, or if the patient they are looking for isn't found, to add a new record (to both tables).

    I have a form (frmPatientInformation). The recordsource is currently set to qryPatientMaster. At the top of the form is an unbound combo box (cboSelect) that displays Patient names and IDs from tblPatientID - the bound column is the ID.

    The combo box has an AfterUpdate event that updates the filter to display data for the currently selected record:
    DoCmd.ApplyFilter , "[tblPatientID.IDSUBJECT] = " & Me.cboSelect

    So far, so good.

    Also on this form, I have a command button labeled "Add New Patient" - this open a form (recordsource=tblPatientID) that allows the user to enter the ID and name for the new patient. This form has a command button with a Click event that adds a new record to tblPatientID:

    Private Sub cmdAdd_Click()
    ' Open recordset and move to last record
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Select * from tblPatientInformation", dbOpenDynaset)
    If rs.EOF And rs.BOF Then
    MsgBox "File is Empty"
    Else
    rs.MoveLast
    End If
    ' Add a new record and update the table
    rs.AddNew
    rs("IDSUBJECT") = Me.txtIdSubject.Value
    rs.Update

    DoCmd.Close acForm, "frmNewPatient"

    End Sub

    And, finally, here is my problem. At this point, I want to go back to my original form (frmPatientInformation) with a filter set to only display the new record (i.e., the one we just added via frmNewPatient). I feel certain this should be easy, but I've tried doing this a bunch of different ways and can't seem to get it to work.

    Thanks in advance.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    It sounds like you're trying to get a query to return a record that doesn't exist yet...

    If you try changing
    Code:
    FROM tblPatientID INNER JOIN tblPatientInformation ON tblPatientID.IDSUBJECT = tblPatientInformation.IDSUBJECT;
    into
    Code:
    FROM tblPatientID LEFT JOIN tblPatientInformation ON tblPatientID.IDSUBJECT = tblPatientInformation.IDSUBJECT;
    you might be able to select the new patient ID. Also, your code to add the new patient ID doesn't add the new name - is that right?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jun 2009
    Posts
    21
    Quote Originally Posted by weejas
    It sounds like you're trying to get a query to return a record that doesn't exist yet...
    Yup... I think you're right. I am new to this and wasn't "getting" that the new name/ID data weren't being written out to tblPatientID until I closed the form.

    I added a Save, and now it seems to work... *if* I change the data source to a table, rather than the query.

    (and yeah... the code is only writing out to tblPatientInformation which does not include the name -- the form where the user enters the new ID and name is bound to tblPatientID, so it is writing out the new record once it is saved)

    I am sure I am doing this in a convoluted way... but, for now it is working.

    Thanks so much for taking the time to reply.

Posting Permissions

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