Automatically Add New Memo Field

[TheShabz]

That would be helpful. If you can send me what you have currently, with made-up data, I can work off that.

[Luke]

Ok thanks a lot, the attachment below is very similar to the actual data I will be using.

[TheShabz]

Can you reattach it as an mdb? I dont have Access07 at work

[Luke]

I could only use up to 500 KB to attach the database so I only made a table. I didn't have room for the query or form. The query includes the next review date in which I use the expression builder "DateAdd([Review Cycle],1,[Last Review])".

[TheShabz]

First run the compact and repair utility (Tools>database utilities>compact and repair) then compress the db into zip format. That should make it easier to upload

[Luke]

Ok I clicked on compact and repair and converted it into an mdb format so hopefully that works. If it doesn't let me know. Thanks.

[TheShabz]

Gimmie some time and I'll have something for you soon unless I get caught up with work.

[Luke]

Sounds good thanks for all your help man.

[TheShabz]

Take a look at this. It's basically what you wanted. hit alt+F11 for the VBA. I commented everything on there so it should be pretty straightforward. If there's any code you dont understand feel free to ask or hit up google.

The 3 tables starting with "tbl" are ones I created. the 4th one is the one that was provided. I left it there but it is not being used. Further, on my entity table, I only entered in the first 4, to get each cycle down correctly. Notice that I separated everything into tables and then used the relationship window to connect them together with their key fields.

I didn't include any error catching or anything like that so unless you enter in information correctly, you'll probably get an error. Everything should work, though.

****EDIT****

And of course I forget something. In the VBA, at the bottom of the function cmdSumit_Click(), right before "End Sub" type in Me.subForm.Requery. That will refresh the subform with the newest Review.

[Luke]

Oh. My God. That is siiiiick thank you so much man this is amazing. I typed in Me.subForm.Requery before End Sub in that command, so I'm assuming it worked. Just to be clear though, if I copy/paste your tables and VBA codes and make the same relationships you did in my database it should all work? Thanks again theShabz problem solved.

[Luke]

I created a new database and essentially copy/pasted each table you made along with the queries and made the correct relationships, criteria, etc. The problem is when I open the main form and try to choose an entity a pop-up box appears that says "the record source '~sq_cmainForm~sq_ccmbEntity' specified on this form or report does not exist". Do I have to edit the VBA code? All of my table names and fields are exactly the same as they are in the database you created. Thanks.

-Luke

[Luke]

Nevermind got it to work thanks again

[TheShabz]

Glad you got it to work. Remember though, what I did was at it's bare bones level of functionality. Any little thing can kill it without proper error checking. Try looking into it to add it to the VBA. Consider it practice.

[Luke]

Yea I was messing around with it and noticed that anything can completely kill it, thanks for letting me know. I had a meeting with my bosses yesterday, showed them the form, and they were amazed. It's exactly what they wanted and I took absolutely no credit haha I told them "the Shabz" did the whole thing, so thanks a ton for that. I actually have one more "little" problem though and I was wondering if you had any advice. They wanted me to create a report from this form and I don't think you can create a report from a form can you? Should I try and make the report based off of the queries? and do reports have VBA codes as well? Thanks a lot.

I know you can save the form as a report but when I do so, it's not showing the results I would like it to and I can see the notes for only one entity.

-Luke

[TheShabz]

A report wouldn't be based off the form. What you would do is treat a form and a report as essentially the same thing. The only difference is that forms are interactive where reports really aren't. You would trigger a report generation with criteria from a form, but it's not bound to that form.