A2K Field default value (SQL statement)

This question has been posted in the articles section. I am moving it to the Access forum.

ADMIN

I have a txtbox (txtWeek) on a form (frmMAIN) that needs to have a value default in after another field (txtDate) is updated. I have a table set up to show the beginning and ending dates of each week (tblWeek).

I had this as the AfterUpdate expression on txtDate...

Expand|Select|Wrap|Line Numbers

1. Me.Week= ("SELECT tblWeek.Week FROM tblWeek WHERE ((([Forms]![frmMAIN]![txtDate]) Between [tblWeek].[BeginDate] And [tblWeek].[EndDate]));")

I am getting the "too few parameters expected 1" error.

Any thoughts?

Thanks,
Brad.

1. The field is txtWeek not Week.
2. Setting the value to a SQL string will not execute it.

The field on frmMAIN is "txtWeek", but it's control is "Week" from tblMAIN. The SQL code I referenced is trying to pull in information from tblWeek, which also has a field called "Week".

Indeed... when I use strSQL on the AfterUpdate event of txtDate, it doesn't give me the "too few parameters expected 1" error, but it also doesn't put anything into the field.

Thanks,
Brad.

The field on frmMAIN is "txtWeek", but it's control is "Week" from tblMAIN. The SQL code I referenced is trying to pull in information from tblWeek, which also has a field called "Week".

Indeed... when I use strSQL on the AfterUpdate event of txtDate, it doesn't give me the "too few parameters expected 1" error, but it also doesn't put anything into the field.

Thanks,
Brad.

You are mixing up control source and control name. The source might be Week but the name is txtWeek. When you use Me you are refering to the form and Me.txtWeek refers to the control named txtWeek on the form.

However, you have another problem in that you are trying to get the value from a select statement. You can't do this. You will need to use a DLookup function.

The field on frmMAIN is "txtWeek", but it's control is "Week" from tblMAIN. The SQL code I referenced is trying to pull in information from tblWeek, which also has a field called "Week".

Indeed... when I use strSQL on the AfterUpdate event of txtDate, it doesn't give me the "too few parameters expected 1" error, but it also doesn't put anything into the field.

Thanks,
Brad.

Try using DLookup(). Something like :

You guys are awesome... THANKS!

I ended up making a query (qryWeek) with this makeup...

I then put the AfterUpdate event of txtDate to...
I don't know why I never think to use DLookup. Hopefully this little go-round will help stick it in my brain!

Brad.

That's a perfectly functional alternative Brad.
I'm pleased you got a solution you're comfortable with :)