Change record source button

[Debbiedo]

Hi,

I need to add a button that references a separate record source and
then displays the results in the details section.

To explain, the user(s) links to a table containing a nightly download
of data (view only). They need to compare this data (stored in the
FormsDataTest table) to their own table (CIT Table) using the
Student_ID to determine which students are not included in their own
table (CIT Table). These results need to be displayed in the details
section. The user(s) then clicks on the record's "edit record" button
and a data entry form (CIT Form) is opened up. This form contains a
main form (FormsDataTest data) and an associated subform (CIT Table
data) linked by "Student_ID". The user(s) populates the CIT Table with
the info they need to document and closes the form. Now the tables are
synched. The user(s) then can then use the search feature described
below to find a specific student. These results are also displayed in
the details section. We have over 65,000 students thus this data is
extremely dynamic.

So how can I convert the following code, which opens up in a sepatate
table window, to instead display in the details section when clicked?
The records displayed using the search code (cmdFilter) will need to
be cleared and replaced by the cmdNotNotified results and vice versa
depending on whether the user(s) selects the Search button or the Not
Notified button.

I hope this makes sense. Thanks again.

-------------Code to Convert---------------------

Private Sub cmdNotNotified_Click()
On Error GoTo Err_cmdNotNotified_Click

Dim stDocName As String

stDocName = "Not Notified Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdNotNotified_Click:
Exit Sub

Err_cmdNotNotified_Click:
MsgBox Err.Description
Resume Exit_cmdNotNotified_Click


End Sub

--------Search Filter Code for reference only. This works
great!----------

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long


