recordset syntax (Access 97/SR 1)

Topic

New Reply

Hello All:

I have a database in A97/SR1 with a table named Employees and a form named frmEditEmployees. On the form are most of the fields of the table. I want to use the form to allow the user to edit the data held in the table. I would like to put a command button on the form to perform a “Find” function. I know how to put the button on the form, but am having trouble with part of the code.

I don’t know how to reference a recordset in code. I would like the recordset to contain all the records in the Employees table but the find should only be looking in the LastName field.

Can someone provide some help in what the correct syntax is for referencing a recordset.

Thanks

Reply | Quote

Replies

Here’s a link to a post that explains how to do a find in a DAO recordset based on the value in a combobox.

If you just want to use the built-in find function, which pops up the Find dialog, you don’t need to refer to the recordset in code. Here’s a piece of code I use behind a command button to launch the built-in find dialog.

Private Sub cmdFind_Click()
  On Error Resume Next
  Dim ctl As Control
  Dim strPfx As String

  'set the focus to the last control before the 
  'button was clicked 
  Set ctl = Screen.PreviousControl

  'make sure this isn't a label or a 
  'command button or something else you
  'can't find on -- I use txt as  the prefix for textbox controls
  strPfx = Left(ctl.Name, 3)

  'if it isn't a textbox set the focus
  'to the first control that holds data.
  If strPfx  "txt" Then
    Me.SetFocus
    Set ctl = [txtUnit]
  End If
  ctl.SetFocus
  
  On Error GoTo Err_cmdFind_Click
  'launch the find dialog 
  DoCmd.RunCommand acCmdFind

Exit_cmdFind_Click:
  Application.Echo True
    Exit Sub

Err_cmdFind_Click:
    MsgBox Err.Number & "--" & Err.Description
    Resume Exit_cmdFind_Click
    
End Sub 'cmdFind_Click() 

Reply | Quote