Ok I'm fairly new to VBA so please forgive me if I'm axing a stupid question.
I'm trying to find an event that fires when I move to a new record on a form in Ms Access AFTER the first or next record is displayed, not like the event OnCurrent that fires before the first or next record is displayed.
You see I want to refresh and display some unbound picture fields in a form from a file reference (hyperlink).
Sure my command button can refresh the pictures, but I would like to refresh the pictures automatically. If I don't refresh the pictures at all they stay the same on each record.
Thanks for the attached zip file with a picture form example.
I might have to use something from that example.
However I'm curious as to why my code does not work, here it is:
Private Sub Form_Current()
UpdatePictures
End Sub
Public Sub UpdatePictures()
bitFullPicture_Name_Click
bitFacialPicture_Name_Click
End Sub
Public Sub bitFacialPicture_Name_Click()
If bitFacialPicture = True Then
oleFacialPicture.SourceDoc = "C:\Face\"+txtPopularName + ".jpg"
oleFacialPicture.OLETypeAllowed = acOLELinked
oleFacialPicture.Action = acOLECreateLink
Else
oleFacialPicture = Null
End If
End Sub
Public Sub bitFullPicture_Name_Click()
If bitFullPicture = True Then
oleFullPicture.SourceDoc = "C:\Full\txtPopularName + ".jpg"
oleFullPicture.OLETypeAllowed = acOLELinked
oleFullPicture.Action = acOLECreateLink
Else
oleFullPicture = Null
End If
End Sub
The variables:
bitFacialPicture and bitFullPicture is a checkbox, with an event associated with it, that I use to determine if a face and/or full picture exists for a person.
I either get the following error right away, or sometimes if I try to turn on the second picture.
Run-time error '2785'
The OLE server wasn't able to open the object
Usually when I only turn one of the two pictures (checkboxes) I do not get an error, until I provoke the system by repeatedly pressing an update command button, that goes to UpdatePictures(), and then I get this error:
Run-time error '2753'
a problem occured while Bill Gates was comunicating with the OLE server or ActiveX Control.
Obviously I'm doing something wrong here, allthough me a novice in VBA for access thought is was straight forward, most of the code was examples from the help file.
Anyways I would appreciate any advice on my problem.
The record number is not usable for anything. If you want to be able to go back to a record for some reason, you will need to store the record's bookmark property.
Are you absoultely sure about this? How can you possible know what I, or what anyone else would/could use it for?
Well it is usable to me, since I have an unbound field which content is made by VBA. Specifically the content that is generated is JavaScript code, a dimensioned variable, containing this record number amongst many other things, which I can cut and paste into my HTML pages.
Anyways somebody gave me the simple solution in another forum.
I still would appreciate if somebody would give me a reason for why my above picture code gives me errors.
DKDiveDude , Have a look at you No of posts, then Pats. She knows Access, and her advice should always be viewed as trying to instruct you in Access "Best Practise"
Dont think your gonna get much more help here buddy
As records are created they aren't given a specific ID within the table, they don't show in a specific order.
If you look at a typical table (unsorted, unfiltered) then the records can be in a a certain order, add a few records and, more often than not, the new records could be anywhere in the table the next time you open it.
The Bookmark propery should be used to identify a specific point in your Recordset.
Sure I'm a newbie. Does that justify your arrogance buddy!
"Have a look at you No of posts, then Pats. She knows Access, and her advice should always be viewed as trying to instruct you in Access "Best Practice"
More arrogance, maybe I posted hundreds of posts in other forums. You know this is NOT the only VBA Access forum in the universe. Quote: "Don't you know that assumption is the mother of all f......!". From the movie "Under Siege 2 - Dark Territory"
I do not want your help if my life was dependent on it. You make me so mad, that I could commit a crime if you where right here next to me.
To all you experienced programmers:
Is it really necessary to elaborately explain why I want the help, before it is provided?
To: Mile-O-Phile
Thank you very much for your appreciated reply. And I mean that! After your reply I finally realize that it just seems like a misunderstanding. You really clarified the whole thing. Thanks - I wish there was an option here to give out some kudos. You deserved it.
You see I was talking NOT talking about the ID number that is giving to a record, usually an autonumber. No I was talking about the ABSOLUTE record number, which tell if the current record is the first, last, or anywhere in between.
Let me first say, that I have already gotten a VERY simple solution to my question, here it is:
Dim CurrRecNo As Long
CurrRecNo=Me.CurrentRecord
Let me now try and explain, again why this works for me.
I have a form that displays data from a table. The data is sorted by a person’s first name. When I insert a new record on the form, it is sorted by first name again. I then have an unbound field which content is generated by VBA. Specifically the content is JavaScript code (a dimensioned variable) I paste into an HTML page. Example:
PersonData[1]="John|Male|07261973|2|4|0||3"
When the next record it will may show:
PersonData[2]="Mary|Female|12031981|5|1|1|1|"
I then have another unbound field that gathers, at the push of a command button, JavaScript for all the records in the table, example:
Persons=2
Dim PersonData=New Array()
PersonData[1]="John|Male|07261973|2|4|0||3"
PersonData[2]="Mary|Female|12031981|5|1|1|1|"
That JavaScript code I paste into my HTML document, and other code is then processing the data and dynamically changing the HTML page processing this simple database.
I definitely learned something from this.
Next time I post a thread for help, I need to be VERY VERY VERY specific, to avoid misunderstandings, especially from experienced users, who it seems like, thinks on a different plane that me Newbie. Which is not bad, just frustrating when a rookie like me is trying to get help for what seems like to be a simple question.
I'm disappointed you think what I said was seen as arrogant. I was pointing out, that as a "Newbie" the help you receive here is SUPPOST to help not hinder.
Quote:
"The record number is not usable for anything"
Wow what a statement!
Are you absolutely sure about this? How can you possible know what I, or what anyone else would/could use it for?
That’s Arrogance
I’ll stick with my first observation, Pat know Access
A little homework on this forum would make that very obvious
You see I was talking NOT talking about the ID number that is giving to a record, usually an autonumber. No I was talking about the ABSOLUTE record number, which tell if the current record is the first, last, or anywhere in between.
Dude, what the heck IS the absolute record number? More specifically, what do you THINK it means? I have been working with Access since v2.0 (yeah, I know - I'm giving away my age...) and I have no idea on God's Green Earth what you mean by absolute record number in the context of Access. If there IS such a thing, it is not visible to us low-life users. Only the exalted gods of the great Microsoft temple know the answer to this question.
In practical terms, any meaningful position of the record depends on the query or table keys used to retrieve it. Knowing the ordinal position of the record is a multi-valued sort of thing. And there was a pun in that answer... "sort."
The way to answer your question depends on what you are trying to find out. The "FIRST" and "LAST" (and other positions) are dependent on how you are currently looking at the table. You can literally change the identity of the table's primary key (assuming you had two viable keys for this purpose) without rewriting anything except the index associated with the new key. Before and after the design change, if you opened the table, you would see the records in two different orders. Yet the table's actual records wouldn't have budged an inch. (Well, .... OK, they wouldn't have budged a byte.)
If you think of a table as always being an ordered collection of records, that view is correct in Access but "ordered" doesn't mean what you might think it means. Internally, the order of record appearance depends on factors so widely variant as (1) how long it has been since you compressed the database and (2) whether a prime key was defined at the time you did the compression and (3) whether any record has been updated with respect to its primary key field since the last time the database was compressed.
Otherwise, Access stores records in a shared table in a totally non-predictable order. As a matter of fact, after a database compression, the physical-address order of appearance of a record within a table can jump from LAST to FIRST - or vice-versa - without a change in the nature of the keys.
Looking at it from another viewpoint, SQL (which is technically one of the important components for MOST modern databases when you get down to first principles) does not define the semantics of keyword FIRST (or LAST, either) unless it is in the context of the clauses "ORDER BY" or "GROUP BY." If there is an inherent order in record retrieval, it is strictly implementation dependent. And for Access, I don't think there is such an inherent order. In the case of Access, it is instantiation-dependent, which is even worse!
I'll try to explain this in an example.
Consider the CurrentDB.OpenRecordset(stSQL) statement, which could open a recordset based on an SQL string. Now consider the following cases, and in this case, assume the table has no primary key defined.
stSQL = "SELECT * FROM myTable;"
stSQL = "SELECT * FROM myTable ORDER BY myAutoNumber;"
stSQL = "SELECT * FROM myTable ORDER BY SocSecNum;"
stSQL = "SELECT * FROM myTable ORDER BY LastName, FirstName, MiddleName;"
Now use the OpenRecordset method followed by a myRecSet.MoveFirst method.
All four of these could be valid uses of OpenRecordset. All four of them would give different FIRST records.
The first record ordered by autonumber would tell you the oldest record in the table in terms of record entry order.
The first record by SocSecNum would tell you the record with the lowest social security number.
The first record involving name parts would tell you the first entry in the list when sorted in alphabetic order by first name, last name, and middle name.
The one that has no ORDER BY clause will merely tell you which record happens to occupy the first record currently defined in the record list associated with the TableDef for myTable. But this is totally arbitrary if there is no key associated with the record. It is the first record of a nearly random ordering. If you are familiar with 3GL programming languages that have a "HEAP" concept, you are asking, essentially, for the record in the list with the lowest address order from the HEAP.
I hope you understand what I'm trying to explain, and please don't take this as intentionally arrogant. It is just that you appear to have a conceptual hangup that needs clarification.
Now, one last comment that might help clarify what you wanted to know.
You can find the RELATIVE position of an entry with two domain-aggregate functions.
to find out where in the mix this record sits. If the SQL equivalent is .... ORDER BY [myField] .... then the following are true:
If loRecPos = 1 then the selected record is first in terms of that sort order.
If loRecPos = loFldCount then the selected record is last in terms of that sort order.
If 1 < loRecPos < loFldCount then the selected record is somewhere in the middle of the sort order.
Knowing the record order is NEVER absolute. So when you got an answer you didn't expect, you went temporarily ballistic. Please reconsider your response.
You are just applying what you learned here. Like I said, the order depends on the SORT order.
One more bit of education...
You DO realize that the recordnumber you get this way is the RECORDSET record number, not the table's record number?
You don't need to understand SQL to understand that I was simply telling you that the answer is RELATIVE, not ABSOLUTE. Your solution is quite adequate for your needs, which is truly the most important result of this exercise, despite the little misunderstanding that occurred while trying to figure things out.
Can I run an event in a from when I insert a record?
I have a procedure to copy the values from a field in the previous record to the new one and I call it on the BeforeInsert event. However it does not execute until I type into a field on the form but I'd like it to execute before this. Is this possibe in Access 2003?
