Solved

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

Posted on 2011-10-01
19
410 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now