Combo box retaining last entry

[Egg 'n' Bacon]

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.

[HansV]

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.

[Egg 'n' Bacon]

Cheers Hans

[UsingTime]

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...

[HansV]

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?

[UsingTime]

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.

[HansV]

I'll look at it in an hour or so (I'm on a PC with Access 2003 at the moment)

[HansV]

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:

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

[HansV]

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

[UsingTime]

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".

[HansV]

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.

x109.png

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.

x110.png

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.

[UsingTime]

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...

[HansV]

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.

[HansV]

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:

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

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.

StrippedCrossCover.zip

[UsingTime]

Thanks--I have instituted your recommendations, and all seems to be well! What a great help!

[HansV]

Glad to have been able to help!