Where to Enter VBA Code in a query

[Adi44]

Good Morning
My Images.zip
I have joined today in the hope that somebody may be able to help me with a complicated query problem that has been vexing me this week ! First off let me say that I have posted this question on the Access Programmers forum and received a partial solution, here is the link to that conversation
https://www.access-programmers.co.uk.../#post-1819933

This solution allows me to place two records (Images) side by side on an access form. The problem arises when I want to either sort or search the DB. The offered solution is :-
“ If I do everything for you, you will never learn or understand how your app works. but suggest you will need to do the following:

1. Include the sql of the query as your form recordsource
2. I don't use macro's Assuming you are just trying to use the standard filter option of

me.filter="myfield ='" & mycontrol & "'"
me.filteron=true

then use the following (I'm assuming Regn is text)
me.recordsource=replace(me.recordsouce,"WHERE True","WHERE [Regn] = '" & [Regn] & "'")

if you are using the filter parameter of openform, move your filter parameter (i.,e. "[Regn] = '" & [Regn] & "'") to the openargs parameter instead and in the form open event put
me.recordsource=replace(me.recordsouce,"WHERE True",me.openargs)



3. for sorting - you will need a to use controls to to identify which field field you want to sort on and the direction (think how websites work)
and the code would then be something like

me.recordsource=replace(me.recordsouce,"A.myTableP K>= B.myTablePK","A." & ctlSortbyWhat & cltSortDirection & "B."& ctlSortbyWhat)

ctlSortdirection would probably be a combo using a value list to indicate ">=" is ascending and "<" is descending “

I have been reading up on queries and VBA all week, but still do not have a clue how or where to insert this code into the query. I attach a copy of the DB, would somebody please put me out of my misery !!

Best Regards

Adrian

[June7]

You find the SQL of a query object by switching query builder to SQLView.

A form/report RecordSource property can be set to a table name or query name or SQL statement.

"The code" you seem to be referencing is VBA. The code does not go into query, it goes into VBA editor. You were instructed "in the form open event put ...".

Do you know how to create VBA procedure for an event?

http://www.accessallinone.com/wp-con...ion-To-VBA.pdf
https://support.microsoft.com/en-us/...7-70649e33be4f

[Adi44]

You find the SQL of a query object by switching query builder to SQLView.

"The code" you seem to be referencing is VBA. The code does not go into query, it goes into VBA editor. You were instructed "in the form open event put ...".

Do you know how to create procedure for an event?

http://www.accessallinone.com/wp-con...ion-To-VBA.pdf
https://support.microsoft.com/en-us/...7-70649e33be4f

Good Morning

I am sorry but the limit to my access knowledge is creating a table, a query using the wizard and making a form, so any coding or advanced queries are well beyond my skillset, hence the reason for asking for help, I have tried to read several internet sites to see if i could find anything remotely resembling the instructions, but would not have a clue how to start going about it !

Regards

Adrian

[Adi44]

Good Morning,

I am afraid the limit of my Access knowledge is creating tables, forms using the wizard and queries using the wizard. I have tried to read several internet sites regarding queries, but would not know how to start or where to place the query code in order for my form to sort or search. Hence the reason for asking for help., the query that I attached was kindly provided by a user on access-programmers, but alas i have not been able to modify the form as instructed.

Regards

Adrian

[Welshgasman]

@Adi44
You forgot to mention that you had also crossposted at https://www.utteraccess.com/topics/2.../posts/2797828

[Adi44]

Ah Sorry I thought I put both Links together at the top !!

Regards

[isladogs]

What you asked for (originally at AWF) is not really something that a beginner should normally attempt to do.
However, you were given more than one solution at that forum.
Suggest you try the other solution already provided based on a Web browser control.

If you can't manage that either, then I suggest you either put this idea aside until you have the skills to do it yourself or offer to pay someone to do it for you.

[June7]

Query goes in form RecordSource property. Have you ever opened form or report in design view, explored property settings? I advise not to be dependent on design
wizards - I seldom use them as they do things I don't like.

If you want to employ code to manage database, then you need to learn programming concepts and VBA language. You have been provided guidance and help, none of which requires more than basic skill and knowledge of Access functionality to implement.