UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> After Update Event Procedure Vba Issues When Adding New Combo Boxes To An Existing Navigation Form, Access 2010    
 
   
adsfuse
post Sep 19 2019, 08:56 AM
Post#1



Posts: 4
Joined: 29-August 19



Hi, everyone. Thanks for taking the time to read through this. Any and all help is appreciated.

A little background, I’m new to Access. My employer asked if I would be interested in learning and gave me the Go! With Microsoft Access 2010, Comprehensive book and I’ve since completed the book, so I have a somewhat general idea of how to interact with the software, but I’m still very green, especially when it comes to anything related to VBA.

I’m working with an existing database and will be working on a backlog of projects/fixing broken things. A note about this database, it was assembled and pieced together over years, with very little forethought and/or not always with good design practices. The philosophy always was get it working and we can fix it later. With that said, I’m very interested in learning the “proper” way to do things, or at least implementing practices that help me develop good habits and perhaps help prevent some future headaches. I am limited to the changes I can implement on my own, but if I have some logic and reasoning behind the desired change I can sometimes get the go ahead to implement it.

Now, onto the problem at hand. I’ve been tasked with creating three new combo boxes for a Navigation form. The combo boxes that I am adding are Combo82, Combo86, and Combo88. I’ve included a picture of the form to give an idea of the general layout.

Attached File  Navform.jpg ( 114.13K )Number of downloads: 3


All combo boxes display as “Unbound”.

The “Enter Components into Inventory” section.
A table was created for each combo box with a single field, listing the names for each of the forms associated with the combo box.
The row source for each combo box is a query that references the corresponding table and sorts ascending.

The “View Current Inventory by Lot” and “View Current Inventory by Totals” section
Both of these open reports that either give totals by lot number or totals by component. Similarly, a table with a single field that lists the report names was generated and there is a query for the row source for each of these that pulls this field and sorts ascending.

With that explanation out of the way, I have already created all the forms that I need, created the reports, created the tables and linked everything together and have all values populating correctly in my combo boxes. The trouble that I’m having is with the VBA After Update [Event Procedure] that makes everything work and I’m having difficulty finding other examples that mimic what I’m seeing in this code to get a clearer understanding of what it is doing and what the limitations/benefits of such an approach are.

From my understanding, the After Update triggers once a selection is made in the combo box and then Open Form opens the form ready for a new record and the Open Record opens the report in print preview, maximizes it in the window and sets zoom to 100.

My initial approach was to simply copy the code from Private Sub through End Sub, paste it at the end, and change the combo number to correspond to the combo box I wanted. This worked a few times for Combo82 (though not every time) and never for Combo86 and Combo88.

Everything does work before I make any changes to the code, so it has to be something that I’m doing improperly.

The error that I get is as follows, “The expressions After Update you entered as the event property setting produced the following error: Object library invalid or contains references to object definitions that could not be found. The expressions may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. There may have been an error evaluating the function, event, or macro.”

I’m a bit stumped at how to proceed and most other examples of how to achieve something similar with opening the forms and reports from combo boxes look very different from what I see in the code in this event procedure.

Any avenues to explore to find a solution would be greatly appreciated.

The Event Procedure code is a follows(without any additions for the new combo boxes):
CODE
Option Compare Database

Private Sub Combo0_AfterUpdate()


DoCmd.OpenForm Combo0.Value
DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub Combo4_AfterUpdate()

DoCmd.OpenForm Combo4.Value

End Sub

Private Sub Combo21_AfterUpdate()

DoCmd.OpenForm Combo21.Value
DoCmd.GoToRecord , , acNewRec

End Sub

Private Sub Combo25_AfterUpdate()

DoCmd.OpenForm Combo25.Value
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Combo29_AfterUpdate()

DoCmd.OpenForm Combo29.Value
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Combo31_AfterUpdate()

DoCmd.OpenReport Combo31, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo33_AfterUpdate()

DoCmd.OpenReport Combo33, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
End Sub

Private Sub Combo35_AfterUpdate()

DoCmd.OpenReport Combo35, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo37_AfterUpdate()

DoCmd.OpenReport Combo37, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100

End Sub

Private Sub Combo41_AfterUpdate()

DoCmd.OpenReport Combo41, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
End Sub

Private Sub Combo43_AfterUpdate()

DoCmd.OpenReport Combo43, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo45_AfterUpdate()

DoCmd.OpenReport Combo45, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo47_AfterUpdate()

DoCmd.OpenReport Combo47, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo51_AfterUpdate()

DoCmd.OpenReport Combo51, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100


End Sub

Private Sub Combo53_AfterUpdate()


DoCmd.OpenReport Combo53, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100



End Sub

Private Sub Combo61_AfterUpdate()

DoCmd.OpenReport Combo61, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
End Sub

Private Sub Combo63_AfterUpdate()

DoCmd.OpenReport Combo63, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
End Sub

Private Sub Command72_Click()
  DoCmd.OpenForm "Laser Component Disposition Form", acNormal, "", "", , acNormal
    
    
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Command74_Click()
  DoCmd.OpenForm "Laser Disposition Form", acNormal, "", "", , acNormal
    
    
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Command75_Click()
  DoCmd.OpenForm "Module Disposition Form", acNormal, "", "", , acNormal
    
    
DoCmd.GoToRecord , , acNewRec
End Sub


Thank you in advance for your time and help.
Go to the top of the page
 
Hugoj
post Sep 19 2019, 10:50 AM
Post#2



