What am I doing wrong? How should the code be written?

[gold007eye]

Can someone please help me figure this out.. Can't for the life of me get this to do what I want.

What I am trying to accomplish is that if a "Transaction Description" is Like "Gas*" Then allow that to be used without getting a "NotInList" error message, but if it is anything <> Gas* and isn't on the list to bring up the msgbox asking the user if they would like to add it to the system now.

Example 1:

If you type "Gas (Exxon) $2.64" into the "Transaction Description" combo box; just continue to the next field as normal. (Don't display NotInList Error Msg)

Example 2:
If you type "Stop & Shop (Groceries)" into the "Transaction Description" combo box; then pop-up a msgbox stating that this Transaction Type isn't on the list "Would you like to add it now?"
------------------------------------------

The code I am trying to use is listed below. The part I can't get to work is for the code to allow anything typed into the "Transaction Description" combo box that are LIKE "Gas*" without triggering an error.

Private Sub Transaction_Description_NotInList(NewData As String, Response As Integer)
'-- This Transaction Type is not currently in the System (and is not a GAS type transaction), Let's add it now.
If [Transaction Description] Like "Gas*" Then
    'Display No Error Message Allow this information to be stored
    Response = acDataErrContinue
Else:
    Select Case MsgBox("The Transaction Description " & NewData & " is NOT currently in the system" & vbCrLf & vbCrLf & "Would you like to add it now?", vbQuestion + vbYesNo, "Add New Transaction Information?")
        
        Case vbYes: 'Add New Transaction Type
            DoCmd.OpenForm "Modify Transaction Information", , , , acFormAdd, acDialog, NewData
            MsgBox "Your new Transaction Description has been added to the system.", vbInformation, "New Transaction Information Added"
            Response = acDataErrAdded
            'Response = acDataErrContinue
        Case vbNo: 'Do NOT Add New Transaction Type
            MsgBox "You have selected an invalid Transaction Description" & vbCrLf & vbCrLf & "Please select an Transaction Description from the list...", vbExclamation, "Invalid Transaction Description..."
            [Transaction Description] = Null
            Response = acDataErrContinue
        End Select
End If
End Sub

 

[gemma-the-husky]

it may be that you cant use "like" in vba so instead of

If [Transaction Description] Like "Gas*" Then

you would have to use

if instr(newdata),"gas") then

(sincve newdata is the variable that is being considered not [transaction description], whatever that is.

 

[gold007eye]

I tried that code, but it is throwing the "This item is not on the list" error message.

[Transaction Description] is the combo box name where the data is entered.

Any other ideas?

 

[georgedwilkinson]

VB is case sensitive. And [Transaction Description] is not a valid VB variable name.

Try:

If Left(Me.[Transaction Description].Text, 3) <> "Gas" Then

or some variant.

I also have fears when you say "is not on the list". Is [Transaction Description] a list name, a text box name, something else? It matters.

 

[gold007eye]

I think the code you gave me will work, but how do I get it to accept the "Custom" transaction and continue to the next field?

To clarify [Transaction Description] is a ComboBox (Pulls Data from the "Transactions" table.)

 

[unclejoe]

Remove the colon from the following....

Can someone please help me figure this out.. Can't for the life of me get this to do what I want.

Private Sub Transaction_Description_NotInList(NewData As String, Response 

Else:  [B]'Remove the colon[/B]

        Case vbYes: 'Add New Transaction Type  [B]'here and [/B]

        Case vbNo: 'Do NOT Add New Transaction Type  [B]here.[/B]

End Sub

Then tell us what is the error.

 

[boblarson]

VB is case sensitive.

Not in Access VBA it isn't.

If you try

Msgbox "string" = "StRing"

You will find it does equal each other.

 

[LPurvis]

There's a fundamental problem with what you're attempting.
When you say
'if a "Transaction Description" is Like "Gas*" Then allow that to be used without getting a "NotInList" error message'
you're not actually asking for conditional code to be run - you're requiring the conditional execution of a UI event. And you're not going to get that.

If you have the combo set to raise the NotInList event - then it will do so if and only if the text entered is not in the list.
If the text entered then conforms to your requirement of "Gas*" then you want no data entered and no further prompt...
However NotInList has been raised for your combo - which means that you must have set LimitToList to be true.
In which case you simply cannot exit the combo unless that text is entered...

So it isn't so much a case of the code choice within the event - but the actual event choice.
I'd probably suggest moving to the BeforeUpdate event.
But this absolutely demands that your combo is not set to LimitToList.
This means it can't be based on a list where the bound control isn't the first shown (and hence editable).
So if you just have a list of text values - and you want to enter one of those directly then you're fine. If you're entering a relational key value then you can't enter text on the fly without adding that to your related table. It's absolutely fundamental to relational design.

If you do have direct text entry then you could add text through a decision making process (which in Acc2002 or later might be like):

Private Sub cboBefore_BeforeUpdate(Cancel As Integer)
 
    With Me.cboBefore.Recordset
        .FindFirst "FieldName = """ & Me.cboBefore.Text & """"
        If .NoMatch Then
            If Not Me.cboBefore.Text Like "Gas*" Then
                CurrentDb.Execute "INSERT INTO tblTable (FieldName) VALUES (""" & Me.cboBefore.Text & """)"
            End If
        End If
    End With
 
End Sub

Your only issue then becomes requerying the combo list to reflect that entry as you can't do so in the Update event.
Off the top of my head - you could fudge that with a Timer interval...

[FONT=Courier New]Private Sub Form_Timer()[/FONT]
 
[FONT=Courier New]  Me.cboBefore.Requery[/FONT]
[FONT=Courier New]  Me.TimerInterval = 0[/FONT]
 
[FONT=Courier New]End Sub[/FONT]

And adding a call after your CurrentDb.Execute line earlier of, say
Me.TimerInterval = 100

Though I'm not, in general, a use fan of using Timer events to get around such issues.

 

[gold007eye]

Hmm.. so it looks like trying to use the "NotInList" event is my problem. So what you are saying is that I could code the beforeupdate event to try and accomplish what I need to do?

I want the user to be able to decide if it isn't on the list if they want to add it to the system; and if so open the "Transaction Form" at which point they can add the information required.

THanks for everyone's help. Let me see if I can get this other method to work.