Need help with an access web query

[markarmer]

I have a simple access 2010 database which is used for printing labels. I built the database using the information Allen Browne wrote at: http://allenbrowne.com/ser-39.html (I used the 'Better Solution' as he calls it) and it works great for a standalone database.

I now have the need to make the database accessible through a browser and have succesfully published the tables & forms to Access Services (on a shareopoint 2013 server) but I am stuck with the queries...

The first predicament I have is that Allen's solution requires a Cartesian Product query, which I am unable to create in a web query as the web queries require a relationship between tables.

The second problem is that after printing I use an 'Update to' query to set everything in the 'Quantity' field (quantity is how many of each label needs printing) to 'Null' (zero would also be fine) and again, I am unable to create an Update to Web query to reset everything in the entire column.

Is there any work around or alternative to these two queries that I can impliment for a web database within access? I am VERY much a novice when it comes to Access so as much detail as possible would be grately appreciated if there is a solution I can impliment.

Thank You in advance for taking the time to read this,

Mark

 

[Beetle]

The second part's not hard. Just use a data macro on the table that contains the data for the labels like the following;

Then run it from the click event of a button on your web form;

As for the first part, I'll have to play around with that and get back to you. Not sure off the top of my head.

 

[markarmer]

Thank you very much for replying - I just tried you rrecomendation with the data macro however I'm getting an error seemingly from not putting anything in the Alias field?

I attached a screenshot:

 

[Beetle]

Hmmmm. My example as posted was tested on my end and runs just fine with (as you can see in the example) no Alias in any macro action. Can you post screenshots of your macros? Or, if you have a sanitized version of your app you can upload I can look at it.

 

[markarmer]

Attached are two databases - The standalone is my working Access database that works offline, it is what I am trying to recreate in a web database that I can publish to Access services.

The second is the beginning of my web database, it just has a couple of tables, a form to run the data macro you suggested and the macro itself.

The first will give you an idea of what I am trying to do.

Thank You verry much for your assistance.

Mark

 

[Beetle]

As far as your data macro to reset the Quantity field goes, I can't see your tables because they are on a Sharepont site, so I can't look at it. I do see that you have an embedded macro in the click event of the command button on your form that has the action to run a data macro named queryResetToNull. Did you create a data macro and give it that name, or are you trying to run an actual query? If the latter, that won't work.

I have attached a sample db so you can see how this works. In order to create and/or edit a data macro, the table has to be open, then you go to Table Tools/Table/Named Macro/Create (or Edit) Named Macro (in case you didn't know that already).

As far as your issue with how to repeat the labels, I didn't have time to sort that out today. I will look at it more, but it will be next week before I can do that because I won't have A2010 available over the weekend.

 

[markarmer]

Thank you again for the reply - I will be home in a couple of hours and will try the DB you sent me.

In answer to your question, yes, I opened the Labels database and created the named macro you setout from the 'Table' tab in there, then set the form button to run it.

I will try publishing your database to the sharepoint site to see if maybe that is where the problem lies in mine not working.

Thank you again, have a good weekend.

Mark

 

[markarmer]

Actually I just had time to publish yours... it works as intended so I'm not sure where I went wrong. I will play with it moreover the weekend