Ed:
If the query is updatable then you should be able to edit the data in the
form already, provided that the form's AllowEdits property is True (Yes). An
alternative strategy would be to bind the form to an updatable query which
returns all rows and then incorporate some means of navigating to a
particular record. The following, for example, is the code for the
AfterUpdate event procedure of an unbound combo box on a bound form which
moves the form to the addressee selected in the combo box:
Dim rst As Object
Dim ctrl As Control
Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl
With rst
.FindFirst "AddressID = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
The combo box is set up as follows:
RowSource: SELECT AddressID, FirstName & " " & LastName FROM Addresses
ORDER BY LastName, FirstName;
BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
Another approach, where more than one row might match the value selected in
the combo box, would be to filter the form, in which case the code would be:
Dim ctrl As Control
Set ctrl = Me.ActiveControl
Me.Filter = "AddressID = " & ctrl
Me.FilterOn = True
If you really do want to use an unbound form then here is some simple code
for the module of a form which shows data from the same table, again using a
combo box which, in this case, the user lists the last names of addressees
only:
Option Compare Database
Option Explicit
Dim dbs As DAO.Database, rst As DAO.Recordset
Private Sub cboFindContact_AfterUpdate()
Dim strSQL As String
If Not IsNull(Me!cboFindContact) Then
strSQL = "SELECT * FROM Addresses WHERE LastName = """ & _
Me!cboFindContact & """"
Set rst = dbs.OpenRecordset(strSQL)
With rst
Me!txtFirstName = !FirstName
Me!txtLastName = !LastName
End With
Else
Set rst = Nothing
Me!txtFirstName = Null
Me!txtLastName = Null
End If
End Sub
Private Sub cmdNext_Click()
On Error Resume Next
With rst
.MoveNext
If Err = 0 Then
If .EOF Then .MoveLast
Me!txtFirstName = !FirstName
Me!txtLastName = !LastName
End If
End With
End Sub
Private Sub cmdPrevious_Click()
On Error Resume Next
With rst
.MovePrevious
If Err = 0 Then
If .BOF Then .MoveFirst
Me!txtFirstName = !FirstName
Me!txtLastName = !LastName
End If
End With
End Sub
Private Sub Form_Close()
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub
Private Sub Form_Load()
Set dbs = CurrentDb
End Sub
Private Sub txtFirstName_AfterUpdate()
With rst
.Edit
!FirstName = Me!txtFirstName
.Update
End With
End Sub
Private Sub txtLastName_AfterUpdate()
With rst
.Edit
!LastName = Me!txtLastName
.Update
End With
Me!cboFindContact.Requery
End Sub
Ken Sheridan
Stafford, England
