Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2018, 02:20 PM   #1
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
VBA not requerying form on not in list event

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 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.

Code:
Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant

Response = acDataErrContinue

 'Request permission
MsgBoxAnswer = MsgBox("Do you want to add this new Product SubCategory?", vbYesNo, "Add New Product SubCategory?")

If MsgBoxAnswer = vbNo Then 'You've decided not to add a new Product SubCategory.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductSubCat_IDFK" 'Move the cursor back to the list control.

Else 'Permission granted to add a new Product SubCategory to the list.
    DoCmd.OpenForm ("Frm_AddProdSubCat") 'so open Frm_AddProdSubCat
    DoCmd.GoToRecord , , acNewRec 'go to new record
    Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData  'fill in new value on ProductSubCategory field.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
    
End If

errline:
    Exit Sub
End Sub
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:

Code:
Private Sub SaveCloseAPSC_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord 'save record before close form
On Error GoTo errline

DoCmd.Close

DoCmd.OpenForm "Frm_AddSku"

[Forms]![Frm_AddSku]![ProductSubCat_IDFK].Requery
 
errline:
    Exit Sub
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 also not working.

Code:
Private Sub btnClearForm_Click()
    Me.Refresh
    Me.Requery
    'this button clears any selected data from the comboboxes
End Sub
No such refreshing or requerying is occurring whatsoever.

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 several screen shots of what's going on.

I'm looking forward to someone guiding me in the right direction. Thanks!
Attached Images
File Type: jpg Frm_AddSku.JPG (42.5 KB, 11 views)
File Type: jpg Frm_AddProdSubCat.JPG (45.8 KB, 10 views)
File Type: jpg VBA SCRN SHOT.jpg (98.8 KB, 11 views)
File Type: jpg VBA SCRN SHOT-save and close.jpg (36.3 KB, 10 views)

MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-12-2018, 05:16 PM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,583
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA not requerying form on not in list event

There are many ways to fix this. The one you missed is that after the new sub cat is added you need to set the Response to acDataErrAdded.

But the question is how will you know if the sub cat is really added or the user simply close the Frm_AddProdSubCat withou saving it?

You need to put an extra Unbound textbix to the main form. before opening Frm_AddProdSubCat, set the unbound tbox to False. Then on your Save button of Frm_AddProdSubCat, set the unbound textbox to true:

Froms!Mainform!UnboundTextbox=True


On the Mainform:
...
...
Else 'Permission granted to add a new Product SubCategory to the list.
Me.UnboundTextbox=False
DoCmd.OpenForm ("Frm_AddProdSubCat") 'so open Frm_AddProdSubCat
DoCmd.GoToRecord , , acNewRec 'go to new record
Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData 'fill in new value on ProductSubCategory field.
Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
If Me.UnboundTextbox Then Response=acDataErrAdded
End If
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
MS ACCESS PROBZZZ (06-12-2018)
Old 06-12-2018, 05:51 PM   #3
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

Hey @arnelgp thanks for the reply. I'm currently trying to follow your instructions. That's a great tip on acDataErrAdded. I did not know that.

I'm trying to follow the rest of your advice but I don't know what you mean by before opening Frm_AddProdSubCat, set the unbound tbox to False.

Are you talking about one of the properties on the property sheet for that tbox?

I've added the code you suggested to each form. But I want to make sure I haven't missed a step.

This is super helpful and gets me much further than where I was. But one minor thing is that when I return to the main form, the MsgBox still appears asking me if I want to add the item to the list. The good news though is that now it IS in the list. So the MsgBox just comes back for one last hurrah before going away for good and letting me move on. Is there any way to improve upon this or is there something I've left out?

Thanks for you help so far!

MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 01:02 AM   #4
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,583
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA not requerying form on not in list event

here again.
add an unbound textbox (txtHidden) to frm_AddSku.
set its visible property to No.
some unnecesary codes were commented.
the folkowing is a modified code:
Code:
Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant

Response = acDataErrContinue

 'Request permission
MsgBoxAnswer = MsgBox("Do you want to add this new Product SubCategory?", vbYesNo, "Add New Product SubCategory?")

