Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2006
    Posts
    52

    Unanswered: Can someone tell me what's wrong with my relationship

    Hi
    I am facing a problem in a previous file, so I started over again with a new clean database hoping I'll solve my problem, but still

    The problem I face is in a form called ENT, which is based on query, it has some fields coming from a table called Bill (it has BillID, DoctorID, PatientID),
    DoctorID is linked to a primary key in table Doctor
    Patient ID is linked to primary key in table Patient
    Now, in the table itself, when I enter values for DoctorID or PatientID that do not exist in the original table, it does not accept non existing values, which is good, so I can't enter a DoctorID in the Bill table if that DoctorID does not exist in Table Doctor

    BUT, in the form, it does enter non existing values in DoctorID or Patient ID, so whenever I open table Bill, i see new values are entered!

    I learned how to ban this using code, Thanks to great members in this forum, but, access should not allow this to happen otherwise I think there should be a problem with relationship? and why it enforces referential integrity in tables but not in forms though the form is built on either query or table!

    I uploaded my test file, can someone please see what is the problem...or is this normal?????

    Thanks again in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I tested your database and when I try to create a new bill using the form ENT, if I enter a non existing value for PatientID (or DoctorID or both), Access displays a message saying that a record with a matching PatientID (or DoctorID) key cannot be found in the table Patient (or Doctor).

    The referential integrity seems to work as expected. Do I misunderstand something in your message?
    Attached Thumbnails Attached Thumbnails ScreenShot001.jpg  
    Have a nice day!

  3. #3
    Join Date
    Aug 2006
    Posts
    52
    sinnhdo
    you did not misunderstand me
    yesterday referential integrity was not working,
    and now it is working even with me!!!
    i do not know what is happening
    i remember yesterday i entered new values and it was saved to the original table..even though relationship was set previously
    but it works fine now,, i'll go crazy
    Thanks for checking my database anyway

  4. #4
    Join Date
    Aug 2006
    Posts
    52
    Oops, I checked it again, and now i remember what was the problem
    when I enter a new name in form ENT that does not exist in table Patient it enters a new record in table Patient, what is the correct way to set a form like this, I want to see the name as well not only PatientID

  5. #5
    Join Date
    Aug 2006
    Posts
    52
    any suggestion to solve the problem?
    is this the only solution?
    http://www.dbforums.com/microsoft-ac...ext-field.html
    Last edited by oakeyes; 11-02-11 at 06:45.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The problem arises because both tables Doctor and Patient are included in the query ENT which is the RecordSource of the form. Because of the way the relationships are defined, both tables are updatable from the query (their Identity columns (primary keys) are known or can be determined). Notice that this problem also occurs with the Major control, for the same reasons.

    There are several non-mutually exclusive solutions.

    a) At the database level:
    --------------------
    For each table (Doctor and Patient), add a Not Null constraint on a column that is not part of the query, e.g. for the table Doctor, the column Sex now has the following properties:
    Required: Yes
    Allow Zero Length: No
    while for the column DOB in the table Patient:
    Required: Yes

    As these columns are not present in the query, the creation of a new row in one of the tables will fail if a value is not supplied for a required column, and the form cannot supply such a value, nor does the query that is used as its RecordSource.

    If you now try to enter a name that does not exist for DoctorName or PatientName, this will cause an error (code 3314) to be raised when the form will attempt to save the new record. You can intercept and handle this error in the Error event handler of the form:
    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
        Stop
        If DataErr = 3314 Then
            Response = acDataErrContinue
            MsgBox "You cannot create a new patient or a new doctor from this form.", vbExclamation, "Creation forbidden"
        End If
        
    End Sub
    b) At the form level:
    -------------------

    1. The most simple solution would consist in locking the three controls (PatientName, DoctorName and Major). If the controls are locked, you cannot type anything into them so new rows cannot be created into their related tables.

    2. You can also unbind the controls and have their values set on the Current event of the form:
    Code:
    Private Sub Form_Current()
    
        Dim i As Long
        Dim strName As String
        
        With Me.Controls
            For i = 1 To 3
                strName = Choose(i, "PatientName", "DoctorName", "Major")
                .Item(strName).Value = IIf(Me.NewRecord = True, Null, Me.Recordset.Fields(strName).Value)
            Next i
        End With
        
    End Sub
    3. This is the most usual solution: The controls PatientID and DoctorID are removed (they will be replaced by 2 comboboxes) while PatientName, DoctorName and Major remain unbound and we keep the Sub Form_Current defined above. Now create a combobox that will fetch its rows from the table Doctor (to replace DoctorID) and one that will fetch its rows from the table Patients (to replace PatientID), like this:

    Note: Unspecified properties keep their default values.

    a) Combo_Patient:
    =============
    Format Tab:
    -----------
    Column Count: 2
    Column Width: 0,5cm;2,5cm (here in centimetres, can be in inches, adapt to your needs).
    Width: 1.8cm (see above)

    Data Tab:
    ---------
    Control Source: PatientID
    Row Source:
    Code:
    SELECT Patient.PatientID, Patient.PatientName FROM Patient;
    Bound Column: 1
    Limit To List: Yes

    Event Tab:
    ----------
    After Update: [Event Procedure]

    Other Tab:
    ---------
    Name: Combo_Patient

    b) Combo_Doctor:
    =============
    Format Tab:
    -----------
    Column Count: 2
    Column Width: 0,5cm;1,25cm;1,25cm (here in centimetres, can be in inches, adapt to your needs).
    Width: 1.8cm (see above)

    Data Tab:
    ---------
    Control Source: Bill.DoctorID
    Row Source:
    Code:
    SELECT Doctor.DoctorID, Doctor.DoctorName, Doctor.Major FROM Doctor;
    Bound Column: 1
    Limit To List: Yes

    Event Tab:
    ----------
    After Update: [Event Procedure]

    Other Tab:
    ---------
    Name: Combo_Doctor

    In the form module, the event handlers for both combos are (keep the event handler for the Form_Current event that was defined in the preceding example):
    Code:
    Private Sub Combo_Doctor_AfterUpdate()
    
        Me.Controls("DoctorName").Value = Me.Combo_Doctor.Column(1)
        Me.Controls("Major").Value = Me.Combo_Doctor.Column(2)
        
    End Sub
    
    Private Sub Combo_Patient_AfterUpdate()
    
        Me.Controls("PatientName").Value = Me.Combo_Patient.Column(1)
        
    End Sub
    Notice that, going a tad further, it would be possible to remove the tables Doctor and Patient from the query ENT, and also that the comboboxes should display the names, not the IDs, because primary keys should never be exposed to the users.

    Finally, the code in the form module would be easier to handle if the names of the controls was different from the names of the fields to which they are bound. For instance, we could then write without any ambiguity:
    Code:
    Me.Text_DoctorName.Value = Me.Combo_Doctor.Column(1)
    While now:
    Code:
    Me.DoctorName.Value
    can be a reference to a control, to a field, or both.
    Have a nice day!

  7. #7
    Join Date
    Aug 2006
    Posts
    52
    Sinndho
    I am really so happy with your detailed reply...how can I thank you??
    I read everything and it seems fairly easy but still didn't apply it to my file, I'll try that as soon as possible, I hope I will find my way in applying everything you wrote,
    Just two questions:
    when you say form module, do you mean to create a Module? or just write the code in vb window?
    Last question, do you recommend any good book for me to learn about Access relation ships and forms with examples?I searched amazon but a recommendation from an experienced user would be highly appreciated.

    Thanks zillions for your help, and sorry for consuming much of your time.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome and I'm glad I could help.

    1. By module, I mean the class module that can be automatically associated with any form or report in Access. The module that is open when a form is in design view and you use the ALT+F11 key combination.

    2. I'm actually not able to recommend any book. I learned working with Access from scratch, though I already had a heavy background and experience in databases management and in programming languages in general. That was almost 20 years ago, and at that time MS products such as Access came with a full set of books. I went on from there, attending many presentations and conventions that were organized at each new release of the product.

    In any case, you should not limit yourself to Access: the relational model on which Access is based can be approached in a more general manner. Type "relational database" or "relational model" in a search engine (google, yahoo, bing...) and you'll find many valuable references. Microsoft's own technical site (MSDN) is a true goldmine too.
    Have a nice day!

  9. #9
    Join Date
    Aug 2006
    Posts
    52
    Sinnhdo, thanks again for answering my questions I'll follow your advice

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  11. #11
    Join Date
    Aug 2006
    Posts
    52
    Sinnhdo, I have been working on this part and I have some questions if you do not mind, and of course if you have spare time!

    First of all, I had an issue with the form built on the ENTQuery: when I enter existing values I get current( field must match the join key) msgbox 2-3 times and then I am able to enter values, after searching I knew that it happens because data entry is based on Query

    SO, I deleted the query and built my form based on 3 tables tblBill (BillID, DoctorID, PatientID, etc) tblDoctor(doctorID, DoctorName, Major), tblPatient(PatientID, PatientName), it is structured as form (with only PatientName) and subform with all the rest of fields, the attached picture (form View) explains

    Then I Deleted the control DoctorID, and used a new combobox called Combo_Doctor to let me select DoctorName and Major, I kept txt_DoctorName temporarily, I'll delete it after I make sure cmbo_Doctor is working properly

    Now, I have some issues related to this combo_Doctor: the Values displayed show DoctorID and Name, while I want it to show DoctorName and Major. I do not want to see the Doctor ID, I used this code you gave me but still I can see Doctor ID.

    this is the code

    Private Sub combo_doctor_AfterUpdate()
    Me.Controls("txt_DoctorName").Value = Me.combo_doctor.Column(1)
    Me.Controls("txt_Major").Value = Me.combo_doctor.Column(2)


    End Sub

    These are the properties of Combo_doctor
    Control Source: tblBill.DoctorID
    Row Source : SELECT tblDoctor.DoctorID, tblDoctor.DoctorName, tblDoctor.Major, FROM tblDoctor; 'i tried to remove tblDoctor.DoctorID hoping it won't show doctor id but didn't work

    BTW: I get exclamation mark in design view on combo_doctor, it is shown in the attached picture (design view)

    by the way, I get debug error msg when I use this code in form_current, don't know why
    Private Sub Form_Current()



    ' Dim i As Long
    ' Dim strName As String
    '
    ' With Me.Controls
    ' For i = 1 To 2
    'i get this error even when I use 1 to 3 and add txt_doctorID to the strName
    ' strName = Choose(i, "txt_DoctorName", "txt_Major")
    ' .Item(strName).Value = IIf(Me.NewRecord = True, Null, Me.Recordset.Fields(strName).Value)
    ' Next i
    ' End With

    End Sub


    I did many things, I don't even remember what I did but still can't get it to NOT show doctorID

    & Last Question, When I select a value in the combo_doctor, and then another value in the same combo, it does not work and beeps, so i have to select anything else from any other control and then go back to combo_doctor....is this normal to the after update even? or there is something wrong with my work?

    I tried to solve it my self but couldn't
    I hope that won't take much time from you..
    Thanks in advance
    Attached Thumbnails Attached Thumbnails designview.GIF   FormView.GIF  

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by oakeyes View Post
    Now, I have some issues related to this combo_Doctor: the Values displayed show DoctorID and Name, while I want it to show DoctorName and Major. I do not want to see the Doctor ID, I used this code you gave me but still I can see Doctor ID.

    this is the code

    Private Sub combo_doctor_AfterUpdate()
    Me.Controls("txt_DoctorName").Value = Me.combo_doctor.Column(1)
    Me.Controls("txt_Major").Value = Me.combo_doctor.Column(2)
    End Sub
    Keep the RowSource property as it is (remove the comma after Major: ... tblDoctor.Major, FROM...), so:
    Code:
    SELECT tblDoctor.DoctorID, tblDoctor.DoctorName, tblDoctor.Major FROM tblDoctor;
    but in the Format tab, change some properties, like this:
    1. ColumnCount: 3
    2. ColumnWidth: 0cm;3cm;3cm (can be in inches in your case, adapt the width to your needs but keep zero for the first column).

    Quote Originally Posted by oakeyes View Post
    by the way, I get debug error msg when I use this code in form_current, don't know why
    Private Sub Form_Current()

    ' Dim i As Long
    ' Dim strName As String
    '
    ' With Me.Controls
    ' For i = 1 To 2
    'i get this error even when I use 1 to 3 and add txt_doctorID to the strName
    ' strName = Choose(i, "txt_DoctorName", "txt_Major")
    ' .Item(strName).Value = IIf(Me.NewRecord = True, Null, Me.Recordset.Fields(strName).Value)
    ' Next i
    ' End With

    End Sub
    The fields in the Recordset are named "DoctorID", "DoctorName" and "Major", without the "Text_" prefix that is used for their corresponding controls, so:
    Code:
    Me.Recordset.Fields(strName).Value
    is incorrect as strName goes from "txt_DoctorName" (or perhaps "txt_DoctorID") to "txt_Major". Remember here that the names of the fields in a RecordSet are those of the columns of the table(s) on which the RecordSet is based (except if you use aliasses in a query). It should be:
    Code:
    strName = Choose(i, "DoctorName", "Major")
    .Item("txt_" & strName).Value = IIf(Me.NewRecord = True, Null, Me.Recordset.Fields(strName).Value)
    By the way, also keep in mind that the first column of a Combo or List box is Column(0) (the same is true if you reference the fields of a RecordSet by their numeral position).

    You could simplify the code by using:
    Code:
    If Me.NewRecord = True Then
        Me.txt_DoctorName.Value = Null
        Me.txt_Major.Value = Null
    Else
        Me.txt_DoctorName.Value = Me.Recordset.Fields(DoctorName).Value
        Me.txt_Major.Value = Me.Recordset.Fields(Major).Value
    End If
    Or even:
    Code:
    If Me.NewRecord = True Then
        Me.txt_DoctorName.Value = Null
        Me.txt_Major.Value = Null
    Else
        Me.txt_DoctorName.Value = Me!DoctorName
        Me.txt_Major.Value = Me!Major
    End If
    Quote Originally Posted by oakeyes View Post
    & Last Question, When I select a value in the combo_doctor, and then another value in the same combo, it does not work and beeps, so i have to select anything else from any other control and then go back to combo_doctor....is this normal to the after update even? or there is something wrong with my work?
    No it's not normal. Let's first try to have the code working properly and see if this will not solve the problem you report. If not, we'll come back to it.
    Have a nice day!

  13. #13
    Join Date
    Aug 2006
    Posts
    52
    Thanks a lot, now i understand the trick of 0 column width to hide first column



    Quote Originally Posted by Sinndho View Post

    Code:
    strName = Choose(i, "DoctorName", "Major")
    .Item("txt_" & strName).Value = IIf(Me.NewRecord = True, Null, Me.Recordset.Fields(strName).Value)
    I could run the form view without debugging errors, however, whenever I select any value in the combo_doctor the computer beeps, not allowing me to accept selected value even for the first time.

    tried this alternative but takes me to debug mode
    Quote Originally Posted by Sinndho View Post
    Code:
    If Me.NewRecord = True Then
        Me.txt_DoctorName.Value = Null
        Me.txt_Major.Value = Null
    Else
        Me.txt_DoctorName.Value = Me.Recordset.Fields(DoctorName).Value
        Me.txt_Major.Value = Me.Recordset.Fields(Major).Value
    End If
    this alternative also enables me to run the form, but beeps whenever I select a value
    Quote Originally Posted by Sinndho View Post
    Code:
    If Me.NewRecord = True Then
        Me.txt_DoctorName.Value = Null
        Me.txt_Major.Value = Null
    Else
        Me.txt_DoctorName.Value = Me!DoctorName
        Me.txt_Major.Value = Me!Major
    End If
    when I remove any of the codes above I am able to select values in the combo_doctor and it populates correctly in the Major and DoctorName fields, however, I still cant select 2 times consecutively

    Do you think there is something wrong in the options of the controls txt_major & txt_DoctorName? I tried to unlock & lock them but still facing the same issue. & Yes I also deleted the comma in select query..

    and by the way, um still having that exclamation mark where it says (invalid control property: control source), is it because I put this combo in the subform? coz I have a main form with PatientName and subform with all the rest of data including this combo?, I know silly question, but just wondering

    Thanks again for your help

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to know where each control is situated any more (main form or subform). Could you please precise the current organisation on the interface?

    As a general guideline:

    1. From the parent (main) form, you reference a control on a subform by using:
    Code:
    Me.<SubFormControlName>.Form.<ControlName>
    Where:

    a) <SubFormControlName> is the name of the control of type subform/subreport.

    b) <ControlName> is the name of the control in the subform.

    Note: Be aware that the name <SubFormControlName> can be different from the name of the subform it contains. It's the name of a control not the name of a form.


    2. To reference a control on the parent form from a subform, you use:
    Code:
    Me.Parent.<ControlName>
    Where: <ControlName> is the name of the control in the parent form.

    Note: Me.Parent is invariant.


    3. To reference a control on a subform from another subform, you use:
    Code:
    Me.Parent.<SubFormControlName>.Form.<ControlName>
    With <SubFormControlName> and <ControlName> as in 1.

    You can test whether a form has a parent (i.e. is a subform) or not, using:
    Code:
    Private Function HaveParent() As Boolean
    
        Dim frm As Form
        
        HaveParent = True
        For Each frm In Application.Forms
            If frm.Name = Me.Name Then
                HaveParent = False
                Exit For
            End If
        Next
    
    End Function
    The Forms collection contains a reference (a Form object) for each form open as a main form. This works because when a form is open as a subform, it is not added to the Forms collection of Access.
    Have a nice day!

  15. #15
    Join Date
    Aug 2006
    Posts
    52
    Sinnhdo, thanks for the new information
    Actually, All controls I was working on are on the subform, i was just wondering whether i need to put some of controls on the main?

    I have uploaded the file, if you do not mind, can you please have a quick look and pinpoint what's wrong? i just want to know where and what is the problem coz I'm totally puzzled at this point
    Thanks in advance
    Attached Files Attached Files

Posting Permissions

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