How to highlight an entire row of records and hold this format

[postman]

How to highlight an entire row of records and hold this format while user
navigates to correct record, (6 fields) so an append query (via a control)
can then be run to append the selected row to another table.

I have a product search form with:

1. An unbound text box (that is the criteria for a query) for user data
entry eg: Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*")
2. A control button 'refresh form data' that refreshes form data once data
input complete.
3. a datasheet subform for the query.

When the form opens the curser is in the text box awaiting data entry, the
user then refreshes form data and the records matching query criteria are
shown in the datasheet.

So will need to have the row automatically selected once the form data has
been refreshed (nice), or manually select the row by clicking on the arrow
next to the record row (which highlights that entire row), then
navigating to the record with keyboard up/down keys to select the required
row. I know how to build an append query based on a form controls etc. so
have done that ok. I would guess that the "selected" record row are the
required data for the append query & nothing more needs to be done on the
query.

MY QUESTION:
1. How to highlight an entire record row automatically, and hold this format
while user navigates to correct record.

2. How to use the record row in an append query.
Would the code look like:

INSERT INTO [Tbl_SBuy Temp] ( Title, Format, SPrice, BCPrice, BTPrice )
SELECT Forms!Frm_FindTitles_BOOKS!TITLE AS Expr1,
Forms!Frm_FindTitles_BOOKS!FORMAT AS Expr2,
Forms!Frm_FindTitles_BOOKS!SPrice AS Expr3,
Forms!Frm_FindTitles_BOOKS!CBPrice AS Expr4,
Forms!Frm_FindTitles_BOOKS!TBPrice AS Expr5;

Thanks in advance.

 

[postman]

Ok to complicated...try this:
Once I type "XYZ" into the text box I TAB over to the Refresh data button,
THEN Tab again to the sub form WHERE I would like to have all fields for the
row HIGHLIGHTED whilst I navigate with up/down curser keys to the correct
record.
That row of highlighted fields will be used in an append query.
This code was given previously:
me.RecordSet.FindFirst "id = " & me.OpenArgs
but not sure how to implement it now with my reconstructed form.

 

[Marshall Barton]

How to highlight an entire row of records and hold this format while user
navigates to correct record, (6 fields) so an append query (via a control)
can then be run to append the selected row to another table.

I have a product search form with:

1. An unbound text box (that is the criteria for a query) for user data
entry eg: Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*")
2. A control button 'refresh form data' that refreshes form data once data
input complete.
3. a datasheet subform for the query.

When the form opens the curser is in the text box awaiting data entry, the
user then refreshes form data and the records matching query criteria are
shown in the datasheet.

So will need to have the row automatically selected once the form data has
been refreshed (nice), or manually select the row by clicking on the arrow
next to the record row (which highlights that entire row), then
navigating to the record with keyboard up/down keys to select the required
row. I know how to build an append query based on a form controls etc. so
have done that ok. I would guess that the "selected" record row are the
required data for the append query & nothing more needs to be done on the
query.

MY QUESTION:
1. How to highlight an entire record row automatically, and hold this format
while user navigates to correct record.

2. How to use the record row in an append query.
Would the code look like:

INSERT INTO [Tbl_SBuy Temp] ( Title, Format, SPrice, BCPrice, BTPrice )
SELECT Forms!Frm_FindTitles_BOOKS!TITLE AS Expr1,
Forms!Frm_FindTitles_BOOKS!FORMAT AS Expr2,
Forms!Frm_FindTitles_BOOKS!SPrice AS Expr3,
Forms!Frm_FindTitles_BOOKS!CBPrice AS Expr4,
Forms!Frm_FindTitles_BOOKS!TBPrice AS Expr5;

It's easy enough to get the text box to select a record in a
subform (even if I don't understand what/why a "refresh" is
needed), but I don't think you can "highLight" a row in a
datasheet (sub)form. The current record is usually
indentified by reverse colors, but that will not "stick"
when you navigate to another record. I have no idea what
you mean by "correct record" or why you would want to
maintain a hightlight on an "incorrect" record???

It's a little complicated, but you could highlight two or
three different records with different colors by using a
continuous view (sub)form instead of datasheet view.

 

[postman]

Just to answer your points to clarify all this:
1. The refresh button simply updates the query match all records starting
with "xyz", the user then navigates to the record they want to select,
simply because there are lots of formats eg; music formats like cd, cda,
cd5, vynil, with the same artist & title, they also have different prices,
so batch records works best. Hope that helps.

