Combo box retaining last entry
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Combo box retaining last entry
This is a little hard to explain, but here goes;
I've got a data entry form, with one field getting it's data from a combo-box. As the user will have numerous records to enter, I'd like to have the combo-box default to what was selected on the previous record, but still able to select an alternative value.
Easy, hard, I haven't figured it out.
I've got a data entry form, with one field getting it's data from a combo-box. As the user will have numerous records to enter, I'd like to have the combo-box default to what was selected on the previous record, but still able to select an alternative value.
Easy, hard, I haven't figured it out.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
In the first place, it's easy to copy the value of the same field in the previous record to the current record: click in the combo box (or any other bound control) and press Ctrl+' (apostrophe).
If you'd like a control to be filled with the value from the last record automatically, set its Default Value property to
=DLast("FieldName","TableOrQuery")
where FieldName is the name of the field the control is bound to, and TableOrQuery is the name of the table or query that acts as record source for the form.
If you'd like a control to be filled with the value from the last record automatically, set its Default Value property to
=DLast("FieldName","TableOrQuery")
where FieldName is the name of the field the control is bound to, and TableOrQuery is the name of the table or query that acts as record source for the form.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Combo box retaining last entry
Cheers Hans
-
- NewLounger
- Posts: 16
- Joined: 29 Apr 2010, 01:38
Re: Combo box retaining last entry
I have a similar situation on a continuous form (subform). I would like all of the six fields in each new record in the subform to be populated from the previous record (except for the date field, where I would like it to be the previous date, plus one day). I know this has been done a lot--I just can't seem to get it to work right...
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
Hi UsingTime,
Welcome to Eileen's Lounge!
This depends on the form and its record source. Could you attach a stripped down, compacted and zipped copy of your database?
Welcome to Eileen's Lounge!
This depends on the form and its record source. Could you attach a stripped down, compacted and zipped copy of your database?
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 16
- Joined: 29 Apr 2010, 01:38
Re: Combo box retaining last entry
Here is the stripped copy. If that is not done correctly, let me know. The "Patients" form is the main form. The "Encounters" subform is the one that I would like to have automatically filled in. This is to assign people ("Doc" = physician and "PA" = Physician Assistant) to see patients each day. The Hospital name, the unit, and the room number change only infrequently, so I would like all of those to duplicate the one above. The Doc and PA fields, however, should be done manually each day, so should not be automatically duplicated. And, as these assignments are made on a daily basis, I would like for the date to auomatically add one day to the previous record.
Thanks for any help you can provide.
Thanks for any help you can provide.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
I'll look at it in an hour or so (I'm on a PC with Access 2003 at the moment)
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
Expressions in the Default Value property won't work here - you should clear them again.
Instead, set them using code, in the On Current event of the subform:
Instead, set them using code, in the On Current event of the subform:
Code: Select all
Private Sub Form_Current()
Dim varID As Variant
If Not IsNull(Me.Parent.PtID) Then
varID = Nz(DMax("ID", "Encounters", "PtID=" & Me.Parent.PtID), 0)
Me.EncDate.DefaultValue = Chr(34) & (DLookup("Date", "Encounters", "ID=" & varID) + 1) & Chr(34)
Me.Hospital.DefaultValue = Chr(34) & DLookup("Hospital", "Encounters", "ID=" & varID) & Chr(34)
Me.Unit.DefaultValue = Chr(34) & DLookup("Unit", "Encounters", "ID=" & varID) & Chr(34)
Me.RoomNumber.DefaultValue = Chr(34) & DLookup("RoomNumber", "Encounters", "ID=" & varID) & Chr(34)
Me.Doc.DefaultValue = Chr(34) & Me.Parent!DefaultDoc & Chr(34)
Me.PA.DefaultValue = Chr(34) & Me.Parent!DefaultPA & Chr(34)
Else
Me.EncDate.DefaultValue = ""
Me.Hospital.DefaultValue = ""
Me.Unit.DefaultValue = ""
Me.RoomNumber.DefaultValue = ""
Me.Doc.DefaultValue = ""
Me.PA.DefaultValue = ""
End If
End Sub
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
Or even shorter:
Code: Select all
Private Sub Form_Current()
Dim varID As Variant
varID = Nz(DMax("ID", "Encounters", "PtID=" & Nz(Me.Parent.PtID, 0)), 0)
Me.EncDate.DefaultValue = Chr(34) & (DLookup("Date", "Encounters", "ID=" & varID) + 1) & Chr(34)
Me.Hospital.DefaultValue = Chr(34) & DLookup("Hospital", "Encounters", "ID=" & varID) & Chr(34)
Me.Unit.DefaultValue = Chr(34) & DLookup("Unit", "Encounters", "ID=" & varID) & Chr(34)
Me.RoomNumber.DefaultValue = Chr(34) & DLookup("RoomNumber", "Encounters", "ID=" & varID) & Chr(34)
Me.Doc.DefaultValue = Chr(34) & Me.Parent!DefaultDoc & Chr(34)
Me.PA.DefaultValue = Chr(34) & Me.Parent!DefaultPA & Chr(34)
End Sub
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 16
- Joined: 29 Apr 2010, 01:38
Re: Combo box retaining last entry
That worked very well. Thank you so much! I do have one question. What is it that triggers the next record to be created? It seems that whenever any one of the fields in changed, it generates the next record. I guess that is not really a problem, since that data is not stored unless a change is made on that newly created one. I think I am just used to not seeing a new record appear until the last field on the form is "tabbed out of".
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
Access always displays a virtual new record at the end of a table, query (if it is updateable) or form (if its record source is updateable and new records are allowed).
But by default, the new record is blank, so you may not really notice it.
The new record doesn't really exist yet, it only comes into existence when you start entering data into it.
With the code I provided, the fields in the virtual new record in your subform are pre-populated with default values. But still, the record only becomes real when you start entering data. If you don't do that, the new record will vanish into thin air when you move to another record in the main form, or when you close the main form.
If you look at the underlying table (Encounters), you'll notice that the 'new' record that you see in the subform doesn't exist there - you see an almost blank new record. The fields are only pre-populated with default values in the subform.
But by default, the new record is blank, so you may not really notice it.
The new record doesn't really exist yet, it only comes into existence when you start entering data into it.
With the code I provided, the fields in the virtual new record in your subform are pre-populated with default values. But still, the record only becomes real when you start entering data. If you don't do that, the new record will vanish into thin air when you move to another record in the main form, or when you close the main form.
If you look at the underlying table (Encounters), you'll notice that the 'new' record that you see in the subform doesn't exist there - you see an almost blank new record. The fields are only pre-populated with default values in the subform.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 16
- Joined: 29 Apr 2010, 01:38
Re: Combo box retaining last entry
To save 4 keystrokes, how do I make it so when I exit the one record, the focus goes to the "Doc" combo box in the new record, since I don't normally need to change the first four?
I was trying something like:
Private Sub EncDate_AfterUpdate()
Me.Doc.SetFocus
End Sub
But, obviously, I don't really know what I am doing...
I was trying something like:
Private Sub EncDate_AfterUpdate()
Me.Doc.SetFocus
End Sub
But, obviously, I don't really know what I am doing...
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
You could add the line
Me.Doc.SetFocus
to the existing code in the On Current event of the (sub)form.
Alternatively, you could set the Tab Stop property of the first four controls to No. That way, they will be skipped when you press Tab.
Me.Doc.SetFocus
to the existing code in the On Current event of the (sub)form.
Alternatively, you could set the Tab Stop property of the first four controls to No. That way, they will be skipped when you press Tab.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Combo box retaining last entry
For optimal results, you may also want to update the default values of Doc and PA in the subform when the user changes the Default Doc or Default PA in the main form:
By the way, it'd be better to use the primary key from the Physicians, PAs, Hospitals and Hospital Unit Names tables instead of the names. For example, if a hospital is given a new name (something which occurs quite often in the country where I live), you only need to change it in the Hospitals table, and all references in forms and reports will automatically reflect the change.
If you do this, you'll have to change the Bound Column of the combo boxes from 2 (the name column) to 1 (the ID column).
Creating relationships with enforced referential integrity will ensure that the user can't create "orphan" values, for example assign a Doc to a patient that doesn't occur in the Physicians table.
See the attached version.
Code: Select all
Private Sub DefaultDoc_AfterUpdate()
Me.Encounters_Subform!Doc.DefaultValue = Chr(34) & Me.DefaultDoc & Chr(34)
End Sub
Private Sub DefaultPA_AfterUpdate()
Me.Encounters_Subform!PA.DefaultValue = Chr(34) & Me.DefaultPA & Chr(34)
End Sub
If you do this, you'll have to change the Bound Column of the combo boxes from 2 (the name column) to 1 (the ID column).
Creating relationships with enforced referential integrity will ensure that the user can't create "orphan" values, for example assign a Doc to a patient that doesn't occur in the Physicians table.
See the attached version.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 16
- Joined: 29 Apr 2010, 01:38
Re: Combo box retaining last entry
Thanks--I have instituted your recommendations, and all seems to be well! What a great help!
-
- Administrator
- Posts: 78671
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands