423,491 Members | 2,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,491 IT Pros & Developers. It's quick & easy.

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

P: 5
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_AddProdSubCat.

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_IDFK 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 ProductSubCategory in Frm_AddProdSubCat, the new ProductSubCategory 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_AddProdSubCat (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_IDFK].Requery seems to be having no effect. ProductSubCat_IDFK 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, 13 views)
File Type: jpg Frm_AddProdSubCat.jpg (15.0 KB, 13 views)
File Type: jpg VBA SCRN SHOT.jpg (64.8 KB, 13 views)
File Type: jpg VBA SCRN SHOT-save and close.jpg (21.2 KB, 16 views)
Jun 12 '18 #1
Share this Question
Share on Google+
8 Replies


Nauticalgent
P: 69
Quick question: What is the source/data for ProductSubCat_IDFK 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
Expert 100+
P: 1,359
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
Expert Mod 15k+
P: 31,037
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

P: 5
@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("County"). 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
Expert Mod 15k+
P: 31,037
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("County"). 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

P: 5
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
Expert Mod 15k+
P: 31,037
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

P: 5
totally. I'll give it a whirl.
Jun 13 '18 #9

Post your reply

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