By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,033 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

A2K Field default value (SQL statement)

BradHodge
Expert 100+
P: 166
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.
May 14 '07 #1
Share this Question
Share on Google+
7 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
This question has been posted in the articles section. I am moving it to the Access forum.

ADMIN
May 14 '07 #2

NeoPa
Expert Mod 15k+
P: 31,347
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.
May 14 '07 #3

BradHodge
Expert 100+
P: 166
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.
May 14 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
May 14 '07 #5

NeoPa
Expert Mod 15k+
P: 31,347
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 :
Expand|Select|Wrap|Line Numbers
  1. Me.txtWeek = DLookup("[Week]", _
  2.                      "[tblWeek]", _
  3.                      Format(Me.txtDate,"\#mm/dd/yyyy\#")
  4.                      " Between [BeginDate] And [EndDate]")
May 14 '07 #6

BradHodge
Expert 100+
P: 166
You guys are awesome... THANKS!

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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblWeek.Week FROM tblWeek WHERE ((([Forms]![frmMAIN]![txtDate]) Between [BeginDate] And [EndDate])); 
  2.  
I then put the AfterUpdate event of txtDate to...
Expand|Select|Wrap|Line Numbers
  1. Me.txtWeek = DLookup ("[Week]", "qryWeek") 
  2.  
I don't know why I never think to use DLookup. Hopefully this little go-round will help stick it in my brain!

Brad.
May 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,347
That's a perfectly functional alternative Brad.
I'm pleased you got a solution you're comfortable with :)
May 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.