Customize detail in form

[Rpschwar]

What I am trying to do is create a form where each line is a member of a group based on a query so the number of rows will vary. There will be 4 column with each person for 4 weeks of information. Is there a way to format each week for each row like you can if it was done in a report? I want there to be a click event on each row/column that will do something based on the member ID and week selected.

[pbaldy]

Conditional Formatting can be used to affect the format of controls on a continuous or datasheet form. The click event can refer to data to know what record is clicked on.

[Rpschwar]

That's not what I was needing. I was needing to have each row/column to show results based on a query. I found an example below that does part of what I needed. If you put the command "=fnName(String1, integer1)" in the recordsource of the text box in the detail section of the form it will return a value into the field. Where String1, and Integer1 are variables passed.

Public Function fnName( _
sVariable as String, _
iVariable as Integer _
) As String


On Error GoTo Err_fnName


Dim con As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sSQL As String


sSQL = ""


Set con = Access.CurrentProject.Connection
Set rst = New ADODB.Recordset


rst.Open sSQL, con, adOpenDynamic, adLockOptimistic


If rst.BOF And rst.EOF Then


'No records found
'Do something!


Else


'Found a value, return it!
fnName = rst(0)


End If


rst.Close
Set rst = Nothing


con.Close
Set con = Nothing

[pbaldy]

You should be able to use field names and get different results.

=fnName(Field1, Field2)

Normally the form would be based on a query that returned the appropriate results.

[Rpschwar]

That's basically what I have for the data source. Can i put the same type of function call in the click event to do something else passing the same parameters?

[pbaldy]

Sure. Not sure what you're trying to accomplish but:

VariableName = fnName(Me.FirstControl, Me.SecondControl)

Or if the control already has the value you can just grab it from there. Can you attach a sample db demonstrating what you're trying to accomplish? I'm having trouble visualizing it.