VBA Not Working... at all?

[Reese]

I am new to Access and am currently creating a database system to manage program booking, invoices & scheduling. I have been able to learn the basics pretty easily and have a solid grasp of how to create forms, link them to different parts of a database, etc.

Where I hit stumbling blocks is the coding. So far I've been able to find solutions to many of my problems online, but now nothing works.

Literally. All of the VBA event procedure codes that I had written or copied from online no longer work--even if they did just a few days ago.

Here are two examples of the codes that were working and no longer are:

**Example 1 (This is so that if a box is checked, the billing contact info is copied over to the event contact info automatically)***
Private Sub Check85_AfterUpdate()

If Me.Check85 = True Then
Me.[Event_Street] = Me.[Billing_Street]
Me.[Event_City] = Me.[Billing_City]
Me.[Event_State] = Me.[Billing_State]
Me.[Event_Zip] = Me.[Billing_Zip]
End If

End Sub

***Example 2 (This is to create a button that will open a print preview of a report that just relates to the current entry--not all entries)***

Private Sub cmdPrintEdReport_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[ID] = " & Me.[ID]
DoCmd.OpenReport "ZM_Education_Invoice", acViewPreview, , strWhere
End If
End Sub


Does anyone have any suggestions of where things got messed up? Thanks.

 

[pbaldy]

If code isn't running, the database probably isn't in a trusted location, or you haven't explicitly enabled it.

 

[Reese]

The code was working as of Thursday, then wasn't working on Sunday and it's in the same location.

Is there some type of command or setting that I may have accidentally changed that would disable all of the codes?

 

[pbaldy]

Not that I know of. Is there a security option below the ribbon? Can you post the db here?

 

[mdlueck]

Before going to the trouble to post the DB file, perhaps in the VBA editor set a Break Point somewhere where you expect code execution to pass through, perform the task you believe should run the code, and see if the break point pauses execution of the code. If not, it ain't running the code.

 

[Reese]

Thank you mdlueck!

I clicked the "Toogle Break Point" option at the first "End Sub" that the user would encounter on the form. (I'm assuming that's what you meant.)

