Changing a value in a Recordset

[sogibear]

Hello,
I'm passing a control into a function because i will need to run this function many times over. i can access the name of the control quite easily, the name of the control is the same name of a field in a table. See Below :
__________________________________________________________
Function ClearActivityFromProgramme(ctl As Control)
Dim TimeSlot As Variant

TimeSlot = ctl.Name

ctl.value = ""

rstprogramme!TimeSlot = 0
End Function
__________________________________________________________

Problem is that this statement 'rstprogramme!TimeSlot = 0' makes the computer think that i'm trying to access a field called Timeslot, i'm actually trying to access a field whos name is stored within the variable Timeslot, is there a way i can deferentiate between the two ?

thanks

 

[JeremyNYC]

Does it work if you do
rstprogramme(TimeSlot) = 0
instead?

Also, why is TimeSlot a variant? Why isn't it a string?

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[GComyn]

Is 'rstprogramme' a recordset or a form?

If it is a form, use rstprogramme.controls(TimeSlot)

If it is a recordset, use rstprogramme.fields(TimeSlot)

GComyn

 

[sogibear]

Thanks guys, i'm using a recordset.
I've tried
rstprogramme.fields(TimeSlot)

but i get a 'Compiler error' saying 'Invalid use of property'.

It seems that that this property is Readonly ??

 

[GComyn]

Ok.. where are you getting the recordset? how is it being passed to the function? Is it a global recordset?

you could try to be more specific with what property you are setting.

Like:

rstprogramme.fields(TimeSlot).value = 0

GComyn

 

[sogibear]

Yes, the recordset is created and opened in the forms_Open event.

Unfortunately i get an error that says, "Item cannot be found in the collection corresponding to the requested name or ordinal" when i use this :

rstprogramme.fields(TimeSlot).value = 0

I think that the system still thinks that 'TimeSlot' is the name of a field in the recordset as opposed to a variable containing the name of the field in the recordset.

cheers for the help, i'm fresh out of ideas though

 

[JeremyNYC]

Sub ReferToFieldInRstWithVariable()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSql As String
Dim strField As String

Set db = CurrentDb
strSql = "SELECT CompanyName FROM tblCompany"
Set rst = db.OpenRecordset(strSql, dbOpenSnapshot)
strField = "CompanyName"
MsgBox rst(strField)
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing
End Sub

This code certainly works for me, which convinces me that there's nothing wrong with this way of refering to a field in a recordset. When you walk through your code, what is the value of TimeSlot? Are you positive that the control is named exactly the same as the field in the recordset? (I personally never do that, but that's just a preference thing.) Are you sure that you're only looking at the controls you want to, and not including command buttons or anything like that?

Also, do yourself a favor and change that variable to a string--it will eliminate all sorts of possible failures.

Jeremy Wallace ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.AlphaBetCityDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[sogibear]

Hello Guys,

Thanks for all your help and you're all right !

I've been an idiot, the problem was not with the way i was referencing the field in the recordset, the problem was that the control was NOT named exactly the same as the field in the recordset, the control's called 'cboMonAM' and the field is called 'MonAM'. i shall use a little string function to chop the first 3 characters of the beginning of the control's name and that should be that.

DOH !
Apologies all round
Thanks again