SUBFORM goto a Selected Record

[ChrisR]

Hi guys

My app is a simple Main form with a few Subforms that are not linked,
and a few pop forms.

Problem is:

I have a pop form with a Listbox with a list of records related to the
subform in the Main form. I'd like to double click on the selected
file in the listbox and load that record into the the Subform, easy
hey!

The coding I'm using is:

Private Sub lstHist_DblClick(Cancel As Integer)

'on error resume next

Dim strLinkCriteria, strNo As String
Dim lngNo As Long
Dim rst As DAO.Recordset

If Not IsNull(Me.lstHist.Column(3)) Then
lngNo = Me.lstHist.Column(3)
strNo = str(lngNo)

Set rst = Form_frmOrd.RecordsetClone
strLinkCriteria = "[OrdID]=" & strNo
rst.FindFirst strLinkCriteria
If rst.RecordCount > 0 Then
Form_frmOrd.Bookmark = rst.Bookmark
Form_frmOrd.Requery
Else
MsgBox "Record not found"
End If
rst.Close

DoCmd.Close acForm, Me.Name
End If

End Sub

This is the coding in the Listbox of the pop up form.
frmOrd is the Subform
frmMain is the Main form
OrdID is the Primary Key Field for table that is the recordsource of
frmOrd Form.

Any help would be appreciated

Thank guys

Chris

[Allen Browne]

You are on the right track, but a number of issues:

1. The Requery throws you back to the first record, immediately after you
found the one you want.

2. The Column of the listbox returns a string value, so it is a good idea to
check for a zero-length string as well as a null.

3. Referring to the subform through the Forms collection avoids the chance
of instantiating something that is not released later.

4. The form's RecordsetClone cannot be closed. Instead, just set your object
variable to nothing.

5. If OrdID happens to be a field of type Text, you will need extra quotes.

Try this:

Private Sub lstHist_DblClick(Cancel As Integer)
Dim strNo As String
Dim strLinkCriteria, strNo As String
Dim rst As DAO.Recordset

strNo = Nz(Me.lstHist.Column(3), vbnullstring)
If Len(strNo) > 0 Then
With Forms!frmMain!frmOrd.Form
Set rst = .RecordsetClone
strLinkCriteria = "[OrdID] = " & strNo
rst.FindFirst strLinkCriteria
If rst.NoMatch Then
MsgBox "Record not found"
Else
.Bookmark = rst.Bookmark
End If
End With
DoCmd.Close acForm, Me.Name
End If
Set rst = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ChrisR" <ch***@krisaracomputers.com.au> wrote in message
news:97**************************@posting.google.c om...

Hi guys

My app is a simple Main form with a few Subforms that are not linked,
and a few pop forms.

Problem is:

I have a pop form with a Listbox with a list of records related to the
subform in the Main form. I'd like to double click on the selected
file in the listbox and load that record into the the Subform, easy
hey!

The coding I'm using is:

Private Sub lstHist_DblClick(Cancel As Integer)

'on error resume next

Dim strLinkCriteria, strNo As String
Dim lngNo As Long
Dim rst As DAO.Recordset

If Not IsNull(Me.lstHist.Column(3)) Then
lngNo = Me.lstHist.Column(3)
strNo = str(lngNo)

Set rst = Form_frmOrd.RecordsetClone
strLinkCriteria = "[OrdID]=" & strNo
rst.FindFirst strLinkCriteria
If rst.RecordCount > 0 Then
Form_frmOrd.Bookmark = rst.Bookmark
Form_frmOrd.Requery
Else
MsgBox "Record not found"
End If
rst.Close

DoCmd.Close acForm, Me.Name
End If

End Sub

This is the coding in the Listbox of the pop up form.
frmOrd is the Subform
frmMain is the Main form
OrdID is the Primary Key Field for table that is the recordsource of
frmOrd Form.

Any help would be appreciated

Thank guys

Chris

[ChrisR]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

You are on the right track, but a number of issues:

1. The Requery throws you back to the first record, immediately after you
found the one you want.

2. The Column of the listbox returns a string value, so it is a good idea to
check for a zero-length string as well as a null.

3. Referring to the subform through the Forms collection avoids the chance
of instantiating something that is not released later.

4. The form's RecordsetClone cannot be closed. Instead, just set your object
variable to nothing.

5. If OrdID happens to be a field of type Text, you will need extra quotes.

Try this:

Thanks Allen
For your time and effort in your reply.
But the coding still falls over and doesnt select the record in the
loaded subform.

I get a error at:-
With Forms!frmMain!frmOrd.Form

with a runtime error: 2465

'MS Access cant find field "frmOrd" referred to in your expression.'

frmMain = Main Form
subFrmPrim = Subform on the main form
frmOrd = Subform.SourceObject
[OrdID] = Primary Key - LONG

The frmMain is unbound and is not linked to the subforms, its used as
a max window with a few buttons to access the subforms.

I get the same error too when I click on a cmdButton on the main form
and open a subform and use setfocus to jump to the 1st tab on the
subform.

If you can help thanks!

Chris


Private Sub lstHist_DblClick(Cancel As Integer)
Dim strNo As String
Dim strLinkCriteria, strNo As String
Dim rst As DAO.Recordset

> End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ChrisR" <ch***@krisaracomputers.com.au> wrote in message
news:97**************************@posting.google.c om...

Hi guys

My app is a simple Main form with a few Subforms that are not linked,
and a few pop forms.

Problem is:
I have a pop form with a Listbox with a list of records related to the
subform in the Main form. I'd like to double click on the selected
file in the listbox and load that record into the the Subform, easy
hey!

The coding I'm using is:

Private Sub lstHist_DblClick(Cancel As Integer)

'on error resume next

Dim strLinkCriteria, strNo As String
Dim lngNo As Long
Dim rst As DAO.Recordset

If Not IsNull(Me.lstHist.Column(3)) Then
lngNo = Me.lstHist.Column(3)
strNo = str(lngNo)

Set rst = Form_frmOrd.RecordsetClone
strLinkCriteria = "[OrdID]=" & strNo
rst.FindFirst strLinkCriteria
If rst.RecordCount > 0 Then
Form_frmOrd.Bookmark = rst.Bookmark
Form_frmOrd.Requery
Else
MsgBox "Record not found"
End If
rst.Close

DoCmd.Close acForm, Me.Name
End If

End Sub

This is the coding in the Listbox of the pop up form.
frmOrd is the Subform
frmMain is the Main form
OrdID is the Primary Key Field for table that is the recordsource of
frmOrd Form.

Any help would be appreciated

Thank guys

Chris

[Allen Browne]

Okay. Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
What is the Name property of the subform control? (on the Other tab).

The Name can be different from the SourceObject (i.e. the name of the form
that gets loaded into your subform control).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ChrisR" <ch***@krisaracomputers.com.au> wrote in message
news:97**************************@posting.google.c om...

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message

news:<40***********************@freenews.iinet.net .au>...

You are on the right track, but a number of issues:

1. The Requery throws you back to the first record, immediately after you

found the one you want.

2. The Column of the listbox returns a string value, so it is a good idea to check for a zero-length string as well as a null.

3. Referring to the subform through the Forms collection avoids the chance of instantiating something that is not released later.

4. The form's RecordsetClone cannot be closed. Instead, just set your object variable to nothing.

5. If OrdID happens to be a field of type Text, you will need extra quotes.
Try this:

Thanks Allen
For your time and effort in your reply.
But the coding still falls over and doesnt select the record in the
loaded subform.

I get a error at:-
With Forms!frmMain!frmOrd.Form

with a runtime error: 2465

'MS Access cant find field "frmOrd" referred to in your expression.'

frmMain = Main Form
subFrmPrim = Subform on the main form
frmOrd = Subform.SourceObject
[OrdID] = Primary Key - LONG

The frmMain is unbound and is not linked to the subforms, its used as
a max window with a few buttons to access the subforms.

I get the same error too when I click on a cmdButton on the main form
and open a subform and use setfocus to jump to the 1st tab on the
subform.

If you can help thanks!

Chris

Private Sub lstHist_DblClick(Cancel As Integer)
Dim strNo As String
Dim strLinkCriteria, strNo As String
Dim rst As DAO.Recordset

> End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ChrisR" <ch***@krisaracomputers.com.au> wrote in message
news:97**************************@posting.google.c om...

Hi guys

My app is a simple Main form with a few Subforms that are not linked,
and a few pop forms.

Problem is:

I have a pop form with a Listbox with a list of records related to the
subform in the Main form. I'd like to double click on the selected
file in the listbox and load that record into the the Subform, easy
hey!

The coding I'm using is:

Private Sub lstHist_DblClick(Cancel As Integer)

'on error resume next

Dim strLinkCriteria, strNo As String
Dim lngNo As Long
Dim rst As DAO.Recordset

If Not IsNull(Me.lstHist.Column(3)) Then
lngNo = Me.lstHist.Column(3)
strNo = str(lngNo)

Set rst = Form_frmOrd.RecordsetClone
strLinkCriteria = "[OrdID]=" & strNo
rst.FindFirst strLinkCriteria
If rst.RecordCount > 0 Then
Form_frmOrd.Bookmark = rst.Bookmark
Form_frmOrd.Requery
Else
MsgBox "Record not found"
End If
rst.Close

DoCmd.Close acForm, Me.Name
End If

End Sub

This is the coding in the Listbox of the pop up form.
frmOrd is the Subform
frmMain is the Main form
OrdID is the Primary Key Field for table that is the recordsource of
frmOrd Form.

Any help would be appreciated

Thank guys

Chris

[ChrisR]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

You are on the right track, but a number of issues:

1. The Requery throws you back to the first record, immediately after you
found the one you want.

2. The Column of the listbox returns a string value, so it is a good idea to
check for a zero-length string as well as a null.

3. Referring to the subform through the Forms collection avoids the chance
of instantiating something that is not released later.

4. The form's RecordsetClone cannot be closed. Instead, just set your object
variable to nothing.