Posts: 35
Joined: 22-August 10



Hi!

First I would like to say that the naming of the comboboxes makes it difficult for you when writing code. Start using standards like cmbInv_x. cmb for comboboxes and then a descriptive name. It makes your Life much easier when you try to find a reason for a problem.

You can also streamline the code a lot. If you look at my example I have created a procedure that uses the name of the control as an argument and the procedure opens the form and uses the argument to decide which form to open. Then copy that code to the comboboxes that opens a form.

Private Sub cmdTest_AfterUpdate()
Formval (ActiveControl.Name)
End Sub


Sub Formval(frmnamn)
DoCmd.OpenForm Controls(frmnamn).Value
End Sub

When it comes to the comboboxes that doesnt work its difficult to say anything. I dont have the form, the comboboxes and their data, and the code. If you submit this with dummy data maybe I can help.
Go to the top of the page
 
adsfuse
post Sep 20 2019, 11:39 AM
Post#3



Posts: 4
Joined: 29-August 19



Hi Hugo,

Thanks for getting back to me. Sorry for the delayed response, but it took some time to figure out how to strip down the databases and then a little more to go through and try and delete as many unrelated objects as I could quickly identify. Not sure if this was the best way to do it, but it was my first time making a dummy of an existing database.

The IPS_DB_Admin is the primary database that I'm working in with linked tables to the laserbld and Modules databases.

Either the forms "Navigation Form" or "Select Enter Inventory Form" (which displays on the Navigation Form) are the primary forms in question that have the combo boxes.

That's a good point about the naming conventions, so for example Combo21 = cmbEntInv_LC, Combo33 = cmbViewInv_LC, etc? That's definitely something I'll begin working on.

As far as the combo boxes that didn't work. It wasn't so much that they didn't work as that when I tried to append the following snippets to the end of the code in the Event Procedure:

CODE
Private Sub Combo82_AfterUpdate()

DoCmd.OpenForm Combo82.Value
DoCmd.GoToRecord , , acNewRec
End Sub


or

CODE
Private Sub Combo86_AfterUpdate()

DoCmd.OpenReport Combo86, acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100

End Sub


The entire code breaks and none of the combo boxes work, not just the new ones. Even if I then delete the snippets, the code remains broken and I have to resort to my backup of the database to get it working again.

Now onto your example procedure, I'm grateful that you took the time to write something up but I think it's still a little above my head at the moment. If we used the hypothetical combo box "cmbEntInv_LC" would the procedure look something like this?

Private Sub cmdTest_AfterUpdate()
Formval (ActiveControl.cmbEntInv_LC)
End Sub


Sub Formval(frmnamn)
DoCmd.OpenForm Controls(frmnamn).Value
End Sub

And then repeat like that for each combo box that opens a form? Also, would I put this as the Event Procedure for the After Update property for the combo box?

Thank you again for your help.

[attachment=91608:IPS_DB_Admin.zip]
[attachment=91609:laserbld.zip]
[attachment=91610:Modules.zip]
Go to the top of the page
 
Hugoj
post Sep 20 2019, 12:52 PM
Post#4



Posts: 35
Joined: 22-August 10



Hi again!

First the procedure. You wrote:

Private Sub cmdTest_AfterUpdate()
Formval (ActiveControl.cmbEntInv_LC) - this should be as I wrote ActiveControl.Name - it means the name of the Active Control - the selected combobox. That name then transfers to the procedure. You can read more about this in a book about creating functions and using arguments. Then you will understand the concept.
End Sub


Sub Formval(frmnamn)
DoCmd.OpenForm Controls(frmnamn).Value
End Sub

I dont know which version of Access you are using but what I could see is that you have problems with the references to different libraries. Go to VB-window, Tolls, References. You have some missing references and also an old one. ActiveX Data Objects 2.1 is old and should be changed to 6.1. Deselect the old one and scroll down in the list and select 6.1.
I dont know if you need the one you are missing.
After you have 6.1 then try again. If it doesnt work Close the database, start again.



Go to the top of the page
 
adsfuse
post Sep 20 2019, 01:47 PM
Post#5



Posts: 4
Joined: 29-August 19



Hi again,

I'm running Access 2010. I've gone ahead and deselected ActiveX Data Objects 2.1 and selected 6.1.

I've replaced the existing code in the After Update [Event Procedure] with the procedure you suggested. When I make a selection for any of the combo boxes related to forms nothing happens. I've attempted closing and reopening the database with no differing results.

I'll go look into creating functions and using arguments to try and wrap my head around why it's not working.

By chance, do you know which library references I'm missing? If not, it's okay, just figured I would ask.

Thanks
Go to the top of the page
 
Hugoj
post Sep 20 2019, 02:15 PM
Post#6



Posts: 35
Joined: 22-August 10



I have attached a Picture from what I see. Maybe they are not missing in your version.

Im using a Swedish version so SAKNAS means missing in English.


Attached File(s)
Attached File  pic.jpg ( 112.23K )Number of downloads: 3
 
Go to the top of the page
 
adsfuse
post Sep 20 2019, 02:28 PM
Post#7



Posts: 4
Joined: 29-August 19



Attached File  References.JPG ( 45.26K )Number of downloads: 1


Ah, okay thanks. They don't display as missing on my end. The only notable difference I can see is that I don't have Microsoft Access 16.0 Object Library as an option (only up to 14.0), so I'll go look into that.

Thank you again for your time and help.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th October 2019 - 02:09 AM