2. I need to append all the fields for that record (row)eliminating user
error having to select all the fields. Highlighting the entire row in the
sub-datasheet as default, you can do this manually by selecting the arrow
next to the record but want it to be default when tabbing into the
sub-datasheet then simply user scrolls with cursor keys to the record that
fits & hits the append button.
Thanks.

How to highlight an entire row of records and hold this format while user
navigates to correct record, (6 fields) so an append query (via a control)
can then be run to append the selected row to another table.

I have a product search form with:

1. An unbound text box (that is the criteria for a query) for user data
entry eg: Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*")
2. A control button 'refresh form data' that refreshes form data once data
input complete.
3. a datasheet subform for the query.

When the form opens the curser is in the text box awaiting data entry, the
user then refreshes form data and the records matching query criteria are
shown in the datasheet.

So will need to have the row automatically selected once the form data has
been refreshed (nice), or manually select the row by clicking on the arrow
next to the record row (which highlights that entire row), then
navigating to the record with keyboard up/down keys to select the required
row. I know how to build an append query based on a form controls etc. so
have done that ok. I would guess that the "selected" record row are the
required data for the append query & nothing more needs to be done on the
query.

MY QUESTION:
1. How to highlight an entire record row automatically, and hold this
format
while user navigates to correct record.

2. How to use the record row in an append query.
Would the code look like:

INSERT INTO [Tbl_SBuy Temp] ( Title, Format, SPrice, BCPrice, BTPrice )
SELECT Forms!Frm_FindTitles_BOOKS!TITLE AS Expr1,
Forms!Frm_FindTitles_BOOKS!FORMAT AS Expr2,
Forms!Frm_FindTitles_BOOKS!SPrice AS Expr3,
Forms!Frm_FindTitles_BOOKS!CBPrice AS Expr4,
Forms!Frm_FindTitles_BOOKS!TBPrice AS Expr5;

It's easy enough to get the text box to select a record in a
subform (even if I don't understand what/why a "refresh" is
needed), but I don't think you can "highLight" a row in a
datasheet (sub)form. The current record is usually
indentified by reverse colors, but that will not "stick"
when you navigate to another record. I have no idea what
you mean by "correct record" or why you would want to
maintain a hightlight on an "incorrect" record???

It's a little complicated, but you could highlight two or
three different records with different colors by using a
continuous view (sub)form instead of datasheet view.

 

[Marshall Barton]

OK that helps a little, but I still don't understand what
you want in the way of highlighting records.

You have also intoduced a whole new concept when you mention
using a subdatasheet. As I said before, you can not control
the highlighting in a datasheet form, much less a
subdatasheet. If you are using that term in a casual way,
be aware that it is misleading and not communicating your
real requirements.

As for copying a record using code, that's fairly
straightforward, but you have to specify the details of how
that should happen. Which table should it be appended to?
Which fields should and should not be copied? What are the
names of the controls on the form and what fields (and their
type) are they bound to? For instance, if all the fields
(except an AutoNumber primary key?) in the table are to be
copied from the form's current record's visible controls to
the same table that the form is bound to, you can use the
button wizard to generate a simple minded record duplication
procedure. If all those presumptions are not met, then a
different procedure will be needed.
--
Marsh
MVP [MS Access]

Just to answer your points to clarify all this:
1. The refresh button simply updates the query match all records starting
with "xyz", the user then navigates to the record they want to select,
simply because there are lots of formats eg; music formats like cd, cda,
cd5, vynil, with the same artist & title, they also have different prices,
so batch records works best. Hope that helps.

2. I need to append all the fields for that record (row)eliminating user
error having to select all the fields. Highlighting the entire row in the
sub-datasheet as default, you can do this manually by selecting the arrow
next to the record but want it to be default when tabbing into the
sub-datasheet then simply user scrolls with cursor keys to the record that
fits & hits the append button.

How to highlight an entire row of records and hold this format while user
navigates to correct record, (6 fields) so an append query (via a control)
can then be run to append the selected row to another table.

I have a product search form with:

1. An unbound text box (that is the criteria for a query) for user data
entry eg: Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*")
2. A control button 'refresh form data' that refreshes form data once data
input complete.
3. a datasheet subform for the query.

