Macro for command button

[Rookie]

I have created a form in an Access database file. On this form I would like
to have a button that moves data from 1 field to another. I just cannot seem
to make my button work.

For example - I have 3 fields - Current Date, Previous Date and Old Date.

I would like to press my button that I named "new data entry" and this
should then do the following : Delete info in Old Date, Move info in Previous
Date to Old Date and Move info in Current Date to Previous date.

After pressing this button I would like to insert a new date into Current
Date.

Hope somebody can help me

 

[Steve Schapel]

Rookie,

This could be done using an Update Query. The SQL view of such a query
woud look something like this...
UPDATE YourTable SET [OldDate]=[PreviousDate],
[PreviousDate]=[CurrentDate], [CurrentDate]=Null WHERE YourIdField =
[Forms]![YourForm]![YourIdField]

You can then use a macro with the OpenQuery action to run this update.

However, without knowing details of your project, I would suspect that
your wanting to do this is indicative of a design problem with your
table. If you are interested in reviewing this aspect, please post back
with details of what these date fields are all about.

 

[Rookie]

Hi Steve,
Thanks for your quick response. My programming skills are limited. (I have
done some small programs in Basic, DOS, many moons ago). I understand some
of the basic principles though. Not too familiar with SQL but a quick
learner.

Regarding my project. The records in my database contain about 12 fields.
Some data in the fields will remain the same like Name etc. However 3 fields
will change every time new data is entered. These are Datefield, Textfield,
Numberfield.

I need to see the prior 2 entries on my form. So I have created a form that
displays Name and other fields that remain the same. I then have 9 fields
visible on the form in 3 groups of 3. These are named CurrentDatefield,
CurrentTextfield, CurrentNumberfield, PreviousDatefield, PreviousTextfield,
Previous Numberfield, OldDatefield, OldTextfield and OldNumberfield.

When I open the form for a specific record there will be data in all 9 of
these field boxes (if I entered more than 3 times of course). I then want to
press a button called '"New Data Entry" (Got the button already on my form -
it just does not work).

This button should then delete contents in all 3 Old... fields, Move all 3
Previous... fields to the corresponding Old... Fields and move all 3
Current... fields to the corresponding Previous... fields. The 3 Current...
fields should then be empty so that I can enter new data in CurrentDate,
CurrentTextfield and CurrentNumberfield.

Hope this is not too confusing.

Rookie,

This could be done using an Update Query. The SQL view of such a query
woud look something like this...
UPDATE YourTable SET [OldDate]=[PreviousDate],
[PreviousDate]=[CurrentDate], [CurrentDate]=Null WHERE YourIdField =
[Forms]![YourForm]![YourIdField]

You can then use a macro with the OpenQuery action to run this update.

However, without knowing details of your project, I would suspect that
your wanting to do this is indicative of a design problem with your
table. If you are interested in reviewing this aspect, please post back
with details of what these date fields are all about.

--
Steve Schapel, Microsoft Access MVP

I have created a form in an Access database file. On this form I would like
to have a button that moves data from 1 field to another. I just cannot seem
to make my button work.

For example - I have 3 fields - Current Date, Previous Date and Old Date.

I would like to press my button that I named "new data entry" and this
should then do the following : Delete info in Old Date, Move info in Previous
Date to Old Date and Move info in Current Date to Previous date.

After pressing this button I would like to insert a new date into Current
Date.

Hope somebody can help me

 

[Steve Schapel]

Rookie,

I think I understand what you are doing. And I think the Update Query
idea I suggested before should do what you want... Did you try it? Or
do you need more specific help with making it?

Having said that, it would realy be better to have your data in 2
separate tables. One for the fields that "remain the same", and the
other for the 3 fields that get updated. It would be like this...

Table: BaseData
SomeID
TheName
SomethingElse

Table: ChangingData
ChangingID
SomeID
TheDate
TheText
TheNumber

Of course, change the names of the fields to something a lot better than
that... but be aware that 'name', 'date', 'text', and 'number' are all
Reserved Words (i.e. have a special meaning) in Access, and as such
should not be used as the name of a field or control.

Then you have a continuous view form based on the second table, which
you put as a subform on the main for based on the BaseData table. That
way, you can add a new record whenever you want, and you can have as
many date/text/number values for each base record you like... but if you
only want 3, just delete one off whenever you add a new one on. This
would be a more correct approach to database design for your project.

 

[Rookie]

Thanks Steve,
Will be a bit busy over next 2 days, but will have time on weekend to
redesign my project with your new recommendations.
ps I tried the Update Query advice but didnt get very far.
Will contact again after weekend if any problem.
Thanks again

 

[Rookie]

Hi Steve,

Thanks, I used your suggestions and it works even better than what I had
originally planned. My problem was with the design. The 2 tables are
working well.
Now having to figure out the queries.

 

[Steve Schapel]

Very good, Rookie. Just post back, either here or in the .queries
newsgroup, if you need help.