Tricky Visual Basic Code help...

[Guest]

I am trying to import data from a record on one table to another through
several forms. First, I have a query that grabs a set of records from the
source table based on a criteria the user enters and displays in a tabular
form. Then, I have a command button in the header of the form that opens a
second form, call it the submit form, with blank fields to allow the user to
enter data and not affect the source table. The entered data is stored to
records on the submit table.

What I am trying to accomplish is to import some fields of data from the
source table to the submit table based on the record the user clicks on in
the first form. Know the submit form opens with doCmd.OpenForm and there are
several options with this funciton. Is there code that can accomplish the
task?

 

[Guest]

Let me see If I understand what it is you are doing.
You have TableOne.
You have a form that allows you to select and display records from TableOne.
The user can modify the data in the record from TableOne, but you do not
want the changes applied to TableOne.
The modified record on the form will be written to TableTwo as a new record.

If the abov is correct, what do you do if the user selects a record from
TableOne and that record's primary key is already in TableTwo? Do you
overwrite the existing record in TableTwo, do you create a new record in
TableTwo (if so, what about the primary key?), or do you tell the user the
record is already in TableTwo?

If I have these answers, I can show you how to do this with only one form.

 

[Jeff L]

When you click your button, you could run a query that's based on the
source table and select the fields you want to put into the submit
table. Something like:

"Select Into SubmitTable(Field1, Field2, Field3) " & _
"Select Field1, Field2, Field3 " & _
"From SourceTable " & _
"Where ID = " & Me.ID

If you are selecting the entire record from Source then it would be
"Select Into SubmitTable " & _
"Select * " & _
"From SourceTable " & _
"Where ID = " & Me.ID

Hope that helps!

 

[Guest]

Thank you, this helps a lot.

 

[Guest]

I am having issues with the Me.ID part of the code you gave me. When I run
the query, I get the message

Compile Error
Invalid Use of Property

VB highlights the last part of that code.

Thanks for the help!

 

[Jeff L]

Is your syntax correct? You have to put
Docmd.RunSQL "Select Into SubmitTable(Field1, Field2, Field3) " & _
"Select Field1, Field2, Field3 " & _
"From SourceTable " & _
"Where ID = " & Me.ID

Also, Me.ID is the name of the field that makes your record unique
(your primary key). If you don't call it ID, then substitute the
correct name....Me.YourFieldName.

 

[Joshua.Buss]

I am trying to do something very similar actually, but I'm a complete
beginner to VBA and don't know exactly where to begin.

I have an access file that has two linked tables, which each link to
separate SQL databases using separate ODBC connections.

I have a form that has a view of table1 and a set of buttons for
manipulating the data in database1.

The tough part is the button that's going to go on this form which puts
moves the current record into a new record in table2 (database2).

Through extensive testing we found it's impossible to simply do this
with the built-in form builder tools - it only works if the tables are
local and not linked. However, queries which manipulate data between
linked tables work fine, which is what gives us the notion we can do
this with a little VBA code and a couple custom queries.

So far, the plan is the make the button do this:
1) Set a "pushFlag" column in the record in table1 to a specific
identifying value ("1" for now)
2) Runs an insert query to insert "all records with pushFlag = 1"
into table2. (this would create a copy of the record)
3) Sets the "status" field in the new record in table2 to a specific
value. ("done" for now)
4) Runs a delete query that first checks for duplicates (records that
exist in both the table1 and table2) and if a duplicate is found the
table1 record is removed. If a duplicate is not found, then the insert
must have failed for some reason. Instead of deleting the record the
user is notified that the insert failed, probably because of mal-formed
data in the record.

Does this sound remotely feasible? Would anyone be willing to help me
get this to work?

 

[Jeff L]

Sure it's feasible. Where are you stuck?

1) Set a "pushFlag" column in the record in table1 to a specific
identifying value ("1" for now)
Ok. Me.PushFlag = 1

2) Runs an insert query to insert "all records with pushFlag = 1" into
table2. (this would create a copy of the record)

Does Table2 have the same structure as Table1? If so, the query is
quite simple.
Docmd.Runsql "Insert into Table2 Select * from Table1 where pushFlag =
1;"

If not then the query changes a bit:
Docmd.Runsql "Insert into Table2(Field1, Field2, Field3, etc) " & _
Select Field1, Field2, Field3, etc From Table1 where pushFlag = 1;"

Make sure the number of fields from each table is the same.

3) Sets the "status" field in the new record in table2 to a specific
value. ("done" for now)
Docmd.Runsql "Update Table2 Set Status = "Done" where ID = " & Me.ID

4) Runs a delete query that first checks for duplicates (records that
exist in both the table1 and table2) and if a duplicate is found the
table1 record is removed. If a duplicate is not found, then the insert
must have failed for some reason. Instead of deleting the record the
user is notified that the insert failed, probably because of mal-formed
data in the record.

If the Insert fails, you would get an error message upon execution of
your code. Not sure the duplicate checking is really necessary.

Also, it looks like you are doing this for one record at a time. If
so, you don't really need the PushFlag field in your table. Just
identify your record via the unique Id field in your table (the primary
key). I demonstrated how to do that in #3 with the Me.ID

Hope that helps!