Feeding data to the right bits

[tassiegal]

I am in the process of setting up a research register for work.
Its fairly simple, in that it has a couple of tables (Investigator, disciple, sub discipline, site, and project info) and a couple of switchboards (essentially Add and search).
I am trying to remember how to set up a report, so that it is generated based on the responses to a form full of drop down boxes. Depending on what is entered in the drop down boxes is dependent on what gets put into the report.
I THINK I need a query in there as well somewhere, but am not sure how to tie it all together.
Its so long since I have done ANYTHING in Access (previous job was sensible and used MySQL and custom designed front end).

 

[vbaInet]

You can use the WHERE clause of the OpenReport method to do this. Have a look at the following link and substitute OpenForm for OpenReport.

http://baldyweb.com/wherecondition.htm

Welcome to the forum!

 

[tassiegal]

Thank you! I think this should work - but I realised the easier way is to restrict the selection to ONE of the variables at a time. Also means I wont get the anguished "BUT I AM NOT GETTING ANY RESULTS" phone calls and emails.

 

[tassiegal]

Ok - so going on my new thinking I need to go
Form with drop down to get where clause ----> feed to query which selects everything in the table and uses the answer from the from to generate the where clause -----> Report based on Query.

But I cant work out which order I need to make this all in....
Am thinking Query, Form, rejig queries, report?

 

[vbaInet]

Did you read the website I gave you?

Form with combo box for selecting a value > report is based on table or query > WHERE part of the OpenReport command is used to filter the opened report based on the selection in combo box.

 

[tassiegal]

Yeah I did. I was getting myself confused - and am still a little confused, but have printed the page out and am going to attempt it later on today. not 100% sure if it will give me what I want as I am not necessarily using summaries (unless you are calling drop down lists the summary).
Will let you know if this works for what I want to do.
Thanks again for your help.

 

[vbaInet]

(unless you are calling drop down lists the summary).

I'm not calling drop down lists anything. You mentioned drop downs in your previous post which would mean you're referring to a combo box, so I just simply used that in my reply.

Let us know if you need some more help.

 

[tassiegal]

I'm not calling drop down lists anything. You mentioned drop downs in your previous post which would mean you're referring to a combo box, so I just simply used that in my reply.

Let us know if you need some more help.

That makes sense. Sorry, its been a long week with panicking students on a Friday making it seem even longer. I have printed out the link you pointed me to, and am going to sit down on monday, with email turned off and phone off the hook and translate it so it reflects the names I am using for things.

For some reason VBA programming is something I find very hard to get my head around, so I need to really stop, concentrate and work through it. I really appreciate the help you have offered. I suspect if I had sat down and actually mapped this whole thing out before I started it, as I was taught to do, I wouldn't be in such a pickle now!

 

[vbaInet]

I suspect if I had sat down and actually mapped this whole thing out before I started it, as I was taught to do, I wouldn't be in such a pickle now!

It's always good to properly plan before you engage in any database development so I second your statement. That said, even with the best of planning we sometimes find ourselves in difficult situations.

I hope you get the time to digest what is explained in the link. Good luck!

 

[tassiegal]

It's always good to properly plan before you engage in any database development so I second your statement. That said, even with the best of planning we sometimes find ourselves in difficult situations.

I hope you get the time to digest what is explained in the link. Good luck!

Thanks, if my high school IT teacher knew I had attempted this without mapping he would have my head! Come to think of it my old boss would have as well. I suspect I have also over complicated things (as is my want). I deleted a table today when I realised it was easier to just have all the disciplines listed in ONE table, as opposed to calling a second table for some disciplines that had sub disciplines.
I am hoping Monday allows me to digest as well, because if it resembles today I may be forced to go ask the pharmacist for some valium.

 

[vbaInet]

I am hoping Monday allows me to digest as well, because if it resembles today I may be forced to go ask the pharmacist for some valium.

I hope it's not going to be anything else other than valium.

Since you have noticed flaws in your design it would be a good design to ask for some advice on your table structure and relationships on the forum. You can create a new thread in the Tables section of the forum and someone will be able to help.

 

[tassiegal]

I might do that, thanks!

 

[vbaInet]

Cool! Welcome to the forum by the way.

 

[tassiegal]

I hope it's not going to be anything else other than valium.

Nah - though working in a rural clinical school based in a small hospital makes the potential to GET valium very high!

 

[tassiegal]

Update - I appear to have worked it out. I ended up applying a where clause to an open report macro button limiting the results to the data from the form the button was on. It also helps that I started using the right tables in the where clauses (I was trying to filter a report which I hadn't generated yet....).
Thanks everyone for your suggestions/advice/code help.

 

[vbaInet]

Just fyi, the Open Report macro is the same thing as the OpenReport command so whichever one you're comfortable is fine.

Good work!

 

[tassiegal]

Thats something I didn't know! Thanks. The macro just made more sense to me for some reason.