Autofill New Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there anyway to take information entered in a record, and if it meets
certain criteria, have a new record automatically generated with different
information?

Example: Someone enters information for a inter-company cash deposit and I
want a new record to automatically be generated for the other company as an
inter-company cash withdraw.

Thanks in advance for any assistance.
 
The answer is yes, but it will take some moderately advanced VBA coding to
accomplish it. Do you have any VBA experience?
 
Well, I think we can get throught this. First, we need to have some info
available:
1. How do you know this is a record that will need aother new record?
2. "automatically" entered with "different information" How will you know
what different information needs to be entered.
3. If a user later needs to modify one of the records, will the other also
have to be changed and should it be "automatic" like the original entry?
4. If one transaction is a deposit, the deposit amount will be a positive
number, I assume. Would the withdrawal amount be a negative number?
5. Are deletes allowed? If so, and one record is deleted, should the other
be automatically deleted?
6. Will both transactions be in the same table?
7. What is the name of the table(s)
8. What are the names of the fields in the table that will be involved?
9. What are the names of the controls on your form that will be involved?
10. Is this a single record form or a continuous form?
11. What is the primary key for your table?

Post back with answers, and I should be able to come up with a solution for
you.
 
Well, I think we can get though this. First, we need to have some info
available:
1. How do you know this is a record that will need another new record?
Click to expand...

When the user enters a record as ICD (Inter-Company Deposit), this should
generate a corresponding record with ICR selected. The company and disburse
to fields entered in the first record should be carried to the new record but
reversed. The deposit amount should also be in the new record, but as a
negative instead of positive.
2. "automatically" entered with "different information" How will you know
what different information needs to be entered. Please see above.
Click to expand...
3. If a user later needs to modify one of the records, will the other also
have to be changed and should it be "automatic" like the original entry?
Click to expand...

If possible, it would be helpful if the related recorded was also changed
automatically.
4. If one transaction is a deposit, the deposit amount will be a positive
number, I assume. Would the withdrawal amount be a negative number? Yes.
Click to expand...
5. Are deletes allowed? If so, and one record is deleted, should the other
be automatically deleted? Again, if possible. Both records would need to be deleted.
Click to expand...
6. Will both transactions be in the same table? Yes
Click to expand...
7. What is the name of the table(s) DailyCash
Click to expand...
8. What are the names of the fields in the table that will be involved?
Click to expand...

Index, Company, Date, TransType, CashAmount, DispursTo, Notes.
9. What are the names of the controls on your form that will be involved?
Click to expand...

The only items on the form are the corresponding fields, which have the same
names.
10. Is this a single record form or a continuous form? Single record.
Click to expand...
11. What is the primary key for your table? Index is the primary key which just automatically generates a number for each new record.
Click to expand...


Thank you so much for all of your help, I hope I have given you a clear
enough picture.
 
Sorry to take so long to get back to you. I don't work on Fridays, so I just
got this when I came in this morning.

Thanks for all the useful info. The one thing I think I forgot to ask, if
is there is any way to tie these two records together. That is, if one of
them is your current record, is there a way to know that it has a partner
record and to be able to navigate to it.

In any case, to add the record, I would suggest you put the code you need in
the After Update event of your form. In the Form Open event, create a
recordset for DailyCash and in the Form After Update event, create a new
record. Be sure to declare your record set variables at the module level.
At the module level:
Dim rstDailyCash as Recordset
Dim dbf as Database

In the Form Open event:
Set dbf = CurrentDb
Set rstDailyCash = dbf.OpenRecordset("DailyCash", dbOpenDynaset)

In the Form After Upddate event:

With rstDaiyCash
.AddNew
![Company] = Me.txtDispursTo
![CashAmount = Me.txtCashAmount * -1
.....etc 'I think you see what I mean here, just add your fields
and controls
.Update
End With

The modify and delete will take a little more. You probably have a Delete
command button, so you would have to find the related record and do a delete.
As to the modify, that should also probably go in the Form After Update, but
then you will have to know if you are adding a new record or modifying an
existing record and add the logic to the code above.

I hope this gives you a start, post back if you have more questions.
 
Back
Top