Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update a field on a subform after combo box change 2

Status
Not open for further replies.

NKA

Programmer
Mar 20, 2002
76
NZ
I am pulling my hair out over this one and am now waving the white flag!

I have a combo box which is populated from a table. The combo box is for [Priority]. I would like to populate the [TargetDate] automatically based on the date the job was identified and the priority.

ie; a High Priority job has a target date of 14 days from date identified; a medium has 28 days etc etc

To further complicate the issue, the fields I am referring to are on a subform.

I would like to have the user see the target date change when the priority is changed.

Can anyone help? I must also point out that I am not a great one for fancy coding either... I get easily confused! NKA

The answer's always easy - if you know it! ;-)
 
You should be able to put code like this in your main form:

If [Priority] = "High" Then
Me.<subformcontrolname>.Form!TargetDate = Me.Subform.Form!IdentityDate + 14
Else
Me.Subform.Form!TargetDate = Me.Subform.Form!IdentityDate + 28
Endif

<subformcontrolname>
is the name of the control on your main form that holds the subform.

 
Hello NKA,
If the priority information comes from a table, I recommend that you do not hard-code the length. You will find your data easier to manage and update if you add a column to your priority table to hold the length in days.

Lets assume that your table, after adding this column, looks like this:
Code:
Priority    Length
--------    ------
High        14
Medium      28
Low         56
On your form, you will make the combo box diplay column 1. Column 2 will be invisible to the user. Simply make the combo box Row Source = to the priority question and the Bound Column = 1.

In the
Code:
After Update
event of the combo box, add the following:
Code:
'Note: the combo box columns are 0-based, so 1 = column 2
Me.[Name of subform].Form.TargetDate = [Name of combo box].Column(1)
Simply replace &quot;Name of subform&quot; and &quot;Name of combo box&quot; with the appropriate values.
Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance °•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°
 
Sorry the code should be:
Code:
Me.[Name of subform].Form.TargetDate = Now + [Name of combo box].Column(1)
If you are adding to the current date.

Or, if you are adding the days to date that the user has already entered:
Code:
Me.[Name of subform].Form.TargetDate = Me.[Name of subform].Form.TargetDate + [Name of combo box].Column(1)
Robert
•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•
Professional, affordable, Access database solutions and assistance °•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°•°
 
Thank you both for your help.. but it still doesn't work!

Perhaps it would help if I mentioned I am using Access 97 and that all the fields I am referring to are on the subform.

I have tried both methods - and get the error message &quot;Method or Data Member not found&quot;.

SUB_FRM_Task is the name of the subform and is also the control name for it on the main form

This is the actual code I am trying to use (hard code method):

If ([Priority]) = &quot;High&quot; Then
Me.SUB_FRM_Task.Form!Target = Me.SUB_FRM_Task.Form!Date_Audit + 14
Else
Me.SUB_FRM_Task.Form!Target = Me.SUB_FRM_Task.Form!Date_Audit + 28
End If

And this is the actual code (using additional field in table):

Me.SUB_FRM_Task.Form.Target = Me.SUB_FRM_Task.Form.Date_Audit + Priority.Column(1)

(I did say I wasn't very good with coding!!!) Any ideas? NKA

The answer's always easy - if you know it! ;-)
 
Ooops!

Should've played with the code more before jumping back on here!

I have actually solved the problem myself (perhaps I am actually understanding code after all!).

The code now reads:

Me.Target = Me.Date_Audit + Me.Priority.Column(1)

Part of the reason for it not working initially is because I forgot to make the combo box with 2 columns!! It does help!

I will give you both a star as both codes are useful! Many thanks. NKA

The answer's always easy - if you know it! ;-)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top