Populating form txt boxes from tables

[Mollybot]

Access 2000 - I have created a form containing a subform which allows the
user to select a drop down menu to populate the first field, on selecting the
value I would like to be able to automatically populate another field in the
same record with a value from the same row of the source table for example:

Form text boxes relating to table values

Course, Date taken, Date Renewal, Term

The source table the form is linked to contains Course and Term as its
values course is the drop down of txtbox1 in the form and Term is the value I
wish to have automatically entered into the Term.txtbox. From here I want to
calculate the renewal date from the Date taken by adding the years from Term.


I have written the code to add the years from term to the date taken to
generate the date renewal but do not have enough knowledge of Access or
programming to see how to retrieve the value from the term field based on the
selection made in the course drop down.

I do hope this all makes sense. I would appreciate any help you can offer.

Thank you in advance, Mollybot.

 

[Klatuu]

Are you not using a bound form? If you were, the Term value would be loaded
into the Term textbox when you select an existing record.

Me

 

[Mollybot]

Hi Klatuu,

The form is bound to a table which holds all four sets of data which
populate when the form loads, it's when I want to create a new record. The
table has a lookup field which refers to the Course, Term Table for the field
course. Once the course is selected I want to take the corresponding term
and add it to the form Term txt box for caculating the renewal date.

Thanks for your quick response.

Mollybot.

 

[Klatuu]

Okay, that makes sense. You can populate the text box in the After Update
event of your combo box.

If Me.NewRecord Then
Me.txtTerm= DLookUp("[Term]", "TermTable", "[Course] = '" & _
Me.cboCourse & "'"
End If

 

[Mollybot]

Thanks Klatuu that worked an absolute treat, cheers.

Mollybot

Okay, that makes sense. You can populate the text box in the After Update
event of your combo box.

If Me.NewRecord Then
Me.txtTerm= DLookUp("[Term]", "TermTable", "[Course] = '" & _
Me.cboCourse & "'"
End If

Hi Klatuu,

The form is bound to a table which holds all four sets of data which
populate when the form loads, it's when I want to create a new record. The
table has a lookup field which refers to the Course, Term Table for the field
course. Once the course is selected I want to take the corresponding term
and add it to the form Term txt box for caculating the renewal date.

Thanks for your quick response.

Mollybot.

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George