Adding multiple records into my access-2003 tables

[James Burton]

I would like to add records with preset data, if it is possible.
my tables with data as an example:
Name:Perodin, Sid: 05515, date:110425, effDate:110416, SO#:444-11, LikeOff:5, Reason:5th violation of smoking policy, Discp: $10fine, 5 hours study hall.

I would like to be able to put in a macro or form so that i input the SID# and offense number (and other data that changes), it auto pulls the name, adds today's date, and auto adds the x# violation and x# Discipline.

So if i entered 05515 and then 6, it would add the record like so:
Name:Perodin, Sid: 05515, date:110425, effDate:110416, SO#:444-11, LikeOff:6, Reason:6th violation of smoking policy, Discp: $10fine, 6 hours study hall.


what i am after is the ability to enter an offense type, then the student id and have it fill in where the info is the same.

example: for a smoking offense i would pull up smoking, be it a form or vba, enter the student id and other specific data, and have the for or vba fill in the rest. the "rest" that i am refering to is stuff like the offence type, the discipline and the codes we use to separate data.

[ADezii]

I strongly suggest that you read up on Database Normalization via the following Link,
http://bytes.com/topic/access/insigh...ble-structures
but if you insist on following this course of action, the following Code will point you in the right direction:

Dim strName As String
Dim strDate As String
Dim strEffDate As String
Dim strSONum As String
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblDemo", dbOpenDynaset, dbAppendOnly)
 
'At a bare minimum, need a Sid and Offense Number
If IsNull(Me![Sid]) Or IsNull(Me![LikeOff]) Then Exit Sub
 
If DCount("*", "tblDemo", "[Sid] = " & Me![Sid]) = 0 Then
  MsgBox "A SID of " & Me![Sid] & " does not exist in the Database!", vbExclamation, "No SID"
    Exit Sub
End If
 
strName = DLookup("[Name]", "tblDemo", "[Sid] = " & Me![Sid])
strDate = DLookup("[Date]", "tblDemo", "[Sid] = " & Me![Sid])
strEffDate = DLookup("[EffDate]", "tblDemo", "[Sid] = " & Me![Sid])
strSONum = DLookup("[SO#]", "tblDemo", "[Sid] = " & Me![Sid])
 
With rst
  .AddNew
    ![Name] = strName
    ![Sid] = Me![Sid]
    ![Date] = strDate
    ![EffDate] = strEffDate
    ![SO#] = strSONum
    ![LikeOff] = Me![LikeOff]
    'Let's avoid the whole Suffix issue
    ![Reason] = "Violation #" & CStr(Me![LikeOff]) & " of smoking policy"
    ![Discp] = "10$ fine, " & CStr(Me![LikeOff]) & " hours study hall"
  .Update
End With
 
rst.Close
Set rst = Nothing

[NeoPa]

I would just strongly suggest you design the database so that it's normalised (see link in previous post) and ensure that associated information is stored in an appropriate place and not repeated in an inappropriate one.

Default values are another matter (I don't think that's what you're describing here) and there is a .DefaultValue property to most controls to handle this where appropriate.

[James Burton]

i apologize if i am unclear, this is my first time trying to improve our data entry. our data base is over 10years old and i am limited in the changes i may or may-not make.

I would put in the SID, the offense#, the date of offense (so named in the table), the SO# is also entered by us, as it is the paper tracking number.
The stuff i want to automate is when i enter an offense, i need a standard statement entered into the table, so if a smoking violation, the reason field would have "# violation of tobacco violations", if was a cell phone violation, the reason would read "# violation of Cell Phone policy". there would be similar entry's for discipline. i am taking over a system that has been in place, and it requires you type everything in. i do not feel the repeat data needs to be typed. the tables report out to very specific and limited style.

for info, there are 2 databases, the first is student info. the second contains the records of all student offenses and awards. i can make some changes, i have put a relationship between the two so that while in the student DB, you can open 1 student and add his award/offense. if in the Off./award DB, you must enter all the student info into each record. this is very redundant and in my opinion, pointless, but i am forced to keep it.

I hope this clarifies the question and my situation.

[NeoPa]

Let me see if I can be clearer too. I see no reason why the comments that are repeated cannot be held in a separate table with an ID for each comment and the user can be allowed to select from this table in a ComboBox such that only the ID is stored in the updated record, but the reason is always shown.

[James Burton]

Thanks to you all for you help and time .

[NeoPa]

I hope that helped James :-)