On Dbl Click 1

[equestrian]

I have frmCustomerLookup with 2 combo boxes and a subform. One combo box allows the user to select a customer. The other combo box allows the user to select a part number. The subform then shows all invoice lines where the selected customer has bought the selected part. This all works fine.

Sometimes a part may have more than one possible part number (maybe 2 vendors use different number). We call these cross references. Another possibility is there may be 2 totally different part numbers that can be called the same thing.

Here are 2 examples:

Our part number: AGEAR Cross Reference: 30412

Our part number: HOUSING Cross Reference: 123
Our part number: CASE Cross Reference: 123

So, here is what I am trying to accomplish. User types 123 in combo box (cboPartNumber) and another form (frmXref) is opened On Not In List. This form has a text box (123 is filled in automatically) and a subform (frmXref_sub) that lists all cross references for 123. The user double clicks on the appropriate part number and the form closes and fills cboPartNumber with the part number selected in frmXref_sub.

Everthing works except I cannot figure out how to get the value to be filled in on cboPartNumber. I am attaching a copy of db if this would make it more clear. Does anyone have any suggestions?

 

[Remou]

The lookup form:

Private Sub cboPartNumber_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

    CR = Chr$(13)
    
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not a part in the database." & CR & CR
    Msg = Msg & "Do you want to check cross references?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm "frmXRef", , , , acFormReadOnly, acDialog, NewData
        Result = Forms!frmXRef.Tag
    End If
    
    If IsNull(Result) Then
        Response = acDataErrContinue
        MsgBox "Please try again!"
    Else
        Me.cboPartNumber.Undo
        Me.cboPartNumber = Result
        Response = acDataErrContinue
    End If
    DoCmd.Close acForm, "frmXRef"
End Sub

frmXRef_sub

Private Sub Form_Current()
    Me.Parent.Tag = Me.InvtNum_pk
End Sub

frmXRef

Private Sub cmdClose_Click()
    Me.Visible = False
End Sub

 

[equestrian]

Remou,
I tried the code you provided. I am getting the following error after closing frmXref:

RunTime Error 2450

Microsoft Access can't find the form 'frmXref' referred to in a macro expression or Visual Basic Code

I click on debug and

Result = Forms!frmXRef.Tag

is highlited.

Do you have any suggestions?

 

[Remou]

The form should not be closed, it should be hidden util the tag is read.

frmXRef

Private Sub cmdClose_Click()
    Me.Visible = False
End Sub

 

[Remou]

I did not make that clear. Switch the X close off and add a command button that says, say, Close but has the code shown above.

 

[equestrian]

Thank you. That took care of the problem. I should have realized that I needed to create a button and disable the x since the form is later closed by the lookup form.

One more problem if you don't mind. After frmXRef is closed (using the button) the cboPartNumber shows the value that was selected in the frmXRef. However, I get the following message:

The text you entered isn't an item in the list. Select an item from the list or enter text that matches one of the listed items.

I click on OK. The combo box drops down and I can select an item. However, the subform is not filtered by the item that I select.

I suspect I have two things happening. I believe the message is the standard access message. So, is it still looking at what was originally typed in? The other is that the AfterUpdate event must not be triggering.

 

[PHV]

the message is the standard access message

...
        Me.cboPartNumber = Result
        Response = [!]acDataErrAdded[/!]
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[equestrian]

This is the code that I have:

Private Sub cboPartNumber_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String

    CR = Chr$(13)
    
    If NewData = "" Then Exit Sub
    Msg = "'" & NewData & "' is not a part in the database." & CR & CR
    Msg = Msg & "Do you want to check cross references?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
        DoCmd.OpenForm "frmXRef", , , , acFormReadOnly, acDialog, NewData
        Result = Forms!frmXRef.Tag
    End If
    
    If IsNull(Result) Then
        Response = acDataErrContinue
        MsgBox "Please try again!"
    Else
        Me.cboPartNumber.Undo [COLOR=Red]
        Me.cboPartNumber = Result
        Response = acDataErrAdded [/color]
    End If
    DoCmd.Close acForm, "frmXRef"
End Sub

So, it is making cboPartNumber = Result, but still giving the message.

 

[Remou]

When I tested, I used

Response = acDataErrContinue

Rather than acDataErrAdded, because data is not being added, it is simply found.

 

[equestrian]

Thank you Remou. This works great. It was not filtering the data when it got back to frmCustomerLookup, but I added a call to the filter sub. That took care of the problem.

Thank you again!!!