473,698 Members | 2,152 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

incrementing a field on a continuous form

43 New Member
Hi - I have a field called Sequence on a continuous subform. The Sequence field belongs to a table called Project_Draw. This table has many 'Draw' records for each 'Project' record. So then the subform is tied to the main form by Project_ID.

I want the Sequence field to automatically increment each time I add a new record. I can't use the autonumber as I need to start a new sequence for each new Project_ID.

I've tried putting a max([Sequence]) in the subform's footer and that gives me the max sequence used so far. Then on the Current Record event, I've tried referencing this and adding 1 to it and putting that result in the Sequence field. That works fine if I've come to the record for the first time, but if I'm entering one record after another, the max([Sequence]) field in the footer doesn't seem to refresh in time, so I don't get an incremental value. I've also tried requerying the max field, but again with no luck.

I'm sure there must be an easier way to do this, but I can't figure it out.

Thanks in advance for your help.

Bill
Oct 14 '09 #1
3 4668
NeoPa
32,569 Recognized Expert Moderator MVP
Try using DMax() instead Bill. Let us know if you need more precise assistance (It sounds like you're pretty well up on the concepts actually).
Oct 15 '09 #2
bkberg05
43 New Member
Hey NeoPa and thanks. I'm pretty bad with syntax. I tried the dmax, but am not getting the syntax right.

The first argument is the field name, which I put in brackets as [Sequence]

The second argument is the domain, which I took to be the table. The table is called Project_Draws, but I don't know how to do the syntax for this.

The third argument is the condition. I thought it would have to be the maximum sequence where the Project_ID of the subform was equal to the Project_ID in the Project_Draws table. But again, I'm not sure how to write that.

Also, am curious why dmax might work where max wouldn't.

Thanks,
Bill
Oct 16 '09 #3
NeoPa
32,569 Recognized Expert Moderator MVP
DMax() stands for Domain Max. It's a Domain Aggregate function. You can specify the domain (including the criteria) within which it is aggregating. Max() simply aggregates across the domain already specified. It's used in various different situations and always refers to that situation exclusively. On a form it refers to the Max found within a specified control (not field I believe) on that form. It can be referenced in a control formula, but I'm not sure it can be within the VBA.

I know you have a table :
Table=[Project_Draw]
Expand|Select|Wrap|Line Numbers
  1. Field       Type    Index
  2. Project_ID  Number  Composite PK
  3. Sequence    Number  Composite PK
  4. ...
I assume you also have a controls called [Project_ID] and [Sequence] on the form in your SubForm control. Read that last bit carefully. It is how sub-forms work. The main form has a SubForm control, within which is held a form. I suggest for your form's (that is the form within the SubForm control) AfterUpdate event some code similar to the following :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.     With Me
  3.         .Sequence.Default = DMax(Expr:="[Sequence]", _
  4.                                  Domain:="[Project_Draw]", _
  5.                                  Criteria:="[Project_ID]=" & .Project_ID) + 1
  6.     End With
  7. End Sub
Oct 16 '09 #4

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

Similar topics

25
10241
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 data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
15
2286
by: Paul T. Rong | last post by:
Hi everybody, This time a very very difficult case: I have a table "tblStudent", there are 50 students, I also made a form "frmStudent" based on "tblStudent", now if I don't want to show all the students in a classical (vertically, from top to bottom) way, but make them "sitting" in 50 text boxes, 5 rows 10 columns, how to do it? Thanks in advance.
2
3263
by: ThompsonJessical | last post by:
I am trying to use a button with the vba coding for SendObject Function to email a specific report based on the value of another field of the current record. The form is a continuous form and the values are either of the field are "IACUC" or "IBC". If the value is IACUC, I want the IACUC report to send, if IBC the IBC report to send. The email button is located on the continous form details with the record specified and report is...
5
1573
by: James | last post by:
I have a Continuous form in Access 2000. The form contains a date field and the user has requested( demanded nicely) that they want the date field to be yellow if there is no data in the field. Does anyone know how to do this?
9
9692
by: Ecohouse | last post by:
I have a main form with two subforms. The first subform has the child link to the main form identity key. subform1 - Master Field: SK Child Field: TrainingMasterSK The second subform has a master-child link to the first subform. subform2 - Master Field: subTrainingModule.Form!TrainingModuleTopicSK Child Field: TrainingModuleTopicSK
1
1466
by: Paul Glavey | last post by:
Hello anyone any idea how to do this. The problem I am having has to do with Continuous Forms. In the header of the form I have 4 Combo boxes. For now lets just work on one combo box. The combo box name is 'PYID'. The table name is 'Invoice' and the field name is 'PYID' and that field is column 3. Thats if in access columns start at 0. OK What I want to do is this, when a user selects a value from PYID combo box in header it will be the...
4
5537
by: Susan Bricker | last post by:
I have a form that displays record information in Continuous Record display (scrollable list). One of the fields in the record is an Integer value called "rcode" (reason code). But, I don't want to display the reason code integer value. I want to display the reason code descriptive text (string data). So I have a TEXT BOX object on the form to display the text. I have an public array defined(global variable defined in CommonProcs...
1
3509
by: asandiego | last post by:
Hey guys, this is my first post here but have been checking this site a lot for anything I need. I hope someone can lead me to what I should do or just an idea to what can be done. What I'm trying to do: I have a Form that has a subform. My primary form has a field that autonumber and this field has a one to many relationship to a field in my subform. The subform has another field that I want to AutoNumber or increment number BUT has to go...
1
5344
by: MikiEns | last post by:
Hi all, Please excuse me if this is incorrect I am new to MS access and this Forum and appreciate any advice on posting. I would Like the Tab key on the key board to take the cursor down through a continuous form then across (am happy to do the across bit manually) and continue down the next field. I have looked all over the internet for answers but found only turning the "tab stop" to off. This only works when you only want to go down one...
0
8676
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8608
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9164
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8898
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7734
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4619
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3051
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2332
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.