Assigning control (sources) to a form using VBA (1 Viewer)

[johnain]

Hi all

I am very new to all of this, so I hope this is not a daft question. But I have hunted for a solution for a little more than a day so help would be appreciated.

My scenario is that I am using a one time table that is a copy of my real transaction table (called GLAcTranLine) to enter general ledger transactions. Both my form and my sub-form are unbound. The form is called GLFI50 and the sub form is called GLFI50TranSub on disk and inside the main form. That is, both the sub form and its description inside the form (in the label that appears when I build the sub form with the wizard) bear that name.

So in the load for the main form I carry out these job steps.

1. I take a "No Data" copy of the transaction table GLAcTranLine, to get the structure. That works fine. The name I give to it is prefaced with GLFI50 and ends with a random number. That works fine too.

2. I insert a blank record into the new table. That also works fine.

3. Assign the control source to the Sub form and to its individual objects.
That just dies in a crumpled heap. Neither the form not the individual columns code works.

The error I get (in both cases) is the Run-time error '2465' Application-defined or object-defined error.

I believe this means that it just cannot work out what idiotic error I have made. The code all passes the compiler test.

I am very new to Access-VBA and I do get massively confused by sub-form naming and behaviour. So I will welcome any input on my Syntax or anything else that anybody can spot.

Here is the SUB.

=================================================


Private Sub Form_Load()

'STEP 1. I take a "no data" copy of Create the Work Table and assign it as a control source to the sub form
'=======================================================================

'(Copies the structure of the source table to a new target one. It isn't a temporary table so I need to
'find a way of accigning a temporary name and then using that name on this prog.
'The True on the end says "Do not copy the data)"

'GLFI50Work is the Global variable that holds the temporary table name.
Dim RandomInt As Single
RandomInt = CInt((9999 - 1000 + 1) * Rnd() + 1000)
GLFI50Work = "GLFI50" & [RandomInt]
'DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "GLAcTranLine", GLFI50Work, True
'Insert a blank record into the table
'-------------------------------------

'STEP 2 Insert a blank record
'============================2.
DoCmd.SetWarnings False
DoCmd.RunSQL _
"INSERT INTO " & GLFI50Work & _
" (AutoSeq, DRCR, DocRef, AccRef, LineDesc, Amount, STaxRate, STaxRateValue, AmountDocCurr, CostCentre)" & _
" VALUES (0, ' ', 0, ' ', ' ', 0, ' ',0,0,' ' )"
DoCmd.SetWarnings True

' STEP 3 Now assign the Control source to each column in the sub Form Transub


Dim SelString As String
SelString = "SELECT " & _
GLFI50Work & ".AutoSeq, " & _
GLFI50Work & ".DocRef, " & _
GLFI50Work & ".DrCR, " & _
GLFI50Work & ".AccRef, " & _
GLFI50Work & ".LineDesc, " & _
GLFI50Work & ".Amount, " & _
GLFI50Work & ".AmountDocCurr, " & _
GLFI50Work & ".STaxrate, " & _
GLFI50Work & ".STaxRateValue" & _
GLFI50Work & ".CostCentre, " & _
" FROM " & GLFI50Work & ";"
Me.[TranSub].Form.Control = SelString

' Now the individual objects
Me.[GLFI50TranSub].Form.AutoSeq.Control = AutoSeq
Me.[GLFI50TranSub].Form.Docref.Control = Docref
Me.[GLFI50TranSub].Form.DrCR.Control = DrCR
Me.[GLFI50TranSub].Form.AccRef.Control = AccRef
Me.[GLFI50TranSub].Form.LineDesc.Control = LineDesc
Me.[GLFI50TranSub].Form.Amount.Control = Amount
Me.[GLFI50TranSub].Form.AmountDocCurr.Control = AmountDocCurr
Me.[GLFI50TranSub].Form.STaxrate.Control = STaxrate
Me.[GLFI50TranSub].Form.STaxRateValue.Control = STaxRateValue
Me.[GLFI50TranSub].Form.CostCentre.Control = CostCentre

'Propose today's date.
'---------------------
Me.DocDate.Value = Date

End Sub



=================================================

 

[spikepl]

I am very new to all of this

Yeah, well, since you have carefully picked all the bits of access that offer an advantage and thrown them away, we could have guessed

