473,242 Members | 1,483 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

SUBFORM goto a Selected Record

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
Nov 12 '05 #1
7 11122
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

Nov 12 '05 #2
"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

Nov 12 '05 #3
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

Nov 12 '05 #4
"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
Nov 12 '05 #5
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

Nov 12 '05 #6
"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
Nov 12 '05 #7
"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
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Stuart | last post by:
Hi all, I'm trying to make a form that lets the user enter a name into a text box, and have the first record that matches that name be selected in a datasheet subform. For example, say the...
3
by: deko | last post by:
I have a form with a subform datasheet - I need code behind the OnDelete event of the subform: Private Sub Form_Delete(Cancel As Integer) 'do something that depends on which record is deleted...
6
by: ChrisR | last post by:
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...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
37
by: Tim Marshall | last post by:
From http://www.mvps.org/access/tencommandments.htm 9th item: Thou shalt not use "SendKeys", "Smart Codes" or "GoTo" (unless the GoTo be part of an OnError process) for these will lead you...
4
by: bobg.rjservices | last post by:
running access 2k; adp w/ linked tables to SQL server; I am absolutely stymied, and ticked off beyond belief - I can not believe how much time I've wasted trying to do something that should be...
11
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
2
by: Keith Wilby | last post by:
I have developed an appointments system database and I use the attached code in the main form's current event to detect appointment clashes in the subform. Is it possible to go to the offending...
10
by: thayes5150 | last post by:
I have an access database that uses a subform to display records, one column of which is charge data (currency). My users need to verify the charges against the paper form they are scanned from, so...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.