Do Not Run a Code if Text is not Autofilled.

[zashaikh]

Hello


I am using a separate table to to autofill my form after I Update the cmbFoodInvolved combo box. My code is below in bold.




Private Sub cmbFoodInvolved_AfterUpdate()
Me.txtIngredient.Value = Me.cmbFoodInvolved.Column(1)


Me.txtCategory.Value = Me.cmbFoodInvolved.Column(2)
Me.txtCharaceteristics.Value = Me.cmbFoodInvolved.Column(3)
Me.txtOralDescription.Value = Me.cmbFoodInvolved.Column(4)
Me.txtQuantity.SetFocus
End Sub



However, when I enter a product that is not stored in the Combo Box, I am automatically redirected to the txtQuantity field. (Because I set the focus).



How do I NOT run the above code if the product I enter is not stored in the Combo Box?

I hope that makes sense



I was told that the following code would work, but it does not.

Private Sub cmbFoodInvolved_AfterUpdate()

If Not IsNull(Me.cmbFoodInvolved) Then
Me.txtIngredient.Value = Me.cmbFoodInvolved.Column(1)
Me.txtCategory.Value = Me.cmbFoodInvolved.Column(2)
Me.txtCharaceteristics.Value = Me.cmbFoodInvolved.Column(3)
Me.txtOralDescription.Value = Me.cmbFoodInvolved.Column(4)
Me.txtQuantity.SetFocus
End If

End Sub

[aytee111]

Is FoodInvolved the product that you are entering, or is that in a separate field? You can set the combobox to not allow entry if it doesn't exist, in which case the code will not be executed, also you could have a NotInList event procedure.

[zashaikh]

Yes. FoodInvolved is the product I am entering. The combobox automatically fills in a list of about 100 Food items. However, I sometimes enter an item that is not on the list. When there is an item not on the list that I want to enter, I want Access to not impliment any codes relating to the autofill

[Micron]

Use the NotInList event as suggested, to add the list item either automatically or according to a prompt response.
https://msdn.microsoft.com/en-us/lib.../ff845736.aspx
When this event fires, the AfterUpdate event does not, but the FORM error event does. So try moving the code you have to a separate sub and call it from the AfterUpdate and form Error event, then it should fire either way. However, any run time error will trigger this event if the form has focus, so you have to code for the error specific to the NotInList event: 2237 Maybe something like this:

Private Sub cmbFoodInvolved_AfterUpdate()
DoMyStuff
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2237 Then 
  DoMyStuff
Else
  'code to announce error or whatever is needed to be done
End If
End Sub

Private Sub DoMyStuff()
  With Forms!frmNameOfYourForm
   .txtIngredient = .cmbFoodInvolved.Column(1)
   .txtCategory = .cmbFoodInvolved.Column(2)
   .txtCharaceteristics = .cmbFoodInvolved.Column(3)
   .txtOralDescription = .cmbFoodInvolved.Column(4)
   .txtQuantity.SetFocus
  End With
End Sub

Please use code tags for more than a few lines.

[zashaikh]

Thank you for the help.

I never received an error message to begin with however. When I enter a Food name that is not in my autofill table, then nothing autocorrects (which is what I want) and the Focus is Set to txtQuantity (which I do not want).

I tried that code you suggested above, but it made no difference.

[Micron]

The Error Event is a form event which may or may not generate a message. I don't know what you mean by "autocorrects" - that's a spell check type of feature. If you don't want the focus to shift to a control, then edit the sub and the call to the sub so that you pass a parameter to it. Check the value of the parameter and shift or do not accordingly.
As my signature says, some form of "didn't work" doesn't help us to help you. Post what you tried, what happened and what didn't happen that was supposed to

[Micron]

P.S. I think you will also have to reveal more about the combo box involved (row source, record source if bound). You also say I am automatically redirected to the txtQuantity field but I don't get how that happens if the NotInList event is firing on the combo. As I wrote, I believe the AfterUpdate doesn't run if the NotInList event runs and is not handled. Maybe you are handling that but just haven't said so. More info is needed.

[zashaikh]

Forgive me. I meant to say "autofill". Not "autocorrect".

I will try to clarify. I am using the following form (its actually a subform) (sorry, I had to censor some things).


When I type " App " into cmbFoodInvolved and press <tab>, it i s autofilled to "Apple Pie." txtIngred ient is also autofilled to "apples". cmbCategory is autofilled to "dessert". txtChar acteristics is autofilled to "Sweet." This autofill feature is happening because on the Properties Tab on cmbFoodInvolved , I have tblAutofillProduct listed as the row source. Everything at this point works perfectly how I want it.



However, if I type the value "Pumpkin Pie" into cmbFoodInvolved , the txtIngredient , cmbCategory , txtCharacteristics fields will not be autofilled because there is no reference to a "Pumpkin Pie" in the tblAutofillProduct. But my code still .SetsFocus to txtEstimatedQuantity. I do not want the Focus Set to txtEstimatedQuantity. I want it to just default back, so when I hit <tab>, I am taken to txtIngredient.