Please provide a verbal description (devoid of db-speak) of what the point of this application is and how you intend to go about it (not the tiny mechanical details that we have seen so far, becasue they do not give the big picture). I am asking because I suspect that you are committing some very basic datastructure errors, that people often do, by using Excel-thinking and not db-thinking..

A screenshot of the relations window showing your tables expanded in full and their relations would also be useful.

 

[johnain]

Yeah, well, since you have carefully picked all the bits of access that offer an advantage and thrown them away, we could have guessed

Please provide a verbal description (devoid of db-speak) of what the point of this application is and how you intend to go about it (not the tiny mechanical details that we have seen so far, becasue they do not give the big picture). I am asking because I suspect that you are committing some very basic datastructure errors, that people often do, by using Excel-thinking and not db-thinking..

A screenshot of the relations window showing your tables expanded in full and their relations would also be useful.

Hi Spike.

A bit aggressive, that. Never mind. Thanks for replying.

The Application will eventually be an accounting and crm/sales / sales order management system for companies that work through agents.

These companies agree contracts with agents in any one of several currencies for the delivery of a service by the company to a third party - so the Agent is the company's customer. This agent will frequently be located in a different country to the company.

The actual company who receives the service, and agrees a contract with the same agent for the receipt of the service will usually be located in a different country again.

These two contracts will usually be agreed with the same currency (but not always). That currency will normally be the currency of the end-user Company - or its head office.

So this is the key. There is a need to be able to bill and manage orders for the same company (the Agent) in any number of currencies, and to manage and bill expenses and other costs in different currencies as well.

Additionally each contract / Agent / Contract / Country needs to be evaluated to distinguish profitable from unprofitable contracts. Margins can be thin !


So ...

Sales Order Management by Agent / Contract / Currency
Sales Order Processing by Agent / Contract / Currency
Credit Management and collection
Multi-Currency Expenses management and claim management at the same levels.
Purchasing
Document materials management.
Contact Management.
Time planning and charging.

I am expecting a development cycle of around 12 months, particularly since I am in a learning curve.

I have an initial, contracted pool of 27 clients waiting for this application. First deliverable s will be General Ledger and Sales Order Management / Billing.

I have a strong, identified route to market for another 600 or so potential user companies.

The context of GLFI50 is that it is my first real transaction processing program and in most of the development environments that I have worked in it is a sensible one to start with.

I appear to have offended you Spike and I regret that. But we all have to learn.

John

 

[JHB]

..
My scenario is that I am using a one time table that is a copy of my real transaction table (called GLAcTranLine) to enter general ledger transactions. Both my form and my sub-form are unbound....

Please explain why you're doing that, (because it is not normal)?

 

[johnain]

Please explain why you're doing that, (because it is not normal)?

Hi. Thanks for that.

I am setting up a general ledger accounting entry with one header record for each document and then many transaction lines (up to a couple of hundred or so) - which must balance DR vs CR overall to zero.

There must also be a sequence number allocated to the header and each of the transactions at the point of posting as a link between all of these table rows so that the transaction can be pulled back together for display.

For audit purposes there must be no gaps in the allocation of this auto-sequence to each row of each record created. Auditors do not like that.

The auto-sequence cannot be the Document reference because for both audit and logical reasons, different types of accounting transactions must fall into different number ranges (e.g. one for sales invoices, another for purchase invoices).

So it is necessary to construct the overall transaction and then validate it and finally post it into the two tables involved. The auto-sequence cannot be allocated util just before the final update because this could potentially lead to gaps where transactions are abandoned by users - which happens in any accounting system.

My approach will provide a solution to all of these issues.

My understanding of Access is that it updates as entries are made. I cannot see how I could process the transaction described above with that approach. But I can with a "Work" table.

I do understand that this is not the way in which most Access users will work with tables. If I could declare a transaction and then commit it at the end for the database update then that would be fine, but this does not seem to be the case. Even then I am not sure how I would present the user with an empty grid in the Sub-form as a starting point because I would be using the real transaction table which is already populated. And I don't like the idea of adding unbalanced transaction rows to to an accounting table. Much rather make sure it all balances and then post all of them.

I understand that it is not the way that Access normally works, but it is the way an accounting transaction would normally be processed. An unbound form / sub-form will achieve that.

My only issue is referencing the sub form, Once I have done that it will sing and dance for me. It's my only roadblock.

 

[spikepl]