If MsgBoxAnswer = vbNo Then 'You've decided not to add a new Product SubCategory.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductSubCat_IDFK" 'Move the cursor back to the list control.

Else 'Permission granted to add a new Product SubCategory to the list.
    me.txtHidden=0
    DoCmd.OpenForm formname:="Frm_AddProdSubCat", openargs:=NewData 'so open Frm_AddProdSubCat
    'DoCmd.GoToRecord , , acNewRec 'go to new record
    'Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData  'fill in new value on ProductSubCategory field.
    'Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    if me.txthidden then
        Response=acDataErrAdded
    end if
    DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
    
End If

errline:
    Exit Sub
End Sub
'=======

Frm_AddProdSubCat code:
Code:
 private sub form_load()
 if nz(me.openargs,"") <> "" then
 DoCmd.GoToRecord , , acNewRec
 me.ProductSubCategory=me.openargs
 end if
 end sub
 
Private Sub SaveCloseAPSC_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord 'save record before close form
Forms!Frm_AddSku!txtHidden=-1
On Error GoTo errline

DoCmd.Close

'DoCmd.OpenForm "Frm_AddSku"

'[Forms]![Frm_AddSku]![ProductSubCat_IDFK].Requery
 
errline:
    Exit Sub
End Sub
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
MS ACCESS PROBZZZ (06-13-2018)
Old 06-13-2018, 09:11 AM   #5
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

Thanks! I copy pasted your code from above into my database but now it's back to not updating the list at all.

Here's the code I'm using that works the best so far. I realize I misinterpreted what you meant and put Response = acDataErrAdded here

Code:
Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant

Response = acDataErrAdded

'Request permission

....
But it's working...ish.

Here's the working...ish code in it's totality. In this version I've just called the hidden text box txtUnbound

Code:
Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)
On Error GoTo errline

Dim MsgBoxAnswer As Variant

Response = acDataErrAdded

'Request permission
MsgBoxAnswer = MsgBox("Do you want to add this new Product SubCategory?", vbYesNo, "Add New Product SubCategory?")

If MsgBoxAnswer = vbNo Then 'You've decided not to add a new Product SubCategory.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductSubCat_IDFK" 'Move the cursor back to the list control.

Else 'Permission granted to add a new Product SubCategory to the list.
    Me.txtUnbound = False
    DoCmd.OpenForm ("Frm_AddProdSubCat") 'so open Frm_AddProdSubCat
    DoCmd.GoToRecord , , acNewRec 'go to new record
    Forms![Frm_AddProdSubCat]![ProductSubCategory] = NewData  'fill in new value on ProductSubCategory field.
    Me.ProductSubCat_IDFK = Null 'Make the list control empty for the time being.
    DoCmd.GoToControl "ProductCategory_IDFK" 'Move to the next desired field.
    If Me.txtUnbound Then Response = acDataErrAdded
    
End If

errline:
    Exit Sub
    
End Sub
and

Code:
Private Sub SaveCloseAPSC_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord 'save record before close form
On Error GoTo errline

DoCmd.Close

DoCmd.OpenForm "Frm_AddSku"

[Forms]![Frm_AddSku]![ProductSubCat_IDFK].Refresh
 
[Forms]![Frm_AddSku]![txtUnbound] = True
 
errline:
    Exit Sub
End Sub
In addition to my custom MsgBox I get on the not in list event of my main form, I then get a default MsgBox on my edit form asking "Do you want to edit the items in the list?" Then when I save and close the edit form and return to the main form I still get my original custom MsgBox. If I click no on that, all is good, the item is in the list and I can proceed with data entry.
MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 09:54 AM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,583
Thanks: 55
Thanked 2,093 Times in 2,005 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: VBA not requerying form on not in list event

It is very simple. Look at the sample that i have created to mimic your forms.
Attached Files
File Type: zip subCat.zip (32.8 KB, 8 views)
__________________
"Never stop learning, because life never stops teaching"

Last edited by arnelgp; 06-13-2018 at 10:03 AM.
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
MS ACCESS PROBZZZ (06-13-2018)
Old 06-13-2018, 12:51 PM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: VBA not requerying form on not in list event

