Where did all my values go to?

Or: Mess with values that you enter in a datasheet view.
This is the scenario:
There is a form which contains a sub form which is linked to values in a text box on the form (or to values in a different sub form). The form is displayed as datasheet.
You enter the values you need, all of them are from different linked tables,
and when you go the the next record your values had disappeared and instead you get totally different values!
For example:
the value should be "style" in the outside form
so for the style "myStyle" on the Parent Form the values in the sub form should be:

Bold 5%
Italic 10%
Normal 30%

But when you insert them they're seems to be taking from an existing style somewhere else. When you look in the table in the database, the values had entered correctly, it's just that the display is wrong.


The solution:
Add the id of the linked value to the subform, just hide it. That sort the mess out!

Environment:
Ms-Access 2003, Linked SQL tables.

Has google docs lost their mind????

Goggle Docs is my favourite word processing application. Simple: I use it for all of my list of changes in the database. Whatever I need to do, I just type it in. I make sure nothing personal/confidential is in there, and I've found that this is the best way to log changes or to write notes to myself that I can access from home or from work.
Today I tried to create a new document for an article that I would like to write, and when I pressed "create new" there was only "from template". Well, I would like to use my beloved "empty document". Oops, you can't do that. Why? Why? Why? Will I have to give up Docs? Will I write my article with pen and paper? Will I be able to read my handwriting afterwords? And why can't I "give feedback"? That's very unlike the expected behavior from a Giant, is it?
I will let you know the answers, whenever I have them...

String or binary data would be truncated

Scuffling with ‘String or binary data would be truncated’

While the above post was absolutely right about this problem, origins & solution, mine was a bit different:
The solution lay not in the table, but with the table audit. While the description column was defined as [varchar (30)] in the original table, in the Audit table it had to be settled with [Varchar (20)]. Obviously, it wasn't enough: you give the user room to write more in the description column, and they'll use it.

