Update default value in linked table

[John thomas]

Hi I have a split database ,and I need to update the Table default value of a field
Rather than go into the table I would prefer to use a form
I found this code but it wont work,I presume becouse my data base is split


Private Sub UpdateInvoiceReportNumber_Click()
If Not IsNull(Me.txtDefValue) Then
CurrentDb.TableDefs("PaymentsT").Fields("SelectInvoice").DefaultValue = Me.txtDefValue
MsgBox "Default Value has been changed to " & Me.txtDefValue
Else
MsgBox "You must enter a new Default Value before you can assign it!"
txtDefValue.SetFocus
End If
End Sub


I get an error not supported on linked Tables
Any ideas Please keep it easy im new to VBA

 

[pbaldy]

Untested, but I suspect instead of CurrentDb you'll need to set a database variable to the back end database.

 

[vbaInet]

I guess it's as the error states, such changes can't be made to linked tables. If you want to change the Default Value, you can do it at form level.

 

[gemma-the-husky]

more importantly, why would you want to change the default?
is this a one-off or a regular thing. If a regular thing, then really using the default is not the right solution.

 

[pbaldy]

I guess it's as the error states, such changes can't be made to linked tables. If you want to change the Default Value, you can do it at form level.

Changes can be made to linked tables, you just have to work with the back end:

Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("PathToBE")
db.TableDefs("PaymentsT").Fields("SelectInvoice").DefaultValue = Me.txtDefValue
Set db = Nothing

 

[vbaInet]

Should have been phrased "shouldn't be".

 

[John thomas]

Thanks for all your Help will give p baldy a try .

Maybe there is a simpler way
Gemma the Husky, asks why I want to change the Default

I have a report that is a customers Invoice .This invoice report must never change over time.If i was to look up this report 2 years from now it should be exctly as given to my customer .
If I make a small or large change to the design of the invoice this will change all previous invoices and then they will not be exact replicas of that given to the customer .
So i have made copies of the invoice report and numbered them ,a corresponding number is used in the table .If now I decide to update the report I use the next number report and change the default accordingly in the table .
Depending what the default is relating to that customers order I can always open the correct report .I do not envisage changes being made that often

 

[vbaInet]

You could look at this in a couple of ways:

1. For each invoice batch generated, save the report name (or the corresponding number), batch number and date/time in a separate table and recall the report based on this data.

2. Create a table that stores the invoice ID, the report name (or the corresponding number) and date/time and recall with the relevant parameters

3. Save hard copies of each report, archive it and recall when necessary.

For the first two cases, you will need a separate table that stores a history of the report changes, from versions 1 through to the current version. You may choose to affix the date/time to the name of the report or include this information in the report history table just mentioned.

Your table report history table can be used to identify which report to use as the current one.

 

[gemma-the-husky]

I am sure vbainet's solution will work.

I would probably have a table by invoice date or number, identifying the invoice print to be used as changes are made, rather than store the default in a given field. Then for any given invoice number or date, look up the corresponding report print to be used.

However, I expect that many suppliers' invoices change appearance over time, and it is not necessarily an issue if an invoice reprint does not look exactly the same as an old one, as long as the data is correct. I mean would you really want to send out old invoices with wrong phone numbers or addresses on them? If a customer asks for a copy, he probably hasn't got the original to compare it with anyway.

and another solution would be to produce and save a pdf of each invoice at the time it is raised.

 

[John thomas]

Hi Dave and everyone who has come up with idears
I do keep hard copies of all my Invoices ,But after many years it’s a real Headache trying to find them
I have also recently been scanning and saving them .It works well But sometimes I forget ,and its just another job I could do with out
And guess so long as the details of The Invoice are correct It dosnt matter too much If the invoice does change a little
However as ive gone to the trouble of automating my Invoices I want to get them looking exactly the same as the original ,Using the default in the table allowed me to do this .But meant going back to the back end to manually change it .Hence my original Question .
P baldy’s answer im sure would work but is to complicated for me . Thanks Pbaldy.
Having read vbanet and your post .I have decided not use a table default but have now created a new table that keeps track of invoice changes and can all be altered at form level
And it works just fine
A big thank you for aeveryone’s help