I also have another problem relating to this code, but I believe that will be resolved if I can resolve the problem as set forth above. For a reminder, the code I am using is

[Micron]

That helps. What I think you need is something like this

Private Sub cmbFoodInvolved_AfterUpdate(Cancel As Integer)
If IsNull(DLookup("NameOfField", "tblAutofillProduct ", "NameOfField = '" & Me!cmbFoodInvolved.Text & "'")) Then
  Me.txtIngredient.SetFocus
Else
  With Forms!frmNameOfYourForm
   .txtIngredient = .cmbFoodInvolved.Column(1)
   .txtCategory = .cmbFoodInvolved.Column(2)
   .txtCharaceteristics = .cmbFoodInvolved.Column(3)
   .txtOralDescription = .cmbFoodInvolved.Column(4)
   .txtQuantity.SetFocus
  End With
End If

Change it to use the name of your table field. This assumes the value being looked up is text and not some sort of number. The code uses the .Text value of the control, which is what is visible, which is not always the same as what its .Value is. In your case, I suspect they'll be the same since you seem to want to be able to add the entered value and this is occurring in the AfterUpdate event. Not sure exactly what's going on there in your case because not all questions were answered.

[zashaikh]

Thank you.

I am a little confused about:

If IsNull(DLookup("NameOfField", "tblAutofillProduct ", "NameOfField = '" & Me!cmbFoodInvolved.Text & "'")) Then

What am I entering for "NameofField" and "NameofField"?

I receive the following error:




Also, does it make a difference if I am typing

   Me.txtIngredient = Me.cmbFoodInvolved.Column(1)
   Me.txtCategory = Me.cmbFoodInvolved.Column(2)
   Me.txtCharaceteristics = Me.cmbFoodInvolved.Column(3)
   Me.txtOralDescription = Me.cmbFoodInvolved.Column(4)
   Me.txtQuantity.SetFocus

Instead of

 With Forms!frmNameOfYourForm
   .txtIngredient = .cmbFoodInvolved.Column(1)
   .txtCategory = .cmbFoodInvolved.Column(2)
   .txtCharaceteristics = .cmbFoodInvolved.Column(3)
   .txtOralDescription = .cmbFoodInvolved.Column(4)
   .txtQuantity.SetFocus
  End With

[Micron]

Change it to use the name of your table field was the first part of my post. I don't know what the name of your field is in tblAutofillProduct since you haven't revealed that, or I have missed it. You can help us help you by trying to answer all questions asked, for I'm thinking, had you posted the row source for the combo, the field name would have been revealed, thereby saving you unnecessary trouble. See my PS post
P.S. I think you will also have to reveal more about the combo box involved (row source, record source if bound).

As for Q2, the only difference is my example requires less typing and Access only has to process the form reference one time as opposed to multiple form references (Me) as in your example. It also compartmentalizes code, making it easier to not only type out, but read and decipher that several things are about to happen to the thing referenced by the With block opening statement.

[zashaikh]

Thank you for the response. And I am sorry about forgeting to answer those questions. You posted that you needed more info from me (#8) three minutes before I posted #8. So I did not see it.

My row source for cmbFoodInvolved is as follows:

SELECT [tblProductAutofill].[Food Involved], [tblProductAutofill].[Ingredient], [tblProductAutofill].[Category], [tblProductAutofill].[Characteristic], [tblProductAutofill].[Description] FROM tblProductAutofill ORDER BY [Food Involved];

Below is exactly how I have it entered.

If IsNull(DLookup("Food Involved", "tblProductAutofill", "Food Involved = '" & Me!cmbFoodInvolved.Text & "'")) Then
  Me.txtIngredient.SetFocus
Else

And thank you for the second answer.

[Micron]

Ja, that close posting thing happens to me often. So this is different than what you last posted, but you don't say if it works now or not. I'm wondering if there's an issue with the criteria part of the DLookup or lack of square brackets around your field name because you have spaces in your field name. Maybe read this on how to name things:
https://access-programmers.co.uk/for...d.php?t=225837
http://allenbrowne.com/AppIssueBadWord.html

[zashaikh]

Forgive me. I forgot to say that it does not work. And I tried it with and without those brackets, but I still get an error message.

When I try to enter a value that is already in my combo box, I a dialogue box pops up reading "Procedure declaration does not match description of event or procedure haveing the same name". When I click okay, I get the same error message as I showed in POST#10.

And thanks for those too links! Extremely helpful. Ive been trying to find info like that.

[Micron]

Again, you haven't posted the entire event code - if you don't, I can't continue to help. You just keep saying "I tried but..." and you're only post snippets of the procedure.
If you didn't create the AfterUpdate event from the property sheet, you should have and should re-create it that way. You then copy from the sample but don't copy the Private... and End Sub lines since you would now already have them. The problem sounds like the event declaration has a typo.

By the way, my sample is missing the End Sub line but I don't think that's the reason for the error.