'Build Student ID criteria expresion
If Not IsNull(Me.txtFilterStudentID) Then
strWhere = strWhere & "([Student_ID] = """ &
Me.txtFilterStudentID & """) AND "
End If


'Build Last Name criteria expresion
If Not IsNull(Me.txtFilterLastName) Then
strWhere = strWhere & "([Last_Name] Like ""*" &
Me.txtFilterLastName & "*"") AND "
End If


'Build First Name criteria expresion
If Not IsNull(Me.txtFilterFirstName) Then
strWhere = strWhere & "([First_Name] Like ""*" &
Me.txtFilterFirstName & "*"") AND "
End If


' Build School Name criteria expression
If Len(Me.cboFilterSchool.Value & "") > 0 Then
strWhere = strWhere & "School_Name='" &
Me.cboFilterSchool.Value & "' And "
End If


' ... build "Approval" criteria expression
Select Case Me.frameApproval.Value
Case 1 ' Approved
strWhere = strWhere & "SPNDSBUS='X' And "
Case 2 ' Not Approved
strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And "
Case 3 ' Both
strWhere = strWhere & "([SPNDSBUS]OR(Len(SPNDSBUS & '') = 0))
And "
End Select


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing here"
Else
strWhere = Left$(strWhere, lngLen)


Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Jeff Boyce]

It sounds like you're saying that you have (potentially) new data for
(potentially) some records and you're having a human interpret whether the
information is new and then manually re-enter the data to synchronize the
two datasets.

If so, why? What "added value" does the human touch provide?

I ask because it might be possible to have Access do the updating instead.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need to add a button that references a separate record source and
then displays the results in the details section.

To explain, the user(s) links to a table containing a nightly download
of data (view only). They need to compare this data (stored in the
FormsDataTest table) to their own table (CIT Table) using the
Student_ID to determine which students are not included in their own
table (CIT Table). These results need to be displayed in the details
section. The user(s) then clicks on the record's "edit record" button
and a data entry form (CIT Form) is opened up. This form contains a
main form (FormsDataTest data) and an associated subform (CIT Table
data) linked by "Student_ID". The user(s) populates the CIT Table with
the info they need to document and closes the form. Now the tables are
synched. The user(s) then can then use the search feature described
below to find a specific student. These results are also displayed in
the details section. We have over 65,000 students thus this data is
extremely dynamic.

So how can I convert the following code, which opens up in a sepatate
table window, to instead display in the details section when clicked?
The records displayed using the search code (cmdFilter) will need to
be cleared and replaced by the cmdNotNotified results and vice versa
depending on whether the user(s) selects the Search button or the Not
Notified button.

I hope this makes sense. Thanks again.

-------------Code to Convert---------------------

Private Sub cmdNotNotified_Click()
On Error GoTo Err_cmdNotNotified_Click

Dim stDocName As String

stDocName = "Not Notified Query"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdNotNotified_Click:
Exit Sub

Err_cmdNotNotified_Click:
MsgBox Err.Description
Resume Exit_cmdNotNotified_Click


End Sub

--------Search Filter Code for reference only. This works
great!----------

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long


'Build Student ID criteria expresion
If Not IsNull(Me.txtFilterStudentID) Then
strWhere = strWhere & "([Student_ID] = """ &
Me.txtFilterStudentID & """) AND "
End If


'Build Last Name criteria expresion
If Not IsNull(Me.txtFilterLastName) Then
strWhere = strWhere & "([Last_Name] Like ""*" &
Me.txtFilterLastName & "*"") AND "
End If


'Build First Name criteria expresion
If Not IsNull(Me.txtFilterFirstName) Then
strWhere = strWhere & "([First_Name] Like ""*" &
Me.txtFilterFirstName & "*"") AND "
End If


' Build School Name criteria expression
If Len(Me.cboFilterSchool.Value & "") > 0 Then
strWhere = strWhere & "School_Name='" &
Me.cboFilterSchool.Value & "' And "
End If


' ... build "Approval" criteria expression
Select Case Me.frameApproval.Value
Case 1 ' Approved
strWhere = strWhere & "SPNDSBUS='X' And "
Case 2 ' Not Approved
strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And "
Case 3 ' Both
strWhere = strWhere & "([SPNDSBUS]OR(Len(SPNDSBUS & '') = 0))
And "
End Select


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing here"
Else
strWhere = Left$(strWhere, lngLen)


Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Debbiedo]

It sounds like you're saying that you have (potentially) new data for
(potentially) some records and you're having a human interpret whether the
information is new and then manually re-enter the data to synchronize the
two datasets.

If so, why?  What "added value" does the human touch provide?

I ask because it might be possible to have Access do the updating instead..

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I need to add a button that references a separate record source and
then displays the results in the details section.

To explain, the user(s) links to a table containing a nightly download
of data (view only). They need to compare this data (stored in the
FormsDataTest table) to their own table (CIT Table) using the
Student_ID to determine which students are not included in their own
table (CIT Table). These results need to be displayed in the details
section. The user(s) then clicks on the record's "edit record" button
and a data entry form (CIT Form) is opened up. This form contains a
main form (FormsDataTest data) and an associated subform (CIT Table
data) linked by "Student_ID". The user(s) populates the CIT Table with
the info they need to document and closes the form. Now the tables are
synched. The user(s) then can then use the search feature described
below to find a specific student. These results are also displayed in
the details section. We have over 65,000 students thus this data is
extremely dynamic.

So how can I convert the following code, which opens up in a sepatate
table window, to instead display in the details section when clicked?
The records displayed using the search code (cmdFilter) will need to
be cleared and replaced by the cmdNotNotified results and vice versa
depending on whether the user(s) selects the Search button or the Not
Notified button.

I hope this makes sense. Thanks again.

-------------Code to Convert---------------------

Private Sub cmdNotNotified_Click()
On Error GoTo Err_cmdNotNotified_Click

   Dim stDocName As String

   stDocName = "Not Notified Query"
   DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_cmdNotNotified_Click:
   Exit Sub

Err_cmdNotNotified_Click:
   MsgBox Err.Description
   Resume Exit_cmdNotNotified_Click

End Sub

--------Search Filter Code for reference only. This works
great!----------

Private Sub cmdFilter_Click()
       Dim strWhere As String
   Dim lngLen As Long

   'Build Student ID criteria expresion
   If Not IsNull(Me.txtFilterStudentID) Then
       strWhere = strWhere & "([Student_ID] = """ &