Good news: All the code now seems to work in all of the forms. (Even the ones that don't relate to that particular "End Sub".)

Bad news: I'm completely confused as to what happened. I'm also worried that something like this may happen in the future. Do either of you know what I might have accidentally done to cause the problem initially?

I also noticed that, for some reason, the ID numbers associated with each entry changed from starting at 1 to starting at 3. Last I knew, as of Thursday, they started at 1. Any idea how this might of happened, how I can change it back and how to avoid it in the future?

It doesn't really matter for the moment, because the current entries are fake and just for testing. I want to avoid any such changes once the database actually goes into use.

Thanks again!

 

[Adam Caramon]

You probably are already aware of this, but as you said you're new to VBA I will mention it just in case.

This:

Private Sub Check85_AfterUpdate()

If Me.Check85 = True Then
Me.[Event_Street] = Me.[Billing_Street]
Me.[Event_City] = Me.[Billing_City]
Me.[Event_State] = Me.[Billing_State]
Me.[Event_Zip] = Me.[Billing_Zip]
End If

End Sub

Only triggers when Check85 has been updated. As such, if you click checkbox 85 and everything looks like it is working, and then you close the form and reopen it and now it no longer looks like it is working, that is why. You would need a separate Sub to check on each record (On_Current, for example).

 

[mdlueck]

A break point allows you to stop at that line of code (LOC) and perform debugging operations such as stepped execution where the VBA code pauses after each LOC it executes, or to enter real-time commands into the Immediate window, or you can even put a Watch on a variable / object to see what is inside it.

Anyway, a break point a the End Sub LOC might not exactly allow usefulness, beyond the fact that now we know VBA code is being executed in your database.

In the VBA window, perform a Debug \ ClearAll Breakpoints to turn the breakpoint back off, so your application does not stop on that LOC now each time.

See if then the database works as expected.

"ID numbers changing" I believe is a different question. I see nowhere in this thread any background on ID's and days of week being associated. So more information needed.

 

[Reese]

In the VBA window, perform a Debug \ ClearAll Breakpoints to turn the breakpoint back off, so your application does not stop on that LOC now each time.

See if then the database works as expected.

mdlueck: I cleared the break points and everything is good to go, thanks. As for the ID change, you're right it wasn't mentioned previously. I included it because I noticed it after I first started this thread. If you think I should start a separate thread for it I can certainly do that.

In the VBA window, perform a Debug \ ClearAll Breakpoints to turn the breakpoint back off, so your application does not stop on that LOC now each time.

See if then the database works as expected.

Adam: Thanks for the suggestion. For me, the situation is actually opposite situation. When I first click Check85, the information isn't transfered over (at least visibly in the form). Once I click the Save button I coded and/or move to a new entry in the form series (i.e. move from ID 3 to ID 4) it then appears. Which is fine by me, though if it would visibly update in the form immediately that would be nice.

The On_Current suggestion doesn't seem to work. Could you explain this function more? Or any other functions that you think might work better?

 

[mdlueck]

As for the ID change, you're right it wasn't mentioned previously. I included it because I noticed it after I first started this thread. If you think I should start a separate thread for it I can certainly do that.

How about you do that, as "unexpected behavior" does not match the origional subject line of "VBA Not Working... at all?"

 

[Adam Caramon]

The On_Current suggestion doesn't seem to work. Could you explain this function more? Or any other functions that you think might work better?

If you can post a version of your database, it will probably much easier to explain.

 

[Reese]

Okay, now I'm really confused. The codes have stopped working again, despite working when I last worked on the program yesterday. And now the Break Point solution doesn't seem to be working.

The database is currently 7.12Mb. I created a copy and removed most of the tables, forms and reports so that it was just a sample, but for some reason the database size hasn't changed. I think the Access gods just hate me.

Are the deleted forms, etc. in the copy still hidden somewhere? Or is there a way for me to export just a few of them into a new database? Thanks.

 

[mdlueck]

but for some reason the database size hasn't changed.

Here you go...

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk/forums/showthread.php?t=219948

Do that on a BACKUP copy of your database file.

 

[Reese]

The back up for my database doesn't look like it's been updated since December. How do I update it? Sorry that I'm needing so much guidance here. I feel like a n00b (though, I guess to Access, I am).

 

[Reese]

Never mind, I found how to back up the database. I'll post a sample of my file in a little bit.

 

[Reese]

Okay, rather than trying to work all of that out, I was able to use the compact database function, then create a zip file from the database.

The attached file includes all of the database tables, forms, etc. Please just use the "ZooMobile_Booking_Form", the "ZooMobile Spreadsheet" and the "ZM" reports that are associated with it. That's the most complete section of the database and if we can figure out the problem and solution there, I can fix it in the other sections myself.

Please excuse any Access "faux pas" that I have made... I know some parts of the design or code may be inelegant and disorganized, but as a beginner I'm happy if I can make it work reliably.

 

[mdlueck]

The back up for my database doesn't look like it's been updated since December. How do I update it?

Just exit out of Access (to make sure no application has the file open) and make a copy of the file at the Command Prompt or in Windows Explorer. Nothing rocket science in the least.

 

[mdlueck]

All right, I have your DB up on my machine, have the ZooMobile_Booking_Form open.

What should I be looking for that "VBA is not working"?

 

[Reese]

The easiest test is the "Print" buttons on the bottom. Each one should open the relevant report in print preview mode, just displaying the one ID that is currently displayed on the form. The Save at the bottom should also automatically save and update all of the fields without having to move to a new ID on the form.

The "Event Address Same As Billing" and the "Event phone same as Billing" checks also have codes that were working (copying the information from the billing address and phone to the event address and phone). But with those I had noticed that the changes didn't come into effect until after I had a) moved to a new ID in the form or b) pressed the Save button near the bottom.

Thank you.

 

[mdlueck]

All four print preview buttons bring up reports in print preview mode.

The save also appears to fire, however simply moving to the next record via the VCR buttons at the bottom of the form also performs autosave. This is default behavior for bound forms. I put a break point on the Command288_Click() event to proove the button was firing the VBA.