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    
post May 3 2017, 11:31 AM

Posts: 308
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.
Go to the top of the page
post May 3 2017, 01:20 PM

Posts: 308
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:

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

    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)

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

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

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

Hope this helps someone!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    12th December 2018 - 01:39 PM