copy/paste field from previous record... (1 Viewer)

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
Hi,

I only know enough VBA to insert codes in the right place! Here is what we would like – in plain English. Can you “translate” in VBA?

It is a continuous subform (Names: Form = ClientUpdate / Subform = ClientUpdateSub. All of the data implicated here is direct and in the subform's query table – including the button we want to program.

(This is my example but it wont stay in columns in this "post box" (sorry) )

IDNo App _ID App_Freq App_Date GetDates Action_Date
22 18 56 21-Jun-13 BUTTON 14-Jun-13
21 19 56 16-Aug-13 BUTTON 9-Aug-13

*

The GetDates Command BUTTON should generate the red data above, like:

Private Sub GetDates_Click()

Go to a new record
Go to the field App_Freq and fill it with:
Copy/paste the data from the field of the same name in the previous record
Go to the field App_Date and fill it with:
data based on this calculation using the PREVIOUS record fields: App_Date + App_Freq (which are days)
Go to the field Action_Date and fill it with:
data based on this calculation: App_Date (of current record now filled out - step above), minus 7 (days).
End Sub

That’s it!

All the dates’ format is set to medium date.
An error message should come up in case App_Freq is empty, for example…

Also note that the data can be manually changed at any time and should not revert to its last calculation. The button only generate data into a new record.

THANK YOU
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
Hi Valery,
Welcome to the forum.
First of all, I'm not sure the date shown in "Action_Date" should be saved in a table because it is a calculated date and calculations should not usually be saved but calculated as and when needed.
Also, I'm not sure you need the button.
Take a look at the attached db which I think gives the functionality required.
If you require any explanations, please post back.
 

Attachments

  • Bob01.mdb
    156 KB · Views: 395

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
Ok - I guess I really did not explain this well.

This is to facilitate data entry. At any time the user can change the frequency manually which will impact on the dates.

I need the dates to enter automatically. BUT - they can be changed by the user - at any time.

Right now, I use CTRL apostrophe to copy the frequency from the last record.

For the date, I have a two button that each open up a different subform with the dates calculated. But I have to manually select the data, copy the data, paste the data from the form, and then, close the forms. Also none of it can be done using the mouse - its CTRL C, CTRL V.

The calculation cannot be part of the field themselves. It has to be done in a hidden field (which I have done). But I don't know how to tell it to select, copy, and paste by the click of the Command Button - based on the frequency the user entered.

The frequency is often the same as the previous record (people getting treatment at the same frequency i.e. an appointment every 56 days) - but it can change. I want it to be copied upon a new record created but the user can just type over it - if required.

Those dates have to be saved. They represent for each appointment booked for a patient - the date it was booked and the date to call him/her to remind him/her of the appointment. I have a reminder form based on those dates. A calendar generated by those dates.

I was hoping this was easy -like an "acCommand_paste"... or something...

I hope this is clearer and I want to really want thank you for answering me. It is my first time using a "post" or a "forum". All these concepts are new to me. I am always weary of giving my name and email on the internet to people I don't know... I sure hope someone can help.

------------

I have included a reduce to minimum sample of the dtb. Instructions on how I am currently functioning are right there on the form that opens with autoexec.
 

Attachments

  • SampleForHelp.accdb
    780 KB · Views: 233
Last edited:

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
OK, Please take a look at the amended db attached.
Does this meet your requirements?
Those dates have to be saved. They represent for each appointment booked for a patient - the date it was booked and the date to call him/her to remind him/her of the appointment. I have a reminder form based on those dates. A calendar generated by those dates.
I still don't agree that the "Action_Date" needs to be saved. See qryForm1 which is now used as the Record Source of Form1.
If you want to post a db for us to look at, please convert it to A2003 mdb format. I, for one, am still using A2003.
 

Attachments

  • Bob02.mdb
    200 KB · Views: 277

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
If the action_date is not saved, how would it produce my reports based on this date - Action due date, Action description, Action done + this is submitted with my timesheet. It provides a daily account of Actions performed for what patient, at what date.

So please explain why you would not save it?

Plus, can you use the sample I provided and include the VBA coding in it? That would be very helpful.

Thank you for trying to help me.
 

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
Can you tell me how to save the sample in MS 2003? When I try Save As, there are no option in the list + I don't see a way to export... So don't know how to do it.
 

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
OK it is is in MS Access 2002-2003.
 

Attachments

  • SampleForHelp_2002 MS Version.mdb
    688 KB · Views: 202

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
Where is it amended? I don't see the button I was looking for.. mine are there, opening the same subforms...

Please tell me where you made this work?
 

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
I think I see what you did - if I change the frequency the date changes. Boy! I guess I am not clear in my request.

ONLY dates in A NEW RECORD have to be inserted.

I think I will figure this out another way - or open a new thread altogether and explain this in a totally different way.

Just want to know how I can delete all these threads and posts

thanks anyway.
 

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
Re: copy/paste field from previous record... ANYONE?

Bob or other person wanting to help. Let's START OVER and let me totally rephrase my request:

Hi,

My question involves an appointment booking table and three of its fields.
They are:

(1) Frequency [App_Freq]
FYI: the Frequency is the interval, in days, at which an appointment is required – this can change in future – a client begins seeing the therapist every 56 days, later, may only need an appointment every 120 days… Generally, the frequency remains the same for a while, then changes for future appointments

(2) Appointment Date [App_Date]
FYI: All patients get a first time appointment date with a standard frequency set at 56 days and re-evaluated at each appointment, by the therapist and the patient. Each appointment is recorded in its own record in the Appointment Table which is linked to a Patient Table containing Patient ID, name, address, etc.

(3) Action Date [App_ActionDueDate]
FYI: This date is set for an action (could be a reminder call, a call for more info, an expected return call, etc.). It is generally set 7 days prior to the appointment but can be different – i.e some people like to be reminded 2 days before their appointment…). The 7 days is an approximate date that accommodates most actions to be performed.

