Results 1 to 10 of 10
  1. #1
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7

    Access 2010 Office365 Web Database

    I am brand new to this forum and am hoping someone will let me know if I am on the right track with my office365 Sharepoint web access 2010 database. I have created an access web database that has 4 tables currently with lookup fields being used to have relationships for forms and reports.

    The lookup field instead of the classic relationship table linking is really strange to me but I have forged ahead. My first issue is that I have imported raw data from an excel spreadsheet into a new table. The problem is that I that the field listed as TANK_1 was imported from excel. That field is just a numerical field instead of the lookup field I created to the left called TANK_ONE. This field performs a lookup from the tank table. Is it possible to take field TANK_1 and have it change to the lookup field with the 201334 value? Otherwise I have to individually select for 500 records each tank which will take forever.

    Click image for larger version. 

Name:	Tank Issue.png 
Views:	3 
Size:	30.9 KB 
ID:	10851

    My second question is on the form setup and the way it shows on the sharepoint site. On the very bottom of the page there is arrows and save and create new record etc.. Is it possible to take these out and once the field user hits the save record button, they cannot go back and change the record. I am worried that the field user might start hitting those buttons on the bottom and change old records.

    I would like the field user to hit save record and then that record is not editable by them after the fact.

    Hopefully this makes sense.

    Click image for larger version. 

Name:	Form Issue.png 
Views:	3 
Size:	11.3 KB 
ID:	10852


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't really understand the first question. Is the TANK_1 value the foreign key field? Are the tank number values the primary key in Tank table? If so, the TANK_ONE field is not needed. Set up the lookup on TANK_1 field.

    Do you want them able to see all existing records?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7
    The TANK_1 value is just a numerical value that was part of the excel upload. My understanding of the web database limitation for upload to sharepoint is that you have to use the primary keys that access creates. So I don't believe there is anyway to change primary keys on a web database. TANK_ONE is another field I created that looks up the tank values from the tank table which is linked to other tables by another field lookup. I was just hoping I could just change the TANK_1 field to a lookup field and it would know that 201334 is an acceptable value.

    On the second part, I do not want the field user to see the records. I would really like just the top portion and with only the ability to input new data. My worry is that our field worker is accidentally on record 27 and is messing up the database without knowing it.

    Thanks for your help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is the form DataEntry property available in web design? That will not allow existing records to display on the form, only records created in the current session are available and the form always opens to a new record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7
    Yes that did it, thanks for the help. Now one other question, on my form I am having a heck of a time getting the right setup for the different tanks. On the screengrab below you can see that TANK_2 is referencing the TANK_TABLE, but it is a combo box selection. I would like the three tanks, TANK_1, TANK_2 AND TANK_WATER to stay fixed on the numbers shown, that are referencing the tank table. So when the field worker inputs the data for the day I would like her to only do data entry on T1_FEET, T1_INCHES and not have the ability to select different TANK_1 numbers from the dropdown list, I would like it to be fixed to just 201334 for TANK_1 and 201335 for TANK_2 and 1121400W for TANK_WATER.

    Thanks again.

    EDIT: forgot the image.

    Click image for larger version. 

Name:	Form Issue 2.jpg 
Views:	4 
Size:	48.1 KB 
ID:	10857

  6. #6
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7
    June7 is this idea on my prior post a possibility in the form style? So the field is fixed to a tank number and not selectable? I attached a screengrab showing the issue. Thanks again for the help.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Make it a TextBox, Locked Yes, TabStop No or make it Visible No.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7
    Thanks June7. I needed to enter the default value as 1 instead of the tank number itself as it was referecing the TANK_1 table ID field instead of the lookup field. Thanks again for your help in the matter.

  9. #9
    jhts is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2013
    Posts
    7
    Just when I think I have got this thing working I fail again. My new problem is that I have a lookup field called TANK_NUMBER that references the TANK_TABLE. You can see the actual values on the screenshot below. Yet when I run my report it is now showing the primary ID key that access assigns instead of the actual value. This is shown in the second screenshot. This wasn't happening before I saved a local copy of the database and then relinked it back to the live sharepoint page. I have looked at the properties and can't seem to get the value to change. Any help would be greatly appreciated.

    Click image for larger version. 

Name:	Lookup Field Issue 2.png 
Views:	4 
Size:	12.2 KB 
ID:	10985

    Click image for larger version. 

Name:	Lookup Field Issue.png 
Views:	3 
Size:	14.8 KB 
ID:	10986

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Include Tank_Table in the report RecordSource so the related fields will be available. Bind textbox to field from Tank_Table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. MS Access 2010 web database and Sharepoint 2010
    By sandeep23 in forum SharePoint
    Replies: 0
    Last Post: 06-05-2012, 11:44 AM
  2. Replies: 1
    Last Post: 01-05-2012, 02:34 PM
  3. Access 2010 Database Problem
    By RayMilhon in forum Access
    Replies: 5
    Last Post: 11-28-2011, 03:04 PM
  4. Access 2010 Database Documenter
    By Julie417 in forum Access
    Replies: 1
    Last Post: 08-12-2011, 06:24 PM
  5. Saving Access 2010 database to Access 2007
    By Bajaz001 in forum Access
    Replies: 2
    Last Post: 04-11-2011, 12:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums