Pull data from one table to form, and save to another? 1

[Turb]

Hi!
Is this possible?
I need to be able to pull data, parts of a record (s), from one table into my form, enter other data on the form and save the total to another table altogether. I have tried queries, and macros, but am new to Access 97 and frankly am getting a little frustrated. Any help would be a boon.
Thank you. G. Hughes
Ind. Engineering Tech.

http://www.cdpowercom.com

 

[Dodgey]

When the user commits the save you need to add this code to the "onClick" event handler:

Dim sSQL as String

sSQL = "INSERT INTO TABLE_2(FIELD_1, FIELD_2, FIELD_3) "
sSQL = sSQL & "VALUES('"
sSQL = sSQL & ME.txtField_1 & "','"
sSQL = sSQL & ME.txtField_2 & "','"
sSQL = sSQL & ME.txtField_3 & "')"

docmd.setwarnings false
docmd.runsql sSQL
docmd.setwarnings true

Four things to keep in mind.
a) TABLE_2 corresponds to the new table. Where FIELD_1, etc, corresponds to the fields in your new table. More can be added as required. Just follow the example.
b) ME.txtField_1, etc, corresponds to the controls on the form, which contain either existing or new data. This ust be kept in the same order as the FIELD_1, FIELD_2, etc fields. Note that the values are all encompassed in the ' ' quotes. This would not apply to non alphanumeric fields.
3) The setwarnings will not allow Access to produce any confirmation messages which may allow for confusion on the user's behalf. This is optional.
4) This is not tested and is only one way to skin this cat.

If I've taken time to help, please help me by marking this as helpful.

 

[Turb]

Dodgey, thank you.
At present, I have a macro running on the "onClick" event handler; RunCommand:SaveRecord, RunCommand:QuickPrint, GoToRecord:Next. Can the code be incorporated into the macro? Or should I try an entirely different approach?
I have to save the form data and print a single copy of the record (in the form layout) for shipment with the unit.
Thanks again. G. Hughes
Ind. Engineering Tech.

http://www.cdpowercom.com

 

[Dodgey]

Encompass this code within a new function:

a) Select modules at database window.
b) Create a new module
c) Type in:

Public Function myFunctionName () as boolean

Dim sSQL as String

sSQL = "INSERT INTO TABLE_2(FIELD_1, FIELD_2, FIELD_3) "
sSQL = sSQL & "VALUES('"
sSQL = sSQL & Forms![myFormName]![txtField_1] & "','"
sSQL = sSQL & Forms![myFormName]![txtField_2] & "','"
sSQL = sSQL & Forms![myFormName]![txtField_3] & "')"

docmd.setwarnings false
docmd.runsql sSQL
docmd.setwarnings true

myFunctionName = true
End Function

d) Close and save it. Then in the macro you use, use the runcode action and where prompted for a function, type in myFunctionName.

NOTE: You have to explicitly specify the form name now. Modify the form name as required.

Good Luck!

If I've taken time to help, please help me by marking this as helpful.

 

[Turb]

Oops!
Now I get this error:

"The object doesn't contain the Automation object 'myFunctionName'. You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations."

Here's a snippet of the code:

Public Function myFunctionName() As Boolean
Dim sSQL As String
sSQL = "INSERT INTO MOTIVE TEST SHEET TABLE(TEST SHEET NUMBER, COMPANY) "
sSQL = sSQL & "VALUES('"
sSQL = sSQL & Forms![MOTIVE TEST SHEET FORM]![TEST SHEET NUMBER] & "','"
sSQL = sSQL & Forms![MOTIVE TEST SHEET FORM]![COMPANY] & "')"

'Test sheet number' and 'company' are two of text boxes on the form, but not in tab order. I named this module 'addToTable' and have the macro set for 'RunCode:myFunctionName'. Could it be the length of the code? I have 73 text boxes on the form.
Ind. Engineering Tech.

http://www.cdpowercom.com

 

[Turb]

Eureka!!
Got it.
Create a SQL query like the following example:

INSERT INTO Mytable (myfield1, myfield2)
VALUES (Forms!MyForm!myfield1, Forms!MyForm!myfield2);

and save it as MyQuery (or whatever).
Open MyForm in design view and set the BeforeUpdate property to [Event Procedure]; (or set one of the form's command button's OnClick property to [Event Procedure]).
In the event procedure, enter the following:

DoCmd.OpenQuery "MyQuery"

Thank you Dodgey, for putting me on the right track! Ind. Engineering Tech.

http://www.cdpowercom.com