How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.
Combobox and Subform question
I have a combobox on my main form with a table as its rowsource.
I have a subform on my main form whose recordsource is a table. The subform is in continuous forms format. The table tied to the subform has 4 fields in it (Field1, Field2, Field3 and Field4). Only fields 2 through 4 are displayed in the subform. Field1 is not displayed.
When a user enter a new record through the subform (by scrolling to the bottom of the continous forms and entering data in the blank line, I need Field1 to be populated with the current combobox value. If this is not done, then when the user creates a new record, Field1 will be blank because there is nowhere in the subform to manually enter data for Field1 since Field1 is not displayed. So the question is, how do I copy the combobox value into Field1 when a user creates/adds a new record to the table using the subform. Thanks.
I have a subform on my main form whose recordsource is a table. The subform is in continuous forms format. The table tied to the subform has 4 fields in it (Field1, Field2, Field3 and Field4). Only fields 2 through 4 are displayed in the subform. Field1 is not displayed.
When a user enter a new record through the subform (by scrolling to the bottom of the continous forms and entering data in the blank line, I need Field1 to be populated with the current combobox value. If this is not done, then when the user creates a new record, Field1 will be blank because there is nowhere in the subform to manually enter data for Field1 since Field1 is not displayed. So the question is, how do I copy the combobox value into Field1 when a user creates/adds a new record to the table using the subform. Thanks.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You set the link child and link master fields to the fields that have to match. So the controlsource of the combo and field1 for the child.
If the main form combo does not have a controlsource (is unbound) then you can type into the master link the full forms reference :
forms!formname!comboboxnam
You must be sure that the combobox value is determined as you expect. The displayed value in a combobox is not necessarity the bound value.
If the main form combo does not have a controlsource (is unbound) then you can type into the master link the full forms reference :
forms!formname!comboboxnam
You must be sure that the combobox value is determined as you expect. The displayed value in a combobox is not necessarity the bound value.
Also, you can use the BeforeInsert Event of your sub-form as shown below ...
Me.Field1 = Forms!MainFormName!ComboBo
ET
Me.Field1 = Forms!MainFormName!ComboBo
ET
The links are set in the properties of the subformcontainer control. One way to see them is to double click the edge of the subform.
I haven't tried the link child / link master method yet but I tried this method and can't get it to work:
Me.Field1 = Forms!MainFormName!ComboBo
I tried placing a textbox in the detail section of my subform to hold Field1 because I didn't have it before (which was the desired setup). The textbox is named Field1 and its control source is set to Field1, which is the exact name of the field in the table tied to the subform. When I enter data into any of the fields (obviosuly other than Field1) in the blank row of the subform, a new record is created but Field1 is remaining empty. It is not being populated with the combobox value.
Me.Field1 = Forms!MainFormName!ComboBo
I tried placing a textbox in the detail section of my subform to hold Field1 because I didn't have it before (which was the desired setup). The textbox is named Field1 and its control source is set to Field1, which is the exact name of the field in the table tied to the subform. When I enter data into any of the fields (obviosuly other than Field1) in the blank row of the subform, a new record is created but Field1 is remaining empty. It is not being populated with the combobox value.
link master / link child method:
I have it kind of working. I'm having a problem though. As I indicated previously, Field2, Field3 and Field4 (and now Field1 also) are displayed in the detail section of my subform. I can see that Field1 of the 'new record' row is populated with the combobox value. That's fine. BUT...when I enter data into Field2 of that same row, an insert if occuring and the entire row is blank (including Field1). However, if I enter data into Field3 or Field4 and don't enter anything into Field2, then it stays on that row and the 'new record' will have Field1 populated like I'm trying to do. Why is an insert occurring when I enter data into Field2 (the leftmost field displayed in the subform)? The insert messes things up.
I have it kind of working. I'm having a problem though. As I indicated previously, Field2, Field3 and Field4 (and now Field1 also) are displayed in the detail section of my subform. I can see that Field1 of the 'new record' row is populated with the combobox value. That's fine. BUT...when I enter data into Field2 of that same row, an insert if occuring and the entire row is blank (including Field1). However, if I enter data into Field3 or Field4 and don't enter anything into Field2, then it stays on that row and the 'new record' will have Field1 populated like I'm trying to do. Why is an insert occurring when I enter data into Field2 (the leftmost field displayed in the subform)? The insert messes things up.
IIf field2 is bound then I can't think of any reason why you would get this effect.
If you don't enter anything in f3 and f4 and then move on to a new record (or anywhere else) does the record get saved without f1 being filled?
If you don't enter anything in f3 and f4 and then move on to a new record (or anywhere else) does the record get saved without f1 being filled?
dbfromnewjersey .... If you use the Form's Before Insert Event as shown below, you will need to replace MainFormName and ComboBoxName with your actual control names. As long a Field1 is in the underlying query/table that's the Record Source for your Form then it will work.
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Field1 = Forms!MainFormName!ComboBo
End Sub
ET
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Field1 = Forms!MainFormName!ComboBo
End Sub
ET
Also make sure you are using the BeforeInsert Event of your Sub-Form where the detail records are being added.
ET
ET
Now that I have the link master / link child set up, this is what my subform looks like after making a combobox selection:
A number of rows are populated with data (because I made a combobox selection).
The very last row in the subform ( the one where you would enter a new record into...with the asterisk on the far left) has the fields arranged/displayed as follows:
Field1 Field2 Field3 Field4
By the way, I originally wasn't using/displaying Field1 in the subform detail row line but I stuck it in because of dealing with this problem.
Anyway, on the 'new record' row (the one with the asterisk to the left) has Field1 populated with the combobox value. The other fields on that row are blank. If I check the table, there isn't a record saved for that row that has nothing populated but Field1. That's fine.
Now, let's say I enter data into Field3 or Field4. The little pencil icon will appear on the far left of the row that has Field1 already populated. If I enter data into either of those fields, a record will be created with Field1 being populated. That's fine.
Now let's say instead of (or in addition to) entering data in Field3 or Field4, I enter data in Field2.
A completely blank row gets inserted. The row that has Field1 already populated gets pushed down below that blank row. So if I enter data into Field2, Field1 will not be populated with the combobox value (because when I enter data into Field2, a completely blank row gets inserted).
A number of rows are populated with data (because I made a combobox selection).
The very last row in the subform ( the one where you would enter a new record into...with the asterisk on the far left) has the fields arranged/displayed as follows:
Field1 Field2 Field3 Field4
By the way, I originally wasn't using/displaying Field1 in the subform detail row line but I stuck it in because of dealing with this problem.
Anyway, on the 'new record' row (the one with the asterisk to the left) has Field1 populated with the combobox value. The other fields on that row are blank. If I check the table, there isn't a record saved for that row that has nothing populated but Field1. That's fine.
Now, let's say I enter data into Field3 or Field4. The little pencil icon will appear on the far left of the row that has Field1 already populated. If I enter data into either of those fields, a record will be created with Field1 being populated. That's fine.
Now let's say instead of (or in addition to) entering data in Field3 or Field4, I enter data in Field2.
A completely blank row gets inserted. The row that has Field1 already populated gets pushed down below that blank row. So if I enter data into Field2, Field1 will not be populated with the combobox value (because when I enter data into Field2, a completely blank row gets inserted).
Which version of Access?
What is the datatype of field2?
Is field2 using any sort of Lookup or multi-value feature?
What is the datatype of field2?
Is field2 using any sort of Lookup or multi-value feature?
Access 2003.
Field2 is Text. Indexed Duplicates OK.
Another thing I should mention that I noticed is like I indicated previously, the subform gets populated with records as the result of a combobox selection. Let's say I enter/change data in Field2 of any of the records displayed in the subform (not just the 'new record' row). When I do that, Field1 goes blank. This doesn't happen if I enter /change data in Field3 or Field4.
Field2 is Text. Indexed Duplicates OK.
Another thing I should mention that I noticed is like I indicated previously, the subform gets populated with records as the result of a combobox selection. Let's say I enter/change data in Field2 of any of the records displayed in the subform (not just the 'new record' row). When I do that, Field1 goes blank. This doesn't happen if I enter /change data in Field3 or Field4.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial