Access 2000-2007 conversion - VB failing?

I have a relatively simple contact database written with MS Access 2000 and 
works fine running under Win XP but in Access 2007 running under Windows 7 I 
have some problems with one of the forms.

I use a Combo Box (Combo206) at the top of a form to choose the 'Name' field 
from a table so that the record can be displayed in the form body. When run 
under Access 2007 the list of 'Name' fields is displayed but the form does 
not update with the contents of the related record. I've tried converting the 
entire file to the new Access 2007 format but this didn't resolve the problem.

How can I fix this? I suspect the problem is with the VB code. Here's the 
code which works under A2000:

Sub Combo206_AfterUpdate()
On Error GoTo Err_Combo206_AfterUpdate
   ' Find the record that matches the control.
   Me.RecordsetClone.FindFirst "[ID] = " & Me![Combo206]
   Me.Bookmark = Me.RecordsetClone.Bookmark

Exit_Combo206_AfterUpdate:
   Exit Sub

Err_Combo206_AfterUpdate:
   MsgBox Err.Description
   Resume Exit_Combo206_AfterUpdate
End Sub

Additionally the code that used to set focus to the Combo Box now doesn't:

Private Sub Form_Open(Cancel As Integer)
Combo206.SetFocus
End Sub
0
Utf
11/25/2009 4:40:02 PM
access.gettingstarted 618 articles. 1 followers. Follow

6 Replies
658 Views

Similar Articles

[PageSpeed] 21

Try something else. You need to check the DAO 3.6 reference in the VBA if
you're using DAO.
Anyway, your code aren't right.

Booleanboy wrote:
Sub Combo206_AfterUpdate()
On Error GoTo Err_Combo206_AfterUpdate
   Dim rs as DAO.Recordset
   Set rs = Me.RecordsetClone
   ' Find the record that matches the control.
   rs.FindFirst "[ID] = " & Me![Combo206]  'Assuming number datatype
       if rs.nomatch then
         msgbox "No record matching FindFirst"
       else
       Me.Bookmark = rs.Bookmark
       end if
     set rs = Nothing    'destroy
Exit_Combo206_AfterUpdate:
   Exit Sub

Err_Combo206_AfterUpdate:
   MsgBox Err.Description & " - Error No:  " & Err.Number    'please include
error number
   Resume Exit_Combo206_AfterUpdate
End Sub

>Additionally the code that used to set focus to the Combo Box now doesn't:

If it doesn't work, do you have any others events that might trigger it? or
cause the setfocus to another control? The error may cancel the setfocus.

Private Sub Form_Open(Cancel As Integer)
Me.Combo206.SetFocus
End Sub

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1

0
AccessVandal
11/26/2009 1:33:50 AM
Typo:

Sorry, recordsetclone does not work with nomatch.

      if rs.EOF then   'change it to rs.EOF
        msgbox "No record matching FindFirst"
      else
      Me.Bookmark = rs.Bookmark
      end if

Booleanboy wrote:
snip..

Or like this.....

  Dim rs as DAO.Recordset
  Set rs = Me.RecordsetClone
  ' Find the record that matches the control.
With rs
      .FindFirst "[ID] = " & Me![Combo206]  'Assuming number datatype
      if .EOF then
        msgbox "No record matching FindFirst"
      else
      Me.Bookmark = .Bookmark
      end if
End With
    set rs = Nothing    'destroy

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1

0
AccessVandal
11/26/2009 9:24:48 AM
Thanks for assisting me - it is much appreciated.

Forgive me, I really don't have much experience here but if I substitute 
your code:

Sub Combo206_AfterUpdate()
Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
' Find the record that matches the control.
With rs
..FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if .EOF then
msgbox "No record matching FindFirst"
else
Me.Bookmark = .Bookmark
end if
End With
set rs = Nothing 'destroy  
End Sub

for my original Sub Combo206_AfterUpdate() code it doesn't work here. Am I 
doing this correctly?

Just to be clear, the [ID] field is a unique numeric which is the Primary 
Key for the table. The value typed into or selected from a list in the 
ComboBox is the text content of the [Name] field associated with [ID].

Thanks again.


"AccessVandal via AccessMonster.com" wrote:

