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... - 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.
7 6443
This question has been posted in the articles section. I am moving it to the Access forum.
ADMIN
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... - 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.
- The field is txtWeek not Week.
- 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.
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 : - Me.txtWeek = DLookup("[Week]", _
-
"[tblWeek]", _
-
Format(Me.txtDate,"\#mm/dd/yyyy\#")
-
" Between [BeginDate] And [EndDate]")
You guys are awesome... THANKS!
I ended up making a query (qryWeek) with this makeup... -
SELECT tblWeek.Week FROM tblWeek WHERE ((([Forms]![frmMAIN]![txtDate]) Between [BeginDate] And [EndDate]));
-
I then put the AfterUpdate event of txtDate to... -
Me.txtWeek = DLookup ("[Week]", "qryWeek")
-
I don't know why I never think to use DLookup. Hopefully this little go-round will help stick it in my brain!
Brad.
NeoPa 32,556
Expert Mod 16PB
That's a perfectly functional alternative Brad.
I'm pleased you got a solution you're comfortable with :)
Sign in to post your reply or Sign up for a free account.
Similar topics
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?
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |