Access 2013 Web App with Sharepoint 2013

[miguel.escobar]

Hi and thank you for taking the time to read this!



I'm currently creating an Access 2013 Web App with Sharepoint 2013 and its awesome! The only bad side of this its that I'm stuck in this:

• I have a one-to-many relationships from a dimension tables (one) to my fact table (many side).
• My Dim table is defined by 4 columns
  
  • ID
  • Movie (lookup of another table)
  • Country (lookup of another table)
  • Release Date (dates)
  
  
• and my Fact table has many columns and one of them is the ID of the dim table above.

The thing is, I don't want the user to select an "ID". I want them to be able to do something like:

1. Choose the Country and the Movie and then the ID will be automatically populated
2. Choose from a Concatenation of Country and Movie but, unfortunately, the real text of those fields are located in other tables so I can't create the calculated field.

Any ideas on what I could possibly do?

Thanks in advance!

[Dal Jeanis]

I'm missing something. Do you not have access to the other tables? This should be trivial.

User selects Country from table 1 list, you get id. User selects movie from table 2 list. Query loads third box with all information that matches, which should be either zero or one result, unless the same movie had multiple release dates.

So, what is the issue? I do this all the time in vanilla Access 2003 or 2010.

With two boxes, you have each box, in the AfterUpdate event, call the same routine, that builds the SQL and sets the record source for the third combo box.

[Dal Jeanis]

Are you saying that sharepoint doesn't support joining tables?

[miguel.escobar]

Hey Dal!

I do have access to the other tables. Sharepoint 2013 does support joining the tables but you don't get DELETE or UPDATE queries. Also, the forms changed tons.

The thing is that I'm going with the datasheet view or grid and I'm using this new Web Access 2013 app and that's what I'm trying to strive for (a datagrid)..also that I'm a complete newbie here.

Thanks for the quick reply!

[miguel.escobar]

In short...no, the forms! syntax doesn't work with Access Web App 2013 more about that here:
http://msdn.microsoft.com/en-us/libr.../jj618413.aspx

but if you have any other suggestion it'll be more than welcome!

Thanks again!

[Dal Jeanis]

I'm still not sure what the technical limitation is that you are fighting with.

Choose from a Concatenation of Country and Movie but, unfortunately, the real text of those fields are located in other tables so I can't create the calculated field.

Are you saying that this version of Access has no combo boxes? Ah. Complete newbie. Please look up combo boxes.

Here's a sample join that you could use as the source for a combo box that gets the data from a join. I've left out ORDER BY and WHERE criteria, that will depend on how you code your form. The concatenation will be ugly, but you can tweak the formatting a bunch of different ways.

tblTitle
TitleID   (Key)
TitleText 

tblCountry
CountryID (Key)
CountryText

tblProduct
ProductID  (Key)
CountryID  (Foreign Key to tblCountry)
TitleID    (Foreign Key to tblTitle)
ReleaseDate

SELECT tP.ProductID, tC.CountryText & " " & tT.TitleText & "  Rel: " & tP.ReleaseDate
FROM(tblTitle AS tT INNER JOIN 
       (tblCountry AS tC INNER JOIN tblProduct AS tP
        ON tC.CountryID = tP.CountryID)
     ON tP.ProductID = tT.ProductID;

[miguel.escobar]

Hey Dal!

They say that pictures speak louder than words!



I've used Combo Boxes before, worked with SQL and all type of joins (even many-2-many cases) but the main issue here is that the forms or "views", how they call it in Web apps, do not have all the features that you'd have in your regular Access. The main issue here is that what something easy that could be done with Forms!Parameter1 to be used as a criteria in a query is not available in Access 2013 Web App...apparently, it needs to be done with Data Macro and THATS where I'm nothing but a complete newbie.

I bet that there must be a new way to do what I'm trying to do but I haven't read about it just yet.. =/

btw, that code looks awesome!

[Dal Jeanis]

Ay Caramba!

This is a total shot in the dark, but can you fake it with global variables and a global function to return the global variables?