When the form opens the curser is in the text box awaiting data entry, the
user then refreshes form data and the records matching query criteria are
shown in the datasheet.

So will need to have the row automatically selected once the form data has
been refreshed (nice), or manually select the row by clicking on the arrow
next to the record row (which highlights that entire row), then
navigating to the record with keyboard up/down keys to select the required
row. I know how to build an append query based on a form controls etc. so
have done that ok. I would guess that the "selected" record row are the
required data for the append query & nothing more needs to be done on the
query.

MY QUESTION:
1. How to highlight an entire record row automatically, and hold this
format
while user navigates to correct record.

2. How to use the record row in an append query.
Would the code look like:

INSERT INTO [Tbl_SBuy Temp] ( Title, Format, SPrice, BCPrice, BTPrice )
SELECT Forms!Frm_FindTitles_BOOKS!TITLE AS Expr1,
Forms!Frm_FindTitles_BOOKS!FORMAT AS Expr2,
Forms!Frm_FindTitles_BOOKS!SPrice AS Expr3,
Forms!Frm_FindTitles_BOOKS!CBPrice AS Expr4,
Forms!Frm_FindTitles_BOOKS!TBPrice AS Expr5;

"Marshall Barton" wrote
It's easy enough to get the text box to select a record in a
subform (even if I don't understand what/why a "refresh" is
needed), but I don't think you can "highLight" a row in a
datasheet (sub)form. The current record is usually
indentified by reverse colors, but that will not "stick"
when you navigate to another record. I have no idea what
you mean by "correct record" or why you would want to
maintain a hightlight on an "incorrect" record???

It's a little complicated, but you could highlight two or
three different records with different colors by using a
continuous view (sub)form instead of datasheet view.

 

[postman]

Phew...ok first to answer your points:
Highlighting (selecting) records, for the query to append, I guess wherever
the curser (row) is will do this regardless of it being in one field or the
next in the row, but would like the row (all fields) to be selected
(highlighted by default) when tabbed into the datasheet, just looks & feels
better.

The subdata sheet reference I made is in fact a select query with criteria
"like xyz*" from a combo box on the form to the title field in the query,
made into form datasheet & placed into the form, the refresh button
refreshes the datasheet if the user wants to search again on a new title
etc. It's a simple search "title" & append "record" form.

Ok accepted what about a sub-datasheet?, I have seen forms written in vb I
think that have this as default in datasheets in a form. I did make an forum
enquiry earlier on this, its just that my form has changed considerably
since so I re-posted, just to give you that previous posting answer here:
****************************************************************************
How to open a form that automatically selects a) a row.
ANSWER:
You don't mention how this row is to be selected? Do you with to pass this
value to the form when you open it? It is just not clear how/when/where you
want this row to be selected? To open a form to ONE record, you can simply
go:

docmd.OpenForm "frmCustomers",,,"id = 123"

The above would open he form, and then send the form to the above one
record. However, in your case, it seems we want to open the form, but that
form might have many records, and we just want to position the record to id.
Lets assume this, and use 123 for our example. We can go

docmd.OpenForm "my",,,,,,123

Note the number of commas...we are simply passing the value of 123 to the
form, and we can example this value passed.

So, now, in the forms on load event, we position the form to customer with
id = 123

me.RecordSet.FindFirst "id = " & me.OpenArgs

b) navigate via

arrow controls up & down

The above is generally the default for a datasheet, but if you are using a
continues form, then you need to add the following code to the forms keydown
event handler, and set the forms keypreview = yes.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand
On Error Resume Next

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acNext

End Select

End Sub

c) 'Copy & Paste' the row to another sub-datasheet on the same form via
another control on that form.

Copy and paste sounds like a bad word here, we you want to do is copy the
reocrd via a append query to the other table?

You can go:

dim strSql as string

strSql = "INSERT INTO tblSecondTable ( Description, Catagory, amount ) " & _
" SELECT Description, Catagory, amount FROM tblAnswers " & _
" where ID = " & me!id

CurrentDB.Execute

You also should do a requery on the 2nd form to show the above update...

me.MySubForm2.requery


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
***********************************************************************************

It's a bit missleeding now I have worked out how to enter my search criteria
into the query. Now I just want to know how to tab into the forms datasheet
with the entire row selected.

THANKS.

OK that helps a little, but I still don't understand what
you want in the way of highlighting records.

