Room Assignment Database (2003)

Topic

New Reply

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

Reply | Quote

Replies

> G’day mates!

Are you in Australia now?

Reply | Quote

I wish. I hung out too much with a buddy from down under in Denmark the last weeks.

Reply | Quote

Use Me.Controls(“txtBox” & i).Caption = …

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Recommend revise form Load event as follows:

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

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

End Sub

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

Reply | Quote

ahh as easy as that. thanks Mark!

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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

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

Reply | Quote

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

Thanks Hans

Reply | Quote