Posts by boblarson

You don't need to use the search to find the record in the other table. You would use a SQL Statement and recordset to bring back the appropriate record if there was one:

'SEARCH FOR THE RECORD IN REMARK TABLE
   Dim strSQL As String
   Dim strWHERE As String
   Dim rs As DAO.Recordset
   
   ' I assume that BuildCriteria does NOT return the word WHERE at the beginning
   strWHERE = BuildCriteria("[IDOp]", dbInteger, OpNameClick)

   strSQL = "SELECT * FROM Remark WHERE " & strWHERE

   Set rs = CurrentDb.OpenRecordset(strSQL)
   If rs.RecordCount = 0 Then
     Msgbox "Record does not exist"
   Else
     ' do whatever here with the record you have found from the other table
     ' not sure what it is you really want or what to do with it.
   End If

   rs.Close
   Set rs = Nothing

hi all,
i have created a crosstab query and one of the row heading contains a hyperlink field.
my problem is that the field is not working,
and on top of that it gives corrupted values too.

pls help
thanks

I don't believe you can use that directly in a crosstab. Create your crosstab query without that field and then create a select query which joins the crosstab with the hyperlink field in it.

I forgot to include how to call this.

It should be:

Call AdjustProcNum("YourTableNameInQuotes", "YourFieldNameInQuotes", YourNewNumber)

Copy this function into a standard module (not form, report or class) and name the module something other than the name of the function:

Function AdjustProcNum(strTableName As String, strFieldName As String, lngNewNum As Long)
   Dim strSQL As String
   Dim rst As DAO.Recordset

strSQL = "Select [" & strFieldName & "] From [" & strTableName & "] " & _
         "ORDER BY [" & strFieldName & "]"

Set rst = CurrentDb.OpenRecordset(strSQL)

Do Until rst.EOF
   If rst(strFieldName).Value >= lngNewNum Then
      rst.Edit
      rst(strFieldName).Value = rst(strFieldName).Value + 1
      rst.Update
   End If
   rst.MoveNext
Loop

MsgBox "You can now enter the new process number " & lngNewNum, vbInformation

rst.Close
Set rst = Nothing   
End Function

I'm assuming Outcome is numeric but it wouldn't have to be but you would change the value for NZ to vbNullString if it is text.

MyNum1 = ]")[B]Nz([/B]DLookup("[Outcome]", "Remark", _
      "[IDRemark] = Form![SelectTxt]")[B],0)[/B]
OutTxt.Value = MyNum1

You can try setting the Search button's DEFAULT property to YES and see if that helps.

Tab controls are interesting in that you rarely need to do anything at all with them or reference them. In your case you need to set focus to tab 5 and then refer to the subform on subform. You have to set focus first. I will show you a way to refer to forms and controls which most don't use but with nested subforms it works well.

First the set focus

Forms!frmMain.ifrmTABset.Pages(4).SetFocus

Since the pages are zero based, page 5 would be 4 in the scheme. You can also use just the page name instead of the tab control name. So if your page was actually named page5 then you would be able to go with

Forms!frmMain.page5.SetFocus

And then you need to refer to the subform item

Forms("frmMain").Controls("sfrmMain").Form.Controls("sfrmUser").SourceObject = "sfmSETcfg"

I think you may want to read my tutorial on subforms. It helps with understanding when to use the .Form. and when not to.
You can find it here:
Easy Way to Remember Subform Syntax

I guess you'd have to add an:

If dblNumber 1 = dblNumber2 Then
  FindLargestNumber = dblNumber1
End If

or you can assign it whatever value you want.

This is roughly what you want. Don't know what you want for the result so I returned the value that was the largest.

Option Compare Database

Private Sub cmdCompare_Click()
   MsgBox "The largest number is " & FindLargestNumber(Me.Text1,Me.Text2)
End Sub

Public Function FindLargestNumber(dblNumber1 As Double, dblNumber2 As Double) As Double
  If dblNumber1 > dblNumber2 Then
     FindLargestNumber = dblNumber1
  End If

  If dblNumber2 > dblNumber1 Then
     FindLargestNumber = dblNumber2
  End If

End Function

To install as Administrator, right-click on the setup file and select RUN AS ADMINISTRATOR.

Make sure when you go to install your drivers, that you run the install as Admin because in Vista, if it is changing configuration and it isn't Microsoft Software, it doesn't always let the software install unless it is certified by Microsoft UNLESS you run the install as admin.