You have also intoduced a whole new concept when you mention
using a subdatasheet. As I said before, you can not control
the highlighting in a datasheet form, much less a
subdatasheet. If you are using that term in a casual way,
be aware that it is misleading and not communicating your
real requirements.

As for copying a record using code, that's fairly
straightforward, but you have to specify the details of how
that should happen. Which table should it be appended to?
Which fields should and should not be copied? What are the
names of the controls on the form and what fields (and their
type) are they bound to? For instance, if all the fields
(except an AutoNumber primary key?) in the table are to be
copied from the form's current record's visible controls to
the same table that the form is bound to, you can use the
button wizard to generate a simple minded record duplication
procedure. If all those presumptions are not met, then a
different procedure will be needed.
--
Marsh
MVP [MS Access]

Just to answer your points to clarify all this:
1. The refresh button simply updates the query match all records starting
with "xyz", the user then navigates to the record they want to select,
simply because there are lots of formats eg; music formats like cd, cda,
cd5, vynil, with the same artist & title, they also have different prices,
so batch records works best. Hope that helps.

2. I need to append all the fields for that record (row)eliminating user
error having to select all the fields. Highlighting the entire row in the
sub-datasheet as default, you can do this manually by selecting the arrow
next to the record but want it to be default when tabbing into the
sub-datasheet then simply user scrolls with cursor keys to the record that
fits & hits the append button.

postman wrote:
How to highlight an entire row of records and hold this format while
user
navigates to correct record, (6 fields) so an append query (via a
control)
can then be run to append the selected row to another table.

I have a product search form with:

1. An unbound text box (that is the criteria for a query) for user data
entry eg: Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*")
2. A control button 'refresh form data' that refreshes form data once
data
input complete.
3. a datasheet subform for the query.

When the form opens the curser is in the text box awaiting data entry,
the
user then refreshes form data and the records matching query criteria
are
shown in the datasheet.

So will need to have the row automatically selected once the form data
has
been refreshed (nice), or manually select the row by clicking on the
arrow
next to the record row (which highlights that entire row), then
navigating to the record with keyboard up/down keys to select the
required
row. I know how to build an append query based on a form controls etc.
so
have done that ok. I would guess that the "selected" record row are the
required data for the append query & nothing more needs to be done on
the
query.

MY QUESTION:
1. How to highlight an entire record row automatically, and hold this
format
while user navigates to correct record.

2. How to use the record row in an append query.
Would the code look like:

INSERT INTO [Tbl_SBuy Temp] ( Title, Format, SPrice, BCPrice, BTPrice )
SELECT Forms!Frm_FindTitles_BOOKS!TITLE AS Expr1,
Forms!Frm_FindTitles_BOOKS!FORMAT AS Expr2,
Forms!Frm_FindTitles_BOOKS!SPrice AS Expr3,
Forms!Frm_FindTitles_BOOKS!CBPrice AS Expr4,
Forms!Frm_FindTitles_BOOKS!TBPrice AS Expr5;

"Marshall Barton" wrote
It's easy enough to get the text box to select a record in a
subform (even if I don't understand what/why a "refresh" is
needed), but I don't think you can "highLight" a row in a
datasheet (sub)form. The current record is usually
indentified by reverse colors, but that will not "stick"
when you navigate to another record. I have no idea what
you mean by "correct record" or why you would want to
maintain a hightlight on an "incorrect" record???

It's a little complicated, but you could highlight two or
three different records with different colors by using a
continuous view (sub)form instead of datasheet view.

 

[Marshall Barton]

First, you probably want to forget anything you think you
know about VB forms. They are a completely different
critter from Access forms, and what you described would
require a lot of code. Access's datacentric forms do many
of that automatically, if you can live within its standard
capabilities.

I think you're still using the term subdatasheet
inappropriately. These are the funky things that expand out
of a field when you click on the + sign in the field. You
have very little control over how they look.

You're right that most of what Albert posted no longer
applies to what you're doing now. It's all good ideas, just
not what you want now.

Since, you are using the subform's record source query to
filter the subform's dataset, all records in the subfom will
match the search/filter criteria and it doesn't matter which
record is "highlighted". As you've noted, the entire row is
"highlighted" if you click on the record's RecordSelector
box to left of the record's data. This effect can also be
achieved in code using:
DoCmd.RunCommand acCmdSelectRecord
in the subform's Current event to"highlight" the record no
matter how you navigate to the record.
--
Marsh
MVP [MS Access]

