Updating field in detail section of form from Unbound Text Box

[Guest]

Hello-

I have a form with a subform. In the subform. the user enters data into two unbound combo boxes in the header section of the subform. These entries will be the filter criteria for the detail section of the form. I will need to bring up the records based upon these entries. There is a default entry in each combo box, and they have lookup tables for their possible entries with no option for additions.

I am having trouble getting the text boxes, which have the properties of visible = 'no' and tab stop = 'no' to update based upon the entries in the header combo boxes when the user tabs or hits enter to get to the detail section. The detail section makes up an entire record in a child table. I am trying to have the user be able to select the combo box entries ONCE and then enter data for the entire group of values that have the combo box values as their categories. I must pull up any data that has ALREADY been entered for this two category group earlier should the user return to this combination in the future and want to edit or enter new data.

Any help would be appreciated.

Vincent DeLuca
(e-mail address removed)

 

[Guest]

Update:

I have gotten the fields to update the data from the header correctly to the detail new row using variables.

I still am trying to resolve this issue: I need to have the subform filter the detail section when I enter the form for the first time and whenever either of the combo boxes in the header (the category boxes to make it easy) are updated. I have tried several different blocks of code in many different event triggers without success. The current rendition looks like this (on subform "got focus" event):

CurrentRun = '001'


Me.Filter = "RunNum = CurrentRun'
Me.FilterOn = True

On each of the combo box on LOST FOCUS events, the same block of code would run with the following modifications:

CurrentRun = cboRun
CurrentDepartment = cboDepartment
CurrentFilter = "RunNum
Me.Filter = CurrentRun
Me.FilterOn = True

 

[Marshall Barton]

Update:

I have gotten the fields to update the data from the header correctly to the detail new row using variables.

I still am trying to resolve this issue: I need to have the subform filter the detail section when I enter the form for the first time and whenever either of the combo boxes in the header (the category boxes to make it easy) are updated. I have tried several different blocks of code in many different event triggers without success. The current rendition looks like this (on subform "got focus" event):

CurrentRun = '001'


Me.Filter = "RunNum = CurrentRun'
Me.FilterOn = True

On each of the combo box on LOST FOCUS events, the same block of code would run with the following modifications:

CurrentRun = cboRun
CurrentDepartment = cboDepartment
CurrentFilter = "RunNum
Me.Filter = CurrentRun
Me.FilterOn = True

Your posted code has several errors. Actually, it wouldn't
even compile the way it is above. I suspect that you
retyped it and added several typos from your real code. In
the future, use Copy/Paste so we can see the code as it
really exists in your program.

The line
Me.Filter = "RunNum = CurrentRun'
should be
Me.Filter = "RunNum = " & CurrentRun
so should the othe line where you set Filter.

I can't figure out what the lines
CurrentFilter = "RunNum
Me.Filter = CurrentRun
are supposed to do, but, as is, they are incomplete.

I suggest that you back up and consider the possibility of
putting the combo boxes in the main form and using the
subform control's Link Master/Child properties to do this
job instead of fooling around with the sometimes problematic
Filter property.

 

[Guest]

Thanks, Marsh.

Would putting the combo boxes in the main form allow me to do a multiple subset based upon both values of the two combo boxes at the same time? I am filtering for what we call "Run" and "Department".

Thanks.

Vince

 

[Guest]

Marsh-

Continuation of last letter...

Option 1:
If I stick with the filter property solution, how do I do a subset of the records in the subform based upon a value in the main form? I am having trouble with accurately referencing controls on the subform from the main form and vice versa, even following the examples I can find.

Here is my attempt at the On Current property of the main form. I want the initial subset of data in the subform to be selected based upon the entry in the [Date] field on the main form and the default values in the "Run" and "Department" combo boxes.

Private Sub Form_Current()
'Get All Records for the Subform before applying filter. NOTE: IS THERE A
'BETTER WAY TO DO THIS? THIS IS SLOW!
DoCmd.ShowAllRecords

'Attempt to Filter Subset of Records for the Subform
' Need to add references to the Department field = "H" and Date = the Date Entered on the main form I just switched to.
DoCmd.ApplyFilter , "Forms!ShiftInfo!WorkerPullsforCustomer _ Subform].RunNum = '001'"
End Sub

Also, i am getting a parameter request for the following when I switch records on the main form: Forms!ShiftInfo!WorkerPullsforCustomer. For some reason, it seems this value is not being passed in the previous filter.

Option 2: SHIFTING GEARS TO YOUR EARLIER PROPOSED SOLUTION NOW:
If I am defining master child linking fields, do these need to be field data in a master table or can I use unbound combo boxes for the master link field data as I was attempting to do in the SubForm. Also, do all the fields need to be indexed in both forms? Do they need to be a multifield primary key?

I appreciate your help with this, Marsh. Trying to learn so I won't have so many questions down the road.

Thanks again.

Vince

 

[Guest]

An observation: It seems the Master-Child relationships of the form to the subform require that a field be common to both and that it be indexed and the primary key. I currently have a ShiftID field set up as the primary key for the master-child link. I need to have the worker "pulls" (we are running a warehouse) be filtered by the "run" (truck route) AND by the Department as well as by the ShiftID. I am wondering how this would make sense if the Department and the Shift need to be a key field in the primary table, as we have multiple departments and runs that we need to enter in the child table for each shift. When the Run OR the Department is updated in the combo box, I want the child form to be automatically updated to reflect only the subset of data that applies to all three keys: ShiftID, RunNum and Department.

I am not sure where I would set the run and department combo boxes to in the master form.

Thanks.

Vince

 

[Guest]

Thanks, Marsh. Got the Master/Child forms to work great. Appreciate it!

Vince

 

[Marshall Barton]

Thanks, Marsh. Got the Master/Child forms to work great. Appreciate it!

Vince

Does this mean we're done here or is there still an open
question?