Form Search Help Please?

[Andy G]

I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden) from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'

Any ideas?

Many Thanks

Andy

 

[Marshall Barton]

I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden) from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'

Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Andy G]

Hi Marshall,

Thanks for the advice,

although I don't get any errors, when i now click on the button in frm1, the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
..Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With

Any ideas?

Many Thanks

Andy G

I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'

Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Marshall Barton]

By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.
--
Marsh
MVP [MS Access]

although I don't get any errors, when i now click on the button in frm1, the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With

I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'

"Marshall Barton" wrote

Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Andy G]

Hi Marshall,

Yeah, the form is already open, but i suppose i could code the search form
to close the main for when it opens? or is there another method where both
forms can be open?

Thanks

Andy G

By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.
--
Marsh
MVP [MS Access]

although I don't get any errors, when i now click on the button in frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With

Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'

"Marshall Barton" wrote

Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Marshall Barton]

I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If
--
Marsh
MVP [MS Access]

Yeah, the form is already open, but i suppose i could code the search form
to close the main for when it opens? or is there another method where both
forms can be open?

By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.

although I don't get any errors, when i now click on the button in frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Andy G]

Hi Marshll,

I am using Access 2002/2003

when I place the code from your last post in my search form, in the select
button's onclick event, I receive the following error:

Runtime error '2455':
you entered an expression that has an invalid reference to the property
bookmark

Any ideas?

Thanks
Andy G

I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If
--
Marsh
MVP [MS Access]

Yeah, the form is already open, but i suppose i could code the search form
to close the main for when it opens? or is there another method where both
forms can be open?

By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.


Andy G wrote:
although I don't get any errors, when i now click on the button in frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen
by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Marshall Barton]

Sometimes I type before I think amd lose track of which form
is which. The line:
Me.Bookmark = .Bookmark
should be:
Forms!frm1.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

I am using Access 2002/2003

when I place the code from your last post in my search form, in the select
button's onclick event, I receive the following error:

Runtime error '2455':
you entered an expression that has an invalid reference to the property
bookmark

I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If

Yeah, the form is already open, but i suppose i could code the search form
to close the main for when it opens? or is there another method where both
forms can be open?


By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.


Andy G wrote:
although I don't get any errors, when i now click on the button in frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID (hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but this
filters the records, whereas i only want to jump to the record chosen
by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Andy G]

Aha,

I tried a few things, but being fairly new at this, none of them worked lol
Thanks, will try this tonight.

Andy G

Sometimes I type before I think amd lose track of which form
is which. The line:
Me.Bookmark = .Bookmark
should be:
Forms!frm1.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

I am using Access 2002/2003

when I place the code from your last post in my search form, in the select
button's onclick event, I receive the following error:

Runtime error '2455':
you entered an expression that has an invalid reference to the property
bookmark

I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If



Andy G wrote:
Yeah, the form is already open, but i suppose i could code the search
form
to close the main for when it opens? or is there another method where
both
forms can be open?


By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.


Andy G wrote:
although I don't get any errors, when i now click on the button in
frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show
the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is
the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID
(hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using
ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but
this
filters the records, whereas i only want to jump to the record
chosen
by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Andy G]

Hi Marshall

Brilliant, works a treat!
Many Thanks for all your help and patience

Do you know of any good resources, books, sites etc to help me leartn this
sort of programming?, if not,pls don't worry!

Andy G

Sometimes I type before I think amd lose track of which form
is which. The line:
Me.Bookmark = .Bookmark
should be:
Forms!frm1.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

I am using Access 2002/2003

when I place the code from your last post in my search form, in the select
button's onclick event, I receive the following error:

Runtime error '2455':
you entered an expression that has an invalid reference to the property
bookmark

I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If



Andy G wrote:
Yeah, the form is already open, but i suppose i could code the search
form
to close the main for when it opens? or is there another method where
both
forms can be open?


By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.


Andy G wrote:
although I don't get any errors, when i now click on the button in
frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show
the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is
the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID
(hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using
ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but
this
filters the records, whereas i only want to jump to the record
chosen
by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If

 

[Marshall Barton]

Sorry, but I'm not a good source for book recommendations.
The only book I have used extensively is the
Access <version> Developers Handbook by Litwin, Getz &
Gilbert from Sybex. However, this is more of a reference
book than something for beginners.

If you can not find something useful by searching the Google
newsgroups archives and if no one else jumps in here with
one, you might want to post a new question specific to books
on the Access Object Model and VBA.
--
Marsh
MVP [MS Access]

Brilliant, works a treat!
Many Thanks for all your help and patience

Do you know of any good resources, books, sites etc to help me leartn this
sort of programming?, if not,pls don't worry!

Sometimes I type before I think amd lose track of which form
is which. The line:
Me.Bookmark = .Bookmark
should be:
Forms!frm1.Bookmark = .Bookmark

I am using Access 2002/2003

when I place the code from your last post in my search form, in the select
button's onclick event, I receive the following error:

Runtime error '2455':
you entered an expression that has an invalid reference to the property
bookmark


I suppose you could close the form and reopen it, but if you
test to see if the form is already open before using
theOpenForm method, then you caould do it directly in the
search form.

I don't see where you said what version of Access you're
using. If it's A2000 (A2002?) or later, you can use this
kind of logic:

If CurrentProject.AllForms!frm1.IsLoaded Then
With Forms!frm1.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.cbo2
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
Else
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2
End If


Andy G wrote:
Yeah, the form is already open, but i suppose i could code the search
form
to close the main for when it opens? or is there another method where
both
forms can be open?


By any chance, is frm1 already open? The OpenForm method
does not do much of anything and the Load event will not
fire if the form is already open.


Andy G wrote:
although I don't get any errors, when i now click on the button in
frm1,
the
record stays at record 1

Not sure if it's relevant, but i'm using the following code to show
the
absolute record number
With Me.RecordsetClone
.Bookmark = Me.Bookmark
txtListID.Value = .AbsolutePosition + 1
End With


Andy G wrote:
I have a main form ''frm1'
I have a search form 'frmsearch' with 2 comboboxes

'cbo1' has the row source of 'tbl1' fields

'cbo2' lists the records of the field chosen in 'cbo1'
'cbo2' has 2 columns, one of which is hidden (ListID) ' ListID is
the
Primary Key (numeric) for 'tbl1'

I have a select button which, when pushed, should pass ListID
(hidden)
from
'cbo2' to the 'frm1'
'frm1' should then open up the record chosen in 'cbo2' by using
ListID


Could anybody advise me on how to achieve this, I have managed to do
exactly
what I want using

DoCmd.OpenForm "frm1", , , "[ListID]=" & Forms!frmsearch!cbo2 but
this
filters the records, whereas i only want to jump to the record
chosen
by
'cbo2'


"Marshall Barton" wrote
Change the OpenForm to:
DoCmd.OpenForm "frm1", OpenArgs:= Me.cbo2

Then in frm1's Load event use this code to navigate to the
matching record:

If Not IsNull(Me.OpenArgs) Then
With Me.RecordsetClone
If .RecordCount > 0
.FindFirst "ListID = " & Me.OpenArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End If
End With
End If