VBA not requerying form on not in list event (1 Viewer)

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
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!
 

Attachments

  • Frm_AddSku.JPG
    Frm_AddSku.JPG
    42.5 KB · Views: 313
  • Frm_AddProdSubCat.JPG
    Frm_AddProdSubCat.JPG
    45.8 KB · Views: 306
  • VBA SCRN SHOT.jpg
    VBA SCRN SHOT.jpg
    98.8 KB · Views: 336
  • VBA SCRN SHOT-save and close.jpg
    VBA SCRN SHOT-save and close.jpg
    36.3 KB · Views: 333

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
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
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
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!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
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
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
It is very simple. Look at the sample that i have created to mimic your forms.
 

Attachments

  • subCat.zip
    32.8 KB · Views: 314
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
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
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
@Cronk Yeah there is actually other data in the case I'm working on. But this is good to know for the future. Thanks!
 

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
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"
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
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.
 

Attachments

  • DB-11-arnelgp-access-prog-uk-zip1.zip
    90.2 KB · Views: 261

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
@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.
 

moke123

AWF VIP
Local time
Today, 17:04
Joined
Jan 11, 2013
Messages
3,852
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
 

Cronk

Registered User.
Local time
Tomorrow, 08:04
Joined
Jul 4, 2013
Messages
2,770
@ 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.
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
@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.
 

moke123

AWF VIP
Local time
Today, 17:04
Joined
Jan 11, 2013
Messages
3,852
heres some info on Option Explicit ... http://www.fmsinc.com/microsoftaccess/modules/options/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.
 

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
@moke123 Thanks for the link on the Option Explicit. I'll study up on that.

But I think we might be talking about the same thing when it comes to lookup fields.

Here's how my sql looks in one of my row sources for Tbl_BaseSku:

SELECT Tbl_ProductCategory.[ProdcutCategory_ID], [Tbl_ProductCategory].[ProductCatCode], [Tbl_ProductCategory].[ProductCategory] FROM Tbl_ProductCategory ORDER BY [ProdcutCategory_ID], [ProductCatCode], [ProductCategory];

If there's a different way to do this and get the same data with the same relationships then I don't know about it. If you were thinking I was using the Access Lookup Wizard and entering my list options directly into the table (I just call this embedding for lack of a better word), then no I'm not doing that.

And the attachments are images that I need to see in the interface, record by record.
 

moke123

AWF VIP
Local time
Today, 17:04
Joined
Jan 11, 2013
Messages
3,852
this is what I mean by a table level lookup field

 

Attachments

  • LUField.PNG
    LUField.PNG
    40.2 KB · Views: 551

MS ACCESS PROBZZZ

Registered User.
Local time
Today, 14:04
Joined
Jun 12, 2018
Messages
29
Wait, that's what you mean by table level look up field? I thought that was the way you were supposed to do it. How else can this be done?
 

moke123

AWF VIP
Local time
Today, 17:04
Joined
Jan 11, 2013
Messages
3,852
In the table set your display control back to textbox.
On your form bind the combobox to the appropriate field and then set your rowsource in the combobox properties.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:04
Joined
May 7, 2009
Messages
19,169
Had you post the db earlier, we might solved it the soonest.
 

Attachments

  • ProdCategory.zip
    77 KB · Views: 326

Users who are viewing this thread

Top Bottom