Open form to specific record

[Two Gun]

I'm working with Access 2010 and from a report, I want to use the On Click event with the "Title" control (on the report) to open my data entry form to the same record associated with the Title control.

I'm using a macro:

Form Name: Data Entry
View: Form
Filter Name: Blank (no entry)
Where Condition: [Position Number]=[Reports]![SRC Report (Bureau)]! [Position Number]
Date Mode: Edit
Window Mode: Dialog

This has worked and is working with a similar database, but when I click on the report control in this DB, the data entry form opens, but to the first record in the data set, not to the specific record where the [Position Number] fields match. Probably going to be one of those "duh" moments, but appreciate any help with this.

[June7]

I don't use macros, only VBA. VBA easier to debug. However, the code looks okay to me. What could be different between the two projects that causes this one to fail?

[hertfordkc]

Two guesses:
1) there is a reference ambiguity in the where condition
2) you need to requery

[Two Gun]

I've compared the two DBs and can find no differences. I've compacted & repaired the bad one with no success. I've looked at both report properties and form properties, with no apparent differences. I checked the references in each DB; they're identical. I don't have a clue why this simple macro does not perform in one DB and works flawlessly in the other ... and the "anguish" grows.

[June7]

You tried the 'bad' one on other machines?

Convert to VBA, see if that works better.

If you want, provide project for analysis.

[boblarson]

So you are using the Click event or Double-Click event on a report to open the form? First off, you might want to just make sure that it is passing the correct value you think it should. Substitute a macro to display a message box and the field.

Also, make sure that you do not have the text box name the same as the field name on the report. If the field name is [Position Number] then name the control txtPositionNumber or something like that (best to avoid spaces or special characters in names or field names really).

So once the text box has been changed, see if your original macro then works because it needs to reference the field but it may be failing because the field and text box are named the same.

[Two Gun]

The control name and the control source were the same. I changed the name of the control and wouldn't you know it ... it works. Thanks guys for pointing me in the right direction.

[boblarson]

The control name and the control source were the same. I changed the name of the control and wouldn't you know it ... it works. Thanks guys for pointing me in the right direction.

Yeah, just a little extra info about that -

Additionally, when using any formulas in a control source on a report, you should make sure that any of the controls bound to the individual fields are named something other than the name of the field or else you will get #Name as an error in the control.