how to select record in the subform and to show it in the main form?

[Chipcom]

Hi

I have subform with datasheet view and I need to know how to select
record in the subform and to show it in the main form?


Thanks

 

[Guest]

Hi this just one method

Create a new text box on the main form (NewControlName), On the GotFocus
event of this new box put this (of course you will need to alter the names in
the code to what they are in your DB)


Private Sub NewControlName_GotFocus()
DoCmd.SetWarnings False
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[MainFormID] = " & Str(Nz(Me![NewControlName], 0))
If Not RS.EOF Then Me.Bookmark = RS.Bookmark

Set rst = Me("SubFormName").Form.RecordsetClone
rst.FindFirst "MainFormID = " & SubFormID
Me("SubFormName").Form.Bookmark = rst.Bookmark
DoCmd.SetWarnings True
End Sub



Next - on the datasheet put this on the OnClick event of the form (when you
select a record.


Forms!MainFormName!NewControlName =
Forms!MainFormName!SubFormName.Form!SubFormID
Forms!MainFormName!NewControlName.SetFocus

Set rst = Me("SubFormName").Form.RecordsetClone
rst.FindFirst "MainFormID = " & Forms!MainFormName!SubFormName.Form!SubFormID
Me("SubFormName").Form.Bookmark = rst.Bookmark


I understand this looks like you are doing the same thing twice but the
thing about this is that it will select the record in the main form that
matches the selected record in the datasheet - AND - it will stop the
datasheet moving off the record and requerying back to the first record, so
if you have a large datasheet it really worth it as you will not need to
scrol back to the selected record.

Hope this helps

 

[Chipcom]

What should I do to show the record too?

Hi


The code works partially cause it shows just the number of the record at the mainform without showing the record at the mainform.

What should I do to show the record too?


Thanks

 

[Chipcom]

Hi this just one method

Create a new text box on the main form (NewControlName), On the GotFocus
event of this new box put this (of course you will need to alter the names in
the code to what they are in your DB)

Private Sub NewControlName_GotFocus()
DoCmd.SetWarnings False
Dim RS As Object

Set RS = Me.Recordset.Clone
RS.FindFirst "[MainFormID] = " & Str(Nz(Me![NewControlName], 0))
If Not RS.EOF Then Me.Bookmark = RS.Bookmark

Set rst = Me("SubFormName").Form.RecordsetClone
rst.FindFirst "MainFormID = " & SubFormID
Me("SubFormName").Form.Bookmark = rst.Bookmark
DoCmd.SetWarnings True
End Sub

Next - on the datasheet put this on the OnClick event of the form (when you
select a record.

Forms!MainFormName!NewControlName =
Forms!MainFormName!SubFormName.Form!SubFormID
Forms!MainFormName!NewControlName.SetFocus

Set rst = Me("SubFormName").Form.RecordsetClone
rst.FindFirst "MainFormID = " & Forms!MainFormName!SubFormName.Form!SubFormID
Me("SubFormName").Form.Bookmark = rst.Bookmark

I understand this looks like you are doing the same thing twice but the
thing about this is that it will select the record in the main form that
matches the selected record in the datasheet - AND - it will stop the
datasheet moving off the record and requerying back to the first record, so
if you have a large datasheet it really worth it as you will not need to
scrol back to the selected record.

Hope this helps

--
Wayne
Manchester, England.

Hi

I have subform with datasheet view and I need to know how to select
record in the subform and to show it in the main form?

Thanks- Hide quoted text -

- Show quoted text -

Thanks

Now I need to go to the record that the new textbox shows .
Can you please help me with that?

Thanks