473,569 Members | 2,790 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

VBA not requerying form on not in list event, Access 2016

5 New Member
Hi all,

I'm new to this forum. My Access skills are above basic, but I seem to be having trouble getting to the next level regardless of how much time I invest in it. I'm using Access 2016.

Here's the current issue I'm working on. I have two forms. One called Frm_AddSku, the other called Frm_AddProdSubC at.

I know that Access has a built in form property called List Items Edit Form, but I've added some VBA in an On Not In List Event, that makes the interface a lot cleaner. The problem is that only like 80% of it works, and without that other 20%, I'm not at the goal.

Frm_AddSku has a combo box called ProductSubCat_I DFK and if I type something that's not in the list, my VBA kicks in and launches the MsgBox.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
  2. On Error GoTo errline
  3.  
  4. Dim MsgBoxAnswer As Variant
  5.  
  6. Response = acDataErrContinue
  7.  
  8. 'Request permission
  9. MsgBoxAnswer = MsgBox("Do you want to add this new Product SubCategory?", vbYesNo, "Add New Product SubCategory?")
  10.  
  11. If MsgBoxAnswer = vbNo Then 'You've decided not to add a new Product SubCategory.
  12.     Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
  13.     DoCmd.GoToControl "ProductSubCat_IDFK" 'Move the cursor back to the list control.
  14.  
  15. Else 'Permission granted to add a new Product SubCategory to the list.
  16.     DoCmd.OpenForm ("Frm_AddProdSubCat") 'so open Frm_AddProdSubCat
  17.     DoCmd.GoToRecord , , acNewRec 'go to new record
  18.     Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData  'fill in new value on ProductSubCategory field.
  19.     Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
  20.     DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
  21.  
  22. End If
  23.  
  24. errline:
  25.     Exit Sub
  26. End Sub
  27.  
I have no clue what the root of the problem is, but the symptom is that it is none of my VBA events will requery the Frm_AddSku. So when I return to Frm AddSku after entering a new ProductSubCateg ory in Frm_AddProdSubC at, the new ProductSubCateg ory is not in the combobox list and the Frm_AddSku just relaunches my same msgbox in an endless loop. The database is being updated and the entries are going to the appropriate table. I just can't get Frm_AddSku to reflect that.

The lines that use the command = Null , in both the vbNo logic and the vbYes logic above, do not seem to be working as expected. That's just like an fyi, it's not my main concern. I'm just pointing it out because it might be the source of the problem. Or it might not be. I've also tried using ="" instead of = Null. It doesn't make a difference.

On my Frm_AddProdSubC at (the one that gets launched from Frm_AddSku) I have a button called SaveCloseAPSC with an On Click event as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub SaveCloseAPSC_Click()
  2. On Error GoTo errline
  3. DoCmd.RunCommand acCmdSaveRecord 'save record before close form
  4. On Error GoTo errline
  5.  
  6. DoCmd.Close
  7.  
  8. DoCmd.OpenForm "Frm_AddSku"
  9.  
  10. [Forms]![Frm_AddSku]![ProductSubCat_IDFK].Requery
  11.  
  12. errline:
  13.     Exit Sub
  14. End Sub
The line [Forms]![Frm_AddSku]![ProductSubCat_I DFK].Requery seems to be having no effect. ProductSubCat_I DFK on Frm_AddSku is certainly NOT requerying.

Additionally, I have a clear form button on the first form Frm_AddSku that is not completely working.

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnClearForm_Click()
  2.     Me.Refresh
  3.     Me.Requery
  4.     'this button clears any selected data from the comboboxes
  5. End Sub
Thanks to this thread here, I added a new trusted location, and this button now partially works. But it won't clear any not-in-list text I typed into the field just before the second form is triggered to launch.

I've tried peppering Me.Requery and Me.Refresh all over everything I can think of until the cows came home with no results. If I go up to the Access ribbon and click Refresh All, then Frm_AddSku will refresh. But it seems like I should be able to embed that same command inside my VBA so that I can get an air tight front end.

I've attached a couple screen shots of the forms for reference. As well as screen shots of the VBA.

I'm looking forward to someone guiding me in the right direction. Thanks!
Attached Images
File Type: jpg Frm_AddSku.jpg (15.1 KB, 125 views)
File Type: jpg Frm_AddProdSubCat.jpg (15.0 KB, 131 views)
File Type: jpg VBA SCRN SHOT.jpg (64.8 KB, 188 views)
File Type: jpg VBA SCRN SHOT-save and close.jpg (21.2 KB, 105 views)
Jun 12 '18 #1
8 2647
Nauticalgent
100 New Member
Quick question: What is the source/data for ProductSubCat_I DFK and is it bound or unbound?

Without too much more info, I don't know where to start.

That being said, I do know that you cannot assign a Null value to a control that has a number assigned to it.

IDFK = I Don't Freaking Know?
Jun 13 '18 #2
PhilOfWalton
1,430 Recognized Expert Top Contributor
I tend to use a different approach. Rather than use the NotInList, I double click the combo box to bring up a form to add/edit a record.

Here is an example. I have a form "Town" on which there is a Combo Box to select the appropriate county.

Here is the code
Expand|Select|Wrap|Line Numbers
  1. Private Sub CountyID_DblClick(Cancel As Integer)
  2.  
  3.    If Not CurrentProject.AllForms("County").IsLoaded Then
  4.         DoCmd.OpenForm "County"
  5.     End If
  6.  
  7.     DoCmd.SelectObject acForm, "County"
  8.     DoCmd.Restore
  9.     If Nz(CountyID) = 0 Then
  10.         DoCmd.GoToRecord acForm, "County", acNewRec
  11.     Else
  12.         DoCmd.GoToControl "CountyID"
  13.         DoCmd.FindRecord CountyID
  14.     End If
  15.  
  16. End Sub
  17.  
This is the code on the County Form which updates the Town form.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.  
  3.     If CurrentProject.AllForms("Town").IsLoaded Then
  4.         If Nz(Forms!Town!CountyID) = 0 Then          ' Empty
  5.             Forms!Town!CountyID = 0
  6.             Forms!Town!CountyID.Requery
  7.             Forms!Town!CountyID = CountyID
  8.         End If
  9.     End If
  10.  
  11. End Sub
  12.  
Phil
Jun 13 '18 #3
NeoPa
32,564 Recognized Expert Moderator MVP
NauticalGent:
That being said, I do know that you cannot assign a Null value to a control that has a number assigned to it.
There are times when Nulls are not allowed in a Control but I suspect you may have misinterpreted the cause when you've seen it occur.

Nulls are only not available in a Control when that Control is bound to a Field which is itself set not to allow Nulls. This can happen for numeric Fields, but it can also happen for text Fields. The point is whether or not the Field is configured as Required.
Jun 13 '18 #4
MS Access Probz
5 New Member
@PhilOfWalton thanks! That's a pretty simple solution and it seems to be working.

It updates the darn list at least! Which is the most essential part.

The thing that attracted me to the method I was working on is that it autofilled the text I just entered from my edit form into my mainform. I thought that was a pretty slick interface feature. With this method I still have to go back and select the thing out of the list that I just defined.

Is there a line of code to add to your method that would accomplish that?

Also, I have a dumb question: the syntax you use CurrentProject. AllForms("Count y"). I haven't seen that before. It clearly works but usually I just see Forms("County") . Is there a rule for when I should use CurrentProject. AllForms?

Thanks!
Jun 13 '18 #5
NeoPa
32,564 Recognized Expert Moderator MVP
MS Access Probz:
Is there a line of code to add to your method that would accomplish that?
If you set the .Value of the Control to your newly-created value then that item in the list will be selected.
MS Access Probz:
Also, I have a dumb question: the syntax you use CurrentProject. AllForms("Count y"). I haven't seen that before. It clearly works but usually I just see Forms("County") . Is there a rule for when I should use CurrentProject. AllForms?
Forms() refers to a Collection of open Forms. CurrentProject. AllForms() refers to a Collection of all Forms in the project.

