Using Combo box to select record doesn't work right

[Soundsmith]

I have a form whose job is to display matching results in two subforms (three tables, the form holds a master table, when I move from record to record, the subforms show matching results from the other two tables.)

Everything works fine when I use the nav buttons or record selector. WEhen I use the combo box, the proper subform records show up, but the main table does not follow (the nav counter shows 1, and data in record 1 is partially replaced.)

The combo has two fields, monrLink and caseLink - caselink is the visible one, monrlink is the actual matching field.

The matching field is monrLink, the combo box is cboMLink, the table is tblMONRActive.

I know this is pretty simple, but I can't get them to sync.

the combobox code is:

SELECT [tblMONRActive].[monrlink], MID([tblMONRActive].[monrlink],3,7) & "-" & RIGHT([tblMONRActive].[monrlink],2) AS caselink FROM tblMONRActive;

In the cbobox after_update event, I have tried:

Private Sub cboMLink_AfterUpdate()
' move to selected record
Dim newMonr As String
newMonr = Me.cboMLink.Column(0)
DoCmd.FindRecord (newMonr)
Me.Requery
End Sub

This displays the data, but alters the first record in the tblMONRActive rather than move to that record. I'm not using recordsets here, and would prefer to keep it that way.

Thanks for any help.


David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com

 

[EstuardoSierra]

Soundsmith:
I don't know if i understood but if this is all about sichrony you may add to your code.

Private Sub cboMLink_AfterUpdate()
' move to selected record
Dim newMonr As String
newMonr = Me.cboMLink.Column(0)
DoCmd.FindRecord (newMonr)
Me.fsubform1.Form.requery
Me.fsubForm2.form.Requery
Me.Requery
End Sub

I think it will do the job.

Good luck

Estuardo

 

[Soundsmith]

Thanks, Estruardo. That does not seem to change the problem.

I hasve four fields in trhe main table. The combo box gets its data from the monrLink fields and when I select from the combo box, the data in the subforms is updated, but the main form monrlink field is overwritten, rather than moving to the existing matching record. So record one contains update data for the original record one, but links to the wrong record (the record id, monrlink, has been replaced.

David 'Dasher' Kempton
The Soundsmith

http://www.thesoundsmith.com