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

Automatically input next highest value

daniel aristidou
100+
P: 491
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
Share this Question
Share on Google+
9 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
100+
P: 491
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
Expert Mod 10K+
P: 14,534
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
100+
P: 491
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
Expert Mod 10K+
P: 14,534
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
100+
P: 491
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
Expert Mod 10K+
P: 14,534
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
100+
P: 491

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
Expert Mod 10K+
P: 14,534
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

Post your reply

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