 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / August 2005changing control props in datasheet view for 'active' record
hi, i don't know whether my subject line's heading fairly represents what i'm trying to learn but here goes anyway... i have a form (that uses a query as its record source) in datasheet view. i would like to be able to change the colors (fore and/or background) of the controls on it subject to the condition the record pointer is on a particular record. if you make a change to a controls color properties, when the data is viewed in this form, it applies to everything the user sees not just the record the pointer's on. i don't know if it makes a difference, but records on the form are non-editable, non-deletable and records may not be added using this particular datasheet view. i'd be willing to bet there's some vba type property setting that needs to be tested for but i haven't a clue what it'd be called. thanks for any help in advance. ted >i have a form (that uses a query as its record source) in datasheet view. i >would like to be able to change the colors (fore and/or background) of the [quoted text clipped - 4 lines] >on the form are non-editable, non-deletable and records may not be added >using this particular datasheet view. The only way I know of that can do this in datasheet view is Conditional Formatting (Format menu). To highlight the current record, use the form's Current event to set the record's PK field to a text box in the form's header section. Then use this text box in the condition for each bound control in the detail section.  SignatureMarsh MVP [MS Access] marshall, my pk consists of lastname, firstname, mi, medrecord, irb_number. can you flesh out a little more what you're suggestion would look like. i don't know if this really matters all that much, but my datasheet form is a sub-form of a main form. the main one's called "DataSheet View of Screen" and the subFrm's called "Screening Log DS View". the form header of the subForm's holds nothing but labels with the controls' names. with regards, ted > >i have a form (that uses a query as its record source) in datasheet view. i > >would like to be able to change the colors (fore and/or background) of the [quoted text clipped - 12 lines] > form's header section. Then use this text box in the > condition for each bound control in the detail section. >my pk consists of lastname, firstname, mi, medrecord, irb_number. can you >flesh out a little more what you're suggestion would look like. i don't know >if this really matters all that much, but my datasheet form is a sub-form of >a main form. the main one's called "DataSheet View of Screen" and the >subFrm's called "Screening Log DS View". the form header of the subForm's >holds nothing but labels with the controls' names. Look at the subform's Form object in design view. Make sure the Form Header section exists (View menu). Create a text box named txtCurKey in the header section. Display the Form's property sheet, select the OnCurrent property and click on the [...] button at the right side of the property. When the code window appears, add a line of code at the cursor: Me.txtCurKey = Me.nameofPKfield Now, go back to the form design screen and select the lastname text box. Then use the Format - Conditional Formatting menu item to open the CF window. Select the Expression Is option in the drop down box and enter the expression: [nameofPKfield] = [txtCurKey] and select your special fore and back ground colors. Test it to make sure the last name text box works the way you want it. When it does, repeat the CF settings for the other text boxes. >> >i have a form (that uses a query as its record source) in datasheet view. i >> >would like to be able to change the colors (fore and/or background) of the [quoted text clipped - 12 lines] >> form's header section. Then use this text box in the >> condition for each bound control in the detail section. SignatureMarsh MVP [MS Access] hi marshall, that was somewhat easier to follow (relative newbie over here) but here's a snag thhat i found; although the field txtCurkey is recognized by VBA when i add it to the VBA code i'm writing in On Current event section, the name (i guess that) my PK's called "PrimaryKey" (to judge from its appearance in the Index name column of the table's) is not and VBA chokes on it. what to do? with regards, ted > >my pk consists of lastname, firstname, mi, medrecord, irb_number. can you > >flesh out a little more what you're suggestion would look like. i don't know [quoted text clipped - 42 lines] > >> form's header section. Then use this text box in the > >> condition for each bound control in the detail section. >that was somewhat easier to follow (relative newbie over here) but here's a >snag thhat i found; although the field txtCurkey is recognized by VBA when i >add it to the VBA code i'm writing in On Current event section, the name (i >guess that) my PK's called "PrimaryKey" (to judge from its appearance in the >Index name column of the table's) is not and VBA chokes on it. what to do? The field name is the name of the **field** in the table's design view, not the name of an index. You may or may not have a field named PrimaryKey and you may or may not have an index named PrimaryKey, but they are different things. Are you sure you need so many fields in the primary key index? Wouldn't the irb_number field be sufficient? At least you should be aware that the lastname, firstname and mi fields will **NOT** help identify a unique record, any number of people can have the same name. So, at least you should remove those from the PK index's field list. Then rethink your idea of primary key to make sure you aren't going to run into a wall somewhere down the road. >does your explanation only hold for the case when there's >just one field that's been designated a PK; in my case there >are 5 fields!!?? What we're looking for here is the field or fields that uniquely identify a record. We need this so that the conditional formatting will only be applied to one record. If there are multiple fields required to uniquely identify a record, then you will need a text box in the header section for each field and a line of code to assign each value to the text boxes. Me.txtCurKey1 = Me.nameofPKfield1 Me.txtCurKey2 = Me.nameofPKfield2 . . . The CF expression will then be more like this: [nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] = [txtCurKey2] And . . . SignatureMarsh MVP [MS Access] >> >my pk consists of lastname, firstname, mi, medrecord, irb_number. can you >> >flesh out a little more what you're suggestion would look like. i don't know [quoted text clipped - 42 lines] >> >> form's header section. Then use this text box in the >> >> condition for each bound control in the detail section. hi marshall, i will intersperse my responses/reactions below, but let me add that i've worked on this a bit since getting your response and found what i think is just the solution that my users'll appreciate. essentially, i took your suggestion to heart and figured that i could decide to use the [Last Name] field as my primary key and went on with the CF-ing to create a ds view that illuminates (turns to red) the black foreground characters --- i think the advantage of using just this as my determining factor of 'uniqueness' rather than (as i had previously anticipated) the entire PK comes from the fact that for every n-tuple of same consecutive LNs it illuminates the lot of them giving the user the opportunity to really assure themselves that they're picking/looking at the one with the correct litany of other relevant (dates, etc. etc) information. > >that was somewhat easier to follow (relative newbie over here) but here's a > >snag thhat i found; although the field txtCurkey is recognized by VBA when i [quoted text clipped - 6 lines] > have a field named PrimaryKey and you may or may not have an > index named PrimaryKey, but they are different things. yes, as i've already proven to myself before receiving your reply -- but it is appreciated nevertheless; live 'n learn. > Are you sure you need so many fields in the primary key > index? Wouldn't the irb_number field be sufficient? At [quoted text clipped - 4 lines] > rethink your idea of primary key to make sure you aren't > going to run into a wall somewhere down the road. this is kind of a patient screening database, each record recording the LN, FN, MI, MR# (which are patient specific) and lastly the IRB# which represents the clinical study for which the patient was screened for possible participation; this last bit is what must be unique for the prior combination of fields that comprise the composite PK. in other words, a patent may only be screened for the an IRB# once but IRB# numbers may and do appear multiple numbers of times in the table because there are more than one patient's worth of information being tracked. > >does your explanation only hold for the case when there's > >just one field that's been designated a PK; in my case there [quoted text clipped - 16 lines] > [nameofPKfield1] = [txtCurKey1] And [nameofPKfield2] = > [txtCurKey2] And . . . i get it, i get it.... very nice, this is exactly what i needed and will come back to it if warranted. > Marsh > MVP [MS Access] with every best wish and thanks, ted > >> >my pk consists of lastname, firstname, mi, medrecord, irb_number. can you > >> >flesh out a little more what you're suggestion would look like. i don't know [quoted text clipped - 42 lines] > >> >> form's header section. Then use this text box in the > >> >> condition for each bound control in the detail section. ....just as an afterthought: what's the theory behind this? why do the txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the details)? > >that was somewhat easier to follow (relative newbie over here) but here's a > >snag thhat i found; although the field txtCurkey is recognized by VBA when i [quoted text clipped - 82 lines] > >> >> form's header section. Then use this text box in the > >> >> condition for each bound control in the detail section. >....just as an afterthought: what's the theory behind this? why do the >txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the >details)? It doesn't really matter all that much, but the detail section is where you're displaying the real data and it gets processed multiple times. So, I think it's better to park these utility controls in an out of the way place, especially in datasheet view where the header/footer are not displayed anyway. SignatureMarsh MVP [MS Access] >> >that was somewhat easier to follow (relative newbie over here) but here's a >> >snag thhat i found; although the field txtCurkey is recognized by VBA when i [quoted text clipped - 83 lines] >> >> >> form's header section. Then use this text box in the >> >> >> condition for each bound control in the detail section. i wanted to say thanks again for all the bandwidth. it's been really really helpful. now i have more than one approach to implement in this datasheet. now that i know that there's nothing 'mysterious' about plonking them in the header, it kind of makes me feel better about a2k as well (in terms of the way it hangs together). ted > >....just as an afterthought: what's the theory behind this? why do the > >txtCurKey textboxes 'go' in the header part of the form (and not, e.g. the [quoted text clipped - 93 lines] > >> >> >> form's header section. Then use this text box in the > >> >> >> condition for each bound control in the detail section. Glad to hear that you're making progress on your application and expanding your knowledge along the way. A goal we all strive for. SignatureMarsh MVP [MS Access] >i wanted to say thanks again for all the bandwidth. it's been really really >helpful. now i have more than one approach to implement in this datasheet. [quoted text clipped - 100 lines] >> >> >> >> form's header section. Then use this text box in the >> >> >> >> condition for each bound control in the detail section. hi marshall, just fyi, i have a similar goal which bears on a continuous sub-form and the ability to enable/disable a command button appearing on it that i posted today with the heading: changing cntrl props in continuous view for 'active' record in A2K ted > Glad to hear that you're making progress on your application > and expanding your knowledge along the way. A goal we all [quoted text clipped - 103 lines] > >> >> >> >> form's header section. Then use this text box in the > >> >> >> >> condition for each bound control in the detail section. hey marsh, it's been a month of sundays since we confabulated on this thread and i'm finding an opp'y to utilize the approach in another database. the particulars are almost the same, there are 6 components to the PK so i created 6 txtCurKey# type controlsl that are in the header. here's the VBA Private Sub Form_Current() Me.txtCurKey1 = Me.Last_Name Me.txtCurKey2 = Me.First_Name Me.txtCurKey3 = Me.MI Me.txtCurKey4 = Me.MR_Number Me.txtCurKey5 = Me.IRB_Number Me.txtCurKey6 = Me.RecordNumber End Sub there's another piece i'm adding immediately below because i suspect it might have to do with the reason for this posting (which i'll get to in a minute), i.e. Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.Dirty Then Me.Updated_by = LAS_GetUserName() Me.Last_edited = Now() End If End Sub i have done the CF thing on the pair of fields used in the above (Updated_by and Last_edited) and all's well and good until i attempt to change the active/current record and the cursor enters either of the pair of above said fields. what happens next is that when you attempt to move the cursor away/out from the active/current record to another, the field goes blank and you get a message telling me that "Field 'DaysView.Updated_by' cannot be a zero length string". my gut tells me that if these were in otherways not dependent upon the VBA code that auto-completes them i wouldn't be getting into this, so what's the matter w/ all of this? is there some workaround? i'd love for the user to be able to see these controls change color per the same CF rule i'm applying to the bulk of the datasheet's fields, but if i have to bite the old bullet, then so be it! all the best, -ted > Glad to hear that you're making progress on your application > and expanding your knowledge along the way. A goal we all [quoted text clipped - 103 lines] > >> >> >> >> form's header section. Then use this text box in the > >> >> >> >> condition for each bound control in the detail section. .... does your explanation only hold for the case when there's just one field that's been designated a PK; in my case there are 5 fields!!?? > >my pk consists of lastname, firstname, mi, medrecord, irb_number. can you > >flesh out a little more what you're suggestion would look like. i don't know [quoted text clipped - 42 lines] > >> form's header section. Then use this text box in the > >> condition for each bound control in the detail section. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.