Afterupdate, but notinlist

[kasceus]

Hello, i have a problem, and i am stuck. Sorry if this seems a bit easy- but i am new to access.

I have a combobox, and i have it get information from another form AfterUpdate. However, if the item is not in the list, then i want it to open another form to input the data- then go back to the original form (easy enough to do). My problem is that AfterUpdate takes precidence over NotInList. If the item isn't in the list, then it gives an error of "you entered an expression with no value" - which i took to mean Null (even tried "" just in case). I have tried if statements in every way i can think of to get it to work how i want... to no avail. here is the code from my after update on frm_AFREP_order

Private Sub Combo116_AfterUpdate()
 
    DoCmd.OpenForm "frm_get_info_order", acNormal, , , acFormReadOnly
    DoCmd.Close acForm, "frm_get_info_order", acSaveYes
    Me.NSN.Value = PNSN
    Me.Part__.Value = PPN
    Me.UI.Value = PUI
    Me.WUC.Value = PWUC
    Me.NHA.Value = PNHA
    Me.NHASN.Value = PNHASN
    Me.TO.Value = PTO
    Me.Fig.Value = PFIG
    Me.IND.Value = PIND
    Me.Text157.Value = PCOST
    Me.Nomen.Value = Me.Combo116.Value
End Sub

it opens the other form (frm_get_info_order)

Private Sub Form_Load()
    PNSN = Me.NSN.Value
    PPN = Me.Part_Num.Value
    PUI = Me.UI.Value
    PWUC = Me.WUC.Value
    PNHA = Me.NHA.Value
    PNHASN = Me.NHASN.Value
    PTO = Me.TO.Value
    PFIG = Me.Fig.Value
    PIND = Me.IND.Value
    PCOST = Me.Text32.Value
End Sub

