473,326 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

A2K Field default value (SQL statement)

BradHodge
166 Expert 100+
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
7 6443
MMcCarthy
14,534 Expert Mod 8TB
This question has been posted in the articles section. I am moving it to the Access forum.

ADMIN
May 14 '07 #2
NeoPa
32,556 Expert Mod 16PB
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
166 Expert 100+
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
166 Expert 100+
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
32,556 Expert Mod 16PB
That's a perfectly functional alternative Brad.
I'm pleased you got a solution you're comfortable with :)
May 15 '07 #8

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

Similar topics

20
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
2
by: JP SIngh | last post by:
Can someone please suggest the correct way to store the date & time in SQL Server? I want to store the date and time a record was created. What should the field type be in SQL Server? How can...
5
by: Colleyville Alan | last post by:
I have built a SQL statement that is trying to loop through the fields of a table that was built from a spreadsheet and hence is "short and fat". So rather than hard-coding, I have a loop from...
3
by: John young | last post by:
I have been looking for an answer to a problem and have found this group and hope you can assist . I have been re doing a data base I have made for a car club I am with and have been trying to...
25
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the...
18
by: Dixie | last post by:
Can I set the Format property in a date/time field in code? Can I set the Input Mask in a date/time field in code? Can I set the Format of a Yes/No field to Checkbox in code? I am working on...
3
by: Neil Zanella | last post by:
Hello, I am trying to execute ADO.NET INSERT statement where one of the fields is coming from a password HTML control. When I access the text with password.Value and print with Response.Write...
4
by: Dursun | last post by:
Hi, I am trying to assign NULL to a datetime field in the SQL Server database. Here is the code that does NOT work: INSERT INTO ... .... VALUES ... .... CType(IIf(dateWitness2Date.Checked,...
7
by: SHPsalm139 | last post by:
I'm working on a school application. I have a form where the user selects the marking period (1, 2, 3 or 4). That ties in to the "marking_period" field in a table. If the user indicates, for...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.