How to add a command button to update a current record found

[mchung77]

Hi all,

I am on Access 2007, and know very basic stuffs to create tables, queries, and form search. I have just successfully completed a search form filtered with a combobox. Also I have a built-in subform within the main form to display other results as well. The display results are based on one complex query (relational query). Now I need to add a command button that would take me to another form to update the current record found. This sounds simple to do, but I am just stumpped. Quick on the design:
- When I search a subject in a main form(subject lists in the combobox), it would populate results below in the main form and also subform would populate other results as well.

Need help with:
1. Add a command button so can take me to new form, but would need to have the current record populated.


2. Once updated, then how do I save it?

Any advise is greatly appreciated.

[June7]

1. Try using WHERE CONDITION argument.

2. The edit will be save to table when the form closes. Might want to refresh the first form.

Try code like this in VBA procedure for button Click event.

DoCmd.OpenForm "otherformname", , , "ID=" & Me.ID, , acDialog
Me.Refresh

[mchung77]

Thanks for the quick reply. I am kinda clue less at this point. But on your explanation and your codes, I think I need to create a new blank form to to reference the command button to it, is this right?

[June7]

You want to open 'other' form for editing the record selected on first form? Create a form with a RecordSource of the data you want to edit. The code will open 'other' form filtered by the ID. If the ID is in record of subform, must reference subform in the code syntax.

"ID=" & Me.subformname.ID

Why can't you just edit the data on the form/subform?

[mchung77]

Yes, I want to open other form to edit the record. The current search form data is feed off the query and for some reason i am not able to edit the data as I have tried. Ideally, I would like to create a new edit form to edit record based on the query, so I can pull all the fields together, can I do that?

[June7]

If the form doesn't allow edits then the problem is the query the form is based on. Won't help to have another form based on the same query, still won't be editable. Culprit is usually INNER JOIN.

Show the SQL of the query or provide db for analysis. Follow instructions at bottom of my post.

[mchung77]

Here is a copy of my db. It has very few dummie data but it's good enough for testing.

[June7]

Yes, I think INNER JOINs are part of the issue.

Another part is the Document table. Remove it from qryTopic. Subform holds the related Docment records.

It is okay to display the related employee and title and course info but should not allow edit of that info in this form. Set those textboxes as Locked Yes and TabStop No.

Now can have controls on this form to edit the Topic table records, such as comboboxes to select employee and course ID's.

Your macro only goes to the first record that meets the topic criteria. Could there be more than one for each topic?

[mchung77]

Hi,

I am still lost. I did what you said. I created a cmd button that takes me to an update form page (also created this update form), and in the update form, I set the employee field to combobox but once I select a different employee, it won't allow me to change the record. The employee combobox is point to employee table. Am I missing something here?

[June7]

Post 8 describes approach that does not open another form. The combobox to select employee would be on the 'first' form if set up to allow entry/edit on that form, opening 'another' form not involved in this approach.


If you use 'another' form, do not allow selection of employee on that form. That form should already be open to the desired employee by use of the WHERE CONDITION code described in posts 2 and 4.

[mchung77]

ok, so I think we might have the wrong start. what I want is to have the first form to search for a record based on Topic filtered (this form should not be editable, snapshot only). Then once the record is found on first form, the I want to hit a cmd button and take me to the second form so I can edit the record such as employee name, Training course and etc. What's successful now is that my first form is able to search based on the filter of Topic and I am now able to create a cmd button that takes me to the 2nd form. What's not successful is that I mirrored the 2nd form like the 1st form so the user can see exact same set up and edit, but now the 2nd form can't be edit because of inner join query which I understand. Now, what do i need to do to make sure the 2nd from is editable? I have created the combo box for the employee names... just need yoru input to guide me thru.

Note: Let's not worry about the first form as it is doing what its suppose to do (search for record based on topic filtered) unless it's a show stopper to edit the 2nd form which in this case I don't believe they are associated. and cmd button is working fine following the wizard taking me from first form to 2nd form.

[June7]

You stated in post 5 you had tried to edit data on the 'main' form so offered ideas on how to accomplish that.

You are filtering on topic but want to edit employee name? Do you really mean you want to edit the training records associated with a topic?

Possibly what you need is a form/subform arrangement. I just don't know your data well enough, but certainly need to eliminate the INNER JOIN as RecordSource. http://office.microsoft.com/en-us/ac...010098674.aspx

[mchung77]

AMS Matrix_TestForm.zipBasically all information should be able to update based on the topic searched. The editable fields are employee names (phone and email change based on the employee name selected), Training Course, Comments, Documents. I understand this cannot be done via inner joint of query. So, what is the proper way to do this and at same time to have the same fields show on the form. I made a few changes on the access. If you open frmTopicSearch filter one of topic and click on Update button, then it will take you to the update form to update record. this is where I am having trouble to update this form. What is the best practice to design this update form so I can update all the fields based on the topic selected.

[June7]

Correction to my statement in post 12 - forgot I had seen your database from earlier download.

The qryTopic is now editable but still should not include table Document.

I still don't understand need for the second form. It is essentially identical to the search form. However, neither is properly set up to enter/edit records.

Should not try to edit multiple tables in one query/form.

If purpose of form is to enter/edit records of Topic, then need to bind controls to the fields of Topic, not the fields of the related lookup records. If you want to edit the records of Employee table then use a form bound to Employee table. Right now you have comboboxes bound to the EmployeeName fields of the duplicate Employee tables in the qryTopic. Changing this value will cascade to ALL Topic records that employee record is associated with because this change is to the EmployeeName value in Employee record, not the foreign key fields in Topic. So I repeat, do not allow edit of related lookup records on this form.

I NEVER set lookups in tables: http://access.mvps.org/access/lookupfields.htm