Modul for checking the values in the table row

[Schlingel]

Hi!
I have a small problem again. Hope u could help me.
I really appreciate ur help, u know
I want to create the module that must check and control the value of the variable put in the table. Somehow, it checks nothing. Could u tell me why?!
Here is my code:

Public Sub StundenControlle()
Static S As Integer
Static RTable As Recordset
Set DataBase = CurrentDb
Set RTable = DataBase.OpenRecordset("Nachweis")
S = RTable!Stunde 
End Sub

Here i only want to get the value from the table and it doesn't seem to happen, cause the debugger shows me that the value is not definiert. Why?
Thanks in advance

 

[gemma-the-husky]

you need an rtable.movefirst to position the cursor before you can reference a field.

if you only want the first item (maybe its a control table with only one item) you can just do.

firstitem = dlookup("stunde","nachweis") with no where parameter.

 

[Schlingel]

Thanks, gemma-the-husky
My code now looks like this:

Static S As Integer
Set DataBase = CurrentDb
Set RTable = DataBase.OpenRecordset("Nachweis")

S = DLookup("[Stunde]", "Nachweis")

RTable.Edit
RTable.MoveFirst
DoCmd.GoToRecord acDataForm, "Nachweis", acNewRec
Forms!Nachweis!Rahmen44.Value = S + 1
RTable.Update
RTable.Close
S = 0
End Sub

Still i get only the right value on the first run...
Another question: Can i run the modul programmatically?
Thanks in advance

 

[gemma-the-husky]

you seem to be confusing two approaches.

first the line

S = DLookup("[Stunde]", "Nachweis") retrives the value of a random record (sort of - you are not telling access which record to use). this is only appropriate for a control table containing one record. If you want a specific record then you need

S = DLookup("[Stunde]", "Nachweis","WHERE parameter to retrieve a specific record")

--------------------------
now having opened the record set, you need to find a record first, BEFORE you can edit it


so
RTable.MoveFirst
(or rtable.findfirst "where condition" 'and only then)

RTable.Edit
'this navigates to the first record, and puts it in edit mode
'you now edit fields by the syntax
rtable!fieldname = newvalue
'and then save it as you have done
RTable.Update
RTable.Close
S = 0
End Sub

if you want a NEW RECORD then you need
RTable.ADDNEW
'this automatcally opens the new record buffer, so you don't need an edit command
rtable!fieldname = newvalue
'and then save it as you have done
RTable.Update
RTable.Close
S = 0
End Sub

putting form based commands in the middle of the rst block will do something, but i don't think it will give you the result you want.

 

[Schlingel]

Thanks for all ur help, gemma-the-husky, i appreciate it so much!