adding to a table (1 Viewer)

[Jediphase]

I have two tables: tablea and tableb

tablea has two fields: amountdue and duedate

tableb has three fields: amountdue, amountpaid and paiddate

I have a query that looks in tablea and selects the records that have a due date of the current day which works.

I have a tasks form with the query as a subform

Now how can i get the records from the query into tableb when i open my tasks form. I only want the amount due field from the query to go into tableb.

Thankyou folks

J

 

[Fornatian]

I do not think I am understanding your problem correctly as you have obviously simplified things to 'make it easier'. From what you have said it appears to me that these points are valid:

A. You don't need two tables because everything relates to the same topic - record of payments.

B. You don't need a due date recorded in a field unless that is specific to each and every record - if each client gets a different credit period then store the credit period in a separate field in the customerdetails table, if everyone gets the same credit period store it in a system information table. Whichever you choose, you can calculate the due date in a query using DateAdd("d",[DateInitiatedActionField],[FieldFromTableHoldingCreditPeriods]

Having resolved the design issues, everything else *should* fall into place.

 

[Jediphase]

bit more info

Cheers for your reply bud.

The way the db works is tablea holds amounts due after an agreement.

tableb holds transactions of when those amounts are paid along with some other info.

I want to create new 'transactions' within tableb using the amounts from my tablea query when they are due.

that way in tableb i can clear the 'transactions at a later date.

Basically posting transactions using data from the query.

I hope more info helps. Look forward to your response

Thank you

J

 

[Fornatian]

So am I right in assuming that TableA and TableB share a one-to-many relationship? If not I think they should do because unless I am mistaken TableA records the amount owing and TableB the payments made. I see why you have two tables but I think they require a one-to-many if not already initiated.

I think what we need to look at is how to create entries in TableB through the recordset object. That way you could create forward payment due dates in the table thus allowing a full credit history to be viewable - before I go any further, am I on the right lines?
if so, what is the interval between payments in real terms how are the payment period and payments calculated?

Hope I am on the right track.

 

[Jediphase]

yep

Thats pretty much it. I want amounts to be posted in tableb when the amount due date in tablea = todays date for instance. I have a query that produces the amounts due according to date from tablea already.

I do have the one to many relationship already set up.

Amounts due in table a are due weekly or monthly

need more info boss??

 

[Fornatian]

How do you know whether it is weekly or monthly, where is that info stored? how is the policy recorded?

 

[Jediphase]

ok

righty dokey then,

tablea has another field called payment frequency choices being pcm or weekly

when you first add the amount due it sets the first date. I was going to set it up to add either 7 or 30 days to the due date each time it posts the amount to tableb ready for the next payment.

 

[Jediphase]

still need help on the above subject

s

 

[Fornatian]

right then, I won't do all the work, instead of creating the next forward payment record each time a payment is made, why not create them when you first initiate the record in TableA?

1.Put a button on the form something like "Create Payment Schedule"

2.The button code should be something like:

a.Open the TableB's Recordset

b.Determine the No of entries required based on the weekly/monthly and your determination of how many payments need to made to clear the debt

c.Use looping code using the AddNew method to create records in tableB for each full payment required

d.Outside of the loop add another record if required to manage the last instance where a short payment is required(i.e.£11.00 debt over 6 months = 5 x £2.00 and 1 x £1.00
Alternatively you also create an if...then code in the last iteration of the loop to allow for an overpayment on the last one( 4 x £2.00, 1 x £3.00). This would need to calculate if the payment was less then 2 x standard payments and if so create a payment for the remainder.

Am I still with your policy and aims?

 

[Jediphase]

i think so

I think i shall create the amounts within tableb when initially added to tablea. Then only get table b form to show amounts due to date. How can i get a query to show where a date is todays date and less than todays date?

Is there a command to take data from a query and paste it to a table?

 

[Fornatian]

Use <=Date() as criteria and IsNull to show no payment has been made.

You could use an append query to append records to the end of TableB BUT you would not be able to manipulate the entry dates and payment amount. Which is why people use the recordset and addnew method as it performs the same function as an append query but offers significantly more control over the added data.

Are you confused?
Read up on recordset and addnew and come back if still stuck.

 

[Jediphase]

I have just tried recordset

I have read all the help files and have added code that updates each field for each record to add.

How do i for instance add field1, field2 and field3 from table a to field4, field5 and field6 in tableb using addnew?

This isnt very easy!!

Once I have learnt it once i should be ok!

Cheers for you help bythe way fornation. Im getting there.

 

[Jediphase]

I DID IT!!

Hey forny forny forny!!

I blooming did it!

Cheers bud i set the recordset up then used the with end with commands to use the recordset to add a new record, add the data and update!!

top geezer in my eyes matey

righty dokey then skip i have to go and finish it if i need more help ill be back!

Jediphase

 

[Fornatian]

You did the hard work

Well done Jedi!