There is no need for another form to enter the new data in the combo.
Code:
Private Sub ProductSubCat_IDFK_NotInList(NewData As String, Response As Integer)

    If MsgBox("Do you want to add " & Chr(34) & NewData & Chr(34) & " to Product SubCategory?", vbQuestion + vbYesNo) = vbNo Then
        Response = acDataErrContinue
        Me.ProductSubCat_IDFK = Null
    Else
      CurrentDb.Execute "INSERT INTO tblSubCategory (ProductCat) select " & Chr(34) & NewData & Chr(34)
            Response = acDataErrAdded
    End If
End Sub

I only use another form when there is extra data, associated with the new item that needs to be captured, eg weight, color, phone number

Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
MS ACCESS PROBZZZ (06-13-2018)
Old 06-13-2018, 01:06 PM   #8
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

@Cronk Yeah there is actually other data in the case I'm working on. But this is good to know for the future. Thanks!
MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 02:14 PM   #9
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: VBA not requerying form on not in list event

In the case of capturing other data, I open the form in dialog mode
Code:
docmd.openform "frmGetData", , , , , acDialog
to stop the user switching to anything else.


On the OK button of frmGetData, I do any data validity checks then set the form's visibility to false. That way the code reverts to the calling form.


The calling form checks frmGetData has not been closed, reads the data off the data form, then closes the data form. Something like


Code:
docmd.openform "frmGetData", , , , , acDialog
on error resume next
varData1 = forms!frmGetData!txtData1
if err <> 0 then
  'user cancelled
   response = acDataErrContinue
    exit sub
endif
<read other data fields from frmGetData>
docmd.close acform,"frmGetData"
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
MS ACCESS PROBZZZ (06-13-2018)
Old 06-13-2018, 02:23 PM   #10
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

Thanks @arnelgp. I opened your zip and it definitely works there. I copied your vba into my database and am getting an error that says:

"The expression On Not In List you entered as the event property setting produced the following error: A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX Control. *The expression 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. This error occurs when an event has failed to run because the location of the logic for the event cannot be evaluated. For example, if the OnOpen property of a form is set to =[Field], this error occurs because a macro or event name is expected to run when the event occurs."

Also, when the field auto fills on the edit form from what I wrote in the mainform, it writes like this [Frm_AddSku]![ProductSubCat_IDFK]=AHHH PLEASE WORK. I only entered the AHHH PLEASE WORK part.

I'm attaching a zip of my db. I'm not sure what I'm getting wrong since it clearly works in your file and I'm pretty much just copy pasting and making sure the names match what's in my database.
Attached Files
File Type: zip DB-11-arnelgp-access-prog-uk-zip1.zip (90.2 KB, 9 views)
MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 03:17 PM   #11
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

@Cronk cool thanks. I'll check this method out too. Is there a good reference book of VBA terms somewhere? I only have VBA For Dummies and it's coming up very short with respect to all the code ya'll are dropping here.

What does varData1 mean? Is like a made up thing that you've just defined as = forms!frmGetData!txtData1 ? Doesn't it have to be declared somewhere else in the code?

I was trying some code that might be similar to that using Dim TempId As Integer and Forms![Frm_AddSku]![PartNo_IDFK] = TempId

Code:
Private Sub SaveCloseAPN_Click()
On Error GoTo errline
DoCmd.RunCommand acCmdSaveRecord 'save record before close form
On Error GoTo errline

Const conObjStateClosed = 0
Const conDesignView = 0
Dim IsFormLoaded As Boolean
Dim TempId As Integer

Me.Refresh
TempId = Me.ID 'set the ID (primary key) of the Part Number to Temp variable
''Check if the original data entry form with the list is open
''(This form might have been opened manually)
If SysCmd(acSysCmdGetObjectState, acForm, "Frm_AddSku") <> conObjStateClosed Then
    If Forms("Frm_AddSku").CurrentView <> conDesignView Then
        IsFormLoaded = True
    End If
End If

