use combo selection to autofill dates

[SpindleMania]

mornin all,

i have a databse with 2 tables

ticketsales and T_semester

semester contains 3 columns:

ID (primary Key)
Semester (description shown in combo drop down box)
Start Date
end date

ticketsales is a customer database of sales along with other fields it also has the semester (lookup combo to the T_semester), start date and end date fields.

i have a form created for the ticketsales with the drop down box for semester field, what i can't work out is how autofill the start and end date fields according to the date listed in the T_semester.

e.g

T_semester table contains:

id, semester, start date, end date

1, Autumn 2007, 24/09/2007, 14/12/2007
2, Spring 2008, 07/01/2008, 14/03/2008

in the form if i pick Autumn 2007 in the semester drop down box i would like it to automatically fill the start date with 24/09/2007 and the end date with 14/12/2007 according to what is on the same row as what is selected from the semster field.

sorry if i make this out to be complicated i am not very good at explinations

thanks for you help in advance

 

[statsman]

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0 (zero) in the left most column and increases by 1 for each column you move to the right. The third column from the left would therefore be column 2.

Each column you wish to copy when you select a record in the combo box needs its own line of code.

When you created the combo box using the wizard, you had the option of saving one of the columns into a field at that time. If you did this, you don't have to use the code to save that column, it’s already done.

 

[SpindleMania]

date range

thanks statsman,

that works a charm but the years are wrong in the table i have

start date 24/09/2007
end date 14/12/2007

but in the form it puts:

start date 31/12/1899
end date 31/12/1899

any ideas?

before i forget i can't put the column (#) reference in because it says compile error and says it expects an )

my exact code is:


Private Sub semester_BeforeUpdate(Cancel As Integer)

[ticketStartDate] = ([semester])

[ticketEndDate] = ([semester])

End Sub

 

[statsman]

If the StartDate is Column 2 in your combo box the code should read:

[ticketStartDate]=([Combo55],2)

Combo55 would be replaced with whatever the Combo Box number is on your form.

 

[SpindleMania]

for some reason it is not allowing me to add the ,2 please see attached for a screen shot of the error,

thanks for your help i appreciate it

 

[statsman]

Your code

[ticketStartDate]=([semester])

is incorrect. You are making no reference to the Combo Box which holds the data you want to place into [semester].

Read my previous post.

 

[SpindleMania]

my fault sorry, i renamed the combo box to semester, i think that is where i went wrong.

i remade with a new combo box and it now works fine

thanks for all your help