Replies

No built in functions…so the menu system is out.
It is supposed to be automatic, but without VBA….I think these are conflicting requirements. Without VBA, you lose the automatic part.
A formula in and of itself would get you halfway there, something along the lines of =IF(A1”,1,2) would go in B1. You’d still need some type of sort routine. As I wrote earlier, the formula isn’t necessary, but I can’t imagine the automatic magic without code.

We’ll see what others have to say…

I highlighted A1:A10, and did Data – Sort. The blanks are at the bottom…..and David appears before Jack…which doesn’t matter.

Taking a look at the table structure

tbl2Date                   tbl3SetInfo
----------------           ----------------
JobNumber                  JobNumber
Date                       Date
Set                        Set
ReqStrength                ReqStrength
Location                   Location
Slump                      Slump
Air                        Air
                           ID
                           Strength

I’m not entirely sure why you repeat the ReqStrength, Slump, and Air. If tbl2Date is a table of tests, or something….one could make the key fields a combination of JobNumber, Date, and Location. Theoretically, I would not do the same job more than once at a location on a given date.

Assuming this is correct (and I am not sure what slump and air are), the data that relates to a given job, at a location, on a date would also reside in tbl2Date. I would not repeat these fields in tbl3SetInfo.

Apparently table 3 stores different strengths? Any idea what purpose the ID field serves?

Proposed new table tbl2Date
JobNumber
Date
Location
Set
ReqStrength
Slump
Air

Proposed new table tbl3SetInfo
JobNumber
Date
Location
ID
Strength

When you bring up the properties of the subform from within the main form, go to the Data tab.

Are you able to directly edit the “Link Child fields” and “Link Master fields”? You should be able to add the other two links?

Out of curiosity, why are these fields in different tables? If five fields comprise a unique record, are they indexed?

Under the category of “hmmmmm”….

If one adds a command button (from the Controls toolbar), you may not add control tip text.

If a UserForm is added to a workbook, and a command button is added to the form, control tip text may be added to the button.

I think the original poster was referring to control tips.

Here’s what I came up with

John:

I’ve never tried to do an Advanced Filter on non-contiguous ranges. I took the “cowards” approach and put your blue region below the green.

I haven’t used the union function before.

John:

What was the result of the John routine supposed to be?

I was able to eliminate the error by the following modification:

Sub john()
On Error GoTo Err_john

   Dim oWB As String
   Dim oJoinNewRng01 As Range
   
   oWB = Application.ActiveWorkbook.Name
   
   Set oJoinNewRng01 = Union(Workbooks(oWB).Sheets("Sheet1").Range("A10:A14"), Range("B10:D14"))
   
   oJoinNewRng01.AdvancedFilter Action:=xlFilterCopy, _
         CriteriaRange:=Workbooks(oWB).Sheets("Sheet1").Range("Criteria"), _
         CopyToRange:=Workbooks(oWB).Sheets("Sheet1").Range("I10:L10"), _ 
         Unique:=False

Exit_john:
   Exit Sub
   
Err_john:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_john
   
End Sub

Claude:

Have you tried setting up rules? In O2K I can apply categories to e-mail when they arrive.

Display the formatting toolbar, and select the object in question from the combo box on the left….assuming MS hasn’t moved the toolbar objects around…

When you wrote the following in your original message, I assumed you were only concerned with formatting.

I can’t seem to come up with any creative ways of doing this. I want to manipulate the formatting of the range with numeric values without affecting the blank cells.

You are no longer concerned with formatting? You want the range (defined as Min and Max) of the cells within the specified area?

=MIN(B4:J12) and =MAX(B4:J12) won’t work?

Called from within code, the range B4:J12 could be set to some constant, and you could use

WorksheetFunction.Max() and WorksheetFunction.Min()

Is this what you are after?

You mean other than applying conditional formatting?

Select tbl_Misc.* from tbl_Misc (Obviously use your own table(s) and fields here)
where tbl_Misc.Category In(‘ThisOne’, ‘That One’, ‘The Other Thing’);

I prefer the In() function for a somewhat tidier SQL statement (as opposed to linking OR statements)

Are you intending to provide a listbox with multiple selections possible?

If the form is for viewing only those records that are On Hold or the field is null, why not skip the filters and base the form on this criteria? Go to the form’s Record Source and use the wizard to set these criteria.

I would not think so. What is the setting for the property “Allow Filters”? I would assume that it is “Yes”.