(Yes, of course I had to put the description in the audit table, as the definitions of what's being audit changes all the time)

The order of columns in a Access Query

If you write a query in Access using the design view, and then move to "datasheet view" and change the order of the columns, the columns' order stays the way you've closed it.
But if you try to "Export to Excel", the order of the columns will be the same as it was defined in the design view. You need to change it in the design view in order to get it in the order you want.

summing up decimals

I've just created a view that summs up percent value, that is saved in my SQL back-end as Decimal (5,4).
It resulted in the datatype (DECIMAL (38, 4)). Those 37 non-necessary digits were read by the Access database as Text, not number. If the number is treated as Text, then that causes ceveral problems to the access database: for example, it cannot format it as percent.
Since I don't really need those 37 digits, I've changed it to Decimal (6,4) (with an extra digit just to be on the safe side ; the sum shouldn't anyway exceed the 100%) and re linked the table. That sort this problem out.

Enter Parameter Value when exporting to Excel

The following problem is quite common, I believe, but without a simple solution.

I've create a query which depends on a parameter from the end user (being entered via a form). It runs beautifully.

But on "Export to Excel" (a command that does DoCmd.TransferSpreadsheet) it asks for the parameter value again.

The solution:

dictate the parameter to the query.

That means that instead of placing the parameter in the query, and placing the parameter there (Let's say, SELECT quantity, ..., FROM orders WHERE quantity > Forms!msg_quantity! txt_quantity)

You would place it in the VBA code like that:

Private Sub CreateQuery()

Dim qdf As DAO.QueryDef

Dim sSQL As String

sSQL = "SELECT quantity, ..., FROM orders WHERE quantity > " & Forms!msg_quantity!txt_quantity

Set qdf = CurrentDb.QueryDefs(Me.Form.Caption)

qdf.SQL = sSQL 'This saves the query

qdf.Close

End Sub

It is slower, of course, but if the client really needs exporting to Excel then the client can get it without the unusable "enter parameter value" text box. Trying to hide the text box didn't work in this case.

This way the parameter value is known to the query on export time.

Save changes to the following objects?

Aboout a year and a half ago we changed from using Access (full version) to Access 2007 runtime. It is cheaper (no need to install new Access on each user machine) and it is safer (I can control which objects do the users see) but it is more sensitive, so errors that I didn't have in Access 2003 full version appear all of a sudden in the runtime version. Access 2003 is simply more forgiving, especially if you tell it to load without openeing the database window. It is kind of horrible: you do something, test it, and after uploading the changes the users complains. The following error was in particular weird since I know it was tested in the Access 2007 runtime.
Another pain is to test it & develop on the same machine: each time you change version a prompt comes up with "installing...." which takes at least one minute of my precious time.
So today, after uploading the new version, my users complaint of the following error while hitting the "close" button of a form:
A form appears (see picture) asks you to save the forms. We are talking about users, not developers, and since when can you save changes in Access 2007 runtime?
I thought it was due to the multi user environment, but running a test version from my machine (for which I am obviously the only user) replicated the same problem.
The problem resulted from the following piece of code:

Private Sub Form_Current()

If get_se_listed(Me.pl_security) <> 0 Then
Me.pl_trade.DecimalPlaces = 0
Else
Me.pl_trade.DecimalPlaces = 2
End If

End Sub

Changing the decimal places (an option which is usually done in design time only, and not at run time) resulted in the definition of the form changing as well, and that prompts for the "save" problem. Goggling the issue had left me with only one (!) post about it in the forums, right here.
So first I eliminated the new changes, otherwise the users, non violent people in general, might kill me by the end of the day.
I decided to try and test for myself wheather prompting it not to save on close would actually work:
I added the code for the event "form_close" asking kindly to close without saving
docmd.close acForm, "sub_form_name", acSaveNo

It worked in Access 2003 but it didn't work in Access 2007 runtime version. At lease when I pressed the "no" it prompt me for saving (Again!) but didn't end up with a nasty "Execution for this application has stopped due to a run-time error" which I'm all to familiar with.
Adding the code to the form and not only to the sub_form had not helped. This time it did crash eventually.
In order to avoid the crash you can press 'Yes' and you can press 'No' but do not press 'Cancel'!
Also don't put the line
docmd.close acForm, "form_name", acSaveNo
or acSaveYes in the parent form name.

Trying to play with the Form_current event led to the conclusion that only for an empty function
Private Sub Form_Current()
End Sub
I wouldn't get the prompt. Please note that the form default view is in Datasheet. But I do need the design!

This Round Thing

I work on Ms-Access, VBA and

I have a legacy code and inside it there is a self-written ROUND function, as follows:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = INT(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

I never knew why it was there but as the old saying goes: if it's not broken, don't fix it!

Today I found out that it is broken, i.e. it simply doesn't round to the required precision.

Thanks to Allen Browne, and more specifically to the MVPs of Access, I found out that

a. Access rounding may not existed when the database I am working on was created

b. Even if it did, it uses "banker's rounding" which may not be adequate

c. I shouldn't be using cast as INT, but instead, a different type of CAST.

That's because INT truncates the decimals, which is not my intention.

Like this:

Public Function Round(v1 As Variant, v2 As Byte) As Double

If Not IsNumeric(v1) Then

Round = 0

Else

Round = CLng(v1 * 10 ^ v2 + 0.5) / 10 ^ v2

End If

End Function

And that makes the rounding beautifully done!

The beauty of the function supplied by the MVP's of access is that it can deal with very large numbers, which is a bit problematic in my version.

There isn't enough memory to perform this operation

I usually use MS-Access to program, and I got the following message:

There isn't enough memory to perform this operation. Close unneeded programs and try the operation later.

When I tried to open a certain form (in design view or in form view).

Needless to say, I closed all the programs available (including my messenger) and it didn't help much.

The reason was: too much IIF() in the text boxes of the form. Access had problems loading it.

The solution: Copy the form from the backup.

Move all the IIF required to the back end (in my case: the underlying SQL view).

Now the form loads up quickly and correctly!

Moral of the story: don't use too much IIF().