Inserting Data into Existing Record

[Gary Dolliver]

I have 2 forms and 1 table. form2 is called upon in form1 and opens in a
seperate window, each reference fields in table1. I would like form2 to
capture data and insert into the record initially created on form1 when
closed (form1 will always be open). The below SQL is used on the button
click to close form2, but I am getting syntax errors:

DoCmd.RunSQL "UPDATE Orders " & _
"SET Orders (BillTo_FName) =
(Forms![RECEIVING_MAIN_Billing_Info]![BillTo_FName]) " & _
"WHERE Orders (Order_ID) = (Forms![RECEIVING_MAIN_Check_In]![Order_ID]) "
DoCmd.Close

I am noticing that the actual record does not seem to be populated into the
table until after I close form1 in frustration, which I believe is the reason
I cannot get this to work. However, when trying to use SQL with the INSERT
INTO command, I also do not have any luck. Any thoughts? Thanks!

 

[Jeff Boyce]

Gary

How are your forms related to your table? Why do you have 2 forms?

You've described "how" you are trying to do something, but I don't have a
clear picture of "what" you are trying to do, nor why.

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

 

[Gary Dolliver]

Thanks Jeff, and sorry for the lack of info - perhaps you can shed some light
on making this a little easier for me...

Basically, form1 is a general receiving screen (address info and generic
product info) and form2 is to gather billing information (shipping
information is collected on form1). I do not have this information on form1
as space is limited and probably 9 out of 10 times it will not be relavent.
Therefore, I thought a seperate form, activated by a check box on form1
(yes/no - yes opens form, no duplicates shipping info into bill to fields),
would be able to accomodate this. Ideally, I would like to have all of the
information in one record in table1 (I know I could do this if I seperated
the tables, billing v. shipping, but what would be the fun in that?)

I am using a field Order_ID from table1 to try and keep everything together

Hope that helps, thanks!
-gary

Gary

How are your forms related to your table? Why do you have 2 forms?

You've described "how" you are trying to do something, but I don't have a
clear picture of "what" you are trying to do, nor why.

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

I have 2 forms and 1 table. form2 is called upon in form1 and opens in a
seperate window, each reference fields in table1. I would like form2 to
capture data and insert into the record initially created on form1 when
closed (form1 will always be open). The below SQL is used on the button
click to close form2, but I am getting syntax errors:

DoCmd.RunSQL "UPDATE Orders " & _
"SET Orders (BillTo_FName) =
(Forms![RECEIVING_MAIN_Billing_Info]![BillTo_FName]) " & _
"WHERE Orders (Order_ID) = (Forms![RECEIVING_MAIN_Check_In]![Order_ID]) "
DoCmd.Close

I am noticing that the actual record does not seem to be populated into
the
table until after I close form1 in frustration, which I believe is the
reason
I cannot get this to work. However, when trying to use SQL with the
INSERT
INTO command, I also do not have any luck. Any thoughts? Thanks!

 

[Powderfinger]

Thanks Jeff, and sorry for the lack of info - perhaps you can shed some light
on making this a little easier for me...

Basically, form1 is a general receiving screen (address info and generic
product info) and form2 is to gather billing information (shipping
information is collected on form1). I do not have this information on form1
as space is limited and probably 9 out of 10 times it will not be relavent.
Therefore, I thought a seperate form, activated by a check box on form1
(yes/no - yes opens form, no duplicates shipping info into bill to fields),
would be able to accomodate this. Ideally, I would like to have all of the
information in one record in table1 (I know I could do this if I seperated
the tables, billing v. shipping, but what would be the fun in that?)

I am using a field Order_ID from table1 to try and keep everything together

Hope that helps, thanks!
-gary

Let me get this straight, you are using TWO forms to update ONE table? Are
there any locks on the forms? (i.e. RecordLocks property) This should be set
to "No Locks".

Even so that's not a good way to do it.

Why don't you have fields for Billing info and Shipping info in one table.
You could use a Tab Control if you do not have much room.. Then copy the
Shipping fields to billing when there are the same (i.e. Me.BillingAddress =
Me.ShippingAddress)

 

[Gary Dolliver]

Hello, thanks for the reply, here are the answers below:

Let me get this straight, you are using TWO forms to update ONE table? Are
there any locks on the forms? (i.e. RecordLocks property) This should be set
to "No Locks".

