command button to populate unbound text boxes from subform only works once (1 Viewer)

Hi All,

I have a tabbed form from which the user can select a contact's record from a subform on the first tab, click a edit command button, and the unbound text boxes on the top of that tab populate. The user can then click the second tab with employment history which has blank unbound text boxes and another subform which is linked by the contact id to the first tab. The user can select a record in the second tab, click a command button and the text boxes populate no problem. The problem comes when the user changes the contact on the first tab, and then tries to edit a record on the second tab. Then I get a run-time error '-2147352567 (80020009)' saying the value you entered isn't valid for this field. Any idea why it would work the first time by not the second?

Attach a copy of your db, shaved down to the bare minimum required to produce the problem, AND attach directions as to how to recreate the problem.

This is a test database so it is already shaved down. It opens with Editar Contactos form. Select a contact from the subform on the bottom and click the "Editar" button. Next click the "Empleo" tab, select a record from the subform and click the "Editar" button to populate the fields at the top of the form. There shouldn't be any problem doing that. Then go back to the Information General tab, select a new contact from the subform and click the "Editar" button. Go to the "Empleo" tab, select a record from the subform and click the "Editar" button. That's where the error occurs. Same thing happens with the other tabs now if they previously had the unbound text boxes populated.

Thanks for the help!

Here's another twist. If the contact on the first tab I select has more than one record in the subform in the second tab, it works fine. It seems to only crash if it is the first record on the subform if it is the second time it is being selected if that makes any sense. Once I've selected the second record I can go back to the first one and load it as well. Let me spell that out better.

First I select Phil Dumphrey from the main contacts tab and load his information. On the employment tab, he only has one job and if I select it, it loads fine. Then I go back to the main contacts tab and select Merideth Grey. On the employment tab she only has one job and if I select it, it crashes and I get my error. If I add another job however, and then select the second job in the list, it loads fine AND I can go back and load the first record as well. If I change people again to someone with one job, their employment will load fine, but if I change people again to someone else with only one job, that record crashes. There seems to be something up with the first record. Here's the code that the button works off of, anyone spot something? Do I need to set a record counter somewhere?

Private Sub cmdEditarEmpleo_Click()

'check if data exists
If Not (Me.FrmEditEmpleador.Form.Recordset.EOF And Me.FrmEditEmpleador.Form.Recordset.BOF) Then
With Me.FrmEditEmpleador.Form.Recordset
Me.empleador = .Fields("empleador")
Me.titulo = .Fields("titulo")
Me.Niv_gest = .Fields("nivel_gestion_id")
Me.indust_id = .Fields("indust_id")
Me.tam_empresa_id = .Fields("empres_id")
Me.comenzo_ano = .Fields("comenzo_ano")
Me.ano_izquierda = .Fields("ano_izquierda")
Me.empact = .Fields("patron_actual")
Me.emp_calle = .Fields("calle")
Me.emp_ciudad = .Fields("ciudad")
Me.emp_estado = .Fields("estado")
Me.emp_c_p = .Fields("c_p")
Me.num_anos = .Fields("num_anos_empl")
Me.EmpId = .Fields("EmpId")
'refresh subforms
DoCmd.Requery "frmEditEmpleador"
'disable and enable buttons
Me.CmdAdnEmp.Enabled = False
Me.cmdActEmpleo.Enabled = True
Me.cmdBorrorEmp.Enabled = True
Me.CmdClaroEmp.Enabled = True

End With
End If
End Sub

Add the below line marked with red into your code.

That did it, that was the little piece of code I needed! Thank you so much, you have no idea how much sleep I've lost over this.

You're welcome, good you got it solved, sleep well!

JHB,

For my benefit, could you please explain what that extra code is checking for, as I have only ever tested for EOF/BOF?

TIA

JHB said:

Add the below line marked with red into your code.

Code:Copy to clipboard

    If Not (Me.FrmEditEmpleador.Form.Recordset.EOF And Me.FrmEditEmpleador.Form.Recordset.BOF) Then
[B][COLOR=Red]        If Me.FrmEditEmpleador.Form.Recordset.AbsolutePosition = -1 Then
          Me.FrmEditEmpleador.Form.Recordset.MoveFirst
        End If
[/COLOR][/B]

Click to expand...