Microsoft Access - Form Code - Asked By D on 13-Jan-12 10:20 PM

I have the following code in a form but all it returns in the field is #name?  What am I doing wrong?

Private Sub Reservations_Amount_Owed_BeforeUpdate()
If Me.Combo70 = "Corporate Table" Then
Me.Reservations_Amount_Owed = 2500
Else
Me.Reservations_Amount_Owed = ([Reservations_Number_of_Tickets] * [Reservations_Ticket_Price])

End If

End Sub

wally eye replied to D on 13-Jan-12 10:48 PM
I see a couple of issues. 

Why would you update the Reservations_Amount_Owed value in the BeforeUpdate event of same?  I would expect it to be updated in an AfterUpdate event for Combo70, Reservations_Number_of_Tickets or Reservations_Ticket_Price, probably a separate procedure called by all three AfterUpdate events.

Second, never, never, never hard-code that 2500 into your program!  Create a tblRate, give it a RateCode, start and end dates and a rate.  Then pass the date of the reservation to a function that returns the amount owed based on a given date.

Something like:

Me.Reservations_Amount_Owed = GetRate("Corp", Me.Reservations_Date)

public Sub GetRate(byval RateCode, byval selDate)

  dim dbCurr      as database
  dim rstRate      as recordset

  dim strSQL      as string

  strsql = "SELECT Rate from tblRate " _
    & "WHERE RateCode = " & RateCode _
    & "AND StartDate <= #" & selDate & "# " _
    & "AND EndDate >= #" & selDate & "#;"
  set dbcurr = currentdb
  set rstRate = dbcurr.openrecordset(strsql)
  if rstrate.records.count > 0 then
    GetRate = rstrate!Rate
  else
    GetRate = -1
  endif

  rstrate.close
  dbcurr.close

Proc_Exit:

  set rstrate = nothing
  set dbcurr = nothing

  exit function

End Function

The Me.Reservations... goes in your form, the GetRate would go in a separate module.
D replied to wally eye on 14-Jan-12 03:19 PM
Sorry wally eye:  My post was stupid and  I apologize.  I found a very simple solution to my problem that doesn't include code, which I'm not very good at.  Thank you.
wally eye replied to D on 14-Jan-12 04:49 PM
It's all good, just take it and run with it.