UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Conditional Formatting question, Any Version    
 
   
misscrf
post May 3 2017, 11:31 AM
Post#1



Posts: 307
Joined: 22-September 04
From: Earth


Hey, all. I know this thread from 2013 is a bit old, but it's something I'm researching. I figure it might be best to ask the group of people who have been dealing with this kind of challenge.

I need to set up conditional formatting, for a subform, set in datasheet view, to highlight the fields (or rows) that satisfy the condition, of their ID being in specific table. See the user clicks on one of the rows (thereby clicking on one of the 2 textbox controls). That initiates a stored proc on SQL, which receives a parameter, of the ID for the row clicked. If that ID is in the "exclude" table, we have to maintain, we remove it from there, as the user is un-selecting that row for exclusion. If that ID is not in the table, the stored proc inserts it. After that runs, we need to have the datasheet highlight all rows that are in this exclude table, so they can see the excluded ones. I can set this up as 2 subforms, so you choose on the left, and see the highlights on the right, or 2 listboxes. I'm happy to use the best solution, whatever it is.

Thanks for any thoughts or tips!

p.s. happy to share the other code I'm working with, if that helps at all.

--------------------
misscrf
It is never too late to become what you could have been ~ George Eliot
Go to the top of the page
 
misscrf
post May 3 2017, 01:20 PM
Post#2



Posts: 307
Joined: 22-September 04
From: Earth


So I ended up figuring this out. In case this can help someone else, here is how I did it:

  1. Main form just has a button in the form header to go back to the main menu. It also has a header label, for what is below in the detail.
  2. The detail has a subform, with no form or page header, and it is set to datasheet view
  3. Add an unbound text box, and set it's control source to an nz dlookup
    =Nz(DLookUp("field to look up","table to look up field in","table_field name= " & [subform field/control to compare to]),0)
    The Nz will make a null = 0, so that it can be evaluated with another control, like a textbox bound to a field in the recordsource
  4. In form view of the main form, select a subform datasheet column
  5. Go to the Datasheet tab
  6. Choose conditional formatting
  7. If this is the column being compared, make the format only cells where the Field Value Is equal to [unbound textbox with dlookup]
  8. If this is a different column, make the format only cells where Expression Is [bound_subform control] = [unbound textbox]
  9. set their text font color and background as you wish.


To ensure the screen updates properly, on the main form load VBA, I put:
CODE
Me.frmPracticeGroupFilteringSub.Form.Recalc
Me.frmPracticeGroupFilteringSub.Form.Repaint


For the complete solution, I have the following code, on click of each control in the subform datasheet

CODE
    DoCmd.RunCommand acCmdSelectRecord
    
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = "Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=SQLServerName;Integrated Security=SSPI;"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_StoredProcName"
    cmd.Parameters.Append cmd.CreateParameter("@ParameterName", adInteger, adParamInput, 32, Me.pcode.Value)
    
    cmd.Execute
    
    Me.Recalc


The SQL code for the stored proc, is as follows:
CODE
ALTER PROCEDURE [dbo].[sp_StoredProcName]
    -- Add the parameters for the stored procedure here
    @ParameterName int

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

IF EXISTS (SELECT * FROM tblExcludeTableName WHERE FieldName1 =@ParameterName)
    BEGIN
        DELETE FROM tblExcludeTableName WHERE FieldName1 =@ParameterName
    END
ELSE
    BEGIN
        --select 'it dont exist'
        INSERT INTO  tblExcludeTableName (FieldName1 ) SELECT @ParameterName
    END
END


Hope this helps someone!

--------------------
misscrf
It is never too late to become what you could have been ~ George Eliot
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd July 2017 - 09:45 AM