Access 2003: open current datasheet record in a form for editing

a good and easy way would be using a "split form"

If you don't already have an ID field in you table add one. Then add the following code to the on click event of the fields on the editing form:

Dim sWhere As String

If Not IsNull(Me.txtID) Then
    sWhere = "[tblRecords.ID] = " & [txtID]
    DoCmd.OpenForm "frmEditing", , , gWhere, acFormEdit
End If

Select allOpen in new window

conagraman:

Split Forms are a new feature in Access 2007, the question is about 2003.

(No Points) the datasheet view must be a FORM datasheet and not a table datasheet.

Hello Everybody!!!!!!!
(sorry, I have a 3 year old)

Is the left hand Arrow-Record-Selector-Thing an object that can have an event assigned to it?

Thanks

No, you have to copy the code to the on click event of every txtbox on the form.

ok....so, I did a test.  in the Form properties, I set On Doublclick to msgbox the ID of the record I doublclicked on and this seems to work.

Is there something not right about doing this?

Thanks.

yes you can use the form's onclick event.

if you dont want to use the split form you can do what hello_everybody is talking about :
-have one form that is your datasheet. -
-have another form that you will edit your records in that is based off of the same table as your datasheet-
-then make sure there is a unique field in your table such as a primary key.-
-then go into the onclick event of the datasheet and add the code to open the edit form and goto the record that was clicked on.-

 you can use the code hello_everybody has posted or a simplified version like

 DoCmd.OpenForm "yourFormNameHere", , , "[table.ID] = " & [ID]

note that the onclick event is only fired when you click on the box on the left of the record

I'm missing something. But, how is the data being passed to it?

What is the Recordsource set to at this point?

please see attached code.

Private Sub Form_DblClick(Cancel As Integer)
'MsgBox (Forms.qrymanualregistrations.ManualID)
Dim sWhere As String

If Not IsNull(Forms.frmAllManualregistrations.ManualID) Then
    sWhere = "[qryManualRegistrations.ManualID] = " & Forms.frmAllManualregistrations.ManualID
    DoCmd.OpenForm "frmChangeManualRegistration", , , sWhere, acFormEdit
End If
End Sub

Select allOpen in new window

What's wrong, it doese'nt work?

When you open the form the recordsource for the form is set to sWhere, which is filtered to show only the record coresponding with ManualID.

If you are having trouble, instead of [qryManualRegistrations.ManualID] use [Table.ManualID].

if qryManualRegistrations is the recordset of the datasheet then that is what you should use for the first part of your openform criteria clause.  
its the second part the Forms.frmAllManualregistrations.ManualID that should change. you arent referencing the textbox or a object on your form  you are referencing the field in the recordset of your edit form.
 sWhere = "[qryManualRegistrations.ManualID] = " & Forms.frmAllManualregistrations.ManualID <<< the last part should change
try the code below

'-----------------------------
   

Private Sub Form_DblClick(Cancel As Integer)
'MsgBox (ManualID)
Dim sWhere As String

If Not IsNull(qryManualRegistrations.ManualID) Then
    sWhere = "[qryManualRegistrations.ManualID] = " & ManualID
    DoCmd.OpenForm "frmChangeManualRegistration", , , sWhere, acFormEdit
End If
End Sub

also i changed what you were checking in your isnull function.
remember you cant check to see if something is null in a form that is not open yet

one last thing
change
If Not IsNull(qryManualRegistrations.ManualID) Then
to
If Not IsNull(ManualID) Then

if you dont it will prob error telling you qrymanualregistrations is an invalid qualifier

Works Great....except for one problem.  The form opens, but I cant edit any fields.

No error.  
Allow edits = yes
Enable Yes
Locked No
If the recordset is a query, will the form still open for editing?
Just for kicks, I changed the record set to the table and it works.  Change it back to the query and no work.

thats right change just the edit form to the table.

that creates a problem because I want to change fields outside the single table.  How could I accomplish this?

Here's an example:

The source table refers to a TitleID instead of Mr. Mrs. etc.  So, I have a join of TitleID to Titletable.

Thanks