How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard
Richard, Software Engineer
Category: Microsoft
Satisfied Customers: 36529
Experience:  Over 15 year experience resolving Microsoft Office Issues
32989067
Type Your Microsoft Question Here...
Richard is online now

I've inherited an Access Database. There is a main form which

Resolved Question:

I've inherited an Access Database. There is a main form which shows the records for a table 'tablemembers'. Embedded in this is a sub-form (child61) which is 'tablepayments'. People pay us and transactions are recorded here. It's a related table one to many of course. Transactions are dated [date] and are labelled with a transaction type [As] within this payments table / subtable. The subtable of course only lists transactions from tablepayments that relate to that customer. I want to add a calculated field to tablemembers which will show the last date on which a certain type of transaction - ie "Subscription" was paid. I need this to update as transactions are entered, and I need to be able to use it in queries and filters, and also to inform a next sub due date calculated field (result plus 365??). I'm no expert with expressions ... can you help?
Submitted: 2 years ago.
Category: Microsoft
Expert:  adamd-mod replied 2 years ago.

Hello there,

I've been working hard to find a Professional to assist you with your question, but sometimes finding the right Professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Please note, I am just a moderator for this category, I can only try and find experts to help, I can't answer the question itself.

Thank you!

Adam

Customer: replied 2 years ago.

A few days won't hurt

Expert:  adamd-mod replied 2 years ago.

Hello,

We will continue to look for a Professional to assist you.

Thank you for your patience,

Adam

Expert:  Richard replied 2 years ago.
Thank you for your question, my name is ***** ***** I look forward to assisting you.
Please note, click on Reply to Expert to reply to me, a rating is only needed when we are finished.
Can I look at your database please, this will be easiest.
To provide it, do the following
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 2 years ago.

459204 (anonymised version, sorry ... but it should do the trick)

Expert:  Richard replied 2 years ago.
thats ok, ill check it out now
Expert:  Richard replied 2 years ago.
ok, you can't do this in tables as thats for storing data, you use a query for this.
I have done this for you and it is called qPaymentCalculations.
From what I understood as your requirements, it shows the last payment from someone and then the date (in a year) when the next payment is due.
Check it and tell me if you have any issues
File ID is 585744
Let me know the results please
Customer: replied 2 years ago.

