Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Room Assignment Database (2003)

    G'Day mates!

    Every year I have to create a whole new room assignment picture via Corel Draw. This is pretty nasty work since I have to color each room seperately and add some text to the room's description.

    While playing around with access I had an idea popping in my head: why not have access do all the work?
    Please take a look at the attached database. I think you should get the idea what I want. The final result will have colored backgrounds for the rooms, roomnumbers and room's descriptions - no need to help me there, all I need is the start.

    Please take a look at the code in frmDiagram. I somehow need to create a function which runs through the DLookup for every i in 123 to 140... My problem is that I can't have something like:

    For i = 123 To 130

    "txtBox" & i & ".Caption = DLookup("RoomName","tblRooms", "RoomNumber=" & i)

    Next i

    Since the text boxes, squares and everything will have numbers which are represented in tblRooms there must be some way to just have a simple function...

    Hope you can help
    Dennis

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Room Assignment Database (2003)

    > G'day mates!

    Are you in Australia now?

  3. #3
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    I wish. I hung out too much with a buddy from down under in Denmark the last weeks. [img]/forums/images/smilies/wink.gif[/img]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Room Assignment Database (2003)

    Use Me.Controls("txtBox" & i).Caption = ...

  5. #5
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    Awesome. Works! Now let's see what I can come up with...

    I hope this is a useable solution... My users just can't handle all that clicking in Corel Draw so maybe a solution like this could help them... Design limitations apply now, but still - better that CD.

    Dennis

  6. #6
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    I'm almost thinking I have to work with ADO Recordssets....

    My problem: I want to run that DLoopup command for all records in tblRooms, but ONLY IF tblRooms.Available = True

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Room Assignment Database (2003)

    Recommend revise form Load event as follows:

    <code>Private Sub Form_Load()</code>
    <code> Dim n As Long</code>
    <code> ' Dim test As TextBox 'it's a label not a textbox....</code>

    <code> For n = 123 To 130</code>
    <code> ' Need NZ because if Available is F, DLookup returns Null</code>
    <code> ' Cannot assign Null to label caption</code>
    <code> Me.Controls("txt" & n).Caption = Nz(DLookup("RoomName", _</code>
    <code> "tblRooms", "<!t>[RoomNumber]<!/t>=" & n & _</code>
    <code> " AND <!t>[Available]<!/t>=" & True), "")</code>
    <code> Next n</code>

    <code>End Sub</code>

    Note error will occur if do not account for Null values with DLookup (which returns a Variant, label caption can only accept string value).

    HTH

  8. #8
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    ahh as easy as that. thanks Mark!

  9. #9
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    Hmm ok. I pretty much finished the draft database. (Take a look at attached example).

    Dont need much more than that I have to say - BUT

    Is there any way to Export either the report or the form to other Microsoft Office applications? The best would be if I could export the Report as a .png file.
    I can print off nice looking room plans with Access, but I can't put 'em into a Word document for a letter or in a Microsoft Publisher project for a guide that is needed.

    If I can't export the file to some other format I'm pretty much lost and I guess I have to stick with the usual Corel Draw way, cuz the Room Plan will be on some random page in a guide created withj Microsoft Publisher each year.

    Any suggestions welcome

    Dennis

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Room Assignment Database (2003)

    If you don't have it already, download and install the free Snapshot Viewer for Access 2003, 2002, 2000, and 97.
    Export the report to snapshot format (*.snp). This preserves the formatting of the report, including graphics.
    In Word or Publisher, select Insert | Object..., click Create From File..., then Browse...
    Open your snapshot file.
    You'll probably see only part of the report. Right-click the object, and select Snapshot Viewer Object | Edit.
    You can now resize and scroll the snapshot of the report.

  11. #11
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    I've tried using that before. Working not bad but u always have a black border around the report (instead of white). This can only be changed by little scrolling of the report - nasty work.

    Besides that - nice solution and works (at least in MS Products).

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Room Assignment Database (2003)

    Microsoft doesn't offer another way of exporting a report complete with graphics.

    The only other export method I can think of is PDF.

  13. #13
    2 Star Lounger
    Join Date
    Jun 2004
    Location
    L, Schleswig-Holstein, Germany
    Posts
    184
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room Assignment Database (2003)

    Yeah printing with a PDF Printer is a possibility. I think I have to stick with SNapshot files.

    Thanks Hans

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •