|
 |
access thread: Combo box Find Record
Message #1 by simonds@m... on Mon, 10 Dec 2001 20:37:24
|
|
I have a form whose Record Source is a single table. I have an unbound
combo box in the header of the form that draws on a row source query
selecting Primary Key (Enrolment_Num), First and Last Names (concatenated
into Name expression) and a unique Computer_Num. It provides a drop down
list of all persons in that table. I want to be able to select a name from
the list and the form data on the detail section below it to be displayed
in their respective fields.
Below is the coding so far. The problem is, is that it (the drop down)
only works when I click on the new record button in the navigation bar at
the bottom. The last name stays in the lookup, but it at least allows me
to select a new name and the form data appears properly for that record.
After that, it seems as if there is a lock on the lookup control that
won't allow me to select a different record/name. I have to click on
the "add new record" button in the navigation selectors at the bottom.
___________________________________________
Private Sub cboSelectName_AfterUpdate()
If Not IsNull(cboSelectName) Then
DoCmd.GoToControl "Computer_No"
DoCmd.FindRecord cboSelectName, acEntire, , acSearchAll, True, acAll
End If
End Sub
___________________________________________
Is there a way to just be able to select a new name?
Thanks for any help.
Regards,
Eric
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 10 Dec 2001 13:59:58 -0800
|
|
There's actually a wizard that will write this code for you. Make sure the
control wizards button is pressed on the Form Design toolbar. Then drop a
combo box on your form and choose the third option on the first wizard
screen: "Find a record on my form based on the value selected in my combo
box". Start out pretending you want to use the combo to choose your primary
key field & make sure it works as you expect. Once it does, then change the
combo's recordsource property to the one you're using now (so the bound
column is still the PK, but that column is hidden (has a width of 0) and
only "Name" is displayed). Hopefully that's clear...
HTH,
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Monday, December 10, 2001 12:37 PM
To: Access
Subject: [access] Combo box Find Record
I have a form whose Record Source is a single table. I have an unbound
combo box in the header of the form that draws on a row source query
selecting Primary Key (Enrolment_Num), First and Last Names (concatenated
into Name expression) and a unique Computer_Num. It provides a drop down
list of all persons in that table. I want to be able to select a name from
the list and the form data on the detail section below it to be displayed
in their respective fields.
Below is the coding so far. The problem is, is that it (the drop down)
only works when I click on the new record button in the navigation bar at
the bottom. The last name stays in the lookup, but it at least allows me
to select a new name and the form data appears properly for that record.
After that, it seems as if there is a lock on the lookup control that
won't allow me to select a different record/name. I have to click on
the "add new record" button in the navigation selectors at the bottom.
___________________________________________
Private Sub cboSelectName_AfterUpdate()
If Not IsNull(cboSelectName) Then
DoCmd.GoToControl "Computer_No"
DoCmd.FindRecord cboSelectName, acEntire, , acSearchAll, True, acAll
End If
End Sub
___________________________________________
Is there a way to just be able to select a new name?
Thanks for any help.
Regards,
Eric
Message #3 by simonds@m... on Mon, 10 Dec 2001 22:35:14
|
|
I did that (see the code below), but it still only works when I click on
the new record navigation button at the bottom after the first go round. I
have to then click on the new record button at the bottom again and then
it changes to my selection. If I want to view another record, I have to
click the new record button, then re-select. I'm trying to get around this.
Sub Combo136_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[nEnrol_Rec] = " & Me![Combo136]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
> There's actually a wizard that will write this code for you. Make sure
the
> control wizards button is pressed on the Form Design toolbar. Then drop
a
> combo box on your form and choose the third option on the first wizard
> screen: "Find a record on my form based on the value selected in my combo
> box". Start out pretending you want to use the combo to choose your
primary
> key field & make sure it works as you expect. Once it does, then change
the
> combo's recordsource property to the one you're using now (so the bound
> column is still the PK, but that column is hidden (has a width of 0) and
> only "Name" is displayed). Hopefully that's clear...
>
> HTH,
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: simonds@m... [mailto:simonds@m...]
> Sent: Monday, December 10, 2001 12:37 PM
> To: Access
> Subject: [access] Combo box Find Record
>
>
>
> I have a form whose Record Source is a single table. I have an unbound
> combo box in the header of the form that draws on a row source query
> selecting Primary Key (Enrolment_Num), First and Last Names
(concatenated
> into Name expression) and a unique Computer_Num. It provides a drop down
> list of all persons in that table. I want to be able to select a name
from
> the list and the form data on the detail section below it to be
displayed
> in their respective fields.
>
> Below is the coding so far. The problem is, is that it (the drop down)
> only works when I click on the new record button in the navigation bar
at
> the bottom. The last name stays in the lookup, but it at least allows me
> to select a new name and the form data appears properly for that record.
> After that, it seems as if there is a lock on the lookup control that
> won't allow me to select a different record/name. I have to click on
> the "add new record" button in the navigation selectors at the bottom.
>
> ___________________________________________
>
> Private Sub cboSelectName_AfterUpdate()
>
> If Not IsNull(cboSelectName) Then
> DoCmd.GoToControl "Computer_No"
> DoCmd.FindRecord cboSelectName, acEntire, , acSearchAll, True,
acAll
> End If
>
> End Sub
> ___________________________________________
>
> Is there a way to just be able to select a new name?
>
> Thanks for any help.
>
> Regards,
> Eric
Message #4 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 10 Dec 2001 15:10:00 -0800
|
|
Is there any change if you add the following line to the Form's OnCurrent
event:
Me.Combo136.Value = me.nEnrol_Rec
?
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Monday, December 10, 2001 2:35 PM
To: Access
Subject: [access] RE: Combo box Find Record
I did that (see the code below), but it still only works when I click on
the new record navigation button at the bottom after the first go round. I
have to then click on the new record button at the bottom again and then
it changes to my selection. If I want to view another record, I have to
click the new record button, then re-select. I'm trying to get around this.
Sub Combo136_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[nEnrol_Rec] = " & Me![Combo136]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
> There's actually a wizard that will write this code for you. Make sure
the
> control wizards button is pressed on the Form Design toolbar. Then drop
a
> combo box on your form and choose the third option on the first wizard
> screen: "Find a record on my form based on the value selected in my combo
> box". Start out pretending you want to use the combo to choose your
primary
> key field & make sure it works as you expect. Once it does, then change
the
> combo's recordsource property to the one you're using now (so the bound
> column is still the PK, but that column is hidden (has a width of 0) and
> only "Name" is displayed). Hopefully that's clear...
>
> HTH,
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: simonds@m... [mailto:simonds@m...]
> Sent: Monday, December 10, 2001 12:37 PM
> To: Access
> Subject: [access] Combo box Find Record
>
>
>
> I have a form whose Record Source is a single table. I have an unbound
> combo box in the header of the form that draws on a row source query
> selecting Primary Key (Enrolment_Num), First and Last Names
(concatenated
> into Name expression) and a unique Computer_Num. It provides a drop down
> list of all persons in that table. I want to be able to select a name
from
> the list and the form data on the detail section below it to be
displayed
> in their respective fields.
>
> Below is the coding so far. The problem is, is that it (the drop down)
> only works when I click on the new record button in the navigation bar
at
> the bottom. The last name stays in the lookup, but it at least allows me
> to select a new name and the form data appears properly for that record.
> After that, it seems as if there is a lock on the lookup control that
> won't allow me to select a different record/name. I have to click on
> the "add new record" button in the navigation selectors at the bottom.
>
> ___________________________________________
>
> Private Sub cboSelectName_AfterUpdate()
>
> If Not IsNull(cboSelectName) Then
> DoCmd.GoToControl "Computer_No"
> DoCmd.FindRecord cboSelectName, acEntire, , acSearchAll, True,
acAll
> End If
>
> End Sub
> ___________________________________________
>
> Is there a way to just be able to select a new name?
>
> Thanks for any help.
>
> Regards,
> Eric
Message #5 by simonds@m... on Tue, 11 Dec 2001 05:17:18
|
|
Not exactly. It still won't let me select another record without first
clicking the new record button on the "Record:" nav bar (i.e., the >*
beside the record number) at the bottom of the form.
The great thing about the added code snippet is that the combo box now
clears the last name that was selected when I do click the Record: new nav
button, or when I use the Record: nav buttons to click through the Go To
next, previous, first, or last record (it didn't before but it did still
allow me to select a new name)! Thanks for that debugging part.
After I select a record (in the new record mode) and it goes to that
particular one, I can scroll through the list of names and the black
highlight shows up on individual list items as I move the cursor, but it
won't allow me to select a new name to go to, I still need to click the
new record nav button (form has a custom menu so I don't know if Insert --
> New Record would have the same effect, but one assumes that it would).
It is almost as if the combo box becomes disabled (or control.Enabled =
False), so I tried adding cboSelectName.Enabled = True at the bottom of
the RecordSet Clone statement in the AfterUpdate method, as well as
cboSelectName.Requery to see if it had any affect but it didn't.
This is a database that I inherited, and this component didn't work so I'm
not sure of anything else that may be interfering with the desired result.
Thanks for your input, Roy. ANy other ideas?
Regards,
Eric
> Is there any change if you add the following line to the Form's OnCurrent
> event:
>
> Me.Combo136.Value = me.nEnrol_Rec
>
> ?
>
> -Roy
>
> Roy Pardee
> Programmer/Analyst
> SWFPAC Lockheed Martin IT
> Extension 8487
>
> -----Original Message-----
> From: simonds@m... [mailto:simonds@m...]
> Sent: Monday, December 10, 2001 2:35 PM
> To: Access
> Subject: [access] RE: Combo box Find Record
>
>
> I did that (see the code below), but it still only works when I click on
> the new record navigation button at the bottom after the first go round.
I
> have to then click on the new record button at the bottom again and then
> it changes to my selection. If I want to view another record, I have to
> click the new record button, then re-select. I'm trying to get around
this.
>
> Sub Combo136_AfterUpdate()
> ' Find the record that matches the control.
> Me.RecordsetClone.FindFirst "[nEnrol_Rec] = " & Me![Combo136]
> Me.Bookmark = Me.RecordsetClone.Bookmark
> End Sub
>
>
>
> > There's actually a wizard that will write this code for you. Make
sure
> the
> > control wizards button is pressed on the Form Design toolbar. Then
drop
> a
> > combo box on your form and choose the third option on the first wizard
> > screen: "Find a record on my form based on the value selected in my
combo
> > box". Start out pretending you want to use the combo to choose your
> primary
> > key field & make sure it works as you expect. Once it does, then
change
> the
> > combo's recordsource property to the one you're using now (so the bound
> > column is still the PK, but that column is hidden (has a width of 0)
and
> > only "Name" is displayed). Hopefully that's clear...
> >
> > HTH,
> >
> > Roy Pardee
> > Programmer/Analyst
> > SWFPAC Lockheed Martin IT
> > Extension 8487
> >
> > -----Original Message-----
> > From: simonds@m... [mailto:simonds@m...]
> > Sent: Monday, December 10, 2001 12:37 PM
> > To: Access
> > Subject: [access] Combo box Find Record
> >
> >
> >
> > I have a form whose Record Source is a single table. I have an unbound
> > combo box in the header of the form that draws on a row source query
> > selecting Primary Key (Enrolment_Num), First and Last Names
> (concatenated
> > into Name expression) and a unique Computer_Num. It provides a drop
down
> > list of all persons in that table. I want to be able to select a name
> from
> > the list and the form data on the detail section below it to be
> displayed
> > in their respective fields.
> >
> > Below is the coding so far. The problem is, is that it (the drop down)
> > only works when I click on the new record button in the navigation bar
> at
> > the bottom. The last name stays in the lookup, but it at least allows
me
> > to select a new name and the form data appears properly for that
record.
> > After that, it seems as if there is a lock on the lookup control that
> > won't allow me to select a different record/name. I have to click on
> > the "add new record" button in the navigation selectors at the bottom.
> >
> > ___________________________________________
> >
> > Private Sub cboSelectName_AfterUpdate()
> >
> > If Not IsNull(cboSelectName) Then
> > DoCmd.GoToControl "Computer_No"
> > DoCmd.FindRecord cboSelectName, acEntire, , acSearchAll, True,
> acAll
> > End If
> >
> > End Sub
> > ___________________________________________
> >
> > Is there a way to just be able to select a new name?
> >
> > Thanks for any help.
> >
> > Regards,
> > Eric
>
Message #6 by simonds@m... on Tue, 11 Dec 2001 06:29:05
|
|
Roy,
I discovered what the problem is, I believe!! Form onLoad and Activate
methods had properties set to AllowEdits = False. There was a custom Edit
command button that onClick set AllowEdits = True. Default form property
had AllowEdits set to No in Data tab. Hence, only a new record would
technically allow an edit, or clicking the custom "Edit Record" button to
set the property to True.
Is there a workaround for this, do you think? The department would like to
have the default AlllowEdits = No to protect any accidental change of
data, unless the "Edit Record" button is purposely clicked.
I guess what I'm looking for at this point is to have this combo control
functional (i.e., able to change records), while still having the rest of
the form uneditable until the "Edit Record" button is clicked.
Which brings me to another point. Once the "Edit Record" is clicked, you
can modify the current record, which is what you want. However, I thought
about the need to reset the AllowEdits property back to false whenever the
form is changed to the next record (or closed, etc.). Is there a way to
check the state of the property and then change it? I tried the on change
event for the combo box by saying Me.AllowEdits = False because when the
record nav button was clicked the cboSelectName control would change to
the corresponding record (thanks to your code snippet, Roy -- still much
appreciated).
Any ideas on these couple of quirks?
Best wishes for the Yule Season,
Eric
Message #7 by "Pardee, Roy E" <roy.e.pardee@l...> on Thu, 13 Dec 2001 10:08:17 -0800
|
|
That sounds plausible to me... As for a workaround, I'm afraid I don't have
anything very simple. I implemented something similar in my last project &
ended up working with the .Locked properties of the individual controls,
which was a tad cumbersome.
One potential way to do it would be to put your record selector combo on the
header of the form, and all your substantive data bound controls on the
details section (if you're not set up that way already that is). Then when
you need to toggle the .Locked status of the controls on the detail section,
you loop through just those controls in the detail section & set them as
appropriate. Here's the code I used in my last app:
=====================================
Private Sub LockControls(booLock As Boolean, Optional booHide As Boolean
False)
' Locks and optionally also hides controls in the Detail section of the form
Dim ctl As Access.Control
Dim varCtl As Variant
On Error Resume Next
For Each ctl In m_frm.Section(acDetail).Controls
Select Case TypeName(ctl)
Case "TextBox", "ComboBox", "CheckBox"
ctl.Locked = booLock
ctl.Visible = Not booHide
Case "SubForm"
ctl.Locked = booLock
ctl.Visible = Not booHide
ctl.Form.AllowDeletions = Not booLock
ctl.Form.AllowAdditions = Not booLock
Case Else
' do nothing
End Select
Next ctl
For Each varCtl In m_ctlKeepInvisible
varCtl.Visible = False
Next varCtl
' I'm not sure why this is necessary, but the Err object was not getting
reset...
Err.Clear
End Sub
=====================================
As for managing the locked/unlocked status of the form controls, I put a
button on each form called cmdEditSave. When the form first comes up it has
a caption of "&Edit" and has the effect of unlocking the form controls, and
then changing its caption to "&Save". When pushed a second time, it locks
the form controls & changes its caption back to "&Edit". In the Click event
for the cmdEditSave button, the first thing the code does is read the
caption of the button to figure out whether we're putting the form into
editing mode or rather taking it out. This is working reasonably well for
me.
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Monday, December 10, 2001 10:29 PM
To: Access
Subject: [access] RE: Combo box Find Record
Roy,
I discovered what the problem is, I believe!! Form onLoad and Activate
methods had properties set to AllowEdits = False. There was a custom Edit
command button that onClick set AllowEdits = True. Default form property
had AllowEdits set to No in Data tab. Hence, only a new record would
technically allow an edit, or clicking the custom "Edit Record" button to
set the property to True.
Is there a workaround for this, do you think? The department would like to
have the default AlllowEdits = No to protect any accidental change of
data, unless the "Edit Record" button is purposely clicked.
I guess what I'm looking for at this point is to have this combo control
functional (i.e., able to change records), while still having the rest of
the form uneditable until the "Edit Record" button is clicked.
Which brings me to another point. Once the "Edit Record" is clicked, you
can modify the current record, which is what you want. However, I thought
about the need to reset the AllowEdits property back to false whenever the
form is changed to the next record (or closed, etc.). Is there a way to
check the state of the property and then change it? I tried the on change
event for the combo box by saying Me.AllowEdits = False because when the
record nav button was clicked the cboSelectName control would change to
the corresponding record (thanks to your code snippet, Roy -- still much
appreciated).
Any ideas on these couple of quirks?
Best wishes for the Yule Season,
Eric
|
|
|
View Cart
