Access 2003: Insert/Update records by form with subform using VBA

[ndhvu]

Tables: Buy_Header and Buy_Detail.
- Buy_Header: info. of each buy (buy_id(PK, auto number), date, shop, bought_by, ...)
- Buy_Detail: info. of each item from each buy (buy_detail_id(PK, auto number), buy_id, item_name, price, quantity,...)

Target:
- Make a main-form of table [Buy_Header] with sub-form of table [Buy_Detail] to insert/update records for both tables. Each form of each [Buy_Header] and the sub-form can have many records of [Buy_Detail].
- When update, you can add more records to [Buy_Detail] through sub-form

Problem:
- I can make a main-form that link directly to table/query [Buy_Header] with sub-form link to table/query [Buy_Detail] to insert/update. However, although it is easy, the drawback is that I cannot cancel the process. Right after I input value to the main-form fields and click on any field of sub-form, Access will automatically insert/update data of the main-form to DB, even if I later clear all the field (Undo method is kind of useless here). Therefore, I intent to use VBA for insert/update instead.

- However, even though I can insert/update the info. of the form, I have a problem with the sub-form data.
1. I don't know how to get the [buy_id] field from the main-form to put into the data of sub-form.
2. I cannot distinguish each row of the sub-form. I use [datasheet] type for sub-form and naming each field of it. However, when I want to get the value from any field, with VBA, I can only get the value of the field from top row.

I am a totally Access/VBA newbie (but have a good knowledge of coding). Any idea will be appreciated. Thank you.

[TheSmileyCoder]

I think in a case like this, I would create a temporary table to hold my Buy_Header and a temp table to hold my Buy_Detail, then when your order is done, click a button (or cancel) and transfer the record to the main tables and clear the temporary tables. That will allow you to use the built in subform functionality, and not having to re-invent it all from scratch.

[ndhvu]

Thanks for your reply :).
I just got the same idea about making tables to hold data temporary, transfer and then delete all record after finish. The problem are:
1. Work well with insert but not with update. using this way for update data meaning i have to delete the old data and insert a new one. It ill disrupt the order of records (or at least that is what I think) and making the 2nd problem happen faster.
2. Use up a lot of number for the [auto-number PK]. Since each buy can include a lot of items, the [auto-number PK] of [Buy_Detail_Temp] can reach the limit very fast. Unless I can some way to TRUNCATE the table like on other DB.

But maybe I will stick to this way for now before I can figure out a better solution.

Thank you.

[TheSmileyCoder]

1) When is it you require the ability to Update?

2) The limit is on autonumber field is 2.147.000.000 (roughly) as far as I know. If your approaching that amount of sales, I think you can afford to buy a system instead of developing it yourself!

[ndhvu]

1) The insert form and update form have same structure and I want to find an approach that can work well with both of them in order to reduce the writing and maintenance time (or so I hope), since I also have some other forms with the same relationship.

2) I have no idea I can be that much :), but it still be better if there is a way to TRUNCATE the table. But well, my boss doesn't even care about how the project run anyway. Just make sure the job done is good enough :).

[TheSmileyCoder]

Problem:
- I can make a main-form that link directly to table/query [Buy_Header] with sub-form link to table/query [Buy_Detail] to insert/update. However, although it is easy, the drawback is that I cannot cancel the process. Right after I input value to the main-form fields and click on any field of sub-form, Access will automatically insert/update data of the main-form to DB, even if I later clear all the field (Undo method is kind of useless here). Therefore, I intent to use VBA for insert/update instead.

Lets return to the core of the problem. Maybe all you need is a delete button (we can call it undo, if you like).
I don't know enough about how you intend to use your forms and for what purpose, to give more specific suggestions.

If the problem is that you MUST update both tables at the same time, please specify so. (This might also be directly achievable from the subform)

[ndhvu]

If you ask if after i click [Save] button, the data on the main-form and sub-form will be insert/update to both table, then the answer is Yes.

If users close the form, then data simple won't be insert/update into DB

This is but a simple project for daily tasks for a small office, so a simple solution will be preferred.

[ndhvu]

After search, I intent to use these code for Insert task:

    Dim db As Database
    Dim rst1 As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim rs As Recordset
 
    Dim buyHeaderId As Long
    Dim wrk As Workspace
 
    Set wrk = DBEngine.Workspaces(0)
    Set db = DBEngine.Workspaces(0).Databases(0)
 
    wrk.BeginTrans
 
    Set rst1 = db.OpenRecordset("BUYHEADER", DB_OPEN_DYNASET, DB_DENYREAD)
    rst1.AddNew
    rst1![INVOICE_ID] = Me.txtInvoiceNo
    rst1![BUY_PERSON] = Me.txtUser
    rst1![BUY_DATE] = Me.txtBuyDate
 
    stockInHeaderId = rst1![BUY_HEADER_ID]
    rst1.Update
    rst1.Close
 
    Set rst2 = db.OpenRecordset("BUYDETAIL", DB_OPEN_DYNASET, DB_DENYREAD)
    Set rs = Me.subfrmStockOutDetailInsert.Form.RecordsetClone
    rs.MoveFirst
    While Not (rs.BOF Or rs.EOF)
            rst2.AddNew
            rst2![BUY_HEADER_ID] = buyHeaderId
            rst2![ITEM] = rs.Fields.Item(0).Value
            rst2![QUANTITY] = rs.Fields.Item(1).Value
            rst2![PRICE] = rs.Fields.Item(2).Value            
            rst2.Update
            rs.MoveNext
    Wend
    rst2.Close
 
    Set rst3 = db.OpenRecordset("BUYTEMP", DB_OPEN_DYNASET, DB_DENYREAD)
    rst3.MoveFirst
    While Not (rst3.BOF Or rst3.EOF)
        rst3.Delete
        rst3.MoveNext
    Wend
    rst3.Close
 
    wrk.CommitTrans
 
Insert_Exit:
    Exit Sub
 
Insert_ErrHandler:
    wrk.Rollback
 

However, I not sure if update task should use the same approach.