Append query.

[Homegrownandy]

I have a text box named "scheduled_start" on my "JobsF" form.



Now what I'm trying to do is use two append query's to move the start date contained within the text box.

*The first append query will be on the before update and put this date in the "notesT" in the "old_date" field.
*The second append query will be on the After update and put this date in the "notesT" in the "New_date" field.

I'm trying to track date changes and this part of the process isn't working. For a start I don't know how to run the query on an event. I can open the query using 'openquery' but I don't wish to open it. Re query is also an option but the query I made isn't listed when that's selected.

Its possible I'm going about this totally the wrong way. any help appreciated.

[ItsMe]

I do not recommend using the BeforeUpdate event for controls. It can be useful for forms. Using BeforeUpdate for forms is not a bad thing. Using BeforeUpdate for controls is a bad thing. I doubt that anyone that advocates the use of BeforeUpdate for controls has ever used it in a production app.

Having said that, I suggest you provide us with a high level view of what you are trying to accomplish from an operations point of view. I understand you need to run an append query and are not sure which event to use. A typical scenario would be to use the .Value of an unbound control formatted as date. Then, a command button would execute the append.

[Homegrownandy]

Simply put: When I change a date in a text box, I want the old date and the new date to be put into a notes table.

This is a continuation from here: https://www.accessforums.net/access/...ges-55279.html

The method of how to achieve this doesn't matter to me as long as I can find a method that works.

[ItsMe]

When I change a date in a text box, I want the old date and the new date to be put into a notes table.

As a user types inside of a textbox control, the .Text value of the control changes, too. When the user exits the control, if the .Text value is different from the .Value, the AfterUpdate event will fire. If you are looking for an event, the afterupdate event may be the one you are looking for. However, appending records because of an AfterUpdate event seems radical to me and I would lean towards a Command Button for the user to have a more 'mechanical' control of the situation.

I suggest you build a couple of query objects. A say a couple it is likely the first try will not be a total success. The idea is to create an Action query that will append another table.

Use the query designer to create a new query. Add a table to the designer. When you create the new query, it should default to a Select query. Next to the Select option, within the Ribbon, is Make Table. there are other options too. You want the Append option. When you select Append, you will be prompted to Select a table to append to. The query will retrieve data from the table(s) you initially added/dragged to the designer to the table you select from the dialog window.

To recap, create a Select query that retrieves the data you would like to append. Convert your select query to an Append query and choose a table to append to.

The final step is to MAP the fields. You want to get each column name to map to the appropriate column name in the table you are appending to.

[Homegrownandy]

Ill spend a full day on this on Monday and do some research on append query's again this weekend. I'll still probably be stuck but thanks for your input. I'm determined to get this working.

[NTC]

You don't really need to use an append query. Also I believe from a logical point of view - you don't need any code in the before event.

All you need is to put the code to update the NotesT field in the AfterUpdate event of the Schedule_Start field. Since it does it every time you will have the current info in NotesT before the next change. The code is easy:
me.NotesT = me.NotesT & " : " & me.Schedule_Start (I just threw in a : divider)

this will keep adding each date in your NotesT field....... give it a try

[ItsMe]

...I'll still probably be stuck but thanks for your input...

There are definitely some missing parts and you will need additional guidance for those parts. Creating abstract ideas that define Events and creating abstract ideas that define programming procedures is not easy and takes practice. Then you get to figure out how to marry the two sides.

As an alternative, you may want to consider NTC's suggestion. Depending on how generic your notes can be, it may be a suitable approach. Make sure your Business Rules are clearly defined. Understand any possible need for querying historical data/notes.

[Homegrownandy]

As anticipated, that's over my head. Ill spend some time on it now and I'm sure ill get it working! Thank you!

[Homegrownandy]

"microsoft access can't find the object me"

That's following your suggestion. I just read somewhere I need to put it behind the forms module. Would that not apply it to every change then? I'll keep looking/troubleshooting.

Also I've just noticed a few things;

me.NotesT = me.NotesT & " : " & me.Schedule_Start

NotesT is a table, in that table I wanted the following to apply:

*The first append query will be on the before update and put this date in the "notesT" in the "old_date" field.
*The second append query will be on the After update and put this date in the "notesT" in the "New_date" field.

Obviously this doesn't need to be an append query, any method for getting both of these dates into these fields is fine. However; they must be kept as separate dates. I need access to tell me how many work days were in-between both dates so we can apply charges where applicable.

Hopefully you understand what I mean. Cheers for the answers so far!

[ItsMe]

...they must be kept as separate dates. I need access to tell me how many work days were in-between both dates so we can apply charges where applicable...

If you will need to query your data at a later date, concatenating existing fields into a single field will make querying historical data cumbersome, at best. Perhaps it would be best to take a step, or three, back to determine what the Business Rules are.

If you think you need to append some records to a table, you should try it and understand how to do this. There is more than one way to append records to a table. Learning a couple of different ways would not be a bad thing, even if you do not need it for this circumstance. In other words, understanding how to append records may give clarity to what is possible when you are in the planning stages.

Business Rules will determine table structure, among other things. Your table structure needs to be flexible as the Business Rules are defined. If the business needs to be able to query historical data about changes made to records in one table, it is likely you will need to append records to a separate table. This 'other' table does not have to duplicate records. You can use key values to create a one to many relationship.

[Homegrownandy]

I agree it's better to have too much knowledge than not enough. I'm open to any suggestion, I cant take any steps back as I am still at the first step with this! haha.

I have looked quite a bit into append querys. I don't think this would be suitable. I will spend some time looking into VBA to achieve what I need. I didn't think this would be a difficult problem to solve. I might try and chop up some form auditing code and try and apply it to just the fields I need.

cheers anyway

[ItsMe]

If you do not append a record then you will need to edit an existing record. If you edit an existing record 6 times, where will your historical data be?

[Homegrownandy]

That's what I'm looking at today mate, I have the fields for the historical data I just cant get it there. VBA is probably my best bet.

[Homegrownandy]

https://books.google.co.uk/books?id=...page&q&f=false

For anyone interested this helps a great deal. I have it working now.