To prove the select record

[an]

Hello!

I would like to click in row of combobox and activate the respective record
in form. For example, position it on top or highlighted it.
Thanks in advance.
an

 

[John Vinson]

Hello!

I would like to click in row of combobox and activate the respective record
in form. For example, position it on top or highlighted it.
Thanks in advance.
an

Use the Combo Box Wizard to create a new unbound combo box; use the
option "Use this combo to find an existing record".

John W. Vinson[MVP]

 

[an]

Thank you for your reply.

Please,
"Use this combo to find an existing record" is the same:
"Find a record on my form based on the value I selected in my combo box".
Thanks.
an

 

[Dirk Goldgar]

Thank you for your reply.

Please,
"Use this combo to find an existing record" is the same:
"Find a record on my form based on the value I selected in my combo
box". Thanks.

Yes.

 

[an]

Thanks for reply.

Then, the complet "story" is:

I have:

T_Sectors and T_Quart
(Relationships: One Sector to many Quart)

F_Sectors based in T_Sectors, with CboSectors based in same table, and
CboQuart
based in T_Quart.

In Row Source:
"SELECT [T_Quart].[Quarter] FROM T_Quart WHERE [IdSector]=[Text72];"
where [text72] is an auxiliar textbox .
The CboQuart result of the CboSectors (Filtered records)

In Detal section:
A SubF_Quart, based in T_Quart, show us records resulting from CboSectors too.
I would like to put an evidence a SubF_Quart's record, through click on row
of the CboQuart in F_Sectors.

But I don't know if is it possible...

Thanks in advance.
an

 

[Dirk Goldgar]

Thanks for reply.

Then, the complet "story" is:

I have:

T_Sectors and T_Quart
(Relationships: One Sector to many Quart)

F_Sectors based in T_Sectors, with CboSectors based in same table, and
CboQuart
based in T_Quart.

In Row Source:
"SELECT [T_Quart].[Quarter] FROM T_Quart WHERE [IdSector]=[Text72];"
where [text72] is an auxiliar textbox .
The CboQuart result of the CboSectors (Filtered records)

In Detal section:
A SubF_Quart, based in T_Quart, show us records resulting from
CboSectors too. I would like to put an evidence a SubF_Quart's
record, through click on row of the CboQuart in F_Sectors.

But I don't know if is it possible...

I take it that "SubF_Quart" is a subform? So, you want to use CboQuart,
which is on the main form, to find a record on the subform? Yes, that's
possible, but the combo box wizard won't build it for you. The code for
the combo box would look something like this:

'----- start of code (note: "air code") -----
Private Sub CboQuart_AfterUpdate()

Dim frm As Access.Form

Set frm = Me!SubF_Quart.Form

With frm.RecordsetClone

.FindFirst "Quarter = " & Me!CboQuart

If .NoMatch Then
MsgBox _
"The quarter you selected was not found in the " & _
form's current recordset. Remove any filter " & _
"you've applied, and try again.", _
vbInformation, _
"Not Found"
Else
frm.Bookmark = .Bookmark
End If

End With

Set frm = Nothing

End Sub
'----- end of code -----

Note that the above code assumes that Quarter is a numeric field. If
it's a text field, the .FindFirst call should be like this:

.FindFirst "Quarter = '" & Me!CboQuart & "'"

It also assumes that "SubF_Quart" is the name of the subform control on
the main form, and not merely the name of the form that is its
SourceObject.

 

[an]

DG,
Many thanks for your help.

Exactly:
SubF_Quart is a form, but there are a serious dubt in my spirit:
"The name of the subform control on the main form and not merely the name of
the form..."?
I have IdSector in Link Child Fields and in Link Master Fields. Is it, please?

However, applied your code with convenient change, because Quart is a text
field, don't alter nothing in any record in SubF_Quart, after click in
CboQuart's row (?)

More one time, many thanks.
an

Thanks for reply.

Then, the complet "story" is:

I have:

T_Sectors and T_Quart
(Relationships: One Sector to many Quart)

F_Sectors based in T_Sectors, with CboSectors based in same table, and
CboQuart
based in T_Quart.

In Row Source:
"SELECT [T_Quart].[Quarter] FROM T_Quart WHERE [IdSector]=[Text72];"
where [text72] is an auxiliar textbox .
The CboQuart result of the CboSectors (Filtered records)

In Detal section:
A SubF_Quart, based in T_Quart, show us records resulting from
CboSectors too. I would like to put an evidence a SubF_Quart's
record, through click on row of the CboQuart in F_Sectors.

But I don't know if is it possible...

I take it that "SubF_Quart" is a subform? So, you want to use CboQuart,
which is on the main form, to find a record on the subform? Yes, that's
possible, but the combo box wizard won't build it for you. The code for
the combo box would look something like this:

'----- start of code (note: "air code") -----
Private Sub CboQuart_AfterUpdate()

