Double Click current record to Edit

[projectpupil7]

Hi, I am trying to double click on a subform field called Address which would open a form frmEditWorkItem (based on a query qryWorkItem) then to be able to carry out edits for the current record.

This is my code line:

DoCmd.OpenForm "frmEditWorkItem", acFormAdd, acDialog, "ADDRESS = '" & Me.ADDRESS & "'"

This line does open the form but shows ALL records in the table instead of the current one that I have clicked in.

Could someone correct my code to enable me to do what I need.



Many thanks.

[ranman256]

no need to double-click to edit
EDIT is always on.

in your form, perhaps your subform is not linked via the properties: CHILD FIELD, MASTER FIELD so you only see the 1 record.

[projectpupil7]

The subform is linked through ClientID on the main form and the double click event in subform field Address opens frmEditWorkItem (popup property is set to Yes).

[John_G]

Hi -

Not sure if this is the problem, but you have the order of the arguments wrong in the DoCmd. Try this:

DoCmd.OpenForm "frmEditWorkItem", acFormAdd, , "ADDRESS = '" & Me.ADDRESS & "'", , acDialog

I don't think you need acFormAdd either - what was your intent in using it? You end up with:

DoCmd.OpenForm "frmEditWorkItem", , , "ADDRESS = '" & Me.ADDRESS & "'", , acDialog

John

[projectpupil7]

Hi -

Not sure if this is the problem, but you have the order of the arguments wrong in the DoCmd. Try this:

DoCmd.OpenForm "frmEditWorkItem", acFormAdd, , "ADDRESS = '" & Me.ADDRESS & "'", , acDialog

I don't think you need acFormAdd either - what was your intent in using it? You end up with:

DoCmd.OpenForm "frmEditWorkItem", , , "ADDRESS = '" & Me.ADDRESS & "'", , acDialog

John

bACKTRACK AND UPDATE ON THIS.

Further inspection shows that the code does actually work.

If we look at the subform which has the field Address on it there are a total of sixteen records of which two have the same Address value (On Site) in the field and it is the count of occurances of the field value i'm seeing. This is what was throwing me. See screenshot attached.

I still need to be able to edit only the current record though i.e. the record for the Address i click in.

[John_G]

Hi -

In order to edit only the current record, you'll need to use a field (or fields) which uniquely identifies the record (e.g. the PK), instead of the address in the DoCmd.OpenForm statement. The field does not have to appear on the form, but it does have to be in the form's recordsource.

John

[June7]

As John_G says, use unique record identifier as filter criteria. Addresses and names are poor identifiers.

[projectpupil7]

Thanks Guys your suggestions worked ok. However, i have noticed that if the field is blank the double click event doesn't seem to work therefor preventing edits via the pop up form. Is there a way around this ?.

[John_G]

Use the table's primary key (PK) to identify the record. If you don't have a PK, you should make one - your situation is one of the many reasons why every table should have a PK. A primary key cannot be blank (Null) - MS Access won't allow it.

As I said earlier, the PK doesn't have to show on the form, but it does have to be included in the form's record source.

[projectpupil7]

My bad John_G, i didn't change all the code to look for the PK. Having done so now and problem solved.

Maybe this should be a new question but can you give me a clue as to why when i open the pop up form for a new record to be added none of the fields are editable.

[June7]

Post the code that opens the popup form to new record.

[projectpupil7]

Here you are June7

DoCmd.OpenForm "frmAddNewPO", , , , acFormAdd, acDialog

[June7]

That should work, always does for me.

So the next step is to provide db for analysis. If you want to, follow instructions at bottom of my post.

[projectpupil7]

Thanks June7.
The problem is that i don't seem to be able to create a pop up form to edit existing records and add new records. If i place a button on the forms to do this it won't work neither does the navigation new record option. The pop up becomes completely dead to any editing. I would like one for both the tabbed forms if pos. Just feel free to do what you think best.

[June7]

Ah, familiar db. You changed the nasty red color, looks much nicer. Although the grey font still a little hard to read in some button colors, like darker grey and blue.

Just realized you posted db in post 5, sorry I missed.

Clicking Address on ReactiveWorkPO subform opens PO Details filtered to the selected record. I am able to edit that record.

The Complete, Work Ordered, New Orders buttons do not work - there is no code and these are actually label controls - why, what are these for?

The OrderNo double click does not work.

Using acFormAdd along with filter criteria to open form doesn't really make sense, although doesn't seem to hurt acFormAdd is not needed.

Cannot enter new record into frmPO_Details because of the RecordSource query. It has an INNER JOIN to tblClient which requires related records in both tables to already exist for records to display. Change to RIGHT JOIN or remove tblClient. Could even set the RecordSource to tblOrder instead of query object or build SQL directly in the RecordSource. Nothing in this arrangement will save the ClientID into the new record. Will require more code. This query also has a dynamic parameter that references frmReactiveTracker for filter criteria. I NEVER use dynamic parameter queries.

You might consider setting the subform as Continuous View and eliminate the separate popup form and all the associated complicated code.