Multiple field update

[Naterial]

I have the below code which allows a new Faculty member to be added to the table "MasterTable" it also copies the new Faculty member (by keyboard input) to the "Faculty" table. The problem is that I'd like it to also copy the new MasterID number generated in the MasterTable (Autonum) to the Faculty table. Any idea how?

Public Function Faculty()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable 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")

rst2ndTable.AddNew
rst2ndTable.Fields("Name") = strInput

rst2ndTable.Update
rst1stTable.Close
rst2ndTable.Close


End Function

 

[KevinM]

Assuming that the dest field in the Faculty table is NOT an AutoID...

This is based on two text boxes for First and Last name(txtFirstName & txtSurname) rather than an Input Box...

Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable As DAO.Recordset



Set db = CurrentDb

Set rst1stTable = db.OpenRecordset("MasterTable")

rst1stTable.AddNew
rst1stTable![FirstName]=txtFirstName
rst1stTable![Surname]=txtSurname
rst1stTable.Update


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

'Look up the LAST record added to the Master table
intID =DMax("[AutoIDFieldName]","MasterTable")

rst2ndTable.AddNew
rst2ndTable![IDFieldname]=intID
rst2stTable![FirstName]=txtFirstName
rst2stTable![Surname]=txtSurname
rst2ndTable.Update

rst1stTable.Close
rst2ndTable.Close

 

[Naterial]

Ah, it worked! Thanks! This problem has had me scratching my head for some time.

 

[Naterial]

Hmmn..just out of curiousity, would you know the answer to the following:

At the end of the below code, I toss in a generic "OpenForm" to open the Faculty form automatically. You wouldn't happen to know the code that opens the form automatically to the new record I just added, would you?


Public Function Faculty()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable 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"

 

[KevinM]

DoCmd.OpenForm "faculty", , , , acFormAdd

 

[Naterial]

Hmmn..by doing that, it brings up the form with all the fields blank and doesn't acknowledge the list of existing names. Do the number of commas reflect the total number of fields on the form? The navigation buttons only list one record (blank) even though there are records in the two tables and the new record is entered into the tables correctly. Does this sound more like a form problem or a code problem?

 

[KevinM]

Sorry I misunderstood...should be...

DoCmd.OpenForm "faculty", , , , acFormEdit
DoCmd.GoToRecord , , acLast

No, the commas represent different arguments to open the form.

 

[Naterial]

Hmmn..no, that just did the same thing as before. At first I thought it might be a compile problem, but no. Below I've copied the content of the code, could it be a previous error?

Public Function Faculty()

Dim strInput As String
Dim db As DAO.Database
Dim rst1stTable As DAO.Recordset
Dim rst2ndTable As DAO.Recordset
Dim rst3rdTable 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", , , , acFormAdd
DoCmd.GoToRecord , , acLast


End Function

 

[KevinM]

I edited my last post, try it again

 

[Naterial]

Ah, well that sort of worked. The records loaded in the form correctly, but it automatically went to the last record. To give you an example, I'm adding a test record with a name of "New, Name A." so it gets inserted in the middle of number of records (alphabetically). The edited command brings up the form to the last record (Zimmerman). Is there a command which will open the form to the most recent record?

 

[KevinM]

Then try this then............

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

 

[Naterial]

Hmmn..it seems to be having a compile error at the "&" for a possible missing expression. I wish I knew the quick-fix for this, but I fear my VB skills were taught to me back when VB III was still new.

 

[KevinM]

Not my day today

Syntax error...defo should be...

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

 

[Naterial]

Heh, don't worry. This database has been giving me headaches for three months now. Basically repairing a pretty sizeable .mdb fraught with errors and adding forms and a switchboard. Anyway, that code almost has it, when the Faculty form opens, the newly-entered name pops up all right, but it can't find any of the other records in the tables.

 

[KevinM]

The form is opening filtered to show one record.

You have to remove the filter (right click on form) to see all records.

 

[Naterial]

Well, the filter was my first thought. I disabled it by the properties box, but that didn't work. The right-click you suggested worked, though the form now seems to order everything by MasterID number, ascending, instead of in alphabetical order by name. I keep working with the properties of the form, thinking I can correct the problem on that end. As of yet, no luck. Incidently, would that right-click (Unfilter) be necessary every time the form is opened?

 

[KevinM]

The order will be whatever the form is based on.

Change this in the underlying recordset if you want a particular order.

If you want to open the form at the last ID entered, (which it does), then why do you want it automatically show all records too.

You seem to want to be doing too much all in one go.

 

[Naterial]

Ah, you know what? You're right. I'm trying to cram too much into this. Your previous suggestion will work well with my revised plan. Thanks so much!! I'm going to try to relax now and stop obsession over this.