Phew...ok first to answer your points:
Highlighting (selecting) records, for the query to append, I guess wherever
the curser (row) is will do this regardless of it being in one field or the
next in the row, but would like the row (all fields) to be selected
(highlighted by default) when tabbed into the datasheet, just looks & feels
better.

The subdata sheet reference I made is in fact a select query with criteria
"like xyz*" from a combo box on the form to the title field in the query,
made into form datasheet & placed into the form, the refresh button
refreshes the datasheet if the user wants to search again on a new title
etc. It's a simple search "title" & append "record" form.


Ok accepted what about a sub-datasheet?, I have seen forms written in vb I
think that have this as default in datasheets in a form. I did make an forum
enquiry earlier on this, its just that my form has changed considerably
since so I re-posted, just to give you that previous posting answer here:

[snip]

 

[postman]

Ok Marsh, I might move on if its not practical to hard code the "click on
the record's RecordSelector box to left of the record's data" hardcoded as a
default for the subform datasheet.
Thanks again.

First, you probably want to forget anything you think you
know about VB forms. They are a completely different
critter from Access forms, and what you described would
require a lot of code. Access's datacentric forms do many
of that automatically, if you can live within its standard
capabilities.

I think you're still using the term subdatasheet
inappropriately. These are the funky things that expand out
of a field when you click on the + sign in the field. You
have very little control over how they look.

You're right that most of what Albert posted no longer
applies to what you're doing now. It's all good ideas, just
not what you want now.

Since, you are using the subform's record source query to
filter the subform's dataset, all records in the subfom will
match the search/filter criteria and it doesn't matter which
record is "highlighted". As you've noted, the entire row is
"highlighted" if you click on the record's RecordSelector
box to left of the record's data. This effect can also be
achieved in code using:
DoCmd.RunCommand acCmdSelectRecord
in the subform's Current event to"highlight" the record no
matter how you navigate to the record.
--
Marsh
MVP [MS Access]

Phew...ok first to answer your points:
Highlighting (selecting) records, for the query to append, I guess
wherever
the curser (row) is will do this regardless of it being in one field or
the
next in the row, but would like the row (all fields) to be selected
(highlighted by default) when tabbed into the datasheet, just looks &
feels
better.

The subdata sheet reference I made is in fact a select query with criteria
"like xyz*" from a combo box on the form to the title field in the query,
made into form datasheet & placed into the form, the refresh button
refreshes the datasheet if the user wants to search again on a new title
etc. It's a simple search "title" & append "record" form.


Ok accepted what about a sub-datasheet?, I have seen forms written in vb I
think that have this as default in datasheets in a form. I did make an
forum
enquiry earlier on this, its just that my form has changed considerably
since so I re-posted, just to give you that previous posting answer here:

[snip]

 

[Marshall Barton]

Are we still talking about different things??? I thought
that the DoCmd I posted was a way to hardcode the effect of
clicking on the record selector. In what way is that not
what you wanted?
--
Marsh
MVP [MS Access]

Ok Marsh, I might move on if its not practical to hard code the "click on
the record's RecordSelector box to left of the record's data" hardcoded as a
default for the subform datasheet.
Thanks again.

First, you probably want to forget anything you think you
know about VB forms. They are a completely different
critter from Access forms, and what you described would
require a lot of code. Access's datacentric forms do many
of that automatically, if you can live within its standard
capabilities.

I think you're still using the term subdatasheet
inappropriately. These are the funky things that expand out
of a field when you click on the + sign in the field. You
have very little control over how they look.

You're right that most of what Albert posted no longer
applies to what you're doing now. It's all good ideas, just
not what you want now.

Since, you are using the subform's record source query to
filter the subform's dataset, all records in the subfom will
match the search/filter criteria and it doesn't matter which
record is "highlighted". As you've noted, the entire row is
"highlighted" if you click on the record's RecordSelector
box to left of the record's data. This effect can also be
achieved in code using:
DoCmd.RunCommand acCmdSelectRecord
in the subform's Current event to"highlight" the record no
matter how you navigate to the record.
--
Marsh
MVP [MS Access]

Phew...ok first to answer your points:
Highlighting (selecting) records, for the query to append, I guess
wherever
the curser (row) is will do this regardless of it being in one field or
the
next in the row, but would like the row (all fields) to be selected
(highlighted by default) when tabbed into the datasheet, just looks &
feels
better.

