Update different fields from subform

[monfas]

Hi,

I don't understand much of VBA, so I don't even know if what I'm trying to do is possible... and now this smiley is the best expression of myself. :banghead:

I have one main form, with a text box that will register a record in a table "tReg" with the following fields (among other)

RegNumber (indexed field with no duplicate values)
and 14 fields with ID1, ID2.... ID14

These 14 IDs are part of an ID table "tID", where each ID is a record, and has a place for a name, where I will write the name.

What I want to do is to write a reg number in textbox "Text0", in the main form, this will update in the table tReg, and when I write the Name and IDs in the subform, these will update the ID field in the tID (no problems with that), AND write the ID number in ONE of the IDn field for the corresponding Regx (as in the main form), in table tReg - sor for each Reg x, I will have several IDs in the IDn fields (and one ID should only be inserted once)

I have devise the following code, but doesn't work, can anyone help me in solving this out?

Thanks - the code is the following:

Private Sub Text0_AfterUpdate()
Dim mydb As Database
Dim rsReg As Recordset
Dim i As Integer
Dim stReg As String

stReg = Forms!fReg.text0.value
Set mydb = CurrentDb
Set rsReg = mydb.OpenRecordset("SELECT [tReg].* From [tReg] WHERE RegNumber ='" & strReg & "'", DB_OPEN_DYNASET)

With rsReg
For i = 2 To 15
If .Fields(i).Value = Null Then
.Edit
.Fields(i).Value = Me.ID.Value
.Update
End If
Next i
End with

End sub

Thanks,

 

[monfas]

OK, I have an main ID table where I have a pool of persons (IDs)

The reg table is supposed to have record about travel. Each travel event can have up to 14 IDs.

I want this to be logged for record, so we know that each ID has traveled which several dates (as one person can travel several times).

I tried to have the Reg number in the ID table, but or I would have to have several Reg number (Reg1, Reg2, etc...), or I in alternative I would just have one Reg number, that is updated - in this case I will loose the information from the previous journeys of that specific individual, hence having a Reg number, that is equivalent to a journey event, with up to 14 people who traveled in that "event" - So I can easily locate each travel event, and who traveled then.