Access web-query problem

[George Tekos]

I wish someone could help me on the following.
My target is to choose a value from a combobox (ok with that) and pass that chosen value as a criteria to a web-query.
When I type to the criteria box>>
=[Forms]![formThatContainsTheCombobox]![NameOfComboBox]<<
and save and close, the thing works ok. The query runs and the report is accurate.
If I published the db on my sharepoint site and try to access it, its is not working. It says somehting about invalid reference to expression. Ofcourse if i removed this expression, the database is displayed properly on the web but of course the query returned all the records (and not filtered by my choise in the combobox).
I swear i been infront of my computer more than three days because of that, so you can tell how useful an information would be.

[Narender Sagar]

I think, instead of creating web application (by publishing), you can export all your tables to the SharePoint, and then link your Access Database tables to the SharePoint. This way, your access database will work just like your other normal database, and your query will start responding properly.
By creating web application (on sharepoint) you can not give name of forms in the query as a ref. simply because all those forms are actually not on the sharepoint site.
regards..

[George Tekos]

Mr Sagar thank you for replying.

So,
1)I will have to recreate my database in a non web-mode..the normal way, right??
2)how do you mean "link the database tables to the sharepoint"?

the point is that i want the users of the db to fill up the forms using the browser..i mean by just opening the browser and the form or something else before the form, pops up...is this possible, by just exporting the tables and linking them??
Do i have to use Sharepoint Designer to create the site, or the button that link to the form??


you understand that there are some basic things i am not aware of, but believe me just a piece of instructions from you and i will make it ! :) thank you very much

[Narender Sagar]

Dear Mr George,
The method is that you can create tables and exported to a sharepoint site as lists (This will be backend for your database). Then you can link the tables to the Access database itself to the sharepoint site. Now you can create everything like queries/forms/reports etc. in the Access Database (which will act as front end for your database), And distribute this frontend. Now you can distribute users the copy of this database for their routine use.
Please note that you need to have MS Access 2010 for this. (I am not quite sure for MS Access 2007)
This way user can have full potential of MS Access database. The beauty of this is that, even when the user is offline or if the site link is down (due to any reason) users can still work normally and the data can be synchronized whenever the link is up.
You don't need sharepoint designer for this. (But if you have it you can o course use it)
Hope I am clear..
best regards

[George Tekos]

you are very clear. I will try this method, and it really sounds THE ONE. I guess it is not a problem for you if i asked you more questions while working on it?!!

In case i made it, i am willing to pay you for the potential solutions you offered me. please, it is something that must be done, so i can feel comfortable. Anyway! thank you again and i will write you again.
George

[George Tekos]

unfortunately i spent some more hours but didnt make it.
Of course i saw how the thing, you described me works, but its impossible to create a web form (which is the only object that can be browser-viewed) even based on a linked table...
i wish i am mistaken..

you see its crucial to me, the users to JUST open the browser and fill up the forms, because some of them will be parents and i cant tell them to download or, set up their access for that..

[Narender Sagar]

Okay, for this you can also use, MS Infopath form. You can create the form and publish it on your sharepoint site. Users can fill the form and data will be stored in your sharepoint database. You can also send Infopath forms through email. And best part is users just have to click reply on their mail (through outlook) and fill up the information right there, and send it to you. Moment you receive the mail, the data will get stored in your database.
Only condition is you have to have Office 2010.
kind regards..
Narender

[George Tekos]

