Message box for finding records

[Naterial]

Hello, I have the below code to display message boxes (one box per type of record, Undergraduate, Grad student or faculty) to add a new record to a database. Basically, when I enter a name it inserts the new name in four separate tables and opens the corresponding form to enter the subsidiary information. My question is, what mods would be necessary to use this code to make a second module, one for only editing existing records. That is, instead of entering a new name to insert a new record, enter an existing name to open the form to that record? I was thinking the ".addNew" property would have to be immediately re-moved, but beyond that, I'm not sure. It's been some years since I took VB, (VB3 was the 'new' thing back then).

Public Function GradStudent()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable As DAO.Recordset
Dim rst4thTable As DAO.Recordset
Dim rst5thTable As DAO.Recordset


strInput = InputBox("Add New Graduate Student - (Last Name, First Name MI.)")

If IsNull(strInput) Or strInput = "" Then Exit Function

Set db = CurrentDb

Set rst1stTable = db.OpenRecordset("MasterTable")

rst1stTable.AddNew
rst1stTable.Fields("Name") = strInput
rst1stTable.Update
rst1stTable.Close

Set rst2ndTable = db.OpenRecordset("Student_Names2")
Dim intID As Long

intID = DMax("[MasterID]", "MasterTable")
rst2ndTable.AddNew
rst2ndTable.Fields("Name") = strInput
rst2ndTable![MasterID] = intID
rst2ndTable.Update
rst2ndTable.Close

Set rst3rdTable = db.OpenRecordset("Grad Student Academic History")

rst3rdTable.AddNew
rst3rdTable.Fields("NameID") = strInput
rst3rdTable![MasterID] = intID
rst3rdTable.Update
rst3rdTable.Close

Set rst4thTable = db.OpenRecordset("Employer")

rst4thTable.AddNew
rst4thTable.Fields("NameID") = strInput
rst4thTable![MasterID] = intID
rst4thTable.Update
rst4thTable.Close

Set rst5thTable = db.OpenRecordset("Information")

rst5thTable.AddNew
rst5thTable.Fields("NameID") = strInput
rst5thTable![MasterID] = intID
rst5thTable.Update
rst5thTable.Close

DoCmd.OpenForm "Graduate", , , "[MasterID] = " & intID, acFormEdit

End Function

Public Function Faculty()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset

strInput = InputBox("Add New Faculty Member - (Last Name, First Name MI.)")

If IsNull(strInput) Or strInput = "" Then Exit Function

Set db = CurrentDb

Set rst1stTable = db.OpenRecordset("MasterTable")

rst1stTable.AddNew
rst1stTable.Fields("Name") = strInput
rst1stTable.Update


Set rst2ndTable = db.OpenRecordset("Faculty")
Dim intID As Long

intID = DMax("[MasterID]", "MasterTable")

rst2ndTable.AddNew
rst2ndTable![MasterID] = intID
rst2ndTable.Fields("Name") = strInput

rst2ndTable.Update
rst1stTable.Close
rst2ndTable.Close

DoCmd.OpenForm "Faculty", , , "[MasterID] = " & intID, acFormEdit


End Function


Public Function BA()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable As DAO.Recordset
Dim rst4thTable As DAO.Recordset

strInput = InputBox("Add New Undergraduate Student - (Last Name, First Name MI.)")

If IsNull(strInput) Or strInput = "" Then Exit Function

Set db = CurrentDb

Set rst1stTable = db.OpenRecordset("MasterTable")

rst1stTable.AddNew
rst1stTable.Fields("Name") = strInput
rst1stTable.Update
rst1stTable.Close

Set rst2ndTable = db.OpenRecordset("Student_Names2")
Dim intID As Long

intID = DMax("[MasterID]", "MasterTable")

rst2ndTable.AddNew
rst2ndTable.Fields("Name") = strInput
rst2ndTable![MasterID] = intID
rst2ndTable.Update
rst2ndTable.Close

Set rst3rdTable = db.OpenRecordset("BA")

rst3rdTable.AddNew
rst3rdTable.Fields("NameID") = strInput
rst3rdTable![MasterID] = intID
rst3rdTable.Update
rst3rdTable.Close

Set rst4thTable = db.OpenRecordset("Employer")

rst4thTable.AddNew
rst4thTable.Fields("NameID") = strInput
rst4thTable![MasterID] = intID
rst4thTable.Update
rst4thTable.Close

DoCmd.OpenForm "Undergraduate", , , "[MasterID] = " & intID, acFormEdit

End Function

 

[Rob.Mills]

All you'd have to do is change .AddNew to .Edit

I would suggest using a combobox to select the record.

 

[Naterial]

Hmmn..I tried swapping .addNew and .Edit in one of my backup copies, but I get a run-time error telling me that it would create duplicat values. Is this because the .Update commands are still present?

 

[Rob.Mills]

You know I jumped the gun a little on my suggestion. You will need to add a feature to find the record first.

rst.FindFirst "[ID Field]=" & combobox value


Then run the edit.

 

[Rob.Mills]

You'll always need the update commands whenever you change data in a recordset be it adding or editing.

 

[Naterial]

Would that be just once at the beginning of each function, or before the reference to each table listed?

 

[Rich]

Why are you storing duplicate data?

 

[Naterial]

Rich: the explanation is actually due to the request of my supervisors (University types with pre-set ideas as to how a database should be set up). I just conform to their wishes. It took a -lot- of lobbying on my part to get the normalization down to just -this- level. You wouldn't have wanted to see their plans before-hand! Eep.

 

[Rich]

Isn't it incredible, these are the people supposedly responsible for our families education
You have my sympathy

 

[Rob.Mills]

You'd have to find the record for each recordset

 

[Naterial]

Well, once again, they're leaning toward have an InputBox whereby the name is entered and the form is then loaded to that record, (not using a ComboBox, though that would make an interesting addendum). As a matter of fact, I tried it first using a ComboBox first, but the compiler didn't like it. It was odd it didn't even seem to recognize the word, "ComboBox".

 

[Rob.Mills]

Sorry Naterial... a was a little brief on my instruction again.

Change what's in red to meet your database names:

rst.FindFirst "[ID Field]=" & Forms!FormName!ComboboxName.value

 

[Naterial]

Hmmn..you know what? I neglected to mention that when the form itself is opened, (the requisite Graduate, Faculty or Undergraduate forms) the tables referenced by them are already set up for editing. So basically I just need the message box to open the form to that record. Would the code as listed below (I'm sure there are errors) accomplish more to what I'm asking? I'm starting to think the other code may be redundant.

Public Function GradEdit()

Dim strInput As String
Dim db As DAO.Database

strInput = InputBox("Edit Graduate Students - (Last Name, First Name MI.)")

If IsNull(strInput) Or strInput = "" Then Exit Function

Set db = CurrentDb

rst.FindFirst "[ID Field]=" & Forms!Graduate!InputBox.value

DoCmd.OpenForm "Graduate", , , "[MasterID] = " & intID, acFormEdit

End Function