The subdata sheet reference I made is in fact a select query with criteria
"like xyz*" from a combo box on the form to the title field in the query,
made into form datasheet & placed into the form, the refresh button
refreshes the datasheet if the user wants to search again on a new title
etc. It's a simple search "title" & append "record" form.

you can not control the highlighting in a datasheet form

Ok accepted what about a sub-datasheet?, I have seen forms written in vb I
think that have this as default in datasheets in a form. I did make an
forum
enquiry earlier on this, its just that my form has changed considerably
since so I re-posted, just to give you that previous posting answer here:

[snip]

 

[postman]

Sorry mate, could not get it to work.
happy to accept any comments you have now that I have re-structured the
form.
Thanks.

Are we still talking about different things??? I thought
that the DoCmd I posted was a way to hardcode the effect of
clicking on the record selector. In what way is that not
what you wanted?
--
Marsh
MVP [MS Access]

Ok Marsh, I might move on if its not practical to hard code the "click on
the record's RecordSelector box to left of the record's data" hardcoded as
a
default for the subform datasheet.
Thanks again.

First, you probably want to forget anything you think you
know about VB forms. They are a completely different
critter from Access forms, and what you described would
require a lot of code. Access's datacentric forms do many
of that automatically, if you can live within its standard
capabilities.

I think you're still using the term subdatasheet
inappropriately. These are the funky things that expand out
of a field when you click on the + sign in the field. You
have very little control over how they look.

You're right that most of what Albert posted no longer
applies to what you're doing now. It's all good ideas, just
not what you want now.

Since, you are using the subform's record source query to
filter the subform's dataset, all records in the subfom will
match the search/filter criteria and it doesn't matter which
record is "highlighted". As you've noted, the entire row is
"highlighted" if you click on the record's RecordSelector
box to left of the record's data. This effect can also be
achieved in code using:
DoCmd.RunCommand acCmdSelectRecord
in the subform's Current event to"highlight" the record no
matter how you navigate to the record.
--
Marsh
MVP [MS Access]


postman wrote:
Phew...ok first to answer your points:
Highlighting (selecting) records, for the query to append, I guess
wherever
the curser (row) is will do this regardless of it being in one field or
the
next in the row, but would like the row (all fields) to be selected
(highlighted by default) when tabbed into the datasheet, just looks &
feels
better.

The subdata sheet reference I made is in fact a select query with
criteria
"like xyz*" from a combo box on the form to the title field in the
query,
made into form datasheet & placed into the form, the refresh button
refreshes the datasheet if the user wants to search again on a new title
etc. It's a simple search "title" & append "record" form.

you can not control the highlighting in a datasheet form

Ok accepted what about a sub-datasheet?, I have seen forms written in vb
I
think that have this as default in datasheets in a form. I did make an
forum
enquiry earlier on this, its just that my form has changed considerably
since so I re-posted, just to give you that previous posting answer
here:
[snip]

 

[Marshall Barton]

Could not get it to work??

Restructured the form??

I have no idea what we're talking about now, would you care
to explain what's happening.

 

[postman]

Yup, lets start again...
I have a form with a subform (datasheet) in it. The subform is a query, with
a criteria "like (xyz) &*" that looks to the form for its criteria; an
unbound text box for user input. A refresh button to refresh the form data
(record source is the query), and an append button.
I would like to be able to tab through the controls; 1.texbox 2.refresh
3.subform 4.append button, ok I can do this with tab order no prob.
When I tab into the subform (datasheet 3. above), I would like to have an
entire row highlighted as default (not having to select it manually with
mouse by record selector), so I then can use the up/down keyboard keys to
navigate up/down to the record row I wish to append. Because there are more
than one record returned by the query I want this selected highlighted row
to "hold" while navigating to user choice record.
If achievable I can implement this behaviour into several more forms I have.
Thanks.

Could not get it to work??

Restructured the form??

I have no idea what we're talking about now, would you care
to explain what's happening.
--
Marsh
MVP [MS Access]

Sorry mate, could not get it to work.
happy to accept any comments you have now that I have re-structured the
form.


"Marshall Barton" wrote

 

[Marshall Barton]

