Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1457
  • Last Modified:

Access 2007 - AfterUpdate not working

Okay, I have to be over looking something simple.

I have a Master form with a sub form and everything is working, except a combo box with an Event Procedure set to take the value of the combo box and move the recordset to the appropriate record.

Private Sub SiteLookup_AfterUpdate()
'Move to the record selected in the control
'Note: Site_Code links the Master and Child forms, SiteLookUp is the name of the combo box.

    Me.RecordsetClone.FindFirst "[Site_Code] = " & Me![SiteLookup]      
    Me.Bookmark = Me.RecordsetClone.Bookmark

End Sub

When I use the search option on the Master form it works, just not the Event Procedure on the combo box.
0
REIHELPDESK
Asked:
REIHELPDESK
  • 12
  • 5
  • 3
  • +2
1 Solution
 
Stephan_SchrandtCommented:
Just for clearance, in event procedure of combobox you go to a specific record (and that is working) but afterupdate is not triggering? Or do you expect triggering the afterupdate when changing the value of combobox?
0
 
REIHELPDESKAuthor Commented:
Correct.  The record navigation is working as designed.

The event procedure is not firing when the combo box is updated.  We start typing a valid entry and it comes up in the list. When we tab or hit enter nothing happens.

I even added Msgbox "Test" to debug, and it does not fire either.  It appears the Event Procedure is not working at all.
0
 
Stephan_SchrandtCommented:
What event do you use at combobox?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TorrwinCommented:
"It appears the Event Procedure is not working at all."

Unfortunately, this happens quite often with Access.  Try copying your code to notepad, then delete the event procedure.  Re-create the event procedure within Access and then paste your code into the new procedure.  Somehow that seems to remind Access that the event is connected to the procedure.
0
 
Stephan_SchrandtCommented:
Forget about the last question, I now got it.
0
 
REIHELPDESKAuthor Commented:
Tried that and nothing.  It still does not fire.

I even used the wizard to lookup a record on the form based on a field, and it did not work either.  
0
 
thenelsonCommented:
Does the after update event in the properties window while the combobox is selected show [Event Procedure]?  When you click on the button with three dots to the right of the [Event Procedure], does the visual basic editor open with the cursor on the Private Sub SiteLookup_AfterUpdate() procedure?

You can change the code to:

Private Sub SiteLookup_AfterUpdate()

    Me.Recordset.FindFirst "[Site_Code] = " & Me![SiteLookup]      

End Sub

Note: If [Site_Code] is text not a number, then the line should be:

    Me.Recordset.FindFirst "[Site_Code] = '" & Me![SiteLookup] & "'"

Me![SiteLookup] should be bracketed by apostrophes. Expanded for clarity:

    Me.Recordset.FindFirst "[Site_Code] = ' " & Me![SiteLookup] & " ' "
0
 
REIHELPDESKAuthor Commented:
When deleting and adding the combo box to the master form, the wizard created the following code:

Private Sub Combo58_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SumOfSite_Code] = " & Str(Nz(Me![Combo58], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

It does not fire.  
screenshot.bmp
0
 
REIHELPDESKAuthor Commented:
Site_Code data type in the table is Number.
0
 
puppydogbuddyCommented:
see this link for the proper way to do a findfirst with a combobox.
            http://allenbrowne.com/ser-03.html
0
 
REIHELPDESKAuthor Commented:
Tried this as well.

The Event Procedure is not firing.

I will make a clean copy of the DB and post it.  I don't think I can post a Access 2007, so I will have to save in Access 2003. Give me a few minutes.
0
 
puppydogbuddyCommented:
Try using the combobox's Click event in lieu of the AfterUpdate event and see if that helps.
If te above did not help...Question:
the record you are doing the findfirst on...is it a main form or subform record?
0
 
REIHELPDESKAuthor Commented:
Here is the database with the necessary tables, queries, and forms.

Open the frmAwardsLookupBySiteCode and scroll through the records with the bottom selector, you will note everything is working.

Now, in the top control (combo box) type a record, "4000018".  It should advance to record 10 of 12.

Thanks, Rick Cass
Sample.zip
0
 
REIHELPDESKAuthor Commented:
The record we want to move to, based on the value in the combo box, is part of the recordset of the master form. We can get to it by doing a form search or navigate to it.
0
 
