Results 1 to 14 of 14
  1. #1
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6

    Floor Plan Report- Help


    I created an employee database to track our 200 or so employees, and one of the uses is their cubicle locations. Our facilities manager keeps up with a manual floor plan in Adobe, and wants to find a way to merge the info in the database into the PDF floor plan so that when we update employee locations, we can just pull a new report instead of having to manually change the floor plan as well. I have absolutely no idea how to do this. I though maybe I could use a JPG version of the floor plan as a background, then add the info on top. My real question is, how to I insert individual labels and controls so that each cubicle number will be in the right spot with the right person associated with it? Is this even possible? It would need to look like this afterwards. Click image for larger version. 

Name:	CEDEP Floor Plan - (1) January 2017NO Names_Page_1.jpg 
Views:	41 
Size:	168.9 KB 
ID:	27067
    Last edited by goocran7; 01-17-2017 at 01:34 PM.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You would need to build a report ,with text boxes overtop of cubicle images.
    you could paste the map as 1 large image, then add text boxes on top of them for the user.
    then print the report to PDF.

  3. #3
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Yes, you can insert this as an image and then put text boxes over it in the right places. Your record source for the form would be a query with each cubicle number (a text box) and the person associated.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Yes you use the jpg layout as a picture object or background(send to back or keep in back), then you will put field text boxes in the spots you need them on top. I think you can do this 2 ways, hardcode the locations or not.

    1. Create 1 field for each cube location to hold the name (Name4271C, Name4215C, etc.). Use this table as RecordSource for the form and place these field boxes on the form where the names should go. You can only have 255 fields so take that into consideration. If someone changes a location or someone new is hired, just update the table with the correct name.

  5. #5
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6
    But how do I get those control boxes to link to a piece of the data instead of a field itself? The query I'm using has the name field and cube number field. This will stay updated based on the data we enter. So how do I then connect that specific info to the report? Like one record has cube 3.305A and the name A. Dirks. I want that to sit in the exact spot on the floorplan labeled 3.305A. I want this to automatically update names based on the info in the query.

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You need to place unbound text boxes on the form and name them the same as the value for each cube in your table. Not sure if that will work well with the way yours are named but lets say it does. Then in OnLoad event run something like:

    Dim db as Database, rs1 as Recordset
    Set db = Currentdb
    Set rs1 = db.OpenRecordset("YourQuery")

    rs1.MoveFirst
    Do until rs1.EOF
    me(rs1!cube) = rs1!EmpName 'Cube is the name of the field in your table that has the name of each cube. Change it to your name.
    rs1.MoveNext
    Loop
    Forms!MyForm.Repaint

  7. #7
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6
    I'm very new to access so I need this put in plain English. I've searched everywhere and I can't figure out how to make this happen. I have a query with the name and cube number. How do I get that to translate into this floorplan? I don't want to create a new table with each cube as a field name, as that would take forever, and would duplicate the info that I already have available in our main employee table. My query has 2 fields, Name and CubeNumber.

  8. #8
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I explained in post #6. Let us know specific questions as you do those tasks.

  9. #9
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6
    Those terms make no sense to me as that wasn't part of my training.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you can make a pdf form out of your original document, you can link database fields to the form fields.
    check out this thread to see if it's of any use to you https://www.accessforums.net/showthread.php?t=63695
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I will try to explain in more detail, might not be 100% workable but can get you started.

    1. Create a new form
    2. Select an Image control to place on your form(the control is in the Design section at the top). Select your Cube layout jpg file to display. Stretch it as needed on your form. Go to Arrange and select "Send to Back" so that when you add the text boxes they will show on top.
    3. Go back to controls and click on Unbound Text box and add it to your form. Delete the Label part and size it over the "cube" on your picture. Name that text box the same name that is in your table for that cube (3.305C for example if that is the value for the cube name in your table). I'm not sure it will work with numeric and period in the name but try it. Do this with first 3 cubes to test(3.305C, 3.307C, 3.309C). So now you should have 3 text boxes on top of that image control.
    4. Go back to Controls, select the Button control and place on your form but when wizard starts, hit Cancel. Then Right click the button and select Build Event at the top. Now copy the code below into that function:
    5. We only want to test with first 3 cubes so in your query, set criteria on that column that has the cube value to only look at 3.305C or 3.307C or 3.309C. So when you run the query you should only get those 3 records.
    6. Now compile the code(at the top of the code window, hit Debug and Compile, should not give you any errors. Then save the code and go back and Open the form. Then click the button and see if it puts the names in those 3 text boxes.


    Dim db as Database, rs1 as Recordset
    Set db = Currentdb
    Set rs1 = db.OpenRecordset("YourQuery") 'Change YourQuery to the name of your query that has the data.

    rs1.MoveFirst
    Do until rs1.EOF
    me(rs1!cube) = rs1!EmpName 'Cube is the name of the field in your table that has the name of each cube. Change it to your fiend name.
    rs1.MoveNext
    Loop
    Forms!MyForm.Repaint

    So I know this process works as I have used it before to do the exact same thing but you will still have to work out the kinks.

  12. #12
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6
    Ok, I tried what you said and got this message:

    Run Time Error 3265, Item not found in this collection. Here's the exact code I put:


    Private Sub Command64_Click()
    Dim db As Database, rs1 As Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Employee Seating Query")

    rs1.MoveFirst
    Do Until rs1.EOF
    Me(rs1!Cubicle) = rs1!LastName
    rs1.MoveNext
    Loop
    Forms!MyForm.Repaint
    End Sub

  13. #13
    goocran7 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    6
    Ok, I figured out I was using the name of the field on the query instead of the home table, so I changed that and now get this message:

    Run time error 2465: Can't find the field 4211A referred to in your expression.

    I thought maybe it was the period, so I went to the home table and took it out, then changed the text boxes to match, and tried again. It actually put the names in the box, but I still get this message:

    Run-time error 2450: cannot find the referenced form "MyForm"

    What now? Can I just end that error and continue on, or should I update the code somehow?

    Thanks for getting me this far!!!

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    You need to change "MyForm" to the actual name of your form. If you are seeing the names maybe you don't need that Repaint line so can comment it out with ' in front of the line.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-07-2015, 04:52 PM
  2. Replies: 9
    Last Post: 10-21-2013, 02:10 PM
  3. how many fixtures per floor?
    By jammerculture in forum Access
    Replies: 6
    Last Post: 12-23-2012, 03:50 PM
  4. MLM binary plan
    By DARTHSTRIDER in forum Programming
    Replies: 1
    Last Post: 02-14-2012, 09:41 PM
  5. Floor Maps
    By neo651 in forum Access
    Replies: 1
    Last Post: 11-09-2011, 06:31 PM

Tags for this Thread

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