What I would like is a button to facilitate – expedite – data entry of a NEW RECORD.

When a new record is added – the user enters:

A Frequency (mostly same as last appointment but can be a new one as explained above which is why CTRL ' is used a lot here)
A new appointment date, determined as follows: last appointment date + current frequency
A new Action date, determined as follows: date of current appointment minus 7 days

This data entry gives us the ideal appointment date. That is, the date we seek to book the patient and look for on the Scheduling Calendar. And, although that ideal date is often available it is not always available on the scheduling calendar. So it is manually updated to the actual patient’s final booked appointment date – which is fine.

Can the ideal dates be generated automatically – through a command button – to speed up the INITIAL input and calculation only?

The button should only apply to a NEW record. Once the record is created, any changes to the dates must be done by the user not by an automated process.
 

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
What I would like is a button to facilitate – expedite – data entry of a NEW RECORD.

When a new record is added – the user enters:

A Frequency (mostly same as last appointment but can be a new one as explained above which is why CTRL ' is used a lot here)
A new appointment date, determined as follows: last appointment date + current frequency
A new Action date, determined as follows: date of current appointment minus 7 days
How does the proposed solution, in the db attached in post #10, not meet with these requirements other than it is done without the need of a button.
The "Frequency" is entered automatically (like using CTRL ') but can be changed.
The new Appointment date is produced automatically using last appointment date + current frequency. This can be changed.
The new Action date is produced using date of current appointment minus 7 days. This is recalculated if the frequency or the Appointment date are changed.
 

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
It changes the appointment date of an existing records if the Frequency field is updated.

ONLY NEW RECORD data has to be entered. Anything entered AFTER a new record SHOULD NOT BE AUTOMATED.

The button has to CREATE A NEW RECORD, enter the data in it - AND only the data of those three fields - not the entire previous record - that's IT.

This is, just and only just, to FACILITATE the initial data entry of these ideal dates, in a NEW RECORD only.
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
OK. Try the attached db. Still without a dedicated button for new entries and still using a calculated field in the forms query to produce Action Date. Where does this fail the requirements.
 

Attachments

  • SampleForHelp_2002 MS VersionBob03.mdb
    656 KB · Views: 122

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
1st - when I go to a new record, it enters the previous frequency but not a new appointment date???

2nd - it returns the following: #Name? in the next record

PLUS - I just noticed: EVERY FIELD is copied into the new record???? Like Appt Status, Action_Status...

Why is my request so difficult? Please help me in explaining it to you. I know you can help but you don't seem to understand how simple what I need is...

The data entered - by a BUTTON - CANNOT SHOULD NOT be updatable AUTOMATICALLY after it has been entered. Please help me to phrase this so you understand.

I only want a simple copy/paste of two calculated dates into a new record without the user having to do CTRL C, CTRL V... at the click of a button.
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
Hi Valery,
Please take a look at the attached db. This HAS A BUTTON to create a new record.
Two of your controls have their Default Value properties set ("App_Status" to "Non Fixé" and "App_ActionStatus" to "Call - Schedule") but in an earlier post you questioned why they were being populated. Take a look at the code in the On Click event of the “Add Record” button. If you also want the two afore mentioned controls populated, you will need to Uncomment the two lines of code following the code comment: Uncomment the next 2 lines IF you want those two fields to be populated as well.

I have added an “Option Explicit” Statement to the module of the main form. I would recommend that you add it to all modules.
 

Attachments

  • SampleForHelp_2002 MS VersionBob04.mdb
    840 KB · Views: 162

Valery

Registered User.
Local time
Today, 02:46
Joined
Jun 22, 2013
Messages
363
This is PERFECT! You must have worked hard to that button to be in the main form! I thought it would be in the subform... but what do I know.

This is great! I feel I gave you much grief and I am sorry about it. It had to be right otherwise I could not use it.

Again, THANK YOU - for your patience, perseverance, knowledge sharing and remaining courteous throughout it all!
 

bob fitz

AWF VIP
Local time
Today, 09:46
Joined
May 23, 2011
Messages
4,717
This is PERFECT! You must have worked hard to that button to be in the main form! I thought it would be in the subform... but what do I know.

This is great! I feel I gave you much grief and I am sorry about it. It had to be right otherwise I could not use it.

Again, THANK YOU - for your patience, perseverance, knowledge sharing and remaining courteous throughout it all!
Hi Valery
Thank you for your kind words. It's a pleasure to help if I can.:) Let me know if you need any help to understand the button's code.
 

Users who are viewing this thread

Top Bottom