Associating Numbers with fields

[Carlo]

I need to calculate the total cost of room hire and catering. To book the
catering I have a table which contains all of the options that can be
selected, with tick boxes for each. I have a similar set-up foor room
booking, with the rooms which can be booked. Is is possible to associate
each room and catering option with a price, so that I can bring up a total
for the, say 10, different catering options selected automatically?

I hope that makes sense

Carlo

 

[Danny Lesandrini]

Where do you store those prices now?

 

[Carlo]

At the moment nowhere. We have hard copies, but nothing on the database,
meaning that the price has to be totted up separately.

 

[Danny Lesandrini]

Well, then let me describe the BAD way to do it first.

Type the price into the Tag property of the checkbox and then run code
something like this ...

Dim curTotal As Currency

curTotal = curTotal + Abs(chkOpt01) * CCur(chkOpt01.Tag)
curTotal = curTotal + Abs(chkOpt02) * CCur(chkOpt02.Tag)
curTotal = curTotal + Abs(chkOpt03) * CCur(chkOpt03.Tag)

The problem with this is that you are storing important pricing info in the
Tag property of checkboxes. Not easy to maintain and impossible to query,
but it would work.

Alternatively, if you write the prices to a table, you could do lookups to
the prices in any number of very elegant ways, but until you have that, it's
probably irrelevant to discuss the options.

 

[Carlo]

Right, well in that case, Ill get them into a table. I assume that a very
simple two field table with "item" and "price per item" would do the trick?

 

[Danny Lesandrini]

Yeah, and I might also add a column named CtlName, into which you populate
the name of the checkbox. So if you had a resource named Projector and the
checkbox was chkProjector, then you could use code like this ...


If Me!chkProjector = True then
curTotal = curTotal + DLookup("[Price]",
"tblPrices","[CtlName]='chkProjector'")
End If

Of course, DLookup is evil and shouldn't be used, but if you had only a
handful of lookups, it wouldn't be the end of the world. Alternatively you
could load ALL the prices into module level variables when the form loads
with a single recordset call and then simply access them as needed. After
running the code below in the Open event, the above DLookup could be
replaced with a reference to m_Projector.

Private m_Projector as Currency
Private m_LaserPointer as Currency
Private m_CaptiansChair as Currency

Private Sub Form_Open()
Dim dbs As Dao.Database
Dim rst As Dao.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPrice",dbopensnapshot)

Do Until rst.EOF
Select Case rst!CtlName
Case "chkProjector": m_Projector = rst!Price
Case "chkLaserPointer": m_LaserPointer = rst!Price
Case "chkCaptiansChair ": m_CaptiansChair = rst!Price
End Select
rst.MoveNext
Loop

Set rst = Nothing
Set dbs = Nothing
End Sub