Solved

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

Posted on 2011-10-01
19
415 Views
Last Modified: 2012-05-12
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
Comment
Question by:OnsiteSupport
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +1
19 Comments
 
LVL 10

Expert Comment

by:conagraman
ID: 36898130
a good and easy way would be using a "split form"
0
 
LVL 8

Expert Comment

by:hello_everybody
ID: 36898186
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
 
LVL 77

Expert Comment

by:peter57r
ID: 36898793
(No Points) the datasheet view must be a FORM datasheet and not a table datasheet.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:OnsiteSupport
ID: 36899315
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
 
LVL 8

Expert Comment

by:hello_everybody
ID: 36899329
No, you have to copy the code to the on click event of every txtbox on the form.
0
 

Author Comment

by:OnsiteSupport
ID: 36899347
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36899361
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36899362
note that the onclick event is only fired when you click on the box on the left of the record
0
 

Author Comment

by:OnsiteSupport
ID: 36899375
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
 
LVL 8

Expert Comment

by:hello_everybody
ID: 36899419
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36899444
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36899473
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36899511
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
 

Author Comment

by:OnsiteSupport
ID: 36905684
Works Great....except for one problem.  The form opens, but I cant edit any fields.
0
 
LVL 10

Accepted Solution

by:
conagraman earned 500 total points
ID: 36906402
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
 

Author Comment

by:OnsiteSupport
ID: 36906558
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
 
LVL 10

Expert Comment

by:conagraman
ID: 36906859
thats right change just the edit form to the table.
0
 

Author Comment

by:OnsiteSupport
ID: 36906919
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
 

Author Closing Comment

by:OnsiteSupport
ID: 36933220
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question