Set BackColour using dynamic fieldname (1 Viewer)

[liddlem]

Hi All
Despite HOURS of one-on-one training on how to prevent the capturing duplicate records, I am still called on a weekly basis to merge the information of two students (Who are in fact the SAME person)
Consequently, I am developing an unbound Parent form (FRM_DUP_Student) that contains 2 combo-boxes (Stu_To_Keep and Stu_To_Loose) and two subforms (FRM_Stu_KEEP and FRM_STU_LOoSE).
Each of the subforms is bound to a query that is sourcing its info from the STUDENT table.

The idea is that the user will select the Student that they want to 'keep' and the one that they want to merge from.
The subforms then display the respective data.
The user is then able to compare the data between the two subforms.

Its at this point that I would like to highlight to the user, what data will be 'lost' when they click a 'MERGE' button.

In the ON-current event of the FRM_STU_LOoSE form I am trying this.

Private Sub Form_Current()
Dim Ctl As Control
Dim MyLoose As Variant
Dim MyKeep As Variant
Dim MyType As String
Dim CName As String

For Each Ctl In Me.Controls
MyType = Ctl.ControlType
CName = Ctl.Name
    
    Select Case MyType
    Case 106, 109, 111 ' CheckBox, Textbox, Combobox
        MyKeep = DLookup(CName, "QRY_DUP_Stu_Keep")
        MyLoose = DLookup(CName, "QRY_Dup_STU_Loose")

		'SO....How do I set the backcolor here as this produces a "438 - Object not supported" error?
        If MyKeep <> MyLoose Then
            'Ctl.backcolour = vbRed
            Else
            'Ctl.backcolour = vbWhite
        End If
    End Select
Next
End Sub

Thanks & Regards

 

[JHB]

Checkbox doesn't have a backcolour property, only the label.

 

[arnelgp]

aside form checkbox has no backcolor,
do you get the effect you want?
i think in continuous form or datasheet
all your records there will inherit
the red color you put. if that the
case you need to consider using
Conditional Formatting.

 

[liddlem]

Hi JHB- You make a good point. (I forgot about the Checkbox background)....but my code fails on EVERY controltype.
ArnelGP - Nope - Each subform is a SINGLE record, so that shouldn't be a problem.

Since I need to get around the checkbox issue, I may as well change the LABELS for all unmatched fields.
So my question still remains.... The field name is assigned dynamically, so how do I parse that to read as
Fieldname_Label.backcolor = vbcolour

Can I use a variable like
MyFname = ctl.name
MyFname = MyFname & "_Label"
MyFname.backcolor = VbRed


Thanks

 

[jdraw]

two students (Who are in fact the SAME person)

Is studentID(or similar) the PK of student table?
Can you describe how duplicate records are being entered?
Isn't that what should be corrected/removed?

 

[missinglinq]

Access is US-centric, so I don't believe

Ctl.backcolour = vbRed

will ever work! I'm guessing it needs to be

Ctl.backcolor = vbRed

Linq ;0)>

 

[missinglinq]

On looking further, your DLookUps don't look right, either.

CName is a variable being assigned the name of a Control, while the first argument in DLookUp is a Field in the Domain...or an expression that represents the same.

Also, if you don't give a criteria, as you don't, here, DLookup simply returns a random value in the domain...not a specific one.

Linq ;0)>

 

[liddlem]

Hi Jdraw
Because we have a HUGE turnover of students (roughly 1/3 are NEW to the school every term and 1/3 have been to the school before, but NOT last term) our admin team must process enrolment applications on a REGULAR basis.
To complicate matters, Student A might submit an application using the name FRED SMITH this term, but JOE SOAP next term (This is a CULTURAL thing)
In an attempt to ensure that we maintain only 1 record - I suggested that the FIRSTNAME and SURNAME fields are used to capture the LEAGAL names (as per the birth certificate) and then use ALT_Name and Prefered_Name fields for their alias.

The idea being that the staff member should be searching the system based on the BIRTH CERTIFICATE name and not the name that the applicant fills in to the FIRSTNAME / SURNAME fields of the form. (This implies that they have a copy of the birth certificate in front of them!)

SO . . . The admin staff are NOT checking the system as they should be.
Hence - Duplicate records of the same person. (Different ID numbers)

 

[liddlem]

