Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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,
0
OnsiteSupport
Asked:
OnsiteSupport
  • 8
  • 7
  • 3
  • +1
1 Solution
 
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
 
peter57rCommented:
(No Points) the datasheet view must be a FORM datasheet and not a table datasheet.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
conagramanCommented:
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
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now