473,326 Members | 2,081 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.

Automatically input next highest value

daniel aristidou
491 256MB
I have a database i use to file. when i insert a new record i want it to imediately insert the next value (file number).the problem is the value is acording to subject. eg
Subject1 has a record with a maxvalue of 5.
Subject 2 also Subject2 has a record with a maxvalue of 5
subject 3 may have a a record with a maxvalue of 6 etc.
thus it has to insert the next higest value according to subject.
I tried to do this by making a subform which had a record source updated by a combobox with the subject the subform had a txtbox which showed the highest value. The datafeild then took this value from the subform and added 1 to it.
This did not work any suggestions?

Tnx for any help
Tell me if im not clear on what my problem is and ill try explaining again.
Oct 14 '07 #1
9 3349
MMcCarthy
14,534 Expert Mod 8TB
Forget the form structure for the moment. The first thing we need to understand is your table structures.

Please include the MetaData for all relevant tables. Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Oct 14 '07 #2
daniel aristidou
491 256MB
Forget the form structure for the moment. The first thing we need to understand is your table structures.

Please include the MetaData for all relevant tables. Here is an example of how to post table MetaData :
Table Name=tblBookings
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
SubjectID AutoNumber PK
Subject Text

tblChapters
ChapterID AutoNumber PK
SubjectID Number FK
Chapter Text
DateStarted Date/Time
DateFinished Date/Time

tblNotes
NoteID AutoNumber PK
ChapterID Number FK
NoteName Text
DateCreated Date/Time
FolderPage Number
File Attachment

tblHomework
HomeworkID AutoNumber PK
SubjectID Number FK
DateIssued Date/Time
DateStarted Date/Time
DateFinished Date/Time
FolderPage Number
MarkReceived Number

Sorry i was a long time returning to my post.
So i want to automatically input the next highest value to the Folder page number.
The tables are all bound 1 to many to a table called subject.
i want to input the next highest value by subject.from the three tables.
The highest number will be added to one and be the default value for the folderpage feild in the forms notes and homework
the forms use comboboxes to select the subject and chapter
Thanks mmccarthy and anyone else who helps me reslove this.
Oct 29 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
OK I nearly have it resolved but I have a problem with your structure.

You cannot have FolderPage in tblNotes and tblHomework because one is related to Subject and the other to Chaper. The two don't relate.

If you have for instance a max of five chapter records for subject 1 then you would put the FolderPage number in tblChapters.

However, if you have a max of five notes and homework records for each subject then tblNotes and tblHomework will both have to be related to Chapters.

Otherwise we will have to distinguish between these two values and it gets a little more complicated. It can be done but I need to verify if the structure is correct first or should it be changed.

Mary
Oct 30 '07 #4
daniel aristidou
491 256MB
OK I nearly have it resolved but I have a problem with your structure.

You cannot have FolderPage in tblNotes and tblHomework because one is related to Subject and the other to Chaper. The two don't relate.

If you have for instance a max of five chapter records for subject 1 then you would put the FolderPage number in tblChapters.

However, if you have a max of five notes and homework records for each subject then tblNotes and tblHomework will both have to be related to Chapters.

Otherwise we will have to distinguish between these two values and it gets a little more complicated. It can be done but I need to verify if the structure is correct first or should it be changed.
Mary
Im extremely soory it seems i miss typed.
subject is linked to chapters - 1 to many
Chapters is linked to notes - 1 to many
Chapters is linked to homework 1- to many

Instead of subject ID FK in the table homework it was meant to be:
Chapter ID FK
Oct 30 '07 #5
MMcCarthy
14,534 Expert Mod 8TB
Im extremely soory it seems i miss typed.
subject is linked to chapters - 1 to many
Chapters is linked to notes - 1 to many
Chapters is linked to homework 1- to many

Instead of subject ID FK in the table homework it was meant to be:
Chapter ID FK
OK so if Subject1 is allowed 5 records ...

Is that 5 Chapters per subject

or

5 notes and 5 homework records
Oct 30 '07 #6
daniel aristidou
491 256MB
OK so if Subject1 is allowed 5 records ...

Is that 5 Chapters per subject

or

5 notes and 5 homework records
The subject1 is allowed any number of chapter records
And the chapters are allowed any number of homework and note records.

in case of confusion what i need is a way to put in the highest number +1 for the feild pagefolder automatically.
The problem is the pages are 1,2,3....ect acording to each subject.
Eg subject 1 has A CHAPTER1 AND A CHAPTER2
cHAPTER1 has 2 records using the values for foldernumber. "1" & "2"
Chapter2 has more records which automatically take the value of "3" ect

However
Subject 2 also needs records in chapter 1 with the value of "1" & "2"
annd the record in chapter2 needs to know it has to take the value of "3"
Do you understand my problem........?
Because the last question u asked me confused me.
Id also like to ask where the code would be placed? in a module? the feild textbox or default value?
Daniel

Thanks mary
Oct 30 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Don't worry about the code for the moment I'll deal with that when we hve the logic worked out.

The confusion is arising because I can't see your setup and the tables only tell me so much about how they are relating

You have two tables tied to chapters and I'm not sure how the folder page is supposed to work.

Okay so ...

Subject1 has two records in tblChapters called Chapter1 and Chapter2
Subject2 has three records in tblChapters called Chapter1, Chapter2 and Chapter3

Q1: Is Chapter1 for Subject1 and Chapter1 for Subject2 the same record?

Chapter1 has two records with folder page '1' and '2'

Q2: Where are these records? There are two tables tblNotes and tblHomework. If tblNotes has folder page '1' and '2' does that automatically imply that tblHomework has folder page '1' and '2'

