Add an edit button to a datasheet-type subform

mvanella

Registered User.
Local time
Today, 18:38
Joined
Nov 15, 2012
Messages
29
I have a form for our Repair Order form, and it has a subform that displays all of the technicians' hour entries for that repair order. It shows it in a datasheet view, but I would like to add a button to edit each specific entry, as the data comes from a query. I know the theory behind it, I will be grabbing the ID entry and then launching a pop-up form to grab the record by the ID and allow the user to edit the data, then save or cancel changes, etc. But I don't know how to have an edit button specific to one entry on a datasheet so I can get the ID number. I doubt you can add a button to the datasheet itself, but I want the results of the query to be formatted LIKE a datasheet, with an edit button for each entry.

I am using Access 2010

Any Ideas?
 
Last edited:
On te Edit "On Click" event specify or modify the popups record source. If that data is being simultaneous displayed on you subform, the popup record will be locked/non-updatable. You have to move off the subform record and after the popup midification move the subform back to the correct record.
 
Yes mvanella you are right, you can't add a button to a datasheet view, but you can do a trick.

Add a field to the query, for example:
Edit:"Edit"
Drag the field on to the form as a TextBox
Set the TextBox's Is Hyperlink property to Yes
Add a procedure to the TextBox's OnClick Event, which opens your pop-up form.

Then it looks like showed at the attached picture.
 

Attachments

  • Edit-button.jpg
    Edit-button.jpg
    73.2 KB · Views: 2,402
Yes mvanella you are right, you can't add a button to a datasheet view, but you can do a trick.

Add a field to the query, for example:
Edit:"Edit"
Drag the field on to the form as a TextBox
Set the TextBox's Is Hyperlink property to Yes
Add a procedure to the TextBox's OnClick Event, which opens your pop-up form.

Then it looks like showed at the attached picture.

Thanks. I ended up figuring that out last evening when I was dissecting Northwind 2010. Great idea.
 
One problem I just found. If there are multiple entries in the datasheet, no matter which edit I click on, when I retrieve the txtID.Value for that row it defaults to retrieving the first entry on the datasheet. Is there an index I need to specify here?
 
Each datasheet row's index should be hidden, otherwise you'll get the first one, if any.
 
Each datasheet row's index should be hidden, otherwise you'll get the first one, if any.

So then how do I make sure I am referencing only a specific row? I can click on any edit link in the entire column and it will always edit the first row. It's defaulting to the first row. That's not what I want.
 
Select a row, not a column. The index, whether visible or Hidden, must be in the row. It's accessible, whether visible or hidden.
 
I think you may be misunderstanding. I didn't select a column. I was saying that no matter which edit button I click, and it can be ANY edit button in the entire column, it always edits the first row. So it SHOULD be that the edit button in the 3rd column edits the 3rd row, correct? But it doesn't do that, it edits the 1st row. And it's the same for every single edit in every single row (entire column of edit buttons), they all edit the first row. So, how do I specify the index? You say they are hidden, and that's fine but how do I access them?

Do you have to reference the textbox like this:

txtID[4].Value, retrieves the value for the ID textbox in row 4?
 
I have setup a form as datasheet with an "Edit hyperlink" and it giving me the date I expected, (see picture).
 

Attachments

  • ValueDate.jpg
    ValueDate.jpg
    77.9 KB · Views: 1,303
mvanella

Go to design view of the Form.

Add a command Button. The wizard will ask you what want to do. Follow the instructions. Swith to Continious forms and test this Command button. It should do as you wish.

Once this works copy/paste the code behing the command button and adapt it to the edit field. Then delete the command button and switch back to Datasheet.

This is an easy way for you to learn some basic coding.
 
Rainlover, I'm using Access 2010, it defaults to macros when I make the command button using the wizard. Also the only thing remotely close to what I am doing that was offered by the wizard was "delete record" but that essentially just calls the DeleteRecord method after some checks and error handling. It's not something that I can see how to apply to my situation.

JHB, that's exactly what I want to have happen. Here's what I have in my code for the edit click event:

Code:
Private Sub txtEditLink_Click()
    'Grab the ID value and open form with that record
    DoCmd.OpenForm "frmChangeLaborEntry", acNormal, , "ID = " & Me.txtID.Value, acFormEdit, acDialog
End Sub

So my question is, am I treating txtID the right way? Is there some sort of syntax I am missing that makes it always refer to the first txtID in the first row?
 
I think I may have found the problem but I wont be able to fully test it until tomorrow morning. It's a subform of a subform. Now I opened up the middle form by itself, with the subform on it, and it seems to be working. However, when it's used in its intended context of a subform of a subform on a navigation form it might be conflicting with another textbox.
 
mvanella

I have attached a sample Database in Access 2003. It should run under 2010.

Open the Form frmMain. Then in the Datasheet double click in the field 'Six" There are three records so you should get a different result from each. Double Click on the actual Text Box.

The form is not in anyway tidy. I have not named everything properly it is just a sample. Please when you create your own, use proper naming conventions etc.
 

Attachments

Thanks for all the help guys. I am not sure what is/was going on but when I opened the DB today it is working fine. Hopefully the problem is gone for good, since my code uses the same syntax as your example, Rainlover.
 
Lets know if things change.

Good luck with the project.
 
The row "index" is the unique identifier for the row. Is it/they present (hidden or visible) in each row?

Are you selecting a row?
 
The row "index" is the unique identifier for the row. Is it/they present (hidden or visible) in each row?

Are you selecting a row?

"Row Index."

Why do use "Row Index." instead of "Primary Key" which is what is used by everyone else. Perhaps I should say that I have not see it used by anyone else.

An Index does not mean that it is unique.
 
I'm using "row index" to mean "primary key."

Is primary key present (hidden or visible) in each row?

Are you selecting a row?
 

Users who are viewing this thread

Back
Top Bottom