5. If OrdID happens to be a field of type Text, you will need extra quotes.

Try this:

Private Sub lstHist_DblClick(Cancel As Integer)
Dim strNo As String
Dim strLinkCriteria, strNo As String
Dim rst As DAO.Recordset

strNo = Nz(Me.lstHist.Column(3), vbnullstring)
If Len(strNo) > 0 Then
With Forms!frmMain!frmOrd.Form
Set rst = .RecordsetClone
strLinkCriteria = "[OrdID] = " & strNo
rst.FindFirst strLinkCriteria
If rst.NoMatch Then
MsgBox "Record not found"
Else
.Bookmark = rst.Bookmark
End If
End With
DoCmd.Close acForm, Me.Name
End If
Set rst = Nothing
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ChrisR" <ch***@krisaracomputers.com.au> wrote in message
news:97**************************@posting.google.c om...

Hi guys

My app is a simple Main form with a few Subforms that are not linked,
and a few pop forms.

Problem is:

I have a pop form with a Listbox with a list of records related to the
subform in the Main form. I'd like to double click on the selected
file in the listbox and load that record into the the Subform, easy
hey!

The coding I'm using is:

Private Sub lstHist_DblClick(Cancel As Integer)

'on error resume next

Dim strLinkCriteria, strNo As String
Dim lngNo As Long
Dim rst As DAO.Recordset

If Not IsNull(Me.lstHist.Column(3)) Then
lngNo = Me.lstHist.Column(3)
strNo = str(lngNo)

Set rst = Form_frmOrd.RecordsetClone
strLinkCriteria = "[OrdID]=" & strNo
rst.FindFirst strLinkCriteria
If rst.RecordCount > 0 Then
Form_frmOrd.Bookmark = rst.Bookmark
Form_frmOrd.Requery
Else
MsgBox "Record not found"
End If
rst.Close

DoCmd.Close acForm, Me.Name
End If

End Sub

This is the coding in the Listbox of the pop up form.
frmOrd is the Subform
frmMain is the Main form
OrdID is the Primary Key Field for table that is the recordsource of
frmOrd Form.

Any help would be appreciated

Thank guys

Chris

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxx

Allen

Thanks for repling so quickly.

Main Form Name = frmMain
SubForm Control on the Main Form.Name = subFrmPrim
Subform SourceObject Name = frmOrd

No links - between frmMain - frmOrd

The selected recorded is to be loaded in frmOrd.

frmOrd is bound form to a table Name = tblOrd
PK tblOrd = [OrdID] - Long

Hope this helps, any probs you can contact me on ch***@krisaracomputers.com.au

Thanks for your time.

I need a beer

Chris

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx

[Allen Browne]

If the subform control is named "subFrmPrim", the reference you need is:
Forms!frmMain!subFrmPrim.Form

Main Form Name = frmMain
SubForm Control on the Main Form.Name = subFrmPrim
Subform SourceObject Name = frmOrd

No links - between frmMain - frmOrd

The selected recorded is to be loaded in frmOrd.

frmOrd is bound form to a table Name = tblOrd
PK tblOrd = [OrdID] - Long

[ChrisR]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

If the subform control is named "subFrmPrim", the reference you need is:
Forms!frmMain!subFrmPrim.Form

Main Form Name = frmMain
SubForm Control on the Main Form.Name = subFrmPrim
Subform SourceObject Name = frmOrd

No links - between frmMain - frmOrd

The selected recorded is to be loaded in frmOrd.

frmOrd is bound form to a table Name = tblOrd
PK tblOrd = [OrdID] - Long

Thanks again Allen

But it didnt fix the problem. No error msgs but no record found, when
the selected record is double clicked in the listbox.
Instead of a popup form with a listbox on it, ill put the listbox on
the loaded subform.
Wish I or someone could work out why this coding doesnt work, for
future reference.

Maybe these form properties are wrong, thats loaded into the subform
control.

Form.cycle = "Current record" - Ive tried "All records" too.
Form.recordset = Dynaset

Any over properties worth considering.

Thanks again Allen

Chris

[ChrisR]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message news:<40***********************@freenews.iinet.net .au>...

If the subform control is named "subFrmPrim", the reference you need is:
Forms!frmMain!subFrmPrim.Form

Main Form Name = frmMain
SubForm Control on the Main Form.Name = subFrmPrim
Subform SourceObject Name = frmOrd

No links - between frmMain - frmOrd

The selected recorded is to be loaded in frmOrd.

frmOrd is bound form to a table Name = tblOrd
PK tblOrd = [OrdID] - Long

Thanks again Allen

But it didnt fix the problem. No error msgs but no record found, when
the selected record is double clicked in the listbox.
Instead of a popup form with a listbox on it, ill put the listbox on
the loaded subform.
Wish I or someone could work out why this coding doesnt work, for
future reference.

Maybe these form properties are wrong, thats loaded into the subform
control.

Form.cycle = "Current record" - Ive tried "All records" too.
Form.recordset = Dynaset

Any over properties worth considering.

Thanks again Allen

Chris