Unanswered: Grab VAT from a table and put into a text box
I have searched the forum for this but can't quite find what I am looking for
I have an excel file which I have imported as a linked table in to Access. The table iteself has 1 column called VAT and 1 row the VAT value. As it may changed in the future I want the user to be able to change it externally.
I have the VAT in an orders table as and it looks up the value from the imported table using a combo box.
However on the orders form I want the value of that cell within the imported table to be automatically there. I have tried changing it to a text box, using DLookup and default value etc. Maybe its because I have had no sleep trying to figure it out that I have gone beyond being able to but I know it is so simple and can be done.
There are several ways to do this. Here's what I would do...
You can't use DLookup in a Default Value property - something that annoys me regularly. Assuming the user will have to enter something in the Form before you care to show the VAT value for a new record, you should put this code in the BEFORE INSERT Event of the FORM:
Me![VATFieldOnForm] = DLookup("VAT","tblVAT")
Replace VATFieldOnForm with the form's textbox control name
Replace VAT with the field name in the linked table
Replace tblVAT with the linked table's name
As soon as the user enters something on the form, it will set the VAT value to the linked table's value.
If you really want the Form to show the VAT even before someone types anything, then you can use the ON CURRENT Event of the form, but then you need to use an If_Then_Else statement to make sure the VAT field is blank before you replace it with the looked-up value.
Note: Regardless of the above methods, the user will not be able to change the VAT value from within the Access program if the VAT table is linked rather than imported. Your goal may be to allow them to change it thru the Excel file only, which would be perfectly fine with this setup.