UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Static Data Issues (access 2016), Access 2016    
 
   
AP_Ben
post Feb 16 2020, 02:21 PM
Post#1



Posts: 54
Joined: 11-February 20



Hi, I seem to be running into more problems than this warrants, so I think I must be going in the wrong direction:-

Object: Keep some static data accessible to various forms and reports. Examples Company name/address and Last Invoice Number
tried: setting global variables - these don't seem to be available to me in reports, though they do work fine on the forms. I have tried many variations of syntax, but if you have one that works I'm happy to try again
Company table - this worked fine until I tried putting the company name on a main form - as it wasn't related to any other tables the form wouldn't let me add data so I had to remove it from the recordsource query

This seems to me a very simple requirement. Main company details are unlikely to change often, of course last invoice number would. I'm advised autonumber is bad practice as yo can get 'phantom' gaps in the numbering, however I would be prepared to have a different method for really static data like the company name as opposed to next invoice/creditnote numbers which could change several times a day.

Have I just missed something obvious here?
Thanks
Ben
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2020, 02:38 PM
Post#2


UA Admin
Posts: 37,446
Joined: 20-June 02
From: Newcastle, WA


How does "static" data get established in the first place?

Are you talking about YOUR organization's name? Or the name of companies with which you do business?

If I understand correctly what you are after, you can create a small table that has the relevant facts regarding YOUR organization and refer to it as needed using DLookup() or another method.

Rather than Global Variables, though, I think you'll probably get more satisfactory results from TempVars.

You can initialize a set of TempVars when the Relational Database Application starts and use them at any time.

You are right that Last Invoice is not one of those data points you'll want to handle this way. It changes as each new invoice is created. Just create them as needed.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
AP_Ben
post Feb 16 2020, 04:11 PM
Post#3



Posts: 54
Joined: 11-February 20



Many thanks George. I have the table populated already, so shall now investigate DLookup and tempvars - who knows what I'll find but I will definitely know more than I do now, which must be good.

I'm not quite clear what you mean by handling last invoice number in a different way, but I can use Autonumber on a temporary basis while I checkout those other badboys ;-)

And for clarity, yes the static data is my company details which are obviously required on any invoice or credit note, plus doubtless various other reports etc as I move from functional (and arithmetically correct) versions to presentable ones.
Ben
Go to the top of the page
 
projecttoday
post Feb 16 2020, 04:55 PM
Post#4


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


You mention last invoice so it sounds like the company (-ies) in question are the customers not your organization.

It's all part of the design. You have a table for storing this information. You construct queries to read it as needed along with the transaction data.

--------------------
Robert Crouser
Go to the top of the page
 
AP_Ben
post Feb 16 2020, 05:32 PM
Post#5



Posts: 54
Joined: 11-February 20



What I hear in my head when I write it, is clearly not always what someone else hears in their head when they read it - so I need to be more pedantic :-)

In this case when I say Last Invoice, I'm referring to the last used invoice number (which then gives me the next available number) - or possibly one could store 'Next Invoice Number' which might involve less programming at read time. I'm sure later on in the project I might also need to find the last invoice a particular customer had, but that is a way down the line.

I have now successfully managed to extract data using a query, or SQL SELECT VBA code, however this does seem a bit long winded every time I want to refer to the name of the (my) organisation for instance, and reports seem to struggle having anything apart from a field name as the source on a control. I have also now managed to use DLookup to read values from record1 in the CompanyStatic table, so am progressing.

The other thing I will have to do is read various default values from the customer record (days to pay, default nominal code, credit limit etc) which probably means using the same DLookup mechanism, but have various logistical issue to resolve on the sales invoice front first (such as when they should be allowed to edit an invoice, and what happens if they abandon it halfway through) - does that entail a temporary table perhaps, or could I mark the record as incomplete (with a flag) and then run some sort of query to delete it and its transactions if the user presses cancel? Or just never give them the option to cancel!

So all thoughts and suggestions welcome as I travel this rather winding road....
Go to the top of the page
 
projecttoday
post Feb 16 2020, 05:58 PM
Post#6


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


Information about customers can be joined in with the invoice information. SELECT is necessary in this case. Information about your company can be read with a Dlookup, as you said. It only needs to be read once so no SELECT there.

Do you have your tables set up correctly?

--------------------
Robert Crouser
Go to the top of the page
 
AP_Ben
post Feb 17 2020, 07:17 AM
Post#7



Posts: 54
Joined: 11-February 20



I don't think I have a problem with my tables as such, but I do have a major issue with my logic. I believe it must be possible to use the same form to view records and add them, but so far am failing miserably!

Current situation: frmSalesOrder has subform subInvLines. If I already have invoice records in the system then setting the forms recordsource property to a select query having both Customers table and SalesOrders table shows invoices with their transaction details in the subform quite happily, however I cannot add new records. (add is greyed out, which I believe is because I can't have recordsource set to a query if there is a subform).

If I have recordsource set to just the SalesOrder table, I can add records, and lookup the customer details using DLookup, but when the invoice is finished I can't update the Customers record because the customer table is not referenced in the forms record source

Rather like having ones cake and not being able to eat it.

Have I missed something in this maze, or do I really need to try and duplicate (and maintain) two identical forms, one for adding Invoice records and one for viewing/amending existing invoices?

Thanks

example code that fails when customers is not part of the recordsource:
CODE
strSQL = "UPDATE Customers SET Customers.balance = Customers.balance - " & amount & " WHERE Customers.Account = " & """ & [Forms]![frmSalesOrder]![Account] & """ & ";"
Go to the top of the page
 
projecttoday
post Feb 17 2020, 08:18 AM
Post#8


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


As you've discovered, bound form don't like multiple tables when it comes to updating.

It looks like you're keeping running balances by customer. The first question is is this really necessary? You can make a report that calculates a customer's balance from the transactions. Another thing to keep in mind is if the electricity goes off after the sales order table has been update but before the customer table has been updated there will be a discrepancy between the customer balance and the transactions.

If you still want to do it, what is the error message? You say it doesn't work unless the customers table is joined in? Is there a value in [Forms]![frmSalesOrder]![Account]? Couldn't be record locking. Why didn't you just put a not-visible textbox on the main form for the balance and set the textbox?

--------------------
Robert Crouser
Go to the top of the page
 
AP_Ben
post Feb 17 2020, 08:46 AM
Post#9



Posts: 54
Joined: 11-February 20



Appreciate the reply. There are several reasons why I want to do it, which I will list partly to make sure i have it straight in my own mind:
1) because it would be handy to have any view of the customer (whether on the record itself, or on a list of customers, on reports etc) showing the balance without having to calculate it every single time
2) because it should be simple, and I need to be able to make 'simple' things work or I will never progress to making difficult things work!

I know that the figures could go out of sync, and it would be easy to make a 'verify data' routine to check and/or fix, and am at this stage happy to accept that limitation. There are however other instances where I might want to put information on the customers record and that info might not be calculable, but would face the same difficulty.

HOWEVER: Having put it back into not having the 2nd table included in the recordsource in order to reproduce the error, it does seem to be doing the postings for me. I'm not sure what has changed, and if in my testing I have disabled some bits of the code or something, so I am going to zap my data (again - good job I have set up a button to automate that part!) and put in some more transactions to check everything that is supposed to happen, does happen. Then I will report back
Go to the top of the page
 
projecttoday
post Feb 17 2020, 12:30 PM
Post#10


UtterAccess VIP
Posts: 12,375
Joined: 10-February 04
From: South Charleston, WV


Okay. As you wish. But there is nothing un-handy about clicking a button to produce a report of customer balances wherein the balances are totaled. The computer does all the work.

--------------------
Robert Crouser
Go to the top of the page
 
AlbertKallal
post Feb 17 2020, 01:21 PM
Post#11


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, we have to break this issue down into “several” different issues.

Display of company name on reports.

This is a VERY different issue than that of display some balance or number for a customer balance.

Ok, in this case, we can likely assume that you have some type of config screen or at least a table with ONE record. This table would be of MOST use for:

The company name
Default City, state.
Default area code.

In other words, you would enter all the above information into that form – it would be a table with one row.

So, now, for any and all reports, you might have a textbox on that report, and it would display the company name.

You could/would drop a text box say into the reports header, and do this for the source of the text box:

=MyCompanyInfo()

And you have a global public function with this:

CODE
Public Function MyCompanyInfo() as string

   MyCompanyInfo = dlookup("CompanyName","tblCompanyInfo")

End Function.


So, now every report (or even forms) can now display the company info.

And what about an invoice? That might need “all” of the company information. Well, just create a “tiny” form that displays the information. Say like this:




The result is thus now you have a read made “thing” that without ANY CODE you can drop into any form or report. And it don’t even take one line of code to achieve this goal!!!

So, to display the company information say on a invoice? Then drag + drop the above into our invoice form like this:




And you can even use that “one” company record to setup default for things like city, or even area codes for phone numbers. (You can use functions like the above for the default).

So, a few functions to return the company name, and a tiny handy dandy sub form that can be dropped into ANY form (or report) to display the company information and address information will mean that it takes ZERO code to display that company information – just a simple drag + drop of the sub form!

Note that the above sub form can be used for both forms and reports. So, such an approach means:

You can drop into any form or report a simple “thing” that displays the company information.

Such a drag + drop takes zero lines of code.

For things like next invoice number (which you could ALSO store in that one table with “one” row could also have several additional columns such as

NextInvoice Number
Next PO number

And, what is nice, then your “config” screen that lets you setup and enter company information can also manage and handle the next invoice number (and your config screen will thus now perfume double duty, as it can allow the users of the application to set what the starting or next invoice number will be.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada
Go to the top of the page
 
AP_Ben
post Feb 17 2020, 02:23 PM
Post#12



Posts: 54
Joined: 11-February 20



Many thanks for that insight Albert.

A variety of different approaches, all of which I shall try until I am comfortable with them and can pick the best one for a given situation. I already have what I call my Static Info file - a one-record table that I can access always using ID=1 and which contains all those types of data, but I never thought of a public function to access it. Also drag/drop of a form isn't something I have ever tried.

I have been testing my current implementation, and now have all the relevant figures updating correctly. Just one annoying issue is my combo box to select customers account is broken on the SalesInvoice form. Previously it was bound to the customers table, which is no longer in the recordsource of the form. Is there a way I can get that to populate with account numbers and names so the user can select the correct customer to invoice (in the sales Order table)? As I only have 3 customers in the system currently for testing, I can easily bodge with a typed list but I have a feeling that there must be a way of reading the customer account number/name combination without it being tied to the recordsource of the form?

I shall leave that on the back-burner whilst I hone those other techniques in case a solution pops up here whilst I do that.
Cheers all,
Ben
Go to the top of the page
 
AP_Ben
post Feb 18 2020, 04:10 PM
Post#13



Posts: 54
Joined: 11-February 20



Well everything is working swimmingly, except one irritating thing:
I have a Dlookup which populates the customers name/address etc. I realised that when I was changing the record from a combo box, to a different invoice, the dlookup was not updating.

I moved the lookup code to a private sub, and tried to call if on various events, but not one fires. As a test I assigned it to dbl-click on a control and it works happily
But no event I have tried (a good 10 likely lads so far) has activated. To be clear, when the combo box changes the record, nearly everything on the form changes (invoice number, invoice date, transactions in the subform, account number etc)

Some of the events I have tried include form on dirty, form after update, form datachange, invoice number after update, invoice number change.

What is going on? I can now see why form dirty wouldn't work, because the record data itself hasn't changed at that point so there is no pencil, but surely there must be some event that can trigger when teh data on a form changes?

I know the obvious place is in the combobox afterupdate, however that is running a macro and I can't find a successful way of adding it to the macro. I am beginning to hate macros!
Go to the top of the page
 
AP_Ben
post Feb 18 2020, 04:17 PM
Post#14



Posts: 54
Joined: 11-February 20



I just stumbled across Form Oncurrent - which seems to do the trick. Time for a coffee!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 01:22 AM