I have been reluctant to post on these discussion boards out of embarrassment at the state of my skills. I know that a lot of what I post below will make you cringe. Nevertheless, I have reached a point where I need help – I have been working on this problem for weeks to no avail. I have googled numerous terms but I think the main problem is I don’t even know what search terms to use (among the many searches I have tried are: “Microsoft Access automate”; “Microsoft Access automatically generate”; “Microsoft Access trigger”). On the bright side, I’m pleased to report that I’ll be starting formal coursework in the Fall …at this point, I’m all self-taught.
I am building a Microsoft Access 2000 database for my company to track our patents. The docketing database is being used to (a) hold information about all of our intellectual property (e.g., the date of filing, application number, patent number, etc.), and (b) to keep track of deadlines we need to meet/actions we need to take.
The two main tables in the database are the “Patent Application Basic Info” table and the “Patent Applications Dates” table, the latter of which houses our deadline information.
The main form in the database is called “Patent Applications Actions & Due Dates” (yes, I know that my naming conventions are horrific – I started this database before I knew there were naming conventions …wait until you see my variable names!).
The following is a truncated version of the SQL for the main part of the form, which shows the basic information about each patent:
SELECT [Patent Application Basic Info].[Patent Application No], [Patent Application Basic Info].[Family No], [Patent Application Basic Info].[Filing Date], [Patent Application Basic Info].[Priority Date], [Patent Application Basic Info].[Attorney Ref No], [Patent Application Basic Info].[Country Code], [Patent Application Basic Info].[Application Type], [Patent Application Basic Info].Title, [Patent Application Basic Info].Status,
……it goes on with more variables
FROM [Patent Application Basic Info]
ORDER BY [Patent Application Basic Info].[Family No], [Patent Application Basic Info].[Attorney Ref No];
The form also has a subform (guess what it’s called? – the “Patent Applications Dates subform”), which shows the deadlines for each patent. The child and master fields are linked by the “Patent Application No” variable. The following is the SQL for the subform:
SELECT [Patent Applications Dates].[Completed], [Patent Applications Dates].[Patent Application No], [Patent Applications Dates].[Indicator], [Patent Applications Dates].[Date], [Patent Applications Dates].[Action], [Patent Applications Dates].[Remarks] FROM [Patent Applications Dates] ORDER BY [Patent Applications Dates].[Completed] DESC , [Patent Applications Dates].[Date];
Here is my question -- I want to be able to automatically generate new records/deadlines in the “Patent Applications Dates subform”/“Patent Applications Dates” table based on an event occurring. For example, one of the deadlines/actions the user can select from a combo box in the “Patent Applications Dates subform” is “Maintenance Fee/Annuity Payment due” (the name of the deadline variable is “Action”). The user would also then enter a due date (“Date”). I want the database to be constructed in a manner such that whenever the user selects “Maintenance Fee/Annuity Payment due,” two new records are autogenerated in the “Patent Applications Dates subform”/“Patent Applications Dates” table: one with the variable “Action” set to “Maintenance Fee/Annuity Payment due in 1mo” and the “Date” as one month prior to that of the user-entered record, and another record with the variable “Action” set to “Maintenance Fee/Annuity Payment due in 3mo” and the “Date” as three months prior to that of the user-entered record.
Is this possible?
Thanks in advance for your time…
Elizabeth
I am building a Microsoft Access 2000 database for my company to track our patents. The docketing database is being used to (a) hold information about all of our intellectual property (e.g., the date of filing, application number, patent number, etc.), and (b) to keep track of deadlines we need to meet/actions we need to take.
The two main tables in the database are the “Patent Application Basic Info” table and the “Patent Applications Dates” table, the latter of which houses our deadline information.
The main form in the database is called “Patent Applications Actions & Due Dates” (yes, I know that my naming conventions are horrific – I started this database before I knew there were naming conventions …wait until you see my variable names!).
The following is a truncated version of the SQL for the main part of the form, which shows the basic information about each patent:
SELECT [Patent Application Basic Info].[Patent Application No], [Patent Application Basic Info].[Family No], [Patent Application Basic Info].[Filing Date], [Patent Application Basic Info].[Priority Date], [Patent Application Basic Info].[Attorney Ref No], [Patent Application Basic Info].[Country Code], [Patent Application Basic Info].[Application Type], [Patent Application Basic Info].Title, [Patent Application Basic Info].Status,
……it goes on with more variables
FROM [Patent Application Basic Info]
ORDER BY [Patent Application Basic Info].[Family No], [Patent Application Basic Info].[Attorney Ref No];
The form also has a subform (guess what it’s called? – the “Patent Applications Dates subform”), which shows the deadlines for each patent. The child and master fields are linked by the “Patent Application No” variable. The following is the SQL for the subform:
SELECT [Patent Applications Dates].[Completed], [Patent Applications Dates].[Patent Application No], [Patent Applications Dates].[Indicator], [Patent Applications Dates].[Date], [Patent Applications Dates].[Action], [Patent Applications Dates].[Remarks] FROM [Patent Applications Dates] ORDER BY [Patent Applications Dates].[Completed] DESC , [Patent Applications Dates].[Date];
Here is my question -- I want to be able to automatically generate new records/deadlines in the “Patent Applications Dates subform”/“Patent Applications Dates” table based on an event occurring. For example, one of the deadlines/actions the user can select from a combo box in the “Patent Applications Dates subform” is “Maintenance Fee/Annuity Payment due” (the name of the deadline variable is “Action”). The user would also then enter a due date (“Date”). I want the database to be constructed in a manner such that whenever the user selects “Maintenance Fee/Annuity Payment due,” two new records are autogenerated in the “Patent Applications Dates subform”/“Patent Applications Dates” table: one with the variable “Action” set to “Maintenance Fee/Annuity Payment due in 1mo” and the “Date” as one month prior to that of the user-entered record, and another record with the variable “Action” set to “Maintenance Fee/Annuity Payment due in 3mo” and the “Date” as three months prior to that of the user-entered record.
Is this possible?
Thanks in advance for your time…
Elizabeth
