How can I add data to two tables from one form?

[spherey]

I'll share my latest quandary with you; perhaps someone might have an elegant bit of code that would make this work out as easily as I think it ought to be!
In my database, I have a form which, of course, enters data into its corresponding table. What I want to happen is that, if a user chooses, some of the data from this form is also entered into a second table. Ideally I'd do this with an action button, although a check box with an After Update event procedure would work just as well.
For example, if Form A has six fields, three of which are duplicated in Table B, I want to have a button in Form A which, if clicked, would bring up a form or subform with the information which the user has just entered into those three duplicate fields in Form A, and automatically append this information to the matching fields Table B as well as to Table A. I tried using the wizard to build an action button, but 'Add Data to another Table' didn't seem to be an option. I tried creating a check box and writing a similar code in the after update event to the one above; I wrote

If [Add to Table B] Then
[Tables]![Table B]![Field 1] = [Field 1]
Else
[Tables]![Table B]![Field 1] = Null
End If

and that didn't work; I also tried mapping the field to be duplicated more exactly, like this:

If [Add to Table B] Then
[Tables]![Table B]![Field 1] = [Forms]![Form B`]![[Field 1]
Else
[Tables]![Table B]![Field 1] = Null
End If

and that didn't work either.
If only the "Enforce Referential Integrity" option had not only options for "Cascade Update" and "Cascade Delete" but "Cascade Create" as well, this would be easier!
Any suggestions?

Thanks a lot,

Spherey

 

[zishan619]

Well you can have a 2nd button which would go ahead and place it in another table. The code gies something like this:
Private Sub cmdSumbit_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableName&quot

With rst
.AddNew
!FieldintheTable = Me![field on the form]
!FieldintheTable = Me![field on the form]
So on....
.Update

End With

rst.Close
Set rst = Nothing

Set db = Nothing
End Sub
Hope this helps out
Thanks
Z

 

[spherey]

Hey Z -

Wow, that was fast! Thanks for the code! Where do I put it - in the After Update event, or somewhere else?

- Spherey

 

[Kherozen]

Well seeing [tt]Private Sub cmdSumbit_Click()[/tt]

mean that the code go under the button cmdSumbit and will be execute on the On Click Event


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[zishan619]

You create another button and place the code on the onclick event of that button. So that when the analyst clicks this button it will populate in the other table. Cool.
You can try in the afterUpdate Event for the form. But I do not know if it works there I never tried it out.
Let me know if you have a issue.
Thanks
Z

 

[spherey]

So, I created the button, and input the code in the onlick event. But when I click on the button to add the data in the form to the other table, I get this error:
Compile Error: Unexpected End Sub
And visual basic highlights the very first line of this code (Z, this is the code that you gave me - my only changes were the names of the tables and forms):

Private Sub Add_to_PARC_Button_Click()
Private Sub cmdSumbit_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PARC Table")

    With rst
        .AddNew
            ![Tables]![PARC Table]![Company Name] = Me![Company Name]
            ![Tables]![PARC Table]![Contact Information] = Me![Contact Information]
            ![Tables]![PARC Table]![Address] = Me![Address]
            ![Tables]![PARC Table]![City] = Me![City]
            ![Tables]![PARC Table]![State] = Me![State]
            ![Tables]![PARC Table]![Zip] = Me![Zip]
            .Update
        
    End With

rst.Close
    Set rst = Nothing
  
    Set db = Nothing
End Sub

Visual Basic inputs the first line and adds its own End Sub at the bottom; I thought that the problem might be that there were two lines which began the same way and each seemed to describe the button - but if I erase either one of those lines, the code doesn't seem to apply to the button any longer. It's still in the code builder window, but [Event Procedure] doesn't show up in the On Click event for the button.
Any suggestions? What am I doing wrong?

Thanks for your help,
Spherey

 

[Kherozen]

you had keep the : "[tt]Private Sub cmdSumbit_Click()[/tt]"
that is what cause the error
that should work
[tt]
Private Sub Add_to_PARC_Button_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("PARC Table&quot

With rst
.AddNew
![Tables]![PARC Table]![Company Name] = Me![Company Name]
![Tables]![PARC Table]![Contact Information] = Me![Contact Information]
![Tables]![PARC Table]![Address] = Me![Address]
![Tables]![PARC Table]![City] = Me![City]
![Tables]![PARC Table]![State] = Me![State]
![Tables]![PARC Table]![Zip] = Me![Zip]
.Update

End With

rst.Close
Set rst = Nothing

Set db = Nothing
End Sub


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[spherey]

I cut and pasted that code - and now I get this error message:
Complie Error: User-defined type not defined

When I click OK, Visual Basic is once again highlighting this line:

Private Sub Add_to_PARC_Button_Click()

- Spherey

 

[Kherozen]

the name of your button is Add_to_PARC_Button?
if yes that probably a problem with the reference
and that it doesn't recognize [tt]as database[/tt]

try typing (not copy/paste) the following line
Dim db as Database (if should pop-up a auto-complete as soon as you finish typing the "as" and if Database is not there that mean you are missing a reference)

to add it go to (in the Microsoft Visual basic Editor) tools..reference.. and select
Microsoft Data Access Objects 3.x Library (ie Microsoft DAO 3.6 Library here) and try the code again.


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[spherey]

Wow.
OK, you were right. And that took care of the first problem. 'Database' wasn't recognized, but once I added the DAO library, that took care of it - thank you!
BUT, now when I test the button, I get this error instead:

Run-time error '13'"
Type Mismatch

and Visual Basic highlights this line of the code:

Set rst = db.OpenRecordset("PARC Table")

I thought that that meant that there was probably a problem with the way I described "PARC Table," which is the table want the button to send the data to. And I thought that since you suggested to NOT cut and paste, and to type the code and look for a dialog box to pop up, that that's what I'd do here - and, you guessed it, I got a dialog box that pops up. When I type the open parenthesis ( , I get this:

OpenRecordset(Name As String, [type],[options],[LockEdit])as Recordset.

What do I need to do next? Do I need to describe the table differently?
Thanks for your help, and thanks for bearing with me on this -

Spherey

 

[Kherozen]

well I don't really know those thing so I'm guessing from here(never used the OpenRecordSet function)
but try something like that

set rst = db.OpenRecordSet("SELECT * FROM [PARC Table]&quot
or
set rst = db.OpenRecordSet("dbo.[PARC Table]&quot

but that's really ballpark guesses here

if that doesn't work look through the help of Access(highlight the word and press F1) or do some research in this forum you might find something




jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[spherey]

Thanks for your help, Kherozen.
The first idea:

set rst = db.OpenRecordSet("SELECT * FROM [PARC Table]")

gives me the same error message:
Run-time error '13'"
Type Mismatch

and the second idea

set rst = db.OpenRecordSet("dbo.[PARC Table]")

gives me this error instead:
Run-time error '3078':
The Microsoft Jet database engine cannot find the input table or query 'dbo.[PARC Table]'. Make sure it exists and that its name is spelled correctly.

It definitely exists, and that's how I've spelled the name of the table. I also tried adding [Tables]. in front of [PARC Table] in that example, and I still get the same error.

If anyone else wants to take a crack at this, please jump in - I think we're getting close. If this has come down to just a matter of naming conventions and making sure a specific table can be found and recognized, the solution has to be around the corner...

- Spherey

 

[zishan619]

Try this
dim strSQL as string
StrSQL = "SELECT * FROM [PARC Table]"
Set rst = CurrentDb.OpenRecordset(StrSQL, dbOpenSnapshot)

 

[spherey]

Thanks -

I tried that, so that the beginning of the code now looks like this:

Private Sub Add_to_PARC_Button_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Dim strSQL As String
strSQL = "SELECT * FROM [PARC Table]"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

Does it look OK up to that point?

But typing the code out that way and clicking the button gives me the same error message as before:

Run-time error '13'"
Type Mismatch

and Visual Basic highlights this line:

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

This is turning into quite a mystery. . .

 

[zishan619]

Ok
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
Replace with
Set rst = db.OpenRecordset(strSQL)
Cool
Z

 

[spherey]

Same thing: I click the button, get this message:
Run-time error '13'"
Type Mismatch
and Visual Basic highlights this line:

Set rst = db.OpenRecordset(strSQL)

 

[Kherozen]

can you past all the code please sure to be sure there's a subtle typo somewhere?
BTW I know that somewhere in this forum, there's some other thread about Error 13 you should look at them


jul ^_^
"Computer Science is no more about computers than astronomy is about telescopes"
E. W. Dijkstra.

 

[zishan619]

I got this to work:
Private Sub Add_to_PARC_Button_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Dim strSQL As String
strSQL = "SELECT * FROM [Family Fraud Daily]"
Set rst = db.OpenRecordset(strSQL)
With rst
.AddNew
!account = "45"
.Update
End With
rst.Close
Set rst = Nothing

Set db = Nothing

End Sub
Just change the table to your table and !account to your field in the table. "45" means whatever the field in your form is called but use me![formfieldname]...Cool

 

[OhioSteve]

Perhaps you should consider another approach.

This can all be done with SQL and the traditional Access interface. The only VB command you need is docmd...

In this example, the main table is called "sales". Occasionally, the user will want to copy a record to "special sales".

1) Add a logical field to the sales called isSpecial.
2) Add this field to the appropriate query and to the form itself.
3) Create an append query that copies records form sales to sales special. It has a parameter, isSpecial=true
4) Put a button on the form that says "copy to special sales".
5) In the onclick prop of button, kickoff your append query.

Obviously I left out some details. For example, the append query might need to grab objects on the form, depending on the timing.

 

[spherey]

Kherozen:
Were you asking me to paste the code? This is it:

Private Sub Add_to_PARC_Button_Click()
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Dim strSQL As String
strSQL = "SELECT * FROM [PARC Table]"
Set rst = db.OpenRecordset(strSQL)
With rst
    .AddNew
    ![Company Name] = Me![Company Name]
    ![Contact Information] = Me![Contact Information]
    ![Address] = Me![Address]
    ![City] = Me![City]
    ![State] = Me![State]
    ![Zip] = Me![Zip]
    .Update
    End With
rst.Close
    Set rst = Nothing
  
    Set db = Nothing
End Sub

And, no, there's no typo in there that I can see. The code is what zishan619 suggested, and the tables and fields are all spelled correctly. I'll look around and try to find the Error 13 thread you mentioned.


zishan619:
The last vesion of the code was the same as the one I'd been using - there weren't any changes to be made, other than seeing that I could drop the longer [Tables].[PARC 1 Table] format and just note the name of the field within the form (see above). Because there weren't any changes to make, the same thing happened: I clicked on the button, and got this message:
Run-time error '13'"
Type Mismatch
and Visual Basic highlights this line:

Set rst = db.OpenRecordset(strSQL)


OhioSteve: At this point, I'd consider just about any approach. Your suggestion looks good, and I'm game to try it, but I have to clarify a few things so I can do it correctly [and maybe avoid another 19 threads ;-) ]
* you mentioned that the only vb command I'd need in that example is docmd - how would I set that up? Where would I put it?
* In 1): is a logical field any different from a field?
* In my tables (the form is for the Prospect Table, and sometimes the users will want to copy a record to the PARC Table), the fields to be copied have the same names; for example, the data in the 'Company Name' field in the Prospect Form/Table is to be copied into the 'Company Name' field in the PARC Table if the button is clicked. Does that make a difference?