• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Add a record to a table through a subform

Another crazy question.

I currently have a subform in continuous forms format on a main form. the subform's record source is a table. Every value in Field1 of that table is exactly the same. Therefore, Field2 and Field3 are the only fields displayed in the continuous forms area (the individual records). Field1 is displayed in a textbox near the top of the subform (instead of listing the same value over and over again in the continuous forms area)

The problem:

When I want to add a new record to the table through the subform, I'm placing the cursor in the blank row at the bottom of the continous forms. When I do this, the textbox goes blank (obviously because it's tied to Field1 and the new record has no value entered into it yet)

What I need are two things:

1. Keep the textbox populated with the value that is currently in it even if I place the cursor in the 'new record' row.
2. Have the textbox value copied into Field1 of the new record when I create a new record (ie when I type something into the blank row at the bottom of the continuous forms subform)
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 4
  • 4
1 Solution
 
SheilsCommented:
The field1 is the one that links the subform to the main form. So you do not need to display it in the subform. Just display it in the main form.

Also insert that field1 in the body (not the header) of the subform and set visibility to false
0
 
dbfromnewjerseyAuthor Commented:
No, that's wrong.   I need to display Field1 in the header section of the subform.  When I click on the blank row of the detail section of the subform (to add a new record), I need to have the value in the textbox remain as well as be copied into Field1 of the new record.
0
 
SheilsCommented:
What is field one. I took it to be the field that links the form to the subform. If so you definitely do not need to display it on the subform.

<I need to have the value in the textbox remain as well as be copied into Field1 of the new record>

You do not need to do anything special for this to happen if field1 is the link it will just happen automatically through access inbuild function.

Please post a sample of your DB so that I can figure out exactly what you are doing. Because this should not be an issue the linked field should just add automatically.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
dbfromnewjerseyAuthor Commented:
I've attached a database. Open up Table1.  You'll see 3 records in there. Then close it and open Form1. You'll see Table1 data displayed in the subform. Notice the textbox in the header section of the subform. It's populated with the value 'california'. Click in the empty row at the bottom of the detail section of the subform as if you're going to add a record. When you do, 'california' will disappear from the textbox. I want it to remain in the textbox.  Now add a record to the subform. Add 'Adams'  'Sam' for field2 and field3. When you do, I want the textbox value (or the field1 value of any existing record since the values will all be the same) to be copied into field1 of the new record.  If this isn't done, then by entering only values for Field2 ('Adams') and Field3 ('Sam") into the new record line of the subform will create a record with an empty Field1. Look at the Table1 Expected Results to see how I want Table1 to look after entering the new record.
test-db.accdb
0
 
SheilsCommented:
There was no link between your form and subform. I have fixed it and it works fine.

But I note that the db will work better if you split the table. You should have a table state(tblStates) and a table persons(tblPersons).

tblStates will have fldStateID, fldState
tblNames will have fldPersonID,fldStateID,fldNameID,fldSurnameID

The the datasource for the form will be tblStates and the datasource for the subform will be tblPersons and they would be linked together by fldStateID.
test-db.accdb
0
 
dbfromnewjerseyAuthor Commented:
Thanks but that doesn't work.  My actual database has a combobox on the main form where the user selects a value.  When a selection is made, a query is run which populates a table.  That table is the record source of the subform.  So, every time the combobox value is changed, the records displayed in the subform change.  In other words, the table tied to the subform is a 'tempoary table' that is cleared and repopulated every time a combobox (on the main form) selection is made.  By putting the textbox on the main form as you did, it's value will not change when the combobox value is changed. That's why I put the textbox in the header section of the subform.  

I guess another way to look at it is if you know how to copy the value in the combobox into field1 of the new record instead of from your textbox on the main form into field1 of the new record, that would be fine. But I don't know how to do that.

So, if you know how to do it by that approach, what I would need then is if the user enters data into the empty row at the bottom of the textbox, that means he's creating a new record. When that happens, populate field1 of that new record with the value currently in the combobox.

Unless of course you know of a way to have the value of the text box you place on the main form change along with the change in the combobox value. Then populate the new record with the value from that textbox.
0
 
dbfromnewjerseyAuthor Commented:
oops.... I meant if the user enters data into the empty row at the bottom of the SUBFORM.
0
 
SheilsCommented:
I know how to insert data into a temporary table using the on change event of a combo box but in the current case I really don't see the justification to go through all this trouble. As I mentioned in my last post "db will work better if you split the table. You should have a table state(tblStates) and a table persons(tblPersons).

tblStates will have fldStateID, fldState
tblNames will have fldPersonID,fldStateID,fldNameID,fldSurnameID"

This along with a not-in-list event for the combo box should do the job. I have attached a modified version of my previous example
test-db.accdb
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now