Dim frm As Access.Form

Set frm = Me!SubF_Quart.Form

With frm.RecordsetClone

.FindFirst "Quarter = " & Me!CboQuart

If .NoMatch Then
MsgBox _
"The quarter you selected was not found in the " & _
form's current recordset. Remove any filter " & _
"you've applied, and try again.", _
vbInformation, _
"Not Found"
Else
frm.Bookmark = .Bookmark
End If

End With

Set frm = Nothing

End Sub
'----- end of code -----

Note that the above code assumes that Quarter is a numeric field. If
it's a text field, the .FindFirst call should be like this:

.FindFirst "Quarter = '" & Me!CboQuart & "'"

It also assumes that "SubF_Quart" is the name of the subform control on
the main form, and not merely the name of the form that is its
SourceObject.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

DG,
Many thanks for your help.

Exactly:
SubF_Quart is a form, but there are a serious dubt in my spirit:
"The name of the subform control on the main form and not merely the
name of the form..."?

A subform is displayed in a control on the main form. That control is
the "window" in which another form object is displayed. That control is
what I mean by the "subform control". It has a name of its own, which
may or may not be the same as the name of the form that it displays --
the form that is named in its Source Object property. In the code I
posted, I assumed that "SubF_Quart" is the name of that subform control,
but that might be wrong. You should check that..

I have IdSector in Link Child Fields and in Link Master Fields. Is
it, please?

That sounds correct, but it's not what I was talking about.

However, applied your code with convenient change, because Quart is a
text field,

Is the name of the field "Quart", or is it "Quarter"?

don't alter nothing in any record in SubF_Quart, after
click in CboQuart's row (?)

I'm not sure what you mean. Are you saying that it doesn't find the
record? Do you get the "Not Found" message? If it doesn't find the
record, and you don't get that message, then something's wrong. Please
post the code from the combo box's AfterUpdate event, as you have it
now.

 

[an]

DG,
Many thanks for your reply, and
Sorry for my delay

- The name of the field is: Quarter
(I writed badly...)

- Don't return us any message. Only don´t signalize, in subform SubF_Quart,
what record we before selected in Cbo_Quart in form F_Sector how I would like

- The code from the combo box's AfterUpdate Event is the next:

'*****start code******

Private Sub CboQuart_AfterUpdate()

Dim frm As Access.Form
Set frm = Me!SubF_Quart.Form
With frm.RecordsetClone

' .FindFirst "Quarter = " & Me!CboQuart
.FindFirst "Quarter = '" & Me!CboQuart & "'"

If .NoMatch Then
MsgBox _
"The quarter you selected was not found in the " & _
"Form's current recordset. Remove any filter " & _
"you've applied, and try again.", _
vbInformation, _
"Not Found"
Else
frm.Bookmark = .Bookmark
End If

End With
Set frm = Nothing

End Sub
'******end of code******

Many, many thanks.
an

 

[Dirk Goldgar]

DG,
Many thanks for your reply, and
Sorry for my delay

- The name of the field is: Quarter
(I writed badly...)

- Don't return us any message. Only don´t signalize, in subform
SubF_Quart, what record we before selected in Cbo_Quart in form
F_Sector how I would like

- The code from the combo box's AfterUpdate Event is the next:

'*****start code******

Private Sub CboQuart_AfterUpdate()

Dim frm As Access.Form
Set frm = Me!SubF_Quart.Form
With frm.RecordsetClone

' .FindFirst "Quarter = " & Me!CboQuart
.FindFirst "Quarter = '" & Me!CboQuart & "'"

If .NoMatch Then
MsgBox _
"The quarter you selected was not found in the " & _
"Form's current recordset. Remove any filter " &
_ "you've applied, and try again.", _
vbInformation, _
"Not Found"
Else
frm.Bookmark = .Bookmark
End If

End With
Set frm = Nothing

End Sub
'******end of code******

If you don't get any error message yet it doesn't find the selected
record, we'd better make sure that the code is actually being executed
and, if so, where it goes wrong. Set a breakpoint on the Sub header for
the event procedure, then switch to form view and make a selection in
CboQuart. If the breakpoint doesn't fire, then the code isn't being
called. If it does fire, then step through the code to see what path it
takes, and whether any errors are raised. Either way, report the
results back here.

 

[an]

Sorry for my delay because I was to tried to test this.

The compiler run fine, but after activate F9, troggle breakpoint, and F8 or
CTRL+F8, nothing. (?)

Very grateful.
an

 

[Dirk Goldgar]

Sorry for my delay because I was to tried to test this.

The compiler run fine, but after activate F9, troggle breakpoint, and
F8 or CTRL+F8, nothing. (?)

Did you actually trigger the code by selecting in the combo box? Did
execution stop at the breakpoint? You can't run the code in an event
procedure just by pressing F8 or Ctrl+F8 while viewing the code --
execution must enter the procedure in the normal way.

Make sure that the combo box's AfterUpdate event property, on the Event
tab of its property sheet, is set to "[Event Procedure]".

 

[an]

All think I make all your instructions.
Nothing strange, but we haven't any "feed-back" in records of subform when
click in 2nd combo.

Grateful for your patiente...
an

Sorry for my delay because I was to tried to test this.

The compiler run fine, but after activate F9, troggle breakpoint, and
F8 or CTRL+F8, nothing. (?)

Did you actually trigger the code by selecting in the combo box? Did
execution stop at the breakpoint? You can't run the code in an event
procedure just by pressing F8 or Ctrl+F8 while viewing the code --
execution must enter the procedure in the normal way.

Make sure that the combo box's AfterUpdate event property, on the Event
tab of its property sheet, is set to "[Event Procedure]".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

All think I make all your instructions.
Nothing strange, but we haven't any "feed-back" in records of subform
when click in 2nd combo.

I'm sorry, I don't understand your reply. When you selected a quarter
in the combo box, was the event procedure called, and did execution halt
at the breakpoint? Did you then use F8 to step through the code? What
path did the code take?

 

[an]

Yes, it is.
When I select a quarter in the combo box, appearing the debuger window, the
position of the breakpoint is on start row code: "Private Sub..."

Using F8, or bouton, run all over the code and the execution don't halt on
any row of the code.

After this, the forms and combos work fine. Only don´t put an evidence the
respective record in Subform...

Many thanks.
an

 

[Dirk Goldgar]

Yes, it is.
When I select a quarter in the combo box, appearing the debuger
window, the position of the breakpoint is on start row code: "Private
Sub..."

Using F8, or bouton, run all over the code and the execution don't
halt on any row of the code.

After this, the forms and combos work fine. Only don´t put an
evidence the respective record in Subform...

Maybe I'm not understanding what the subform is actually displaying. Is
the subform in single-form view, or in continuous-forms view? Is the
record you selected in the combo box visible on the subform before you
make the selection? After you make the selection (and code has run)?

The code won't send the focus to the subform, so the combo box on the
main form will still have the focus. If you want the focus to move to
the subform, you could add a line to the code:

Else
frm.Bookmark = .Bookmark Me!SubF_Quart.SetFocus
End If

But if the record you want has not been found, that won't help.

 

[an]

"Is the subform in single-form view, or in continuous-forms view?"
- Continuous-forms.

"Is the record you selected in the combo box visible on the subform before you
make the selection?"
- Sorry. The 2nd combo (to choose Quarter) box is in Main Form too, to
select after 1st combo box (Sector). One Sector to many Quarters.

"After you make the selection (and code has run)?"
- After to make the 1st selection (combo box to sectors), appear only
quarters of this Sector in 2nd combo box.

"The code won't send the focus to the subform, so the combo box on the main
form will still have the focus. If you want the focus to move to the
subform, you could add a line to the code:

Else
frm.Bookmark = .Bookmark Me!SubF_Quart.SetFocus
End If

- Where, please? Both combos box are in main form.

Many thanks, one more time.
an

 

[Dirk Goldgar]

"Is the subform in single-form view, or in continuous-forms view?"
- Continuous-forms.

"Is the record you selected in the combo box visible on the subform
before you make the selection?"
- Sorry. The 2nd combo (to choose Quarter) box is in Main Form too, to
select after 1st combo box (Sector). One Sector to many Quarters.

"After you make the selection (and code has run)?"
- After to make the 1st selection (combo box to sectors), appear only
quarters of this Sector in 2nd combo box.

No, that's not what I meant. The purpose of the code is to let you pick
a quarter in CboQuarters, and have the subform SubF_Quart move to the
record for that quarter. Since the subform is in continuous form view,
it could be that the record is already among those records currently
showing on the subform, even before you select the quarter. I'm asking
whether the record for that quarter is currently among the records shown
on the subform (a) before you choose from the combo, or (b) after you
choose from the combo.

"The code won't send the focus to the subform, so the combo box on
the main form will still have the focus. If you want the focus to
move to the subform, you could add a line to the code:


- Where, please? Both combos box are in main form.

The added line was

Me!SubF_Quart.SetFocus

The other, quoted lines were to show the context, in the code for the
combo box. You should have no trouble locating those lines.

 

[an]

When open the main form, only show us Form Header wth:
- Combo box to choose Sector Name
- Text box to Sum Quarters in choosed sector
- Text box with Sector number

After choose the quarter - in 2nd Combo - open de subform in detail section
with all quarter's records.

I think, through conditional formatting and auxiliar text box in sub form,
to solve this situation (?...)

Many thanks
an

 

[an]

EXACTLY!!!

After insert the last line code, work fine.
Only your patiente - and knowledge - was possible!

Many, many thanks for all!!!
Good luck

an