Yup, lets start again...
I have a form with a subform (datasheet) in it. The subform is a query, with
a criteria "like (xyz) &*" that looks to the form for its criteria; an
unbound text box for user input. A refresh button to refresh the form data
(record source is the query), and an append button.
I would like to be able to tab through the controls; 1.texbox 2.refresh
3.subform 4.append button, ok I can do this with tab order no prob.
When I tab into the subform (datasheet 3. above), I would like to have an
entire row highlighted as default (not having to select it manually with
mouse by record selector), so I then can use the up/down keyboard keys to
navigate up/down to the record row I wish to append. Because there are more
than one record returned by the query I want this selected highlighted row
to "hold" while navigating to user choice record.
If achievable I can implement this behaviour into several more forms I have.

Please don't paraphrase your code. Above, you said the
subform query has the criteria "like (xyz) &*", which looks
more than a little fishy to me, so I suspect that you are
really using something different. Please post this kind of
thing by using Copy/Paste from your query/code into your
message. It's is also important to communicate the type
(Text, numeric, DateTime, etc) of the field that has the
criteria.

OK, your new problem situation looks like the same situation
that we started from. Eventually, I suggested that you try
using the form's Current event to execute the line:
DoCmd.RunCommand acCmdSelectRecord
to which you replied "it didn't work" and that you "have
re-structured the form".

I need to know what happened. Nothing? An error (what
error)? Selected the wrong record? Or what ??? Under what
circumstances did yout get what result?

What did you do to "restructure" the form??

 

[postman]

SORRY record source for the form is the table.

Yup, lets start again...
I have a form with a subform (datasheet) in it. The subform is a query,
with a criteria "like (xyz) &*" that looks to the form for its criteria;
an unbound text box for user input. A refresh button to refresh the form
data (record source is the query), and an append button.
I would like to be able to tab through the controls; 1.texbox 2.refresh
3.subform 4.append button, ok I can do this with tab order no prob.
When I tab into the subform (datasheet 3. above), I would like to have an
entire row highlighted as default (not having to select it manually with
mouse by record selector), so I then can use the up/down keyboard keys to
navigate up/down to the record row I wish to append. Because there are
more than one record returned by the query I want this selected
highlighted row to "hold" while navigating to user choice record.
If achievable I can implement this behaviour into several more forms I
have.
Thanks.

Could not get it to work??

Restructured the form??

I have no idea what we're talking about now, would you care
to explain what's happening.
--
Marsh
MVP [MS Access]

Sorry mate, could not get it to work.
happy to accept any comments you have now that I have re-structured the
form.


"Marshall Barton" wrote
Are we still talking about different things??? I thought
that the DoCmd I posted was a way to hardcode the effect of
clicking on the record selector. In what way is that not
what you wanted?


postman wrote:
Ok Marsh, I might move on if its not practical to hard code the "click
on
the record's RecordSelector box to left of the record's data" hardcoded
as
a default for the subform datasheet.

 

[postman]

The queries criteria code is:
Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*"
Input01 is the unbound textbox on the form the user types in xy..whatever, &
then refreshes the forms data to updat the query.
Data type for the criteria is: text
How have I restructured the form...well I built an append query to take the
selected row feilds to another form, which has the feilds:
Expr1: Forms!Frm_FindTitles_DVD![ds-qry_dB_DVD_Search].Form!TITLE
which is the scource feilds for the append query. Before i did not. Also
there are 4 other expr: feilds for the query that take data from the forms
subform.

I put this code:
DoCmd.RunCommand acCmdSelectRecord
into the form's On Current Event when it was in design view as you have
suggested.
What does it do?

to which you replied "it didn't work"

ahh well I was refering to the original answer to my question by Albert D.
Kallal that I inserted into one of my recent posts. And it was:
me.RecordSet.FindFirst "id = " & me.OpenArgs
& also:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand
On Error Resume Next

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acNext

End Select

End Sub

THANKS.

 

[Marshall Barton]

See comments inline.

The queries criteria code is:
Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*"
Input01 is the unbound textbox on the form the user types in xy..whatever, &
then refreshes the forms data to updat the query.
Data type for the criteria is: text

Ok, now I understand what you meant. That part is working
right?

How have I restructured the form...well I built an append query to take the
selected row feilds to another form, which has the feilds:
Expr1: Forms!Frm_FindTitles_DVD![ds-qry_dB_DVD_Search].Form!TITLE
which is the scource feilds for the append query. Before i did not. Also
there are 4 other expr: feilds for the query that take data from the forms
subform.

