Clearing a Table (1 Viewer)

grenee · Sep 18, 2017

I am seeing VBA code to clear an entire table.

My subform "CashPosting" which opens in Datasheet view, has record source as a table "Posting". When finished using I want to press a button "Clear" to empty the entire table.

Can anyone lend assistance with this problem?

Minty · Sep 18, 2017

Delete * from YourTable

However, it is generally not a good design to continually fill and empty tables, a) There is normally no real need , b) It can cause database bloat over time.

missinglinq · Sep 18, 2017

Your need, as stated, is very suspicious, pretty much shouting bad database design, as Minty has suggested. Perhaps if you could give us your needs, in plain language, we could advise you better.

Linq ;0)>

grenee · Sep 19, 2017

Thanks for your prompt replies and concerns expressed. I am so pleased to provide you my concerns and I am confident you would advise me appropriately.

The situation is related to accounting. I am purchasing several different categories of items on one invoice for cash, and want to post the items by category to an account in a table "Post". I am using a master form "MasterPosting" with its subform "Subposting" The master form has 1 record for the cash payment but the subform has a datasheet view and can accept several entries and can increase its rows as needed.

The entries for each row includes fields "ID", Debit or Credit, Amount, AcNumber, AcName, etc.

The task at hand is to copy the values in the master and subform to a table "GeneralJournal". These forms are just for collecting the data. but after posting there is a need to clear the table in preparation for the next entry.

Personally I wish there was a way for collecting the data without using a normal table. I prefer to use a datasheet control. However when I tried that approach the cell were not accessible. That's why I resorted to creating a normal table.

I would be very grateful if a better and easier way can be shown.

Minty · Sep 19, 2017

Why not just enter the values directly into the General table?
Is there other processing that takes place?

grenee · Sep 20, 2017

Oh. It's too risky to enter the values directly into the tables because a lot of checking and testing has to be performed before entering data into the Journal table. For example it is necessary to ensure that the sum of the debits are equal to the sum of the credits. If a mistake is made it may be too hard to detect or to correct.

static · Sep 20, 2017

grenee said:
Personally I wish there was a way for collecting the data without using a normal table. I prefer to use a datasheet control. However when I tried that approach the cell were not accessible. That's why I resorted to creating a normal table.

You can make an Access form function like a datagrid by creating your own recordset or using a disconnected rs

Code:

Dim adors As New ADODB.Recordset

Private Sub Form_Load()
    'create rs
    Set adors = New ADODB.Recordset
    With adors
        
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .LockType = adLockPessimistic
        
        'add some fields
        .Fields.Append "id", adVarChar, 10
        .Fields.Append "debcr", adVarChar, 10
        .Fields.Append "amt", adVarChar, 10
        .Fields.Append "accno", adVarChar, 10

        .Open
        
        'set some control sources
        Text0.ControlSource = .Fields(0).Name
        Text2.ControlSource = .Fields(1).Name
        Text4.ControlSource = .Fields(2).Name
        Text6.ControlSource = .Fields(3).Name
    End With
    
    'apply to form
    Set Me.Recordset = adors
    
End Sub

Private Sub AddRecord_Click()
    Static id As Byte
    id = id + 1
    adors.AddNew Array("id", "debcr", "amt", "accno"), Array(id, "cr", "£123.45", "12345")
    Me.Refresh
End Sub

arnelgp · Sep 20, 2017

With this setup, how would you update the Base table? And the edited ones? How would you handle validation and duplication.

static · Sep 20, 2017

Who are you asking?

Pat Hartman · Sep 20, 2017

The key to doing this process in the permanent tables is to have a flag in the parent record that gets set when the entry is balanced. Only if the BalancedFlg is True should any query outside the maintenance form query select the data from the parent or child record. If you are in the early stages of building the app, this shouldn't cause any extra work. If the app is largely already built, it will require some modifications to queries and lots of testing to make sure you got all of them.

Regarding using a temp table, you need to be extremely careful with the process. How are you handling multiple users? What happens if the user closes the form without completing the validation? Do you have a reminder system to remind people that they have work in progress that must be completed?

Clearing a Table (1 Viewer)

grenee

Registered User.

Minty

AWF VIP

missinglinq

AWF VIP

grenee

Registered User.

Minty

AWF VIP

grenee

Registered User.

static

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

static

Registered User.

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread