Command Button on Main Form to Add New Record to Subform Datasheet

[nicktaylor_gen]

I have a database which has three unbound main forms (below contains the main forms and their objects/events). New records are not allowed to be added to the borrowers and projects tables. Macros are run upon opening the form to add new records (if there are any). The issue is that newly added loans will not have any action items. Therefore, when the action items form is opened, no records are selected. Is there a way to allow the user to click an add new record button using the txt_linker_project text box? I can't seem to create a macro on the main form which adds a record to the subform data sheet and opens the other single form to that newly created record.

I hope the below is sufficient to help you understand what I'm doing. Perhaps there is a better way.



1. Borrowers

• Bound borrower subform in datasheet format linked to borrower table
  
  • OnCurrent event
    
    • Sets value of txt_linker_borrower equal to selected borrower record ID
  
  
  
  
• 2 bound subforms linked to
• txt_linker_borrower (unbound text box storing borrower ID)
• Projects command button
  
  • OnClick event
    
    • Opens Projects/Loans main form and OnOpen of Projects/Loans form sets value of Projects/Loans txt_linker_borrower equal to Borrowers txt_linker_borrower
    • Closes Borrowers form

2. Projects/Loans

• Bound projects/loans subform in datasheet format linked to projects table
  
  • OnCurrent event
    
    • Sets value of txt_linker_project equal to selected project record ID
  
  
• txt_linker_borrower (unbound text box storing borrower ID)
• txt_linker_project (unbound text box storing project ID)
• Borrowers command button
  
  • OnClick event
    
    • Opens Borrowers main form
    • OnOpen of Borrowers form sets value of Borrowers txt_linker_borrower equal to Projects/Loans txt_linker_borrower after checking to see if Projects/Loans form is open
    • Closes Projects/Loans Form
  
  
• Action Items command button
  
  • OnClick event
    
    • Opens Action Items main form
    • OnOpen of Action Items form sets value of Action Items txt_linker_borrower and txt_linker_project equal to Projects/Loans txt_linker_borrower and txt_linker_project
    • Closes Projects/Loans Form

3. Action Items

• Bound action items subform in datasheet format linked to action items table
  
  • OnCurrent event
    
    • Sets value of txt_linker_action_item equal to selected action item record ID
  
  
• txt_linker_borrower (unbound text box storing borrower ID)
• txt_linker_project (unbound text box storing project ID)
• txt_linker_action_item (unbound text box storing action item ID)
• Projects command button
  
  • OnClick event
    
    • Opens Projects/Loans main form
    • OnOpen of Projects/Loans form sets value of Projects/Loans txt_linker_borrower and txt_linker_project equal to Action Items txt_linker_borrower and txt_linker_project
    • Closes Action Items Form

[ssanfu]

I am pretty much confused on what your question is. Don't know what or where txt_linker_borrower and txt_linker_project means.

I think you want to click a button (on the borrower form?) to add a record to the action table???
Looks like you can just start entering data into the datasheet at the top of the Action Items form.


Any chance you would post your dB? Only need two examples of borrower records, two project records and action records. Change any sensitive data like changing names to Mickey Mouse (or Earthbusters Co.).


Do a "Compact and Repair", then Zip the dB. Attach the zip to a post.

[nicktaylor_gen]

I am pretty much confused on what your question is. Don't know what or where txt_linker_borrower and txt_linker_project means.

I think you want to click a button (on the borrower form?) to add a record to the action table???
Looks like you can just start entering data into the datasheet at the top of the Action Items form.


Any chance you would post your dB? Only need two examples of borrower records, two project records and action records. Change any sensitive data like changing names to Mickey Mouse (or Earthbusters Co.).


Do a "Compact and Repair", then Zip the dB. Attach the zip to a post.

Hi ssanfu,

I'm sorry, I should have explained that the txt_linkers are unbound text boxes. Please see attached and thank you for the help.

nick

PortfolioManagementTest_AccessForums.zip

[ssanfu]

OK, so I've been looking at your dB.... First thing is ...I hate macros!!


What is happening is that when you open the Action Items form, a macro copies the "borrower" id in the Project form to the unbound text box, then copies the "Loan_ID" in the Project form to the projects unbound text box (naming problems) in the Action Items form.
Then another macro copies the the "Loan_ID" from an Action Items sub form to the projects unbound text box on the Action Items main form.
Since there are no action item records for the Borrower and project ("Loan_ID"), the value in the projects unbound text box is wiped out. So when you try to add a new action item record, there is no project ("Loan_ID") available to create the record.

I can't fix it using macros. I don't use macros (ever) and don't want to learn, so.....
Fixing this using VBA code would be fairly easy. How are your VBA skills??



----------------------------------------------------------------------------
Not trying to slam you, but I see things that I think will cause you problems....

In looking at your dB, I did notice many other problems that I would suggest you attend to before moving on.

Every code module should have two lines as the top two lines:

Option Compare Database
Option Explicit

There are spaces and special characters in object names. example bad field name: Borrower Tier (New vs# Old - Variance)
There are fields that begin with a number : 85% CH Flag

Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


There are a few reserved words used as field names: example "Year" and "Month"
http://www.allenbrowne.com/AppIssueBadWord.html


You might also look at
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/Autonumbers


AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.

Linking problems (IMO)






And speaking of tables..... "tbl_kpi_calculations" has 254 fields!! I've never seen that many fields in one table......