Yes, I have one table (holding both billing and shipping info) being updated
by 2 forms. Record locks are off

Why don't you have fields for Billing info and Shipping info in one table.
You could use a Tab Control if you do not have much room.

I do have both billing and shipping info in one table. I cannot use a tab
control as it is already set up on form1 - unless there is a way to put a tab
control within a tab control?

Am I asking for the impossible? Thanks!
-gary

 

[Powderfinger]

You can always put another tab on Form1 and put the billing fields in there.

If you want to use two forms, your two forms should have the same datasource
(table1 ). The Shipping info fields are in Form1, the Billing fields in
Form2. A button on Form1 is used to open form 2. The code in the button
should be something like this:

Dim strCriteria As String
strCriteria = "[Order_ID] = " & Me.Order_ID
DoCmd.OpenForm "form2 ", , , strCriteria, , acDialog

You do not need an update query.

Good luck!

 

[Gary Dolliver]

That did it, thanks so much!!!
-gary

You can always put another tab on Form1 and put the billing fields in there.

If you want to use two forms, your two forms should have the same datasource
(table1 ). The Shipping info fields are in Form1, the Billing fields in
Form2. A button on Form1 is used to open form 2. The code in the button
should be something like this:

Dim strCriteria As String
strCriteria = "[Order_ID] = " & Me.Order_ID
DoCmd.OpenForm "form2 ", , , strCriteria, , acDialog

You do not need an update query.

Good luck!

Hello, thanks for the reply, here are the answers below:


Yes, I have one table (holding both billing and shipping info) being updated
by 2 forms. Record locks are off


I do have both billing and shipping info in one table. I cannot use a tab
control as it is already set up on form1 - unless there is a way to put a tab
control within a tab control?

Am I asking for the impossible? Thanks!
-gary

 

[Powderfinger]

You're welcome. One final thing is you might want to add a save record
(DoCmd.RunCommand acCmdSaveRecord) in the OnClick event of your button
before the strCriteria line.

That did it, thanks so much!!!
-gary

You can always put another tab on Form1 and put the billing fields in there.

If you want to use two forms, your two forms should have the same datasource
(table1 ). The Shipping info fields are in Form1, the Billing fields in
Form2. A button on Form1 is used to open form 2. The code in the button
should be something like this:

Dim strCriteria As String
strCriteria = "[Order_ID] = " & Me.Order_ID
DoCmd.OpenForm "form2 ", , , strCriteria, , acDialog

You do not need an update query.

Good luck!

Hello, thanks for the reply, here are the answers below:

Let me get this straight, you are using TWO forms to update ONE

table?
Are

there any locks on the forms? (i.e. RecordLocks property) This

should be
set

to "No Locks".

Yes, I have one table (holding both billing and shipping info) being updated
by 2 forms. Record locks are off

Why don't you have fields for Billing info and Shipping info in one table.
You could use a Tab Control if you do not have much room.

I do have both billing and shipping info in one table. I cannot use a tab
control as it is already set up on form1 - unless there is a way to

put a
tab

control within a tab control?

Am I asking for the impossible? Thanks!
-gary


:


Thanks Jeff, and sorry for the lack of info - perhaps you can shed some
light
on making this a little easier for me...

Basically, form1 is a general receiving screen (address info and generic
product info) and form2 is to gather billing information (shipping
information is collected on form1). I do not have this information on
form1
as space is limited and probably 9 out of 10 times it will not be
relavent.
Therefore, I thought a seperate form, activated by a check box on form1
(yes/no - yes opens form, no duplicates shipping info into bill to
fields),
would be able to accomodate this. Ideally, I would like to have

all
of

the
information in one record in table1 (I know I could do this if I seperated
the tables, billing v. shipping, but what would be the fun in that?)

I am using a field Order_ID from table1 to try and keep everything
together

Hope that helps, thanks!
-gary

Let me get this straight, you are using TWO forms to update ONE

table?
Are

there any locks on the forms? (i.e. RecordLocks property) This

should be
set

to "No Locks".

Even so that's not a good way to do it.

Why don't you have fields for Billing info and Shipping info in one table.
You could use a Tab Control if you do not have much room.. Then copy the
Shipping fields to billing when there are the same (i.e. Me.BillingAddress =
Me.ShippingAddress)