Trigger an Append Qry upon entry???

[Egad]

I have building a database that records information on participants in an activity our group puts on. To participate in the activity, you have to pay a certain amount of money. When you apply to get into the activity and are approved, you indicate whether or not you’ll make 1 or 2 payments. I’ve got the form set up that asks for the number of payments—call it PymntPlan. That's based on a query.

I’d like it if as soon as someone enters a “1”, this would append a record to a Payments table putting the ParticipantID in there and a “1” in the PaymentNo field. If a “2” is entered, I’d like it to append two records to the Payments table, both with the same participant ID but one with a “1” in the PaymentNo field, the other with a “2” in the field. There is an auto primary key called PaymentID in the Payments table so the records will be unique but I need them to be associated with the participant.

I don’t want to make the person entering the info push a button because
they might forget.

I also don’t want duplicates for the participant so if they inadvertently hit the enter key again in that field when they happen to be in the PymntPlan form I want it to check to make sure there is no record(s) for the participant.

I want to do this so that when we open up a Payments form it will show the participant's name and also display all the fields for the participant's payments. Thus we'd see Payment 1 and it asks for the date for that payment if it doesn't already have one, and the amount of the payment, and method of payment, etc. If the participant had indicated that there would be 2 payments then we'd see two of these. I know how to do subforms but nothing shows up for the participant if the record doesn't already exist for him in the Payments table.

I’m not familiar with VBA or SQL or macros. Although I’ve learned a lot reading in this forum and I’m coming along, I just don’t know how to do this. I figure it has something to do with an Append Query and probably an IIf statement but I’m not sure how to do this or where to put it so it happens automatically. And do I need a lookup table with a blank record?

Help will be greatly appreciated. Thank you.

 

[FoFa]

Is this a one time payment (either 1 or 2, I am lumping them together) or something that could happen say every year. If it is more than once (like a yearly subscription) than you have to account for which time period these payments are subject too also. Given a unique key/s then you could run a delete query to remove any current payments (in case they change their mind, hit enter more than once, whatever) then run the append query to put the information back in. You can control this either via a macro or procedure. You just need to determine which event needs to trigger this. If you use either you should set warnings off, run delete (may not be any records), make sure the delete is feed the proper critiera, then the insert (and you may want to turn warnings back on, but I never do). Like I said, your criteria is going to be critical so you do not delete rows you do not want to. But that at least should get you thinking.

 

[Mike375]

I gain the impression from your posting that you have an overview of Access but at this stage are short on knowledge in the specifics.

You can think of Access in terms of looking at a dictionary and the 1000s upon 1000s of words and then comparing that to the number of words we actually use when writing a letter. In other words Access has its own counterparts of how, because, the, and, why, now, so, on etc. as well as nouns such as car, house etc. and as such we can write very good letters using only a very small percentage of the words in a dictionary.

In other words if you master a few things you can do a lot in Access.

The scenario you describe in your post is very commonly done in Access or at least variations of your scenario.

If you learn how do three actions in a macro (and it is easy to learn) then you can cover a lot of territory, including your problem.

As soon as you start moving records about from one table to another, then you are right, an append query comes into play. In addition, as FoFa has mentioned, a delete query often goes with an append query.

It is simplicity itself to learn to run queries from a macro. Basically you have an OpenQuery action and then name the query to be opened or run. In your scenario you would have an append query run first then the delete query run. That is assuming you wish to move slected records form Table A to Table B. However, if want selected records placed in Table B while the slected records also remain in Table A, then a delete query will not be made to follow an append query.

The next thing you have to do is to "trigger" the macro. As you have said you do not want a situation that relies on someone "clicking" on something. If you go to your form in design view and then right click on the field where the 1 or 2 will be entered and bring up its Properties and then look at Event you will see a whole heap of things listed. There will be things like onClick, DoubleClick, LostFocus, Gotfocus and many more. When your enter a macro name on the line then the macro will run when the event occurs. Obviously onClick is very common but not suitable for you.

The type of Event you will probably look to is LostFocus. This means if the person types in a 1 or 2 and then even moves to another record then macro will run.

The next thing as you have said is to prevent things running more than once.

A simple way to achieve this is to learn the Setvalue action in a macro. The SetValue macro action and its counterpart in code is probably the counterpart of words like and, the, now why etc in a letter in other words data bases can be full of this action. The SetValue action allows you to make an entry in a field. This could be a calculation, it could be the value of another field, it could be the value of another field that has been multiplied by 3 or it could be a date, a time or something such as a number or word that enter in the macro.

In this case we might use the SetValue action in your macro and on the ction line followin the OpenQuery actions. What you do is to pick a field in your table that is not being used or add a field and then the Setvalue action will make an entry in this field. You might for example have the word "done" entered or perhaps a number entered.

If go to the section of Access where you make or design a macro you will see that there is a column for the "action" such as OpenQuery or SetValue and there is also a column for Conditions. When in macro design view you can go to the tool bar and click View then click conditions.

What we now do is to place a StopMacro action on the first line and a condition that will stop the macro from running.

Lets us say your form is called Payments and the field that we will set the value of with the Setvakue action is called [Done]

The macro condition on the StopMacro action line will be

[Forms]![Payments]![Done] Is Not Null

As you can see when the macro runs the last action line make an entry in the field [Done] and once that happens the macro won't run again, at least for that record.

As FoFa has said you need to be careful with your query criteria. You can also use a Setvalue on another field to help. For example you might have a SetValue action following the StopMacro action and an entrry is made to a field and the query selects on this field as criteria. The criteria could be as simple as Is Not Null in the chosen field. This would work nicely if one of your queries was a delete query as the only records that will have an entry in the field you apply a criteria to will be the record where the person makes the payment entry of 1 or 2.

Mike

 

[Rich]

Why would you want to enter payment details that don't exist?