Link to home
Avatar of Jenkins
JenkinsFlag for United States of America

asked on

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.
Avatar of peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Jenkins


But where do I go/how do I set up this link that you're referring to?

The combobox is unbound.
Also, you can use the BeforeInsert Event of your sub-form as shown below ...

Me.Field1 = Forms!MainFormName!ComboBoxName

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.
Avatar of Jenkins


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!ComboBoxName

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.  
Avatar of Jenkins


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.

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?
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!ComboBoxName
End Sub

Also make sure you are using the BeforeInsert Event of your Sub-Form where the detail records are being added.

Avatar of Jenkins


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).
Which version of Access?

What is the datatype of field2?

Is field2 using any sort of Lookup or multi-value feature?
Avatar of Jenkins


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.
Avatar of Jenkins


OK. Figured the bug out. As usual, it was something stupid that I did. The Link Master / Link Child solution works fine. Thanks.