Unanswered: Duplicate values of previous record on subform controls
I have a mainform with subform opened so that I can add a new record to the subform.
New records are made to tracks changes to the mainform; ie Mainform record is Suzie Smith
subform records are distinct to Suzie:
ID Day Shirt Pants Shoes
1 Mon Red Sweater Blue Pants Black Shoes
4 Tues Blue Blouse Blue Pants Black Shoes
8 Wed Sweatshirt Blue Pants Black Shoes
The mainform is locked and the focus is on the new record in the subform. The subform is never blank and none of the existing records are allowed to be changed or deleted.
What I need to do is get this subform opened on a new record that has copied the pants value and the shoes value for Suzies last record (ID 8).
The only data that user will need to enter is the day and the shirt, because Suzie changed her shirt.
I've exhausted all my google searches and tried numerous things. Can anyone offer a suggestion how this can be done?
You could use (adapt the names according to those in your database):
Private Sub Form_Current()
Dim lngID As Long
lngID = DMax("ID", "Tbl_Suzie")
Me.Pants.Value = DLookup("Pants", "Tbl_Suzie", "ID=" & lngID)
Me.Shoes.Value = DLookup("Shoues", "Tbl_Suzie", "ID=" & lngID)
The mainform and subform are 2 different tables, the forms are linked on Name (Access2007) -
table records are:
MAINFORM tblGirls =
SUBFORM tblClothes =
ID Name Day Shirt Pants Shoes
1 Suzie Mon Red Sweater Blue Pants Black Shoes
2 Jane Mon Red Sweater Black Pants Black Shoes
3 Gwen Mon Yellow Sweater Black Pants Black Shoes
4 Suzie Tues Blue Blouse Blue Pants Black Shoes
5 Heidi Mon White Blouse Blue Pants Black Shoes
6 Jodi Mon Red Sweater Blue Pants Black Shoes
7 Dottie Mon Red Sweater Blue Pants Black Shoes
8 Suzie Wed Sweatshirt Blue Pants Black Shoes
9 Jane Tues Sweatshirt Black Pants Black Shoes
10 Gwen Tues Sweatshirt Black Pants Black Shoes
When I open the MAINFORM for Suzie - I need subform tblclothes to open on record
11 Suzie *day* *Shirt* Blue Pants Black Shoes
- user will enter day and shirt for this record
The values copied for tblClothes Criteria needs to be the Max ID for tblGirls record 1 Suzie - which is now 8
So I am taking the Pants and Shoes for Suzie, not necessarily the last Pants and shoes
I know there's a way but can't grasp it exactly
Last edited by Foskbou; 11-12-11 at 09:27.
Reason: I posted too quick :-)
Duplicate values of previous record on subform and duplicate+1
I am trying to adapt some VBA I use from an old post in a new project.
This will go in a continuous subform that holds between 40 - 50 records per main form.
The entering is shipment details - so it is consecutive number ship tickets(being entered) TktNo, and duplicate dates TktDate. Each mainform is a week of shipments, so every 10-13 records the TktNo sequence changes and the date changes.
TktNo = Number
TktDate = Date/Time
The following code enters the same data as the previous record:
Private Sub Form_Current()
For TktNo, how do I change the DLookup to give me last TktNo + 1?
Since this code is to speed up the data entry and for ease of following along with this consecutive data, I need to be able to overwrite the ticket number and date where needed.
Is this the most efficient way to do that and keep integrity in the saved records?