yes. this is another thing i forrgot to tell you. last night , which i spent on my computer :) i was working on infopath. i published a form anyway (after enabling services on server, on sitecollection etc) but the result was as in every tutorial i saw, that the form opens in browser After the user gets in a LIST and presses NEW DOCUMENT (or ADD DOCUMENT)...i mean i would like this WebEnabledInfopathForm to open when entering a site ...(and i couldnt find a hyperlik for that so i put it on a webform, for example!

[Narender Sagar]

Hi Geroge,
If that is only the issue, you can just right click on the "Add New Record" link. Click Copy Shortcut. And you will have direct link to the form. You can circulate it to the users. And it will directly open the form for the users. I just checked it on my sharepoint site and it works perfectly fine.

[George Tekos]

i will explain you.
i have a sharepoint site. i can access it by typing http://server.
on this site i have exported a table from access called MAIN_TAIBLE.
(all site content and it is there).
i open an infopath form. should i open it blank or to create one FROM SHAREPOINT LIST??? probably the second. right?

[Narender Sagar]

Yes, second option is best. You can use infopath for customizing default form (Provided by Sharepoint list) or if you want to send your form through email. But just check, if the default form created by shrepoint list is okay with you, then follow the procedure I suggested in my last post.

[George Tekos]

Well i am working with infopath by the moment. Ive done something like a trick (according to my level of experience) because of infopath form cant open directly when typing the sites url, i created an empty access webDB to take advantage of its "navigation form" ..you know that, right? so in the form i put a photo for the design thing, and a link to the infopath form i created and this form is the one that submits data to the sharepoint list. Unfortunately i cannot get to work the form that will submit data..i have a problem with the query procedure..can you help me on that???
:) thank you friend

[George Tekos]

CORRECTION: the 1st word, of the 6th line, on my previous message is "RECEIVES" :)

[Narender Sagar]

Please write more about Problem in Query procedure. Because just mentioning 'problem' is not sufficient enough.

[George Tekos]

i dindnt want to ask you the question, before i knew how if you wer available..so now i will explain you,
i want to create a form in which i will be able to choose the DATE (from a dropdown box) press a button (that engages the query) and show up the results that refer to the pre-selected DATE value. (to the extra text boxes will have inserted before)...
i know how to make the dropdownbox show the DATE values...the thing i dont know is, HOW TO create a query that its parameter is being passed by the DATE dropdown box...i hope it was clear, and sorry for my english.

[Narender Sagar]

Dear George,
e.g. If you create a form (Dialog, set its Modal property to 'Yes')
Create a Combobox (e.g. Combo1) for DATE.
In your query (design view), You can type following under "Criteria" :

[Forms]![Dialog]![Combo1]

This will show result according to date chosen in the form.
But, your original problem was that when you were doing samething (mentioned above)in sharepoint web database, that time you were facing problem. is that issue resolved?

[George Tekos]

so you probably talk about Access. to set the following parapameter [Forms]![Dialog]![Combo1] means that i have to PUBLISH my whole database to the share point site, first. When I do this (Publish & Parameter) the form is not working on the site.(Im talking about Access).
If I just EXPORT my table to the sharepoint site (so it is not places in FORMS, but as a LIST, i cannot find where to set the parameters from.
1) basic question: Do i have to PUBLISH, just EXPORT table, or both?
2) second Question: after that, how do i create a form that recceives data using a DATE combo(take the existance of the combox, for granted)

thank you very much

[Narender Sagar]

1. First, export just tables to sharepoint site as lists
2. Then create linked tables from the same site
3. Then you can create queries, forms etc. just like a normal database.
4. If you still didn't understand this, pl. let me see your database and I'll guide you further.

[George Tekos]

ive done this before..but..
1)i create a WEBdb and export it as list on sharepoint site. OK
2)open a new WEBdb and right click on the table> import>from sharepoint list OK choose the LINKED TABLE at the next dialog box and the linked table is created OK
3)a))CREATING A FORM: i click ONCE on the linked table, go to "CREATE" tab, choose "WEBFORM">>>cannot create a web form based on a source that is not web compatible...... <<<<<<
b))CREATING A QUERY: i click ONCE on the linked table, go to "CREATE" tab, choose WEB QUERY", opens, the box I choose , tables,queries, all, is empty...
if i choose the NON WEBform or query, it works, but no way to present it in a website, it is not web compatible..
which db should i send you?? the one with the linked table? you wont be able to open it, it asks for connection credentials...

[George Tekos]

if you ever have the time or the will, you can reach my pc through teamviewer, to see directly what i mean...
my mail is [e-mail]
and my cellphone is 00306939527034 (if you missed call me once, i will undestand and login.) i live in greece. UTC+2.

[Narender Sagar]

Okay, Since you are creating WebDb, and web query, So in that case WebDb does now allow to give ref. to a form for passing the criteria to a query.
So here my suggestion is, if you just want to filter the record for a particular date (I believe), then you create a query and under Criteria , right click , then click build, then under Expression Categories, choose desired field. Then click Parameters, write the field name in brackets [your date field name] and choose desired Data type. Click Ok. And Run the query. It will ask user to enter date, and show the desired results.

[George Tekos]

1) what type of query you mean?? Web one? i created WEB.
2)I assume you suggest me to involve all the fields in that query.
3)there is nothing under DESIRED FIELD, and i what do you mean by "choose desired field".??? i know it breaks your nerves but lpz be 100% specific

[Narender Sagar]

1. Yes, I mean Web query only
2. Choose desired field means, you have to Criteria for record filter under the field you want. (For your case it will be a date field for which you wanted to create combo box in a form)
Hope I am clear now. and don't worry I'll answer your queries until you get results.

[George Tekos]

ok . if i choose to create a web query, based on a LINKED TABLE, the query opens there are NO AVAILABLE fields to add, at all....you know, the small window withe the three tabs,that pops up ,after you press the QUERY button..there is nothing inside...so i cannot add fields in the query...at least automatically.

[Narender Sagar]

I would like to see your database, Can you attach it in zip format.

[George Tekos]

i am sending you the database (just before being published) on my sharepoint site

[George Tekos]

hi!! did you get my database???

[Narender Sagar]

yes, I'll get back tomorrow

[Narender Sagar]

Okay, Geroge,
I am attaching pictures for your understanding..
1. Go to design view of 'Query1' (A1)
2. Enter [Date] in criteria (A1)
3. Click 'Parameters' (A2)
4. Give parameter setting (as per picture) (A3)
5. Run your quuery. (A4 & A5)

Hope this will serve purpose.

[George Tekos]

well, it actually works, (havent tried it after publishing though) but in the "enter parameter value" pop-up window you have to type the date yourself ..right? i mean the user has to type down, 12/12/2012 for example. can this change to a date picker box or something?? no way i think.

[Narender Sagar]

Yes, user has to type the date. No, you can't change it to date picker in web query.

[George Tekos]

too bad.. you understand that this solution is not that easy for the users...

[George Tekos]

i really wonder why this function is not working. it sounds crazy, microsoft has not programmed something like this..

[Narender Sagar]

You can at the most write "[Enter Date in dd/mm/yyyy format]" in the criteria place and write same thing in Parameters. This will give users clear idea how to write the date.

[George Tekos]

ha, yes i tried this actually but in my language so it came up with an error somewhere after..i think i will try it in english again..have you checked that the whole thing works on sharepoint , right??? not only locally..

[Narender Sagar]

Yes, I have checked it works on sharepoint.

[George Tekos]

i am creating the final version of the db with the graphics and the rest!!! i have not tried the query trick yet, but if you told me it works, it works.. can you tell me something else please?
in the inputform there is the date picker ok? i want to disable the direct typing on the box, but choosing form the popup calendar. i have also made it Unique and Required but dont know how create my own validation rule, so if someone types something extra or doesnt give a date, a message to show up..is it possible ?? thank you a lot.

[Narender Sagar]

George, as per this sites rules, you have to start fresh thread for different questions. You can close the existing thread, if this serve your purpose. And post a new thread with details, I am sure you will get help from the members of this site.

[George Tekos]

ok i will right now.

[George Tekos]

Mr Narender Sagar, i tried to use the "parameter way" but didnt work. I am using a button that points to a report, based on a query. when i run the button locally, it shows up a parameter window, i type the date and the appropriate results show up. but this parameter window doesnt appear after publishing..can you explain me how you made it work?

[George Tekos]

THE COMOBOBOX IS WORKING PERFECTLY , without parameter input...
it can control the query results now....

i simply cant believe i made it.

speechless