CName is a variable being assigned the name of a Control, while the first argument in DLookUp is a Field in the Domain...or an expression that represents the same.

Also, if you don't give a criteria, as you don't, here, DLookup simply returns a random value in the domain...not a specific one.

Linq ;0)>

Ooooh - Thanks Missinglinq - I was not aware of that.
So . . . How would I (or COULD i even) pass a variable to the DLookup?

 

[liddlem]

Hey Missinlinq - Just checked the return results for the DLookups - They are returning the correct values from each subform.

 

[Mark_]

@Liddlem,

Does your school produce a certificate or diploma that the students will need? If so, I would HIGHLY RECOMMEND that they provide the name that should appear. For where I work we need to constantly remind students that if they change their name they need to do so also with us. We submit records to the State for new nurses and the STATE expects the names to match. Really big issue if we send the wrong name.

Since it is the students responsibility, if we can demonstrate they had not requested a change it is upon the student to resolve.

 

[liddlem]

Hi Mark_
You are absolutely correct!
In fact - it has taken me a number of years to convince that the school board that because of their very unique circumstances, that they should not be using an 'off-the-shelf' database.
Unfortunately I cannot modify their existing front-end (as its has licensing implications) . Nor can i change the table structure without the front-end failing.
So I have written scripts to transpose the existing data into a structure that is more fitting for what they need. Also developing the front-end accordingly.

 

[liddlem]

I have decided to convert the Checkboxes to Textboxes and so now the code is running as expected.
Thanks Missinglinq for the directive re backcoloUr
(This minor fix got the following script working like a charm.)

Dim Ctl As Control
Dim MyLoose As Variant
Dim MyKeep As Variant
Dim MyType As String
Dim CName As String

For Each Ctl In Me.Controls
MyType = Ctl.ControlType
CName = Trim(Ctl.Name)
    
    Select Case MyType
    Case 109, 111 ' Textbox, Combobox
        Debug.Print CName
        MyKeep = DLookup(CName, "QRY_DUP_Stu_Keep")
        MyLoose = DLookup(CName, "QRY_Dup_STU_Loose")

        If MyKeep <> MyLoose Then
            Ctl.BackColor = vbRed
            Else
            Ctl.BackColor = vbWhite
        End If
    Case Else
    End Select
Next
End Sub

 

[Mark_]

Hi Mark_
You are absolutely correct!
In fact - it has taken me a number of years to convince that the school board that because of their very unique circumstances, that they should not be using an 'off-the-shelf' database.
Unfortunately I cannot modify their existing front-end (as its has licensing implications) . Nor can i change the table structure without the front-end failing.
So I have written scripts to transpose the existing data into a structure that is more fitting for what they need. Also developing the front-end accordingly.

I would check with your schools senior administrators, if possible. If there is a legal issue regarding using the wrong name that could force much of the "Fix" you are looking for.

If a student can come, take classes, and receive recognition under any name they desire, that could lead to issues of fraud. For myself, I would do everything possible to make sure I was not included in an investigation. Likewise were our school involved with such an action we would loose our credentials and would no longer be accredited.

In all, your problem is that you are fixing something that should not happen. Even if there is a cultural reason for your students be selective in the name they apply under, if there are legal repercussions for doing so your best to bring these to light and stop the practice.

 

[liddlem]

Thanks for your concern Mark_
The NEW system will require the legal names that (once the record is saved) will be set in stone. The PREF names and SKIN names will be available to change as they wish.
Furthermore :
1. Users will not be able to DELETE records. They can only mark them as obsolete (which hides the record in SOME screens)
2. Every transaction will be recorded in a log file (listing date/time, who and what was changed.) These record will be available for authorised users to browse ... and hold someone accountable when things go wrong.
3. There is also a field to attach a PDF copy of the birth certificate to the record. (Not alterable once saved)
4. I am creating lookup processes that (when adding a new student record) checks the DB for existing students that contain similar character sequences in ANY name field. If any are found, (there should always be at least 3 or 4) this list is displayed. I could also cross ref the birth date, but that too is unreliable as many record their DOB as 1/1/SomeYear or 1/7/Someyear.
5. These students SHOULD also have a unique EDUCATION DEPARTMENT ID which we use as a cross-ref, but that is also not a fail-safe.

In the mean time however - We are keeping nightly backups of the DB and a few forests of paperwork.