OK .. progress! So how do I now get that resulting field to show in Frmmain? Also, the payment of a subscription in tablepayments is marked using the fields [As]. If someone has not paid a subscription (ie they've not paid anything or they've only paid donations or fees - anything except "Subscription" then the result of the query should be empty. Apologies I'm working with both an inherited DB that I am trying to untangle coupled with a lack of knowledge of Access.

Expert:  Richard replied 2 years ago.
ok, now your bringing extra questions to what you have already asked, and just these additional requirements adds a lot more work.
Can you state all the questions you have in one go please.
Customer: replied 2 years ago.

Actually, this was all included in the original question, see above and ...

QUOTE

I've inherited an Access Database. There is a main form which shows the records for a table 'tablemembers'. Embedded in this is a sub-form (child61) which is 'tablepayments'. People pay us and transactions are recorded here. It's a related table one to many of course. Transactions are dated [date] and are labelled with a transaction type [As] within this payments table / subtable. The subtable of course only lists transactions from tablepayments that relate to that customer. I want to add a calculated field to tablemembers which will show the last date on which a certain type of transaction - ie "Subscription" was paid. I need this to update as transactions are entered, and I need to be able to use it in queries and filters, and also to inform a next sub due date calculated field (result plus 365??). I'm no expert with expressions ... can you help?"

UNQUOTE

Expert:  Richard replied 2 years ago.
ok I see you want it to ONLY show Subscriptions, so I have altered the query to show this
It shows the last date it was paid, and will update when transactions are added.
It also shows the next payment due.
You can download the updated copy here 375006
Expert:  Richard replied 2 years ago.
Were you able to download the latest copy I did?
Customer: replied 2 years ago.

Yes, thanks. Sorry I'm trying to do this at the same time as several other things ... such is life. So getting this now to being displayed in frmmain where indicated on the attached is the last stage. Currently these two fields are entered manually when a payment is processed.

Expert:  Richard replied 2 years ago.
so what you really want is on form frmMain to display the latest payment for a subscription and the date the next one is due.
Is this correct?
Customer: replied 2 years ago.

Yup!

Expert:  Richard replied 2 years ago.
ok, this is a different approach that will require coding, doing this now.
Expert:  Richard replied 2 years ago.
ok, its now done as it does not use a query as you are wanting to update a field on an existing form already based on a Table so it needs to be coded.
I did this, and it will add the date when it sees it as blank so not already filled in.
You can get it under ID 526996
Let me know if you have any difficulties please
Customer: replied 2 years ago.

Will look this afternoon, thanks Richard

Expert:  Richard replied 2 years ago.
ok thanks
Let me know if you have problems, or would like any additional information or assistance, else if you can take a second to rate my service by clicking one of the stars at the top of the screen and then submit, that would be greatly appreciated.
Thank you
Customer: replied 2 years ago.

OK ... so I have to admit I'm struggling here. I see the dates there when the DB is opened, on that record, but not on others. The other thing is with you have coded it, how I get that code into the live version of the DB, once we can see it working OK. Can a field not be derived from a query result?

Expert:  Richard replied 2 years ago.
You need to ensure the field is blank first, then when you cycle through the record it adds it.
You need to go to the current property of the form and you will see the code I wrote for it, you copy that to the current property on the proper DB.
The field is derived from a query result, it needs to be done via code though in this circumstance.
Customer: replied 2 years ago.

Gives this ....?

Expert:  Richard replied 2 years ago.
The picture wont open here.
Whats the error say?
And which record number is ***** giving it
Customer: replied 2 years ago.

Uploaded as file 392822

Expert:  Richard replied 2 years ago.
ok, I did not see any record number but I believes its when adding a new record?
Can you check this version please
505168
Customer: replied 2 years ago.

I'm still not getting this... maybe it's just me. Something to do with the nature of the fields you're trying to populate? When you look at frmmain in form view do you see the two calculated dates in place for every record? Maybe the way to go is to create two new fields in datasheet view rather than using the ones I set up for the manual entry, the second one isn't even set up as a date field. If you can see dates in every record, and I can't, why might that be? I've tried emptying the fields and then clicking refresh too. Frustrating!

Expert:  Richard replied 2 years ago.
There are already data i the two fields, it is only going to populate it when the fields are empty.
Creating to new fields will not change anything.
It does not matter if its a date field or not.
so delete the data from the two fields then cycle to the next record and then back and you will see (as long as there is a subscription payment) a date put in.
Customer: replied 2 years ago.

Hi again ... sorry I've been out of office for most of the day.

OK, partly resolved - I can see now that after entering "" in every record in these two fields (using find/replace), I can then open a record in form view or highlight it in datasheet view and the dates pop in (and are subsequently retained).

However if I enter a new subscription, the two dates don't get updated on cycling back and forth to the record, or by hitting "refresh". It only gets updated if I clear the date and then cycle.

Any thoughts?

Bet you're glad you picked this one up!

David

Expert:  Richard replied 2 years ago.
Will look at it now David
Expert:  Richard replied 2 years ago.
Hi,
I checked this and it is working fine for me.
I enter a new record, then when finished go to the next new record, go back and you see sub due and new due date entered in
But you say
"It only gets updated if I clear the date and then cycle."
And as I said, you need to leave it blank in Sub Due and new Due date, my code is entering this in.
Customer: replied 2 years ago.

Leaving blank is fine to start the process off now, but then when someone makes a payment I don't want to have to go through the process of clearing the dates, and then cycling back and forth, especially if I am processing a whole bunch of payments in a session. I might as well still be entering the data manually. The field needs to be dynamic, and update itself in real time as new payments are added. Is this not possible in Access then? If not I'll have to think about an alternative methodology.

Expert:  Richard replied 2 years ago.
Ok, I thought about it and I removed the check for if the text box is blank or not so it will always run now.
this solves the issue for the new records and that you do not need to clear anything.
I uploaded it to 426352
Let me know the results please
Customer: replied 2 years ago.

Thank you, ***** ***** to do the trick. So just to finalise

1. I presume I can copy and paste this code across to the live DB?

Private Sub Form_Current()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

On Error Resume Next

Set rs = db.OpenRecordset("SELECT tblMembers.ID, tblMembers.Forename, tblMembers.Surname, Max(tblPayments.Date) AS MaxOfDate, Max([Date])+365 AS NextDate, tblPayments.As " & _
" FROM tblMembers INNER JOIN tblPayments ON tblMembers.ID = tblPayments.ID " & _
" Where tblMembers.ID = " & Me.ID & _
" GROUP BY tblMembers.ID, tblMembers.Forename, tblMembers.Surname, tblPayments.As " & _
" HAVING (((tblPayments.As)='Subscription'));")

Me.SubDue = rs("MaxOfDate")
Me.New_Sub_Due = rs("NextDate")

End Sub

2. How is this linked to the fields?

3. Are there any complications if a field name is ***** ***** or do I just change the field name as normal and the coding catches up?

4. Are there any issues if I change the field data type from "text" to "date", so that it can be used for filters and queries correctly.

Expert:  Richard replied 2 years ago.
1) Yes that is correct.
2) via this code
Me.SubDue = rs("MaxOfDate")
Me.New_Sub_Due = rs("NextDate")
3) Don't change the nae fields its updating else it will stop wortking.
4) no this is ok.
If you would like any additional information or assistance, please do not hesitate to let me know.
And if you can take a second to rate my service by clicking one of the stars at the top of the screen and then submit, that would be greatly appreciated.
Thank you
Richard, Software Engineer
Category: Microsoft
Satisfied Customers: 36529
Experience: Over 15 year experience resolving Microsoft Office Issues
Richard and other Microsoft Specialists are ready to help you

Related Microsoft Questions