here is the sub to open the info input form on NotInList (doesn't work)

 Private Sub Combo116_NotInList(NewData As String, Response As Integer)
        DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
        DoCmd.Close acForm
    End Sub

Here are some of the things i tried:
(frm_get_info_order)

Private Sub Form_Load()
    If Me.Item_Name = PNOMEN Then
        PNSN = Me.NSN.Value
        PPN = Me.Part_Num.Value
        PUI = Me.UI.Value
        PWUC = Me.WUC.Value
        PNHA = Me.NHA.Value
        PNHASN = Me.NHASN.Value
        PTO = Me.TO.Value
        PFIG = Me.Fig.Value
        PIND = Me.IND.Value
        PCOST = Me.Text32.Value
    Else
        DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
        MsgBox "Item not loaded. Input new item now", vbOKOnly
end if
 
End Sub

(named PNOMEN in frm_AFREP_order)

Another attempt:

Private Sub Form_Load()
    If Me.Item_Name = Null Then
        DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
        MsgBox "Item not loaded. Input new item now", vbOKOnly
    Else
        PNSN = Me.NSN.Value
        PPN = Me.Part_Num.Value
        PUI = Me.UI.Value
        PWUC = Me.WUC.Value
        PNHA = Me.NHA.Value
        PNHASN = Me.NHASN.Value
        PTO = Me.TO.Value
        PFIG = Me.Fig.Value
        PIND = Me.IND.Value
        PCOST = Me.Text32.Value
    End If
 
End Sub

i also replaced "null" with "" just in case, and i used an elseif statement as well- just in case. If i am missing something, please let me know.
If you could, pease help me figure out how to make this work. All help is very much appreciated.

 

[Mihail]

Take a look to this thread.

 

[kasceus]

sadly, i have, but it only answers the notinlist part- which is ignored completely because of the afterupdate part of the combo box.

 

[Glue]

I'm quite the rookie, but have you tried: If Not IsNull(Me.Item_Name)
Also, I've had a lot of success runningRequery to update comboboxes... Me.Item_Name.Requery

Someday, I hope to be more helpful, but for now....

 

[kasceus]

I'm quite the rookie, but have you tried: If Not IsNull(Me.Item_Name)
Also, I've had a lot of success runningRequery to update comboboxes... Me.Item_Name.Requery

Someday, I hope to be more helpful, but for now....

Well, i hadn't tried that yet. However, once i did. it didn't work. With the statement like this:

Private Sub Form_Load()
    If Not IsNull(Me.Item_Name.Value) Then
        'set all variables to be used in frm_AFREP_Order
        PNSN = Me.NSN.Value
        PPN = Me.Part_Num.Value
        PUI = Me.UI.Value
        PWUC = Me.WUC.Value
        PNHA = Me.NHA.Value
        PNHASN = Me.NHASN.Value
        PTO = Me.TO.Value
        PFIG = Me.Fig.Value
        PIND = Me.IND.Value
        PCOST = Me.Text32.Value
      Else
        DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
        MsgBox "Item not in atabase. Please Input information now", vbOKOnly, "Input Item"
 
    End If
 
End Sub

I still get the statement "You entered an expression that has no value"
and like this:

Private Sub Form_Load()
    If Not IsNull(Me.Item_Name.Value) Then
        DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
        MsgBox "Item not in atabase. Please Input information now", vbOKOnly, "Input Item"
    Else
        'set all variables to be used in frm_AFREP_Order
        PNSN = Me.NSN.Value
        PPN = Me.Part_Num.Value
        PUI = Me.UI.Value
        PWUC = Me.WUC.Value
        PNHA = Me.NHA.Value
        PNHASN = Me.NHASN.Value
        PTO = Me.TO.Value
        PFIG = Me.Fig.Value
        PIND = Me.IND.Value
        PCOST = Me.Text32.Value
    End If
 
End Sub

It just takes me to the item input form no matter if the item is there or not. :banghead:

 

[Mihail]

sadly, i have, but it only answers the notinlist part- which is ignored completely because of the afterupdate part of the combo box.

The After Update event occur after the Not In List event.
So, should be no problem here.

 

[kasceus]

The After Update event occur after the Not In List event.
So, should be no problem here.

actually, the afterupdate part (albeit later in the code) takes precidence over notinlist.

 

[Mihail]

Private Sub Combo116_NotInList(NewData As String, Response As Integer)
  DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
  DoCmd.Close acForm
End Sub

Help yourself by returning to the link I give you and by reading the entire thread .

And NO.
The After Update is NOT before Not In List. It is AFTER.

 

[kasceus]

Help yourself by returning to the link I give you and by reading the entire thread .

And NO.
The After Update is NOT before Not In List. It is AFTER.

ok, so apparently, it wasn't obvious, but i tried that (moving the code around).
however i gave up on trying to do it the "proper" way. and i have decided to do it my way. i remedied this by doing what i wanted to do with NotInList by using On Error
code as follows:

Private Sub Form_load()
On Error GoTo err_prompt
  If Me.Item_Name = IsNull(Me.Item_Name) Then
 
     Else
        'set all variables to be used in frm_AFREP_Order
        PNSN = Me.NSN.Value
        PPN = Me.Part_Num.Value
        PUI = Me.UI.Value
        PWUC = Me.WUC.Value
        PNHA = Me.NHA.Value
        PNHASN = Me.NHASN.Value
        PTO = Me.TO.Value
        PFIG = Me.Fig.Value
        PIND = Me.IND.Value
        PCOST = Me.Text32.Value
    End If
    DoCmd.Close acForm, "frm_get_info_order", acSaveYes
    Exit Sub
 
 
err_prompt:
    DoCmd.OpenForm "frm_new_item_order", acNormal, , , , acWindowNormal
    MsgBox "Item not in database. Please Input information now", vbOKOnly, "Input Item"
End Sub

i didn't put anything in the "If" part because it would error out before it gets there anyway. It does in fact work for the items that are in the list without creating an error.. which is what i wanted anyway.


... spent too much time on this i think.