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

Assigning a fixed value to a group of records

P: 18
Hi Everybody,
I am creating a database that have one table that stores the records retrieved from an update query. In that table I have a field Batch. I want to assign a Batch No.based on the current date to the set of records through a form. And that value should get incremented by 1 when the date changes. And all these updations are to be made through a form. I am afraid if I have phrased my question correctoly. Kindly help. Thanks
Mar 21 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,342
Your question makes sense, but it is a bit short of detail.
As a general idea though, if you know the number you want to set as the batch number, and you know which set of records you want to assign it to, then the best way of handling this is to create and run an update query.
Running an update query that you create on the fly is not too hard from a form. The VBA code behind the form creates a SQL string which is actually a command with all the necessary details of a query. This string can then be submitted for execution and that's all.
Assume your table is called [tblXmpl] and you want to set the field [BatchNo] to 53 for all records where the [EntryDate] (another field in [tblXmpl]) is today, then you'd use something like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command1_Click()
  2.     Dim strSQL As String
  3.  
  4.     strSQL = "UPDATE [tblXmpl] " & _
  5.              "SET [BatchNo]=53 " & _
  6.              "WHERE [EntryDate]=Date()"
  7.     Call DoCmd.RunSQL(strSQL)
  8. End Sub
This is a very simple version without any variables or literals running behind a simple button on a form. What you need may be a little more complicated but we won't know until you tell us more details.
Mar 23 '07 #2

Post your reply

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