> Typo:
> 
> Sorry, recordsetclone does not work with nomatch.
> 
>       if rs.EOF then   'change it to rs.EOF
>         msgbox "No record matching FindFirst"
>       else
>       Me.Bookmark = rs.Bookmark
>       end if
> 
> Booleanboy wrote:
> snip..
> 
> Or like this.....
> 
>   Dim rs as DAO.Recordset
>   Set rs = Me.RecordsetClone
>   ' Find the record that matches the control.
> With rs
>       .FindFirst "[ID] = " & Me![Combo206]  'Assuming number datatype
>       if .EOF then
>         msgbox "No record matching FindFirst"
>       else
>       Me.Bookmark = .Bookmark
>       end if
> End With
>     set rs = Nothing    'destroy
> 
> -- 
> Please Rate the posting if helps you.
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200911/1
> 
> .
> 
0
Utf
11/26/2009 5:40:01 PM
Sorry, was on holiday.

So what did not work. Can you tell us which line?

I'm not certian that ".EOF" work with recordsetclone. Your combobox
"Combo206" must be unbound to the form's recordsource or the table.

You might want to chnage it to 

Sub Combo206_AfterUpdate()
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = currentdb
set rs = db.openRecordset("YourTableName") 
' Find the record that matches the control.
With rs
.FindFirst "[ID] = " & Me![Combo206] 'Assuming number datatype
if .NoMatch then
msgbox "No record matching FindFirst"
else
Me.Bookmark = .Bookmark
end if
End With
rs.close
db.close
set rs = Nothing 'destroy  
set db = Nothing
End Sub

Booleanboy wrote:
>Thanks for assisting me - it is much appreciated.

-- 
Please Rate the posting if helps you.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1

0
AccessVandal
12/1/2009 12:51:33 AM
Does any code on any form run? Code does not run in 2007 unless your database
resides in a folder that has been declared a “trusted” location.

To trust your folder, click:

Office Button (top left)
Access Options (bottom of dialog)
Trust Center (left)
Trust Center Settings (button)
Trusted Locations (left)
Add new location (button)

-- 
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1

0
Linq
12/1/2009 1:51:05 PM
Thanks - this was exactly the problem. In my defence, the default colour 
scheme for 
Access 2007 is so pale that the notification message wasn't very visible. 

I knew a warning was displayed the first time an 'old' file was run, I 
didn't appreciate that the file was subsequently locked out without 
displaying the message. I've modified the Trust Centre settings now and 
all seems to work OK.

Thanks again - I'd been puzzling over this for a couple of weeks!
0
Utf
12/2/2009 4:42:01 PM
Reply:

Similar Artilces:

Accessing Windows Office Templates
There are some very nice templates at the Windows Office Template Gallery. I would like to download (mostly Word, but a few Excel) some of these templates, but the format comes out with a ".cab" extension that Office v.X doesn't understand. Is there any way to get these templates? Unfortunately, they make the Mac template archive look very weak. TIA Len Hi Len, Microsoft has a Mac template download page: http://www.microsoft.com/mac/resources/templates.aspx?pid=templates There's a link to the on-line gallery on that page. It looks like a newly re-designed gallery to m...

How to link with an Access DB
Hi, I have an Acces DB with many tables. I need to choose the name of a customer in a cell of Excel. For example, in acces I have this tables: Table1 Id Name Last Name City I need to choose the last name from a drop down menu in a spreadsheet and then in other cell I need to put all the data regarding the last name that I choose. I hope to be exaustive, and sorry for my english. :-) Many Thanks Stefano ...

exporting outlook 2000 pst files to a database
I need to export outlook 2000 to a database- attachments and all. Is there a simple way to do this so you can search and retreive messages and attachments in a database form? Thanks Jeff You can export individual folders to access or excel to have them available for searching. I have never tried to export my entire .pst file to access or excel however. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer Jeff <laacid@yahoo.com> asked: | I need to export outlook 2000 to a database- ...

Access 97 can't resize database window
My database window with the listings of forms tables etc was adjusted to a smaller width, but resizing it is completely disabled and renders Access 2007 utterly useless for me. Is there anyway to 'reset' the window? ...