Sorry for all the questions but I need to know how this is relating.

Mary
Oct 31 '07 #8
daniel aristidou
491 256MB

You have two tables tied to chapters and I'm not sure how the folder page is supposed to work.

Okay so ...

Subject1 has two records in tblChapters called Chapter1 and Chapter2
Subject2 has three records in tblChapters called Chapter1, Chapter2 and Chapter3

Q1: Is Chapter1 for Subject1 and Chapter1 for Subject2 the same record?

Chapter1 has two records with folder page '1' and '2'

Q2: Where are these records? There are two tables tblNotes and tblHomework. If tblNotes has folder page '1' and '2' does that automatically imply that tblHomework has folder page '1' and '2'



Mary
Answer to Question 1:[/u] No The subjects do not have the same chapters.
Imagine you are a teacher and you are filing you notes and homework for each subject.- each subject would have its own specific chapters.

I use upadating combo boxes in my forms for notes and homework useing SQL something like this :
Expand|Select|Wrap|Line Numbers
  1. Select chatpername from chapters where subjectidfk = [Subjectcombo]
Ps this is not the exact code.

Answer to Question 2:[u] There is one table for all of the notes and one for all of the homework.

Think like this if im filing my work will i have homework which is notes? Ans no
i will have many documents in the folder some of which are notes and some of which are homework. Thus no, it does not imply that there are records in both tables with the same folderpagenumber.In otherwords for each subject the folderpagenumber is unique.but cannot be demanded as unique due to the fact there are other subjects as well.

I hope i managed to explain this so you can understand it.
Thanks alot Daniel
Oct 31 '07 #9
MMcCarthy
14,534 Expert Mod 8TB
OK this is how the table structure should work. Change the names of the FolderPage number as below to distinguish the fact that they have different values and not to confuse them in code.

tblSubject
SubjectID AutoNumber PK
Subject Text

tblChapters
ChapterID AutoNumber PK
SubjectID Number FK
Chapter Text
DateStarted Date/Time
DateFinished Date/Time

tblNotes
NoteID AutoNumber PK
ChapterID Number FK
NoteName Text
DateCreated Date/Time
NotesFoldPage Number
File Attachment

tblHomework
HomeworkID AutoNumber PK
ChapterID Number FK
DateIssued Date/Time
DateStarted Date/Time
DateFinished Date/Time
HomeFoldPage Number
MarkReceived Number

Now set up the comboboxes as follows:

cboSubject with a row source of tblSubject
cboChapter should have a blank row source but make sure the row source type is set to "Table/Query"

In the after update event of cboSubject put the following code

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboSubject_AfterUpdate()
  2.  
  3.     With Me!cboChapter
  4.         If IsNull(Me!cboSubject) Then
  5.             .RowSource = ""
  6.         Else
  7.             .RowSource = "SELECT ChapterID, Chapter " & _
  8.             "FROM tblChapters " & _
  9.             "WHERE SubjectID=" & Me!cboSubject
  10.         End If
  11.         Call .Requery
  12.  
  13.     End With
  14.  
  15. End Sub
  16.  
cboChapters will now be populated with the chapters appropriate to the subject. Now after the user selects a Chapter you want to provide another check.

Now put the following in the after update event of cboChapter

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboChapter_AfterUpdate()
  2. Dim notesfpage As Integer
  3. Dim homefpage As Integer
  4.  
  5.     If IsNull(Me!cboChapter) Then
  6.         Msgbox "You have to choose a Chapter"
  7.     Else
  8.         ' this will get the max folder page number for the selected chapter
  9.         notesfpage = DMax("NotesFoldPage", "tblChapters", "[ChapterID]=" & Me!cboChapter)
  10.         homefpage = DMax("NotesFoldPage", "tblChapters", "[ChapterID]=" & Me!cboChapter)
  11.         ' add one for the next record
  12.         notesfpage = notesfpage + 1
  13.         homefpage = homefpage + 1
  14.     End If
  15.  
  16.     ' not sure what you want to do with the values when you get them
  17.  
  18. End Sub
  19.  
After all those questions :) I think this will give youwhat you want.

Mary
Oct 31 '07 #10

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

Similar topics

4
by: Mark | last post by:
good spam subject ;). anyway, i'm alittle stumped. i'm in need of putting together a query that gets the next highest salary ( select max ( sal ) - 1?, from an emp_sal type table. another...
22
by: Vincent | last post by:
I would like to develop a site that should be available in several languages, say English, French and German. My question is: how can I suggest browsers of visitors to display the correct language...
8
by: netsurfer | last post by:
Hi: Have a question on making the date automatically filled in by what the user enters in by the date at the top. The date entered at the top would most likely be on a Wednesday then I need...
11
by: Gary | last post by:
I'm using Access 2002. I have a field called Job No. It is a text (data type). The reason being that there is an "I" in front of each number i.e. I1234 I1235 etc The problem is that when...
7
by: Hulo | last post by:
In a C program I am required to enter three numbers (integers) e.g. 256 7 5 on execution of the program. C:\> 256 7 5 There should be spaces between the three numbers and on pressing "enter",...
13
by: Mary | last post by:
I'll pulling my hair out on this one and would be so appreciative of any help. I am creating a data entry form to enter results of a student survey. There are 40 questions on the survey. The...
10
by: strife | last post by:
Hi, This is a homework question. I will try to keep it minimal so not to have anyone do it for me. I am really just stuck on one small spot. I have to figure out the highest number from a users...
6
by: Wayne | last post by:
I'm using the following SQL statement to find the next highest autonumber value in a table where "CDUGActID is the autonumber field in the "CDUGActuals" table: SELECT CDUGActuals.CDUGActID,...
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
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.