This is a separate issue from what I was trying to help you
with. Let's try to stay focused here, one question at a
time.

I put this code:
DoCmd.RunCommand acCmdSelectRecord
into the form's On Current Event when it was in design view as you have
suggested.
What does it do?

That was supposed to go in the subform's Current event
procedure, not in the main form's OnCurrent property.

What it does is select the entire record, the same as if you
clicked on the record selector, which is what I thought was
the object of this exercise.

ahh well I was refering to the original answer to my question by Albert D.
Kallal that I inserted into one of my recent posts.

You should respond to Alberts suggestions in a reply to his
posts. These newsgroups conversations are tough enough to
follow without getting multiple steams of thoughts
intertwined.

 

[postman]

I'm sure it's just a straightfoward form / sub form format question
regardless of the queries tables etc.

The queries criteria code is:
Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*"
Input01 is the unbound textbox on the form the user types in xy..whatever,
& then refreshes the forms data to updat the query.
Data type for the criteria is: text
How have I restructured the form...well I built an append query to take
the selected row feilds to another form, which has the feilds:
Expr1: Forms!Frm_FindTitles_DVD![ds-qry_dB_DVD_Search].Form!TITLE
which is the scource feilds for the append query. Before i did not. Also
there are 4 other expr: feilds for the query that take data from the forms
subform.

I put this code:
DoCmd.RunCommand acCmdSelectRecord
into the form's On Current Event when it was in design view as you have
suggested.
What does it do?

to which you replied "it didn't work"

ahh well I was refering to the original answer to my question by Albert D.
Kallal that I inserted into one of my recent posts. And it was:
me.RecordSet.FindFirst "id = " & me.OpenArgs
& also:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand
On Error Resume Next

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acNext

End Select

End Sub

THANKS.

 

[Marshall Barton]

If we're still talking about selecting the current record in
the datasheet view subform, it is NOT a formatting problem.

Did you put the line of code in the subform as I suggested?
If you did, what happened?

 

[postman]

Original tread has been wiped!

See comments inline.

The queries criteria code is:
Like ([forms]![Frm_FindTitles_DVD]![Input01]) & "*"
Input01 is the unbound textbox on the form the user types in xy..whatever,
&
then refreshes the forms data to updat the query.
Data type for the criteria is: text

Ok, now I understand what you meant. That part is working
right?

How have I restructured the form...well I built an append query to take
the
selected row feilds to another form, which has the feilds:
Expr1: Forms!Frm_FindTitles_DVD![ds-qry_dB_DVD_Search].Form!TITLE
which is the scource feilds for the append query. Before i did not. Also
there are 4 other expr: feilds for the query that take data from the forms
subform.

This is a separate issue from what I was trying to help you
with. Let's try to stay focused here, one question at a
time.

I put this code:
DoCmd.RunCommand acCmdSelectRecord
into the form's On Current Event when it was in design view as you have
suggested.
What does it do?

That was supposed to go in the subform's Current event
procedure, not in the main form's OnCurrent property.

What it does is select the entire record, the same as if you
clicked on the record selector, which is what I thought was
the object of this exercise.

ahh well I was refering to the original answer to my question by Albert D.
Kallal that I inserted into one of my recent posts.

You should respond to Alberts suggestions in a reply to his
posts. These newsgroups conversations are tough enough to
follow without getting multiple steams of thoughts
intertwined.

 

[postman]

As I have already stated YES the line of code was put in...and as I have
already asked "what does it do"? as it appeared to do nothing.
For ref to that question go back 2/3 posts.

I put this code:
DoCmd.RunCommand acCmdSelectRecord
into the form's On Current Event when it was in design view as you have
suggested.

Again what does it do?

Can you see any application in the code from Albert D. Kallal:
me.RecordSet.FindFirst "id = " & me.OpenArgs
& also:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

' key hand
On Error Resume Next

Select Case KeyCode

Case vbKeyUp
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acPrevious

Case vbKeyDown
KeyCode = 0
DoCmd.GoToRecord acActiveDataObject, , acNext

End Select

End Sub

THANKS.

If we're still talking about selecting the current record in
the datasheet view subform, it is NOT a formatting problem.

Did you put the line of code in the subform as I suggested?
If you did, what happened?
--
Marsh
MVP [MS Access]

I'm sure it's just a straightfoward form / sub form format question
regardless of the queries tables etc.