Access 2003 resets form variables during debugging while the form is open
Hello, I am using MS Access 2003. (FWIW, this is about an ADP project, not MDB.) A form has a variable that is initialized (by some function call) in Form_Load() and then stays unchanged for the form's entire lifetime. (In a language more advanced than VBA, it would be a constant, not a variable; unfortunately, in VBA I cannot initialize a constant by non- constant expression). Specifically, in my case it looks like this: Private sTempFileName as String ... Private Sub Form_Load() ... sTempFileName = GenerateTempFileName(...) ... End Sub Priv...

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Can't move things to deleted items: The item could not be deleted. It was either moved or already deleted, or access was denied
This is weird. It looks like I have one user with a damaged 'deleted items' bin. Everyone else is fine. This user had opened up an accout of someone who left the company a while back and apparently tried to delete a few thousand spam messages that they had. Their system eitehr locked up, or they got sick of waiting and did something to stop it, when they did we could no longer mount the information store. I ran several things including an eseutil /p on the database, and then removed the log files and ran the ISINTEG -s server01 -test alltests -fix, which found 2 or three problems and f...

conversion #3
I am trying to upgrade from Outlook 2000 to Outlook 2003 on a new computer. When I attempt to export the data in my email files (inbox, sent,etc.)I do not have the "received" information exported - when mapping the fields I am unable to map several of the email fields; therefore, they are not exported. I have been unable to find the normal email fields to use in my mapping. How do I map normal email fields in an export operation? If I do not export this information my new computer has no idea when I received my old emails/ In the old days, the easiest way to keep all da...

