Populating field values from previous record ID?

[wtucker]

Hey everyone,

I'm designing a form system for our clients that requires us to look at the previous year's data to complete it. Essentially, they submit a renewal form every year, and most things don't change. Is there a pre-fill option to create a new record with a new ID, but fill the fields with last year's data, then just update what I want and save as a new record?

The ID is alphanumeric, but the last 4 are the year: XXXX2017, XXXX2016, etc.

Cheers!

[aytee111]

Not sure what the process is - something like a COPY button, or NEW or some way of creating a new record for this year? When that even occurs, run an Append query to add a new record to the table based on last year's data, changing fields and leaving blank as applicable. After that, filter the form on the client and the Max(ID) which will display the record that you have just added.

[Micron]

You have to select the data before you can append it. However, this cannot be a simple query that would permit the data to be edited since that will affect the old data. You could do it all in code via recordset, but it would be simpler IMHO to have a temp table which you populate all fields as required without grabbing any PK fields first. On the button save event, copy the temp data to the main table, where you're either writing new PK values, or you're not doing that at all (in the case of where the target table PK is an autonumber). Then delete the temp data in the same event. Should work as long as you're not sharing one front end.

[June7]

Along the lines of what aytee111 described, consider:

CurrentDb.Execute "INSERT INTO tablename([ID Field], fieldname, fieldname) SELECT '" & Me.tbxNew & "' AS NID, fieldname, fieldname WHERE ID='" & Me.cbxPrevious & "'"

That example references textbox and combobox for user input/selection of ID values. How automated do you want this?

[Missinglinq]

How is the ID field generated?

Linq ;0)>

[wtucker]

So this is a work project. Before I was hired, the company was (and is currently) using individual spreadsheets to house client data. We have about 10,000 of them, and each houses roughly 200 fields. To break it down, I created about 5 tables that store different data (general info, billing info, financial info, etc.). But I lack the programming expertise to build the database with all the variables. I'm not as concerned with automation as I am with just getting the job done, as getting it into a database to begin with would be a major accomplishment.

The Primary Key is generated by combining the client ID and the current year. Client ID's begin with a letter and the abbreviated version of the year in which they came on, but is in numeric order. (ex. F160001, F160002, etc.). So a client would have a primary key of F1600012016 the first year, and F1600012017 the next. I want to prefill the new F1600012017 record with the F1600012016 data, and make changes as necessary before saving it.

[June7]

Is editing before record is committed to table really that critical?

Process options:

1. create record as suggested in posts 2 and 4 and open form to that record and perform edits

2. open form to new record row and code populates fields with data from the prior year record

3. Micron's suggestion in post 3

[wtucker]

I think this may be the idea I'm most capable of implementing.
The user would pull up the previous year's form, click "Create Reissue", and the Append query would add a new record with specific fields filled in, then filter to that record.
Is there any VBA you could give me to point me in the right direction? On a similar note, since the Max(ID) will be changing, and it's a required field, I'll need to be able to populate it in place of the old one on the new form, then filter to it. I'm not sure how to do that.

[June7]

For example of automatically creating unique sequential identifier, review https://www.accessforums.net/showthread.php?t=23329

Already showed you method of creating the new record in post 4.

Now how you get values into the two referenced controls for you to decide best approach. User can select prior year identifier from a combobox list. Code in combobox AfterUpdate event can call function that generates new identifier, sets it into the textbox, and runs the SQL to create new record, then re-filter form to the new record identifier.

[wtucker]

This is really helpful. Thank you!

One last question. I'd like to automatically calculate the PK based on a concatenation of the other two fields. But I can't get the code right in the expression builder. I need to combine the ClientID and the year of the ContractDate into a string, essentially F16001 + 2017 = F160012017.

I'm trying

"[ClientID] & [(right(ContractDate,4))]"

but I'm not sure a right trim will work on a date field, and it's probably incorrect regardless.

[Micron]

[ClientID] & DatePart("yyyy",[ContractDate])
should work as long as the date is not a string (I think). I find this site useful for this sort of thing https://www.techonthenet.com/access/functions/

[June7]

Or

[ClientID] & Year([ContractDate])

[Micron]

Or [ClientID] & Year([ContractDate])

Maybe I should take my own advice!