specifing the record source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Im making a form which asks for the details about a tenant and then
allocates a room
to them on a yearly basis (its for an academicly orientated company). I
almost have it all working, the only thing i have left is to change certain
details depending on what the user chooses in certain controls.

For example if the user sets the [year] to 2005 then the rental charges
should reflect the 2005 levels and not the 2004 levels etc. how do i change
the controlsource to another field in the same table??

Currently im trying:

Me![WeeklyRent].ControlSource = "tables.StudentRooms.05weeklyrent"

but its not working!!

How do i specify a particular field (its part of the query of the
recordsource if that helps)?

with much thanks

Amit
 
Ok, so after realising how stupid i was with the initial post ive had a look
at other posts on specifiying the control source and have sprung upon using
the Dlookup function to set the control source of a textbox on what is
specified in another field.

this has led to the following code:

Dim intyear As Integer
Dim roomreference As String

roomreference = Me![RoomRefer]

Select Case intyear
Case 2004: Me![WeeklyRent].ControlSource = "=dlookup("[04weeklyrent]",
"[student-rooms]", "[Roomreference]='" & roomreference & "'")"
Case 2005: Me![WeeklyRent].ControlSource = "05weeklyrent"
Case 2006: Me![WeeklyRent].ControlSource = "06weeklyrent"
Case 2007: Me![WeeklyRent].ControlSource = "07weeklyrent"
Case Else: Me![WeeklyRent].ControlSource = "05weeklyrent"
End Select

which gives me an 'expected end of statement:' error with the [04Weeklyrent]
highlighted. Its asking me to put in an colon but im not sure where to put
it,

any suggestions would be gratefully received,

Amit
 
Back
Top