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.
A few days won't hurt
We will continue to look for a Professional to assist you.
Thank you for your patience,
459204 (anonymised version, sorry ... but it should do the trick)
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.
Actually, this was all included in the original question, see above and ...
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?"
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.
Will look this afternoon, thanks Richard
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?
Gives this ....?
Uploaded as file 392822
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!
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.
Bet you're glad you picked this one up!
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.
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.DatabaseDim 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")
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.