Sharing field values from one table to another

[tuccokeith]

I have two tables:

Table 1 (Project) contains Project ID, Project Name, Project Description, Start Date, End Date, Change Indicator, Requestor

Table 2 (Change) contains Change ID,Project ID, Project Name, Change Reason, Change Date, Change Requestor.

I have a form that opens when The Change indicator (Yes/No) is set to True using VBA.

However, I want the Project ID, and Project Name in the Change Table(Table 2) to be automatically filled from the Project Table (Table 1) when the Change Indicator is set to "True".

Can I do this without using a query to link the two tables? Is there a way to use VBA logic to pull the fields from Table 1 to Table 2?

Thanks

 

[psemianonymous]

Usually this is handled by directly copying what is in a control on form1 to a control on form2. Control just means "textbox or similar item". Assuming you have a textbox (or whatever) for your Project ID and Project Name on the original form and also on the second form, you can copy the values over in two statements like so:

Forms![SECOND_FORM]![SECOND_PROJECT ID TEXTBOX] = Forms![NAME OF YOUR FIRST FORM HERE]![NAME OF PROJECT ID TEXTBOX HERE]

Obviously you need to change the names, but that (above) is basically how it can be done.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[tuccokeith]

Hello,

How can I save the results of the form copying the two fields from Table 1 to Table 2 into a revised Table2.

Is there an update table function in VBA that I can use to update the table2 (Change)

 

[psemianonymous]

I have a form that opens when The Change indicator (Yes/No) is set to True using VBA.


I don't understand what you're doing. This quote above indicates you are opening a form that directly edits table2. Thus, if you open up the form, go to a new record, and "write" a value to the Project ID textbox and Name textbox, you have effectively "written" to table2.


Directly writing to table2 involves an SQL statement:
"INSERT INTO TABLE2 VALUES (" & Forms!etc!ProjectIDControlName & ",'" & Forms!etc!etcProjNameControlName & "')"


but I don't think that's what you really want. I think I had it right--open a form, goto a new record, and plop in the new values. Let the user fill in the rest, and if they cancel the change request, so be it. If they properly fill in the info via the form, you can then save the data to table2.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm