Parameter Query

[jojo86]

Yay! It works. A message box did appear as I copied over teh FRM_SEARCh:

"Your Microsoft Access database or project contains a missing or broken reference to the file 'OWC10.DLL' version 1.1
*To ensure that your database project works properly, you must fix this reference"

I'm not too sure what it is going on about, but it has only happened the once, and the query seems to work!

Thanks so much for your help. I may end up asking you more questions about searches as your such a great helper. Thank you again.

 

[Michael J Ross]

Glad it's all sorted.

Feel free to ask anytime.

 

[jojo86]

Well then, I shall! lol. (Btw, I am posting this up as a different thread now, just in case your sick of me! lol)

It is regarding another search button, well, more like an open form button.

If you see for the GIS tab, it doesn't link with the main form (which it should) and I couldn't get my head around it. I tried it for ages and ages but nothing was working. However, this morning I suddenly got an idea by using a linking table... I don't think I need to tell you everything about it as it works now, but not on the main form.

So instead, I am going to have a button on the form that allows the user to click it and it shows the GIS (Objectives really) that relates to the site. Thing is, I don't know how to do it. I am wondering whether having it open the form, but including a where clause in it. For example:

DoCmd.OpenForm "FRM_GIS" where GIS_OBJ.Link2_ID = "'" & [FRM_PRIMARY.Link2_ID].Value & "'"

Or something like that. It needs to show the objectives and measures for that site only, and not be able to show other sites objectives.

I have added an up to date DB for you.

Another question that relates to this. If I do this idea (which is my only option), I have to take it off the GIS tab (as shown in the db attached). Shall I just take it off the FRM_SEARCH_P form too, or copy and paste FRM_PRIMARY? I don't know if that will muck all your hard work with the RTP and Postcode searches...

 

[Michael J Ross]

Hi,

I hope you've had a good weekend.

Work got in the way yesterday afternoon so I couldn't answer.

I've had a look and to be honest i don't think you needed the link table so I've deleted it. In the GIS_OBJ Table I've replaced the Link 2 ID with the RTP_ID.

I've amended the qrySERACHGIS so it picks up the RTP_ID from the Primary form and set the qrySESARCHGIS as the record source for the GIS Form, this seems to work correctly and only brings back the GIS records for the selected RTP.

I've created copies of the above queries and forms to work with the Search results form, again it seems to be working correctly.

I've also edited your relationships for the changes made also.

Have a look and see what you think.

 

[jojo86]

My weekend hasn't gone too bad, and I understand about the work thing, I really appreciate your help though. You are a complete star!!! Thank you for this! It does work, it's brilliant.

There are some other questions I need to ask. The GIS button on the search and primary forms work brilliantly, yet I would like to make it a little better. At the moment, it is using the ID for the table as the record set, meaning when I click "Next Objective", it scrolls through the ID. Is there any possibility to change this so that it scrolls through the Objective Number instead (which is OBJ_ID, not ID in GIS_OBJ)? I ask this because when they scroll, they have to click it many times to through to the end.

Another question. When I add a new site, will this automatically add a new ID number to the objectives, or will that have to be put in manually? The same with Measure, when a new Objective is added, will a new measure ID be added? It's ok if they aren't, as the user will not be adding anything to this table, only me.

Again, thank you so much for this, you are a complete star and I don't know how to thank you any more without saying thank you a lot!

 

[Michael J Ross]

Hi,

I've fixed the scriolling for you by amending the query that is used to source the form.

Something I think may be wrong unless I've minsinterpretated what you are wanting to do is, you are not scoring measures for each site, for each site you look at the measure scores the same as the measures are not held by site but only by objective. Is this correct?

I've attached what we have at the moment.

 

[jojo86]

I think I should have posted this up before you amended the copy you have - I have updated the GIS_OBJ and MEASURE table so that each site's measures are scored individually. I figured a new thing to do (introducing ID's like you have in yours). The OBJ_ID is now an ID, and OBJ_NO is the objective number, same with MEASURE_ID and MEASURE_NO.

Please see attached with what I have done. Would you be able to tell me what you did to amend the query? Also, as the objectives are somehow linked to each site (finally), can I have the form as part of the main form, like before? I would like the user to not need to have more than the main form open to view all information for one site. If it isn't possible, I can definitely keep it as it is!!!

Thank you for this

 

[Michael J Ross]

Please see PM