If IsFormLoaded = True Then 'Check if the original data entry form with the list is open.
    'Refresh the list control so it now includes the newly added item.
    Forms![Frm_AddSku]![PartNo_IDFK].Requery
    Forms![Frm_AddSku]![PartNo_IDFK] = TempId 'Set the list control to the newly added item.
    DoCmd.Close 'Close the current form
    
Else
    DoCmd.Close 'if Frm_AddSku is not loaded then also close this form
End If
 
errline:
    Exit Sub
End Sub
But this didn't work either. I mean, it didn't refresh/requery the list.

There's a lot to wrap my brain around here. Just when I think I'm getting a bit of a handle on VBA, there's more.
MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 03:43 PM   #12
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 649
Thanks: 0
Thanked 203 Times in 191 Posts
moke123 is on a distinguished road
Re: VBA not requerying form on not in list event

PMFJI-

I havent delved too deeply into your database but I wouldn't be surprised if part of your problem is not related to the lookup FIELDS in your TABLE.

they are generally not a good idea. see http://access.mvps.org/access/lookupfields.htm

you may also want to reconsider the attachment fields.

MOST IMPORTANT- You should add to each module -
Code:
Option Compare Database
Option Explicit
moke123 is offline   Reply With Quote
Old 06-13-2018, 04:20 PM   #13
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 1,846
Thanks: 2
Thanked 396 Times in 391 Posts
Cronk will become famous soon enough
Re: VBA not requerying form on not in list event

@ MS ACCESS PROBZZZ

The data in frmGetData needs to be captured before it is closed.


varDataN was meant to represent local variables in the calling form to hold the entered data. It could have easily directly added directly to the required table by adding a new record.
Cronk is offline   Reply With Quote
The Following User Says Thank You to Cronk For This Useful Post:
MS ACCESS PROBZZZ (06-13-2018)
Old 06-13-2018, 05:00 PM   #14
MS ACCESS PROBZZZ
Newly Registered User
 
Join Date: Jun 2018
Posts: 29
Thanks: 18
Thanked 0 Times in 0 Posts
MS ACCESS PROBZZZ is on a distinguished road
Re: VBA not requerying form on not in list event

@moke123 good catch on the Option Compare Database. That's usually in there (by default) I've just done so many versions of this that it must have gotten deleted on my Frm_AddProdSubCat VBA.

What does Option Explicit mean? And do I really need this in every module always? I've seen a lot of examples around that don't use it.

Can you give me an example of how I've misused a lookup field in one of my tables? I think my table fields just relate to other tables. I haven't used any embedded lookup lists.

And regarding attachments, this database isn't ever going to be so big and heavy that that's going to matter. I mean, it won't affect performance significantly. At the end of the day it has to efficiently organize a set of ideas and those attachments are an important part of that.
MS ACCESS PROBZZZ is offline   Reply With Quote
Old 06-13-2018, 07:28 PM   #15
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 649
Thanks: 0
Thanked 203 Times in 191 Posts
moke123 is on a distinguished road
Re: VBA not requerying form on not in list event

heres some info on Option Explicit ... http://www.fmsinc.com/microsoftacces...ons/index.html
and yes you should have it in every module.

I didn't say you misused table level lookup fields, just that they are generally not a good idea. I've never had a use for them after the first few times spending hours trying to figure out why my code wouldn't work. . Its much easier to have lookup tables and use those as the row sources of lists or combo boxes.

As far as attachment fields, many prefer to store the path to a file as text. By storing the file outside access you don't need to worry about file size or bloating and the file can still be opened\displayed\whatever with simple code.

moke123 is offline   Reply With Quote
Reply

Tags
on not in list , refresh a list , refresh requery form , requery a combo box , vba access 2016

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Not In List event to include edit form Wysy Forms 9 12-30-2016 02:50 PM
Requerying a list box based on a query problem JamesWB Queries 1 10-03-2014 11:11 PM
requerying combobox after NotInList event Sketchin Forms 5 09-10-2014 01:14 PM
Requerying a list box LOUISBUHAGIAR54 Forms 7 05-20-2012 07:24 AM
Requerying a list box based on a combo box selection Dannyboy11 Queries 4 07-12-2010 01:25 PM




All times are GMT -8. The time now is 05:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World