thenelsonCommented:
Is it the Site_Code1 (Combo60) on the frmAwardsLookupBySiteCode form? It is working for me.
0
 
REIHELPDESKAuthor Commented:
To determine if it is working correctly.

One must be able to enter a valid Site Code and the the master form moves to the correct record, the associated information will be displayed in the sub form.

When one enters a valid site code and hits enter, the form should advance to the correct record and NOT to the next record.  The next record is not the correct record.

For Example.  If one enters 4000018 you should be moved to record 10 and record 2.
0
 
thenelsonCommented:
I select 4000018 for example and the main form and subform jumps to 4000018
Screen-Capture.gif
0
 
REIHELPDESKAuthor Commented:
Okay, I believe you. Now I have to figure out why it won't work for us.

Once you enter 4000018 and it jumps to record 10, what happens when you enter 4000004?

What version of Access are you using?
0
 
REIHELPDESKAuthor Commented:
We can not get it to work like you.

When we open the form, enter 4000018 in the Combo box 60, it does go to the NEXT record but not the correct record (Record 10 of 12).

When we enter another value we are only taken to the nexxt record and not the corrent one.
0
 
thenelsonCommented:
V 2003
See if the database you uploaded works for you. If it does, the event was probably attached to the code when you created the mdb or the form in the 2007 version is corrupted.

To make sure the event is connected to the code, do this (as I explained before):  Does the after update event in the properties window while the combobox is selected show [Event Procedure]?  When you click on the button with three dots to the right of the [Event Procedure], does the visual basic editor open with the cursor on the Private Sub SiteLookup_AfterUpdate() procedure?

To correct a corrupted database or form:  Going past step 7 will probably not help for this problem. I include the additional steps for your future reference.

Steps (approximately in order) to use to fix a corrupted Access database. (Test after each step.)

1) CREATE A BACKUP OF THE CORRUPT DATABASE. (Just in case)

2) Compact and repair

3) Use jetcomp.exe  http://support.microsoft.com/default.aspx?scid=kb;en-us;Q273956

4) decompile: in run: msaccess "dbPathName.mdb" /decompile
   Compact/repair:  in run: msaccess "dbPathName.mdb" /compact
   compile: in VB editor: debug> compile
   Compact/repair
   http://www.granite.ab.ca/access/decompile.htm

5) Use a backup (you have a lot of those -- yes?)

6) If you can identify one or a few forms or reports causing problems, copy the problem form/report from a backup.  Or create a new form/report and copy all the objects in the old form/report to the new one. Copy the code from the old form/report using the undocumented
    Application.SaveAsText acForm, FormName, "C:\" & FormName & ".txt"
then paste that from Notepad into the new form/report.  If a module, copy the code using SaveAsText, create a new module, paste the code into the new module.


7) Create a new database, use File> Get external data> Import to get all objects of the damaged database and set up the options again.

8) Try a recovery tool:
   www.officerecovery.com/access/index.htm
   www.mvps.org/access/tables/tbl0018.htm

9) Hire a company to repair the database  http://www.pksolutions.com/services.htm

Additional information:
How to Troubleshoot/Repair Damaged Jet 3.0 and Prior Databases
http://support.microsoft.com/support/kb/articles/Q109/9/53.asp

How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;306204

Corrupt Microsoft Access MDBs FAQ
http://www.granite.ab.ca/access/corruptmdbs.htm
Screen-Capture.gif
0
 
puppydogbuddyCommented:
I have Access 2000 and it is working for me also. It sounds your file has a damaged link somewhere.  Try doing a compact  and repair, then test it.  MAke sure your code fully compiles w/o error.  
0
 
REIHELPDESKAuthor Commented:
Okay. I created a new database and imported my tables, queries, forms, etc from the backup and everything appears to be working.  Got to love a corrupted database.

Thanks, Everyone.
0
 
REIHELPDESKAuthor Commented:
Great job.
0
 
thenelsonCommented:
Some steps to follow to reduce the chance of front end corruption:
1. Do not modify code while code is running. this includes a code break and timers.
2. Compile your code often.
3. Decompile and recompile frequently. I have a batch file which will do this automatically: Save backup, decompile, compile batch file (www.thenelson.name/#Decompile)

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now