Add record to two tables from form

Naterial

Registered User.
Local time
Today, 04:43
Joined
Mar 7, 2003
Messages
36
Hello, I was hoping someone could tell me a way to perform what may or may not be a simple problem:

I'd like to build a small form with one text box and one button (Add New Record) which will take the information in the text box and append it to a field in two different tables within the same database.

In this case, it's a Name field I want to be able to append to the Name field in my Master table, as well to another table, (in this case, 'Faculty') so that I'm in effect creating a new record.

Is there a VB code source for this? I've checked many examples on-line.
 
You could try setting up two append queries. Don't add a table into the query window. In the first column type Forms!FormName!txtboxName. Have that append to the field you want.

Then have code to execute both queries.
 
I'm not sure I understand, in what column should that be placed? I've never coded an append query.
 
Never mind that idea. I've never tried it and when I did I realized you couldn't do it the way I suggested it.

Alright, here's another idea. Know much about using recordsets in memory?

You won't even need to create a form for this.

Public Function fFunctionName()

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

strInput=InputBox("Message to give user.")

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

Set db=CurrentDb

Set rst1stTable=db.Openrecordset("[Name of 1st table]")

rst1stTable.Addnew
rst1stTable.Fields("[FieldName]")=strInput
rst1stTable.Update
rst1stTable.Close

Set rst2ndTable=db.Openrecordset("[Name of 2nd table]")

rst2ndTable.Addnew
rst2ndTable.Fields("[FieldName]")=strInput
rst2ndTable.Update
rst2ndTable.Close
 
Hmmn..so it would create a pop-up input box? That's a good idea. As for my experience with recordsets, no, I'm afraid I don't. I have a background in Visual Basic, (though unfortunately it was back when VBIII was still new) and I thought there might be a good VB solution for this. To give you an idea of the overview of this database, I've built a series of forms and I'm hoping to navigate through them with a switchboard and and I was planning on a button to open the form I'd planned with just the before-mentioned text box and button. In the end, there'd be three like it, each copying the entered record 'Name' into the master table, as well as the relevant other table, (faculty, undergraduate, graduate). I'm guessing that (since my other forms are all correct and working) that my next step would be to create my switchboard and code the button (as a function?) for entering a new name, with a modified version of the code you supplied, thereby bringing up the pop-up box. Is that correct?
 
Ahhh... Now this might help you in getting a field for how versatile VBA is. Instead of setting up three different pop-up forms for each table you can set this function up to be used over and over.

This is how. You've got one Main table that will always have the entry entered and then three variable tables.

Set this function up in a module.

Public Function fFunctionName(strTableName As String)

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

strInput=InputBox("Message to give user.")

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

Set db=CurrentDb

Set rst1stTable=db.Openrecordset("[Name of Main table]")

rst1stTable.Addnew
rst1stTable.Fields("[FieldName]")=strInput
rst1stTable.Update
rst1stTable.Close

Set rst2ndTable=db.Openrecordset(strTableName)

rst2ndTable.Addnew
rst2ndTable.Fields("[FieldName]")=strInput
rst2ndTable.Update
rst2ndTable.Close

End Function

Now call this function from the buttons by typing this in it's On Click event

=fFunctionName("[Variable table name]")

This will save you a lot of time.
 
Hmmn..well, at first your solution worked. But I'd forgotten that I'd also have to add a record to two tables on just the first pop-up, (Master, Faculty) but three tables on the other two pop-ups, (Master, Grad, Employer and Master, BA, Employer). I know how to set up the code to add another table, but I'd like to set up three different functions (I don't mind the extra code) that would be called by individual buttons on the switchboard.

Here's an example of what I was thinking:

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

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("Grad Student Academic History")

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

Set rst3rdTable = db.OpenRecordset("Employer")

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

End Function

Now, assuming the above works as it's supposed to, how do I call it from the OnClick event for the button? I've been trying everything I can think of but it can't find it. And does that code appear correct?
 
Code looks good. The way to call it from On Click is this:

=GradStudent()


Now will other people be using this database? If so, you need to look into adding a custom error handler. Otherwise, the user might get themselves into your code accidentally if an error occurs.
 
Well yes, other people will be using the database, but only a select few. Hmmn..I must be saving these modules wrong. It can't find them when I click the test button. (I set up a test form with buttons to activate the modules to see if they work before re-building my switchboard). Should I save the module as the same name as the function?
 
You don't need to give it the same name as the function. Is this a module of a form. If so, then it doesn't matter if the function is public/private. Only the form can itself can call it. A regular module that has public functions can be called from anywhere.

Also, just for the future, you don't need to setup a test form necessarily. Learn how to use the debug window to test your functions.
 
Hmmmn..it can't seem to find it even with a different module name. (I called it Module2)

The onClick property (on the button of the test form) is set to =GradStudent with the code for the function as follows:

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

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("Grad Student Academic History")

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

Set rst3rdTable = db.OpenRecordset("Employer")

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

End Function

Any idea why it can't find it? As for this being a module of a form, in a way it is, but it's not really associated directly with a form. Does it need to be?
 
Gotta have the parentheses after the function where you're calling it.
 
I've tried with with the paranthesis, as well as without. I'm baffled. Is there a syntax where I can do onClick =NameOfModule(NameOfFunction) to reference it explicitly?
 
Ah, never mind, I think I've got it. I'm just using the default module name, (Module1) and putting multiple functions in it. Thanks for all your help!!
 

Users who are viewing this thread

Back
Top Bottom