Outlook 2000 not opening.
I am having a major problem with outlook. Can anyone tell why Outlook will not open when selecting it. When I check in the processes there is an instance of Outlook.exe there but it is not doing anything. When a different user logs onto the machine Outlook will open fine with no problems. I have deleted the users profile and recreated it but to no use. I have tried comparing the local registry keys of the two users and they seem to be the same. Can anyone please help me as I have ran out of things to try. Thanks in advance. Justin Does Outlook start in /safe mode? (I'm assumi...

OWA. CA's. and Exchange 2007
The other day, I had to renew the Certificate on my Exchange 2007 server. Everything seemed to go alright, but eventually I received complaints that outbound email wasn't being sent. Looking at EMC showed all of the email queued up and waiting for something. After I relaxed the settings on my router (Allowed all outbound SMTP instead of restricting it to the Exchange server sending to MxLogic) all of the outbound email went though. I also was receiving reports of Webmail being broken (which was halfway fixed with the help of http://www.microsoft.com/technet/support/ee/transform.aspx?Pr...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Access 97/2000/2003 comparisons
I have a rather large application that uses an access 97 database (DAO). We also have a version that works with access 2000, Oracle, msde, and SQLServer (ADO). We would like to retire the DAO version of the product, but there are places where the ADO version is much slower compared to where we run DAO seeks (very noticeable when looping). So, I have a few questions. 1. Are there any tips/tricks to speed up ADO queries to compare with DAO seeks? 2. Would there be any benifit in using access 2003 over previous versions of the software besides the added features (xml support, etc). 3...

Outlook 2007 addresses
How can I find the list of addresses I have sent emails to? When you start a new email you can put a letter in the To line and list will come up. Where is that list? How can I print it out? Thanks Matt that is the autocomplete cache. You would need a utility to print it out. http://www.outlook-tips.net/archives/2004/20040621.htm http://www.slipstick.com/problems/nicknamecache.asp -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips.net/beginner/ Outlook 2007: http://www.slipstick.com/outlook/ol2007/ Out...

Outlook 2007 Install gone wrong
We recently switched e-mail hosts and I successfully (mostly) switched over my company's accounts from a POP server to our new IMAP server. But I'm trying to set up one more account and am running into issues. I put in all the same settings as for our other accounts, but it won't allow me to create or copy any sub-folders into the InBox folder for this account (it tells me it cannot and this is "probably due to a limitation of your imap server")...but I can do so for the other accounts on the same server. Also, when I try to view the Imap Folders, it says "The...

How do I get Powerpoint 2003 fade in 1 by 1 in 2007
I frequently used the 'fade in one by one' feature in Powerpoint 2003, which allowed me to fade in individual bullet points by way of a click of a mouse. Not only is this feature now not standard in 2007, but I can't work out how to create it: anything that I find, tends, instead, to fade the bullet points in on some kind of automatic time schedule, rather than - as I want - on a mouse click. I want only to use text, no sounds, no pictures, and nothing fancy. Currently, I am reduced to copying my material into an old Powerpoint 2003 presentation, but this is silly....

users with restricted access
We have some users that we have directed to only get their information from a report that has been set up. Because of that, I set up a parameter query to make the information more easy to see. The parameter query prompts for last name or broker #, is there a way, when the last name is entered to include Jr's & Sr's? Or should this be another field in the table to make the last name field more accurate? ...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

no access to send internet email
Hi, Exchange 2003. Can a mail enabled user be restricted to send internal email only and not internet email? Nich Hello, > Exchange 2003. Can a mail enabled user be restricted to send internal email > only and not internet email? yes, that is possible. There are multiple ways to archive this goal. Depents on your infrastructure,too. If you have mailing to external over an SMTP Connector, try this: First look into your SMTP Connector, you will find Delivery Restrictions there. It is self explaining. If you have trouble with that or you won't find it, then i will need additi...

Setup of CRM Server fails with unkown error
I get the following error message: Failed to install Microsoft Customer Relationship Management Server. Failed to provision your initial business. Failed to create organization. Unknown error. (80040E07). What I have already tried to do: I tried to install on our second Domain Controller (Win2K3 English), on our Primary DC (a German SBS Premium Edition), on a German Win2K3 German membership server. Always same results: the setup program can do lot of things, it can also create a new organizational unit in our ADS, create some icons in the start menu. But then it fails unexpectedly. Always...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Excel 2007 PC damages Mac Excel 2008 Files
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel When I open a Mac Excel 2008 file on Excel 2007 and I can work with the file, however when the file is returned to the Mac, it no longer works correctly, leading to a crash as soon as I try to save or eventually it might change the file to look like a bunch of letters and characters. Here is the error report: <br><br>Microsoft Error Reporting log version: 2.0 <br><br>Error Signature: <br> Exception: EXC_BAD_ACCESS <br> Date/Time: 2010-01-15 17:05:56 -0600 <br> Application N...

SQL Server Connection Failed
Hi everybody, this is my scenario: 1. Windows Server 2008 2. WSS 3.0 + MOSS 2007 3. SQL Server 2008. i'm having this problem: "Login failed for user 'sa'. Reason: An error occurred while evaluating the password. [CLIENT: 72.55.191.137]". in this momment i can't see any application from sharepoint. somebody knows how can i do? i don't change the sa password. i don't change anything in the sharepoint configuration. since two days ago i'm having this problem. pls, is very urgent, because i have to deliver this server the next monday to the...

Is Outlook 2000 compatiple with Windows XP Home?
It seems to be having problems if it is... something like Outlook has experience a problem and must close. I rand the compatibility wizard, but it didn't help. Can you? Hi, I have Outlook 2000 installed on my XP Home machine and it works fine. >-----Original Message----- >It seems to be having problems if it is... something like >Outlook has experience a problem and must close. I rand >the compatibility wizard, but it didn't help. Can you? >. > Rod <anonymous@discussions.microsoft.com> wrote: > It seems to be having problems if it is... something...

Installed Font does not display in Word 2007
Only 6 of of 8 recently installed TTF fonts will display in Word 2007 Are the missing ones variants like Italic or Bold? On Dec 25, 1:28=A0pm, GWHA <G...@discussions.microsoft.com> wrote: > Only 6 of of 8 recently installed TTF fonts will display in Word 2007 ...

OL 2000 should not autom send/receive on start
Hi, I found it in OL 2002 but not in OL 2000. How can I define, or better disable, that OL is trying to send/receive mails on starting and closing OL? And it should never automatically try every x minutes to do so. I found this option on OL2000 but my impression is, that OL is still polling the pop3 account for new mails. Any help on this would be appreciated. Thanks a lot Hans Tools->email accounts->send/receive settings. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail se...