Modifying/Appending Record Between Subforms

[David Mulholland]

I accidentally posted this in the General section...it should have gone here...

I need some guidance and perhaps some inspiration. I'm working on a database
containing a main form with 2 subforms side by side. Subform1 is a continuous
form that has records about the positions a military unit has. Subform2 is a
continuous form that starts out wth no records.

Basic Subform1 record fields: UIC, Para, Line, Auth Str, Req Str
Combo PK on UIC, Para, Line

What I'm trying to do is to 'maybe' click a command button on a record in
Subform 1 and have the data of that record append to Subform2. While doing
this it should subtract 1 from Auth Str and Req Str, and change the appended
record's UIC to the contents of an unbound text field on Subform2. If Auth
Str and Req Str would end up being "0", then it should append then delete
that record from Subform1. When the append to Subform2 takes place, it should
either add that record, if it doesn't exist, or add 1 to the Auth Str and Req
Str.

In case the user made a mistake, Subform 2 will have a similar command
button to carry out the same action.

I was hoping someone could point me in the right direction.

 

[Barry A&P]

David

Lot of stuff for a post.. and i believe you need to post a lot more info to
get this done. because you refer to copying records from a subform on the
left to a subform on the right I am not exactly sure what you are doing i am
assuming you are copying from table A to Table B

I am a newbie so let me first get that out of the way, youll need a lot of
VBA to get there but it will be small easy steps. My syntax is way wrong here
im just helping break it down piece by piece
Im not real familiar working with combo PK's

Youll need Seven things
1. to capture the value of [Auth Str] in table A as a variable? "Dim
oldauthstr as Long" (not sure if long is correct)

2. Write an append query to copy the record to table B "Q_AppendAtoB"
3. Write a delete query to delete the record from table A "Q_Deletefrom_A"
4. Write a update Query to subtract 1 from table A's UIC "Q_SubfromUIC_A"
5. Write a update query to Add 1 to table B's UIC "Q_ADDtoUIC_B"
6. Dcount to see if the record exists in B.
7. Then run some If statements in your VBA

think about how you would do it by hand.

Step1. get the current value of table A's [Auth Str] set it to a variable
like Dim OldAuthStr as Long.

Test#1 IF OldAuthStr = 1 we will need to delete it when were done.
IF the record already exists in table B run Query Q_AddtoUIC_B
then run a query to delete the record from table A,

IF the record does not exist in B run query Q_appendAtoB (in the
Query Append Forms!MainForm!Subform2.UnboundUIC to table B's UIC), then run
query Q_deletefrom_A

Test #2. If [Auth Str] is greater than 1 run check to see if the record
exists in table B
IF the record already exists in table B run Query Q_AddtoUIC_B
then run query Q_SubFromUIC_A

IF the record does not exist in B run query Q_appendAtoB (in the
Query Append Forms!MainForm!Subform2.UnboundUIC to table B's UIC), then run
query Q_SubFromUIC_A

Im sorry this is as confusing as it is it may not be the direction you asked
or should be sent in but it is a direction..

Barry