1. Seems I'll have to eat some of my words , so I do - you seem to know what you are doing, and I apologize! And I was not offended in any way, how could I?

2. If you are using the Access autonumber for human-readable data (or even for auditor-readable data) , pay attention to the fact tha Access autonumber is not guaranteed sequential nor guaranteed ascending, just guaranteed unique. For things required to keep auditors happy it is better to "roll your own" number and assign it when desired.

3. Access does support transactions, sp you can commit or roll back, FOr a single form an example is here: http://www.access-programmers.co.uk/forums/showthread.php?t=261029

4. Now to your issue: referencing contols elsewhere. I too do not always remember, so what I do is I create a text box, in its Control Source property click on the ellipses and select Expression Builder, there I navigate (using forms) to the control in question and double click, and paste. Then you have in the ControlSource a ready-made reference from where you currently are to the control in question, that you can copy.

 

[johnain]

1. Seems I'll have to eat some of my words , so I do - you seem to know what you are doing, and I apologize! And I was not offended in any way, how could I?

2. If you are using the Access autonumber for human-readable data (or even for auditor-readable data) , pay attention to the fact tha Access autonumber is not guaranteed sequential nor guaranteed ascending, just guaranteed unique. For things required to keep auditors happy it is better to "roll your own" number and assign it when desired.

3. Access does support transactions, sp you can commit or roll back, FOr a single form an example is here: http://www.access-programmers.co.uk/forums/showthread.php?t=261029

4. Now to your issue: referencing contols elsewhere. I too do not always remember, so what I do is I create a text box, in its Control Source property click on the ellipses and select Expression Builder, there I navigate (using forms) to the control in question and double click, and paste. Then you have in the ControlSource a ready-made reference from where you currently are to the control in question, that you can copy.

Hi Spike

Thanks again for the input. It's appreciated. I have fixed the issue now, but I thought it might be useful for others who face similar issues, and also out of interest for you.

But first ... a word of thanks for that tip on referencing. It is pure gold ! I confess that I sometimes go into idiot mode as soon as a sub form appears on the scene. Also for your input on transactions. I will look carefully at that.

What I have done with my temporary table is :

In the basic form design I specify it as being sourced on the real transaction table GlActranline. That way I can get all of the individual column controls specified in the form build. And the sub-form will load because those values are valid.

I do my step 1 as before

'STEP 1 Invent a temporary Table name
'====================================
Dim RandomInt As Single
RandomInt = CInt((9999 - 1000 + 1) * Rnd() + 1000)
GLFI50Work = "GLFI50" & [RandomInt]
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb().Name, acTable, "GLAcTranLine", GLFI50Work, True


Then, in my Step 2 I insert a blank record, betting that the ID column will be automatically populated with a value 1. It is.

'STEP 2 Insert a blank record
'============================.
DoCmd.SetWarnings False ' Careful now, remember to set it back on
DoCmd.RunSQL _
"INSERT INTO " & GLFI50Work & _
" (AutoSeq, DRCR, DocRef, AccRef, LineDesc, Amount, STaxRate, STaxRateValue, AmountDocCurr, CostCentre)" & _
" VALUES (0, ' ', 0, ' ', ' ', 0, ' ',0,0,' ' )"
DoCmd.SetWarnings True



Finally in step 3 I simply switch the record source ... nothing to do in the columns because those values are already there from the original design.


' STEP 3 Now assign the Control source to each column in the sub Form Transub
'--------------------------------------------------------------------

Dim SelString As String

SelString = "SELECT * FROM " & GLFI50Work & ";"

Forms!GLFI50!GLFI50TranSub.Form.RecordSource = SelString

You will probably be amused to see that I ignored columns selection and just went for a select all. A bit embarrassing (for the purists), but a lot quicker for me. I just hide the columns I don't want to see and change the order in the layout view.

Thanks for your advice on the sequencing. I have created an integer field called AutSeq and stuck it into a control table. It will be used at posting time for all transactions (i.e. use it and add one).

So all in all I feel that I have learned a lot. I actually end up with unbound document header data but a bound sub form.

Now I will get the validations sorted and then write a paramaterised Function to do the update of the General Ledger and the insertion of the transaction lines. That way I can use the same function for Sales Ledger and Purchase ledger postings once I get that far. Plus I can offer it to my users as a batch input for their own external functions.

I an really enjoying this. And I want to thank you and others for their help. It's good to be challenged because it provokes thought.

John