Basic help in VBA for access

[tziviak]

I'm using access event procedure-
I'm a beginner and I'm trying to create an event procedure by an onChange event of a field in a form. I'm having a hard time setting the recordset... so would appreciate if someone could help me.

I need to add/delete records to a table-based on information of a few tables-so do I need two recordSets? one with an sql statement-to extract info from the few tables and another recordSet for the table to update?

I would appreciate if someone can give me some basic code to help me get started.


Here is some code that I tried:

Private Sub Service_Begin_Change()

Dim dbs As Database

Dim rst As Recordset 'sql
Dim rec As Recordset 'table
Dim ddate As Date

Dim strSQL As String


' Return reference to current database.
Set dbs = CurrentDb
Set rec = dbs.OpenRecordset("Quarterlies&quot

' Return reference to Employees table.

Dim RetValue As Integer
strSQL = "SELECT Students.[Last Name], Students.[First Name], Quarterlies.DateDue, Students.[IFSP Date], IFSP.[Service Begin], IFSP.[Service End], IFSP.[Employee ID]FROM Students INNER JOIN (IFSP INNER JOIN Quarterlies ON IFSP.IFSPCounter = Quarterlies.IFSPID) ON Students.[Student ID] = IFSP.[Student ID]"

'Check if rows already exist
txtEmpID.SetFocus
If txtEmpID.Text <> Null Then
Set rst = dbs.OpenRecordset(strSQL)
ddate = rst.Fields(&quot;IFSP Date&quot
RetValue = MsgBox(ddate, vbOKCancel)
Do Until ddate >= rst.Fields(&quot;Service End&quot.Value
ddate = DateAdd(&quot;m&quot;, 3, ddate)
'rst.AddNew
'continue code
Loop
End If
End Sub

2. when I tried to set focus-on another textbox on the form-I got a message that it can't set the focus on that object-what am I doing wrong?

Thank you

 

[Rolliee]

The proper argument for rs.fields(?) is an integer that represents the zero based number position of that field in the table. Thus

for i = 0 to rs.fieldcount 'I beleive it is the # fields
msgbox rs.fields(i).name & &quot; &quot; & rs.fields(i)
next i

will give the name and value for the current record - all fields.

Rollie E