Me.txtFilterStudentID & """) AND "
   End If

   'Build Last Name criteria expresion
   If Not IsNull(Me.txtFilterLastName) Then
       strWhere = strWhere & "([Last_Name] Like ""*" &
Me.txtFilterLastName & "*"") AND "
   End If

   'Build First Name criteria expresion
   If Not IsNull(Me.txtFilterFirstName) Then
       strWhere = strWhere & "([First_Name] Like ""*" &
Me.txtFilterFirstName & "*"") AND "
   End If

  ' Build School Name criteria expression
   If Len(Me.cboFilterSchool.Value & "") > 0 Then
       strWhere = strWhere & "School_Name='" &
Me.cboFilterSchool.Value & "' And "
   End If

   ' ... build "Approval" criteria expression
Select Case Me.frameApproval.Value
   Case 1  '  Approved
       strWhere = strWhere & "SPNDSBUS='X' And "
   Case 2  '  Not Approved
       strWhere = strWhere & "(Len(SPNDSBUS & '') = 0 ) And"
   Case 3  '  Both
       strWhere = strWhere & "([SPNDSBUS]OR(Len(SPNDSBUS & '') = 0))
And "
End Select

   lngLen = Len(strWhere) - 5
   If lngLen <= 0 Then
       MsgBox "No criteria", vbInformation, "Nothing here"
   Else
       strWhere = Left$(strWhere, lngLen)

       Debug.Print strWhere

       Me.Filter = strWhere
       Me.FilterOn = True
   End If
End Sub- Hide quoted text -

- Show quoted text -

What is happening is the the user(s) is drawing from a linked table
that is view only (it is shared throughout the district and cannot be
altered) and using that to update a table on their C drive that
supplements the information contained in the linked table. Records
from the linked table are added and deleted constantly and thus the
user(s) needs to know which records these are so they can add or
delete the required supplemental data on their hard drive. What I want
to to do is make sure all the students are represented in both tables.
The only way this can happen is if the user(s) opens up the data entry
form and adds the supplementary information using the imbedded
subform.The only data that is the same in both tables is the Student
ID. The subform imbedded in the mainform creates this link.

For instance.

John Smith is added to the main districts table. He is eligible for a
free lunch. The district does not track what John's dietary
requirements are, Nutrician Services does that.

Every night there is an automated process that downloads data
containing all the students enrolled in the district and a whole lot
of info about that student such as name, address, whether they require
a free lunch, etc. that Nutrician Services can use but does not want
to retype in its own database. Lets call that file Students.

Nutrician Services has a database that contains a link to this file.
They also have a table called Diet Needs. They share a unique ID
called Student_ID. In the Diet Needs table is information such as food
allergies, special diet concerns, etc. The user(s) gather the
student's diet concerns and then sends the student home with a print
out of information from both tables for the parent to sign.

Nutrician Services must be able to know what students are added and
deleted to the Students table in order to add the supplemental Diet
Needs table. They have to be able to compare tables, find out what
students have been added to the program and then populate the Diet
Needs table using a Data Entry Form.

I want to incorporate this process into the Search Form because if the
user(s) forgets to do add the supplemental data, the search feature
could potentially print out incomplete data without the user(s)
realizing it. I will be attaching a message box to the Print button
alerting them to use the Not Notified button before printing to make
sure that all the students information is present in both tables.

Does this help or muddy the waters more?

 

[Jeff Boyce]

Any time I run across a situation in which data is being duplicated, I ask
myself if there is a need to store the same data in more than one place. In
a well-normalized relational database, the answer is typically "no".

Further, if data in one place MUST be re-entered in another, I try very hard
to remove the human factor, since data-entry leads to fat-fignerde typos
<g>!

If some of your users have local (C:\) copies of data, how are these being
backed up?

I'm not sure I have quite enough info to offer alternatives yet... the
picture I'm seeing is of multiple, scattered, stand-alone, local-only
databases that are being updated manually from a central "master". If
that's an accurate statement, I believe you could improve the accuracy,
improve the backup/safety, and decrease the manual re-entry.


Regards

Jeff Boyce
Microsoft Office/Access MVP



What is happening is the the user(s) is drawing from a linked table
that is view only (it is shared throughout the district and cannot be
altered) and using that to update a table on their C drive that
supplements the information contained in the linked table. Records
from the linked table are added and deleted constantly and thus the
user(s) needs to know which records these are so they can add or
delete the required supplemental data on their hard drive. What I want
to to do is make sure all the students are represented in both tables.
The only way this can happen is if the user(s) opens up the data entry
form and adds the supplementary information using the imbedded
subform.The only data that is the same in both tables is the Student
ID. The subform imbedded in the mainform creates this link.

For instance.

John Smith is added to the main districts table. He is eligible for a
free lunch. The district does not track what John's dietary
requirements are, Nutrician Services does that.

Every night there is an automated process that downloads data
containing all the students enrolled in the district and a whole lot
of info about that student such as name, address, whether they require
a free lunch, etc. that Nutrician Services can use but does not want
to retype in its own database. Lets call that file Students.

Nutrician Services has a database that contains a link to this file.
They also have a table called Diet Needs. They share a unique ID
called Student_ID. In the Diet Needs table is information such as food
allergies, special diet concerns, etc. The user(s) gather the
student's diet concerns and then sends the student home with a print
out of information from both tables for the parent to sign.

Nutrician Services must be able to know what students are added and
deleted to the Students table in order to add the supplemental Diet
Needs table. They have to be able to compare tables, find out what
students have been added to the program and then populate the Diet
Needs table using a Data Entry Form.

I want to incorporate this process into the Search Form because if the
user(s) forgets to do add the supplemental data, the search feature
could potentially print out incomplete data without the user(s)
realizing it. I will be attaching a message box to the Print button
alerting them to use the Not Notified button before printing to make
sure that all the students information is present in both tables.

Does this help or muddy the waters more?