Relationship Analysis

[Alexieoo]

Okay thanks, I was thinking the same thing about the many-to-many relationship but obviously wasn't sure. Does the junction table provide more security to my database in terms of preventing bugs and/or errors?



Also I was wondering what the message means in the screenshot I posted. I am interpreting that if I choose the first option then Access will also record the information back to my tables as I enter it in the form. And the second option just means to keep it in the form?

[June7]

I seldom use the wizard to set up combobox so I never thought much about those options. But I think the opposite is the case - first is for an UNBOUND control that is used to input filter criteria, second is to save the value to a field.

[Alexieoo]

Okay thanks. And sorry, is there more security in my db design if I use a junction table for patients and procedures? Have you ever tested or compared the two options?

[June7]

I NEVER use multi-value fields because they complicate data manipulation (i.e, building queries) and other issues.

What security do you mean - data integrity? This can be managed as well with normal tables.

[Alexieoo]

I see, by security I was thinking of any possibilities such as your example, complicating data manipulation in query building, which is the last thing I want. I am trying to make this DB as userfriendly and error free as possible. And I thank you very much for that.

[June7]

Multi-value fields are user-friendly but pose issues to the developer.

Users should not work directly with tables and queries. If they do, then they become developer.

[Alexieoo]

Multi-value fields are user-friendly but pose issues to the developer.

Users should not work directly with tables and queries. If they do, then they become developer.

Okay but what if I input the records , in tables to create queries that Access can ask the user . For example: a query that will display all the days that a particular procedure was performed in a certain time period ? Or would I have to create a report. Because I know for a fact that this will be needed. Clinicians will need to be able to view how many times a certain procedure was performed in a given time frame

[June7]

Review http://www.allenbrowne.com/ser-62.html

Apply filter to form or to report.

[Alexieoo]

Review http://www.allenbrowne.com/ser-62.html

Apply filter to form or to report.

Hey thank you for the great reference to go off of. I finally got back in touch with my laptop... Anyways whilst creating the filter I endure this message when I create a label . (Refer to screenshot)

[June7]

You mean the 'not associated with a control'. If you don't want to associate the label with a data control, ignore the message.

[Alexieoo]

Okay thank you.

[Alexieoo]

Hey so I have created the filter and modified the code to my design. Yet when I enter data into the filter nothing comes up, I have entered fake records to test it, but does not work. I was wondering if you'd be able to help? Here is the code:

Private Sub cmdFilter_Click()


Dim strWhere As String


Dim lngLen As Long


ConstJetDate = "\#mm\/dd\/yyyy#"


If Not IsNull(Me.txtFilterProcedureName) Then
strWhere = strWhere & "([lblProcedureName] Like ""*" & Me.txtFilterProcedureName & "*"") AND "

End If


If Not IsNull(Me.TxtDateFrom) Then
strWhere = strWhere & "([lblDate] >= " & Format(Me.TxtDateFrom, conJetDate) & ") AND "
End If


If Not IsNull(Me.txtDateTo) Then
strWhere = strWhere & "([lblDate] < " & Format(Me.txtDateTo + 1, conJetDate) & ") AND "
End If


lngLen = Len(strWhere)
If longLen <= 0 Then
MsgBox "No Criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub


Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox
ctl.Value = Null
End Select
Next
Me.FilterOn = False
End Sub






Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = True
MsgBox "You cannt add new procedures to the search form.", vbInformation, "Permission denied."
End Sub






Private Sub Form_Open(Cancel As Integer)
Me.Filter = "(False)"
Me.FilterOn = True
End Sub

[June7]

Please post lengthy code within CODE tags to retain indentation and make it more readable. I can't see anything wrong with it.

Need to debug. Step debugging is a valuable technique for analyzing code. Review link at bottom of post.

[ssanfu]

In the sub "cmdFilter_Click()", you are missing a space between the key word "Const" and the constant name, the constant name is missing the prefix "con" and a backslash is missing before the last hash:

ConstJetDate = "\#mm\/dd\/yyyy#"

should be

Const conJetDate = "\#mm\/dd\/yyyy\#"

You should have these two lines at the top of EVERY code module:

Option Database
Option Explicit

You have this code snippet: (two different variable names)

 lngLen = Len(strWhere)
 If longLen <= 0 Then

and his line should be

 lngLen = Len(strWhere) - 5

You need to remove the last 5 characters of the string: a leading space, the word 'AND' and a trailing space.


Don't understand what you are trying to do in the sub "Form_Open".
What is the purpose of this line?

  Me.Filter = "(False)"

To remove the filter, you would use:

Private Sub Form_Open(Cancel As Integer)
  Me.Filter = ""
  Me.FilterOn = FALSE
End Sub

[Alexieoo]

Greetings,

Thank you so much for your helpful feedback. The purpose of the lines:
Me.Filter = "(False)"
Me.FilterOn = True

Is to initially show no records upon opening the form