Hyperlink in report to open form.

[dwcrockford]

Access 2013

I have designed a form for invoicing - it is set to begin a new record on open.

I have also designed a report which outlines invoices (all or outstanding).

On the report, I have given the "InvNo" a hyperlink format, and assigned an "on click" macro to it that opens the Invoice Form to the selected record. The macro code is

Where Condition: ="[InvNo]=" & [InvNo]
Data Mode: Edit
Window Mode: Dialog

The coding works with one exception. When it opens the form, it goes to "new record", and display results 2 of 2 in the bottom left of the window. If I click "back" arrow, it takes me to the hyperlinked record in the form (the one I wanted). What code am I missing in the macro to disable the "new" part of the form and go directly to the requested record. Can this be done without disabling the "new" on startup of the form when it is used by itself?

D.

Thanks

 

[Russell Hauf]

How do you normally open the form? Just off the top of my head, I can think of a couple things:
- you could try to put the "new record" part of the code in the calling procedure if your users always open the form from say another form or a toolbar
- you could create a custom property on the form, which you can set when opening the form from the report. Then, in your "new record" code, you check to see if this property has been set - if it has, bypass the "new record" code; else run the new record code.

I hope this helps - let me know if you'd like some info on how to create a custom property. And if you do, posting the "new record" code will definitely help.

 

[Micron]

Something else to check:
- make sure [InvNo] is being returned to your macro correctly. If not, there probably is no such record.
- Data Mode overrides form settings for add, delete, edit or entry, so those settings shouldn't affect result - another reason to suspect InvNo value being passed.
- In macros, WHERE is usually passed as a form reference (e.g. [InvNum]=Forms!frmName!ControlName) so the syntax of your WHERE clause may not be resolving correctly (another reason why you might not be getting the record you want).
- you could also try applying InvNo as a filter instead of a WHERE condition, especially if the previous notion is the problem.
- Related to Russel's suggestion, if you can code a bit, your macro can run a sub where you pass opening arguments (OpenArgs) to the form open event (you don't need a custom form property IMHO). So if opened directly, the OpenArg can be whatever, or if opened from a hyperlink, can be "Link" or something you wish to relate to any dashboard form button for example. The form open event in VBA is pretty powerful.

 

[dwcrockford]

Thanks to both of you.

I figured out some acceptable solutions based on your above recommendations.

Thanks
Daryl

 

[Micron]

Good you got it solved. If not too complicated or private, your solution may help others who read this thread in the future so it's nice to see them posted.

 

[dwcrockford]

The easy solution (less coding), as well as noticing there were two different forms needed (initial entry invoice, and follow up invoice). I removed the “new on open” command from the follow up invoice, which is the one I wanted to open from the hyperlink anyway.

But on to a similar (almost exact) situation, but now with the result of missing syntax error.

I have a subset of sales that require to be kept separate from the main invoicing stuff above.
Table: PPTable (ID, Date, SoldTo, Item, etc.)
Form: PPForm (designed from the above table fields)
Query: PPQuery (designed from the table above, specific fields and sorting)
Report: PPReport (designed from the query above)

Everything works fine to this point, so I move on to be able to click the “SoldTo” field in the report to open the Form and go to that particular record (from the table). Same process as in previous posts.

In the report, I assign the “SoldTo” field a hyperlink format, and go to the ******* event.

My macro is:

Open Form
Form Name: PPForm
View: Form
Filter: (blank)
Where Condition=: ="[SoldTo]=" &[SoldTo]
Data Mode: Edit
Window Mode: Dialog

This is the exact same code as used in the previous post (which works), with the exception of the field name (in brackets) is set to [SoldTo] in both spots.
However, now if I click the link from the report, I get the error message:

Syntax error (missing operator) in the query expression ‘[SoldTo]=JoeBlow’ (JoeBlow is one of the SoldTo entries)

Which re-displays itself when I click OK, and then goes to the Macro Single Step window – with error number 2950.

I have double-triple checked that all fields are titled exactly the same between table, form, query and report…. ALL FIELD REFERENCES are set to SoldTo.

What am I missing, and why did the same expression work in the previous post, but does not work in this one. Makes no sense in my limited Access knowledge brain.

Thanks for any help/suggestions – and the simpler step 1, step 2 instructions you can supply is appreciated.

Daryl

 

[Micron]

At first glance, I'd say you are missing quotes around the string value. I suppose the invoice number was numeric, and this one is not. Try
="[SoldTo]='" & [SoldTo] & "'"
Note single quotes in red. This parses to [SoldTo]='JoeBlow'
You need the proper delimiters for any non-numeric data type in an expression (such as # for dates).

 

[dwcrockford]

Perfect - I saw no reference in my "for dummies" collection (including youtube tut's) that the simple addition of single quotes as you outlined is required to parse non-numeric data. Thanks for that little tidbit of info. There are other databases that I'm working on that will also require this little addition.

It works PERFECTLY!

 

[Micron]

Upon further reflection, I suppose delimiter may not be the best term to use since it's also thought of as the character that separates CSV (comma separated value) list members. I didn't have a lot of luck finding any links for you on the subject, which surprises me. Perhaps my terminology is the problem. So here are some pointers I can offer:
- literal text or variables/references that return text must be wrapped in quotes
- literal dates or variables/references that return dates must be wrapped in #
- I avoid using double quotes within double quotes to acheive the desired result. Too messy for me. A malformed string created this way usually results in a truncated version, due to some mis-placed ", resulting in syntax errors.
- if need be, you can use ASCII character codes and the Chr function. However, I've rarely needed it. I believe Chr(39) is the syntax for ' .
e.g. "tblUpdateFlag.ActiveUpdater = " & "'" & dbUser.LoginID & "';" could be written as
"tblUpdateFlag.ActiveUpdater = " & Chr(39) & dbUser.LoginID & Chr(39) & Chr(59)

I cannot think of any other data type besides date that must be wrapped in the proper characters. The whole thing can get kind of hairy for beginners, so I advise the use of aids like temporary debug.print statements to review concatenated string outputs. Or in break mode you can check your variable values in the immediate window by typing (e.g.) ?SoldTo and hitting Return. Note that in break mode, you must pass the line that processes the value of the variable, not be stopped on it. I guess the key thing is to remember the first two points above and you should be able to work through it.

Glad you got it solved!

 

[dwcrockford]

A "glitch" in the above code.

I have found that using the above code (provided by Micron) with the single quote on non-numeric fields works great with one exception. The macro code involved for opening the form to a specific record works fine when they search/record string contains no special characters. In using this code, and the record string contains an additional apostrophe within the record, the code does not work (error message).

This is a portion of a database including song titles.

If I click on the title "Silent Night", the code works wonderfully
If I click on the title "It's the Most Wonderful Time of the Year" I get the message "Syntax error (missing operator) in query expression)

The code works fine on titles that include ?, ! or (), just not ones with apostrophe '

What's the secret to adjusting the code to not give errors if the string contains an apostrphe?

D