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

Can someone help me with the following:

The user is viewing records in datasheet view and wants to select a record and then open it in a form for editing, what's the best method?

Thanks,
OnsiteSupportAsked:
Who is Participating?
 
conagramanConnect With a Mentor Commented:
Make sure the forms properties are set to
Allow edits = yes

Also make sure the properties of the fields you want to edit are set to
Enabled = yes
Locked = no


That should work unless you are getting an error.
If you are getting an error let me know what number it is and I’ll try and help.
0
 
conagramanCommented:
a good and easy way would be using a "split form"
0
 
hello_everybodyCommented:
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

Open in new window


conagraman:

Split Forms are a new feature in Access 2007, the question is about 2003.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
peter57rCommented:
(No Points) the datasheet view must be a FORM datasheet and not a table datasheet.
0
 
OnsiteSupportAuthor Commented:
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
0
 
hello_everybodyCommented:
No, you have to copy the code to the on click event of every txtbox on the form.
0
 
OnsiteSupportAuthor Commented:
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.
0
 
conagramanCommented:
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]
0
 
conagramanCommented:
note that the onclick event is only fired when you click on the box on the left of the record
0
 
OnsiteSupportAuthor Commented:
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

Open in new window

0
 
hello_everybodyCommented:
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].
0
 
conagramanCommented:
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
0
 
conagramanCommented:
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
0
 
conagramanCommented:
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

0
 
OnsiteSupportAuthor Commented:
Works Great....except for one problem.  The form opens, but I cant edit any fields.
0
 
OnsiteSupportAuthor Commented:
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.

0
 
conagramanCommented:
thats right change just the edit form to the table.
0
 
OnsiteSupportAuthor Commented:
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.
0
 
OnsiteSupportAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.