Sharepoint help!

[NWGal]

Update! After a long, frustrating journey which included MS techsupport (useless), I finally found the answer. I split the db, and linked to a 365 web app using ODBC and now I have succeeded in being able to connect my fe to the sharepoint list (web app) from anywhere I put it.
For those interested in this hybrid method I recommend this blog post, http://www.devhut.net/2014/01/13/how-to ... omment-595" onclick="window.open(this.href);return false; but with the added step that once you have your linked tables, you MUST delete your local tables and rename the linked tables with the former local table names.

Glad you finally figured out the issue - I was about to post to see how far you had gotten. And I did know that you needed to either delete or rename your local tables and give your linked tables the same name as the local tables originally had. I'm curious if you are seeing any performance issues with the tables now residing in the cloud?

No, everything sync instantly. What I am having trouble with is my role subform. I have this set up with code behind the oncurrent event of my main form to use checkboxes as a multiselect list, and when it trys to run, First I got an error message that I need to use DbSeeChanges. I found out how to put that in the correct place, at least the first place, but now it tells "you tried to assign the Null value to a variable that is not a Variant data type". I'm not sure what this means, but I think it has to do with the sql behind my web app not liking all the back and forth between the query and temporary table, so to speak.

[Wendell]

When using DAO to manipulate a recordset, you do need to use that parameter as you open the recordset, along with whether it is a snapshot, a dynaset, etc. The error message you are getting actually means that you have data from a table that is being assigned to a variable in VBA that is empty (Null) but the typing for that variable doesn't support Nulls, that is, it is defined as an Integer, Long, String, etc, and not a Variant. If that's not enough to get you started fixing it, post the VBA you are using, and the line where the error occurs.

[NWGal]

When using DAO to manipulate a recordset, you do need to use that parameter as you open the recordset, along with whether it is a snapshot, a dynaset, etc. The error message you are getting actually means that you have data from a table that is being assigned to a variable in VBA that is empty (Null) but the typing for that variable doesn't support Nulls, that is, it is defined as an Integer, Long, String, etc, and not a Variant. If that's not enough to get you started fixing it, post the VBA you are using, and the line where the error occurs.

Here is the whole code with the dbSeeChanges in blue and the error line in red:

Private Sub Form_Current()
Dim strSQL As String
'clear the temp table, if it exists, to avoid error
strSQL = "DELETE * FROM tblTempCR"
CurrentDb.Execute strSQL, dbFailOnError + dbSeeChanges
'check to see if this is a new record
'if not, check existing data
If Me.NewRecord Then
'disable the subform
Me.sbfrmRole.Enabled = False
'create SQL for the make table query
strSQL = "INSERT INTO tblTempCR (RoleID, RoleName, RoleSelected) SELECT "
strSQL = strSQL & "RoleID, RoleName, False FROM tblRole"
Else
'enable the subform
Me.sbfrmRole.Enabled = True
'recreate the query to find all roles that exist for this person
strSQL = "SELECT ConID, RoleID FROM tblConRole"
strSQL = strSQL & " WHERE ConID=" & Me.ConID
CurrentDb.QueryDefs("qryConRole").SQL = strSQL
'create SQL for the make table query
strSQL = "INSERT INTO tblTempCR (RoleID, RoleName, RoleSelected) SELECT"
strSQL = strSQL & " tblRole.RoleID, tblRole.RoleName, qryConRole.RoleID"
strSQL = strSQL & " FROM tblRole LEFT JOIN qryConRole ON "
strSQL = strSQL & " tblRole.RoleID = qryConRole.RoleID"
Me.sbfrmAsgnInd.Visible = _
(DCount("*", "tblConRole", "RoleID=1 AND ConID=" & Me.ConID) > 0)
Me.btnAdd.Visible = (DCount("*", "tblConRole", "RoleID=1 AND ConID=" & Me.ConID) > 0)
Me.sbfrmAsgnVol.Visible = _
(DCount("*", "tblConRole", "RoleID In (2,3) AND ConID=" & Me.ConID) > 0)

End If
'execute the query to fill the temp table
CurrentDb.Execute strSQL, dbFailOnError
'requery the subform to display the correct data
Me.sbfrmRole.Requery
End Sub

[Wendell]

A couple of questions - does the error on the line you indicate fails disappear if you add + dbSeeChanges to that line, and is the table tblTempCR in the SQL database, or is it in the local Access front-end? On the second point, I think it would be better to have it locally if you are running this for multiple users, as you are likely to have conflicts if it is in the SQL Server back-end.

[NWGal]

A couple of questions - does the error on the line you indicate fails disappear if you add + dbSeeChanges to that line, and is the table tblTempCR in the SQL database, or is it in the local Access front-end? On the second point, I think it would be better to have it locally if you are running this for multiple users, as you are likely to have conflicts if it is in the SQL Server back-end.

Adding the dbSeeChange there does not seem to make a difference. As for the table. I have all the tables in the SQL backend. Are you suggesting that I import that table alone to each of the local users desktops when I set them up?

[Wendell]

If adding that to the .Execute line doesn't fix things, then I think you need to look at your data. I suspect one or more of the records you are trying to put in that table has an empty value (Null) in a field in that table that does not allow nulls. Unfortunately SQL Server error messages aren't always what they seem to be - so it takes some detective work to figure out what really going on, and whether it's really a SQL Server error, an ODBC error, or an error originating in Access.

On the second point, if you want all users to see the same information when they display the roles, then a single table in the back-end works OK. But if each user should see different data based on the information in the main form, then I think you need a local table - presumably a small one. Based on other threads about your project, I have gotten the impression that you are using this form to edit roles - is that correct? And it may be easier to debug the errorat this point by making it a local table - then it is just Access complaining.

[NWGal]

Ah ha! I tried the simplest thing first, and it worked!! I set the temp table as a local table, not linked up to the web app. That was all it took! Now I'll just make sure and import that table on each machine I set up. Thank you sooooo much!!!

[Wendell]

You could try making that a MakeTable query in Access, so you wouldn't have to remember to copy it each time, but the overhead is greater when you do that. Of course you will want to turn Warnings Off whether it is a Make Table or and Append query (like you have now)