Phil's code illustrates a way to determine if a Form is already open by checking the .IsLoaded property. If it isn't already loaded then it won't be found in the Forms() Collection, and in this case will need to be opened by the code.
Jun 13 '18 #6
MS Access Probz
5 New Member
great thank you! I'll play around with .value and try to get that to work. Would it be something like .value = NewData?
Jun 13 '18 #7
NeoPa
32,564 Recognized Expert Moderator MVP
MS Access Probz:
Would it be something like .value = NewData?
Broadly speaking - yes.

You'd obviously need to ensure that .Value is properly specified, but yes. That would be it.
Jun 13 '18 #8
MS Access Probz
5 New Member
totally. I'll give it a whirl.
Jun 13 '18 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

2
4140
by: EH Eisenberger | last post by:
It seems a bug in access. When you open any form in MS Access XP and move to another record from where the form started, and then close the Access application, the FormLoad event will be executed while closing. You can prove this if you'll place a call to MsgBox in FormLoad. Any response will be appreciated. Thanks
1
2481
by: Chicken Kebab Abdullah | last post by:
I am making a database of my movie collection and I have a form which Shows a DVD Code in a combo, and a list of the divx movies on the dvd on the right in a list box. The combo boxes bound column is the dvdID but it is hidden just to show the name. Anyway, I use the normal access record navigation arrows to navigate through the dvd's I...
1
2476
by: Todd | last post by:
My form contains a combo box and the "not in list" event is triggerred when the user enters a value that is not among the values listed in the combo box. I want the user to be able to add a new value (which is fairly easy to do) but here's the twist... The form that allows the user to enter the new value exists in a separate Access...
0
1255
by: BeccyBoo | last post by:
This has probably been posted before but i've searched and couldn't find the answer I'm looking for (or just couldn't understand it) but here's what i'm trying to do: i have a table (tbl_participants). One field on this table is R_Category which looks up the table tbl_Category (field "Category"). I have created a form (frm_Participants)...
6
2438
by: zeeshanks | last post by:
Hello Sam, In a Windows form, the listview control is available but if i 'm working in a webform, i cannot find it. Can you please advise how to create a listview in a webform or what other options i have? The following code is from windows form which i would like to use in web form as well: For Each fnditem In fnditems ...
2
15592
by: CD Tom | last post by:
I've installed Office 365 and when I bring up Access the back ground color is White, I've looked in the File, Account, but when I click on the Office Theme I only get two choices Colorful, White, no matter what I choose I only get the white. In Access 2007 I had the I had a choice of Blue Silver or Black. How do I get my Blue back in Access...
1
3568
by: CD Tom | last post by:
I'm thinking about updating from Access 2007 to Access 2016 but have run into a problem. I can't find any place to compile my accdb to an accde. I've installed the office 2016 and can bring up my database with out any problems, but where is the make accde? Thanks for any help.
2
2274
by: Silver993 | last post by:
I have created Access 2016 Database with custom Icon. I can’t figure out what I am doing wrong. Here is the problem: When I tested the database in my developing Computer, it opens with my custom Icon and everything is great as expected. But when I move it to another computer that does not have Access 2016 installed and run my Database in Run-time...
1
2481
by: Murphy | last post by:
I am gettig ready to upgrade from Windows XP / Office 2007 to Windows 10 / Office Professional 2016. Are Access 2007 databases compatible with or convertible to Access 2016?
0
824
by: jalbright1957 | last post by:
Need some help with using the Not in list event. Several tables with one to many relationships and a M2M. Of these tables I have a customer table with a FK pointing to a City table. The City table has a FK pointing to a State table. All works well when adding new customer records provided the state with it's associated city child records have...
0
7694
